using System;
using System.Data;
using System.Data.SqlClient;
namespace DataProxy
{
///
1<summary>
2/// 说明: 本类主要实现对数据库的操作(查询|SP)
3/// 建立者: 黄宗银
4/// 建立时间: 2004-12-4
5/// </summary>
public class DataProxy
{
#region 读取数据
///
1<summary>
2/// 从数据库查询数据
3/// </summary>
///
1<param name="RowsCount"/>
返回最 Top 的记录数
///
1<param name="Columns"/>
查询列名
///
1<param name="Target"/>
查询目标
///
1<param name="Condition"/>
查询条件
///
1<param name="OrderBy"/>
排序
///
1<param name="Connect"/>
连接字符串
///
1<param name="Ex"/>
异常信息
///
1<returns>查询结果</returns>
public static DataSet GetDBData( int RowsCount, string Columns, string Target, string Condition, string OrderBy, string Connect, ref string Ex )
{
Ex = string.Empty;
SqlConnection sqlConnection = new SqlConnection( Connect );
try
{
string[] arrColumns = Columns.Split( ',' );
string strQuery = "SELECT Top " + RowsCount + " " + ((arrColumns[0].Trim() == "*") ? " *" : " [" + arrColumns[0].Trim() + "]");
for( int i = 1; i < arrColumns.Length; i++ )
{
strQuery += ", [" + arrColumns[i].Trim() + "]";
}
strQuery += " FROM [" + Target + "]";
if( Condition != null && Condition.Trim() != string.Empty )
{
strQuery += " WHERE " + Condition;
}
if( OrderBy != null && OrderBy.Trim() != string.Empty )
{
strQuery += " ORDER BY " + OrderBy;
}
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter( strQuery, sqlConnection );
DataSet ds = new DataSet();
sqlDataAdapter.Fill( ds );
sqlConnection.Close();
return ds;
}
catch( SqlException ex )
{
sqlConnection.Close();
Ex = ex.Message;
return null;
}
}
///
1<summary>
2/// 从数据库查询数据
3/// </summary>
///
1<param name="Columns"/>
查询列名
///
1<param name="Target"/>
查询目标
///
1<param name="Condition"/>
查询条件
///
1<param name="OrderBy"/>
排序
///
1<param name="Connect"/>
连接字符串
///
1<param name="Ex"/>
异常信息
///
1<returns>查询结果</returns>
public static DataSet GetDBData( string Columns, string Target, string Condition, string OrderBy, string Connect, ref string Ex )
{
Ex = string.Empty;
SqlConnection sqlConnection = new SqlConnection( Connect );
try
{
string[] arrColumns = Columns.Split( ',' );
string strQuery = "SELECT";
int nStart = 0;
if( arrColumns[0].Trim() == "*" )
{
strQuery += " *,";
nStart = 1;
}
for( int i = nStart; i < arrColumns.Length; i++ )
{
string[] arrColumn = arrColumns[i].Trim().Split( ' ' );
strQuery += " [" + arrColumn[0].Trim() + "]";
if( arrColumn.Length > 1 )
{
strQuery += " [" + arrColumn[1].Trim() + "]";
}
strQuery += ",";
}
strQuery = strQuery.Substring( 0, strQuery.Length - 1 ) + " FROM [" + Target + "]";
if( Condition != null && Condition.Trim() != string.Empty )
{
strQuery += " WHERE " + Condition;
}
if( OrderBy != null && OrderBy.Trim() != string.Empty )
{
strQuery += " ORDER BY " + OrderBy;
}
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter( strQuery, sqlConnection );
DataSet ds = new DataSet();
sqlDataAdapter.Fill( ds );
sqlConnection.Close();
return ds;
}
catch( SqlException ex )
{
sqlConnection.Close();
Ex = ex.Message;
return null;
}
}
///
1<summary>
2/// 从数据库查询数据
3/// </summary>
///
1<param name="Columns"/>
查询列名
///
1<param name="Target"/>
查询目标
///
1<param name="Condition"/>
查询条件
///
1<param name="Connect"/>
连接字符串
///
1<param name="Ex"/>
异常信息
///
1<returns>查询结果</returns>
public static DataSet GetDBData( string Columns, string Target, string Condition, string Connect, ref string Ex )
{
Ex = string.Empty;
DataSet ds = GetDBData( Columns, Target, Condition, null, Connect, ref Ex );
if( Ex != string.Empty )
{
return null;
}
return ds;
}
///
1<summary>
2/// 从数据库查询数据
3/// </summary>
///
1<param name="Columns"/>
查询列名
///
1<param name="Target"/>
查询目标
///
1<param name="PKColumn"/>
主键列名
///
1<param name="PKValue"/>
主键值
///
1<param name="Q"/>
是否加引号
///
1<param name="Connect"/>
连接字符串
///
1<param name="Ex"/>
异常信息
///
1<returns>返回查询结果</returns>
public static DataTable GetDBData( string Columns, string Target, string PKColumn, string PKValue, bool Q, string Connect, ref string Ex )
{
Ex = string.Empty;
string strCondition = null;
if( PKColumn != null )
{
strCondition = "[" + PKColumn + "] = ";
if( Q )
{
strCondition += "'" + PKValue + "'";
}
else
{
strCondition += PKValue;
}
}
DataSet ds = GetDBData( Columns, Target, strCondition, Connect, ref Ex );
if( Ex != string.Empty )
{
return null;
}
return ds.Tables[0];
}
#endregion
#region 执行存储过程
///
1<summary>
2/// 执行某个存储过程通过参数返回值
3/// </summary>
///
1<param name="P"/>
存储过程名
///
1<param name="xsd"/>
数据集对象
///
1<param name="Connect"/>
连接字符串
///
1<param name="Ex"/>
异常信息
///
1<returns>执行结果</returns>
public static void ExecParam( string P, DataSet xsd, string Connect, ref string Ex )
{
SqlConnection sqlConnection = new SqlConnection( Connect );
try
{
SqlCommand sqlCommand = new SqlCommand( P, sqlConnection );
sqlCommand.CommandType = CommandType.StoredProcedure;
if( xsd.Tables["IN"] != null )
{
foreach( DataColumn dc in xsd.Tables["IN"].Columns )
{
sqlCommand.Parameters.Add( "@" + dc.ColumnName, dc.DataType );
sqlCommand.Parameters["@" + dc.ColumnName].Value = dc.Table.Rows[0][dc.ColumnName];
}
}
if( xsd.Tables["OUT"] != null )
{
foreach( DataColumn dc in xsd.Tables["OUT"].Columns )
{
SqlParameter sqlParameter = new SqlParameter( "@" + dc.ColumnName, dc.DataType );
sqlParameter.Direction = ParameterDirection.InputOutput;
sqlParameter.Value = System.DBNull.Value;
if( xsd.Tables["OUT"].Rows.Count > 0 )
{
sqlParameter.Value = dc.Table.Rows[0][dc.ColumnName];
}
sqlCommand.Parameters.Add( sqlParameter );
}
}
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
if( xsd.Tables["OUT"] != null )
{
DataRow dr = xsd.Tables["OUT"].NewRow();
xsd.Tables["OUT"].Rows.InsertAt( dr, 0 );
for( int i = 0; i < xsd.Tables["OUT"].Columns.Count; i++ )
{
string strColumnName = xsd.Tables["OUT"].Columns[i].ColumnName;
xsd.Tables["OUT"].Rows[0][i] = sqlCommand.Parameters["@" + strColumnName].Value;
}
}
}
catch( SqlException ex )
{
Ex = ex.Message;
}
finally
{
sqlConnection.Close();
}
}
#endregion
#region 四种基本语句
///
1<summary>
2/// 执行SELECT
3/// </summary>
///
1<param name="Text"/>
SELECT后的文本
///
1<param name="ds"/>
输出查询结果
///
1<param name="srcTable"/>
用于表映射的源表的名称
///
1<param name="Connect"/>
连接字符串
///
1<param name="Ex"/>
异常信息
///
1<returns>返回行数</returns>
public static int Select( string Text, ref DataSet ds, string srcTable, string Connect, ref string Ex )
{
return Fill( "SELECT " + Text, ref ds, srcTable, Connect, ref Ex );
}
public static int Select( string Text, ref DataSet ds, string Connect, ref string Ex )
{
return Fill( "SELECT " + Text, ref ds, Connect, ref Ex );
}
///
1<summary>
2/// 执行一串SQL语句
3/// </summary>
///
1<param name="Text"/>
SQL文本
///
1<param name="ds"/>
输出查询结果
///
1<param name="srcTable"/>
用于表映射的源表的名称
///
1<param name="Connect"/>
连接字符串
///
1<param name="Ex"/>
异常信息
///
1<returns>返回行数</returns>
public static int Fill( string Text, ref DataSet ds, string srcTable, string Connect, ref string Ex )
{
Ex = string.Empty;
int nFill = 0;
try
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter( Text, Connect );
nFill = sqlDataAdapter.Fill( ds, srcTable );
return nFill;
}
catch( SqlException ex )
{
Ex = ex.Message;
return nFill;
}
catch( Exception ex )
{
throw ex;
}
}
public static int Fill( string Text, ref DataSet ds, string Connect, ref string Ex )
{
return Fill( Text, ref ds, "Table", Connect, ref Ex );
}
///
1<summary>
2/// 执行INSERT
3/// </summary>
///
1<param name="Text"/>
INSERT后的文本
///
1<param name="Connect"/>
连接字符串
///
1<param name="Ex"/>
异常信息
///
1<returns>返回行数</returns>
public static int Insert( string Text, string Connect, ref string Ex )
{
return ExecuteNonQuery( "INSERT " + Text, Connect, ref Ex );
}
///
1<summary>
2/// 执行UPDATE
3/// </summary>
///
1<param name="Text"/>
UPDATE后的文本
///
1<param name="Connect"/>
连接字符串
///
1<param name="Ex"/>
异常信息
///
1<returns>返回行数</returns>
public static int Update( string Text, string Connect, ref string Ex )
{
return ExecuteNonQuery( "UPDATE " + Text, Connect, ref Ex );
}
///
1<summary>
2/// 执行DELETE
3/// </summary>
///
1<param name="Text"/>
DELETE后的文本
///
1<param name="Connect"/>
连接字符串
///
1<param name="Ex"/>
异常信息
///
1<returns>返回行数</returns>
public static int Delete( string Text, string Connect, ref string Ex )
{
return ExecuteNonQuery( "DELETE " + Text, Connect, ref Ex );
}
///
1<summary>
2/// 执行某SQL语句(非SELECT子句)
3/// </summary>
///
1<param name="Text"/>
SQL文本
///
1<param name="Connect"/>
连接字符串
///
1<param name="Ex"/>
异常信息
///
1<returns>返回行数</returns>
public static int ExecuteNonQuery( string Text, string Connect, ref string Ex )
{
int nFill = 0;
Ex = string.Empty;
SqlConnection sqlConnection = new SqlConnection( Connect );
try
{
SqlCommand sqlCommand = new SqlCommand( Text, sqlConnection );
sqlConnection.Open();
nFill = sqlCommand.ExecuteNonQuery();
return nFill;
}
catch( SqlException ex )
{
sqlConnection.Close();
Ex = ex.Message;
return nFill;
}
catch( Exception ex )
{
sqlConnection.Close();
throw ex;
}
finally
{
sqlConnection.Close();
}
}
#endregion
}
}