using System;
using System.Data;
using System.Data.SqlClient;
namespace DataProxy
{
/// <summary>
/// 说明: 本类主要实现对数据库的操作(查询|SP)
/// 建立者: 黄宗银
/// 建立时间: 2004-12-4
/// </summary>
public class DataProxy
{
#region 读取数据
/// <summary>
/// 从数据库查询数据
/// </summary>
/// <param name='RowsCount'>返回最 Top 的记录数</param>
/// <param name='Columns'>查询列名</param>
/// <param name='Target'>查询目标</param>
/// <param name='Condition'>查询条件</param>
/// <param name='OrderBy'>排序</param>
/// <param name='Connect'>连接字符串</param>
/// <param name='Ex'>异常信息</param>
/// <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;
}
}
/// <summary>
/// 从数据库查询数据
/// </summary>
/// <param name='Columns'>查询列名</param>
/// <param name='Target'>查询目标</param>
/// <param name='Condition'>查询条件</param>
/// <param name='OrderBy'>排序</param>
/// <param name='Connect'>连接字符串</param>
/// <param name='Ex'>异常信息</param>
/// <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;
}
}
/// <summary>
/// 从数据库查询数据
/// </summary>
/// <param name='Columns'>查询列名</param>
/// <param name='Target'>查询目标</param>
/// <param name='Condition'>查询条件</param>
/// <param name='Connect'>连接字符串</param>
/// <param name='Ex'>异常信息</param>
/// <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;
}
/// <summary>
/// 从数据库查询数据
/// </summary>
/// <param name='Columns'>查询列名</param>
/// <param name='Target'>查询目标</param>
/// <param name='PKColumn'>主键列名</param>
/// <param name='PKValue'>主键值</param>
/// <param name='Q'>是否加引号</param>
/// <param name='Connect'>连接字符串</param>
/// <param name='Ex'>异常信息</param>
/// <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 执行存储过程
/// <summary>
/// 执行某个存储过程通过参数返回值
/// </summary>
/// <param name='P'>存储过程名</param>
/// <param name='xsd'>数据集对象</param>
/// <param name='Connect'>连接字符串</param>
/// <param name='Ex'>异常信息</param>
/// <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 四种基本语句
/// <summary>
/// 执行SELECT
/// </summary>
/// <param name='Text'>SELECT后的文本</param>
/// <param name='ds'>输出查询结果</param>
/// <param name='srcTable'>用于表映射的源表的名称</param>
/// <param name='Connect'>连接字符串</param>
/// <param name='Ex'>异常信息</param>
/// <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 );
}
/// <summary>
/// 执行一串SQL语句
/// </summary>
/// <param name='Text'>SQL文本</param>
/// <param name='ds'>输出查询结果</param>
/// <param name='srcTable'>用于表映射的源表的名称</param>
/// <param name='Connect'>连接字符串</param>
/// <param name='Ex'>异常信息</param>
/// <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 );
}
/// <summary>
/// 执行INSERT
/// </summary>
/// <param name='Text'>INSERT后的文本</param>
/// <param name='Connect'>连接字符串</param>
/// <param name='Ex'>异常信息</param>
/// <returns>返回行数</returns>
public static int Insert( string Text, string Connect, ref string Ex )
{
return ExecuteNonQuery( 'INSERT ' + Text, Connect, ref Ex );
}
/// <summary>
/// 执行UPDATE
/// </summary>
/// <param name='Text'>UPDATE后的文本</param>
/// <param name='Connect'>连接字符串</param>
/// <param name='Ex'>异常信息</param>
/// <returns>返回行数</returns>
public static int Update( string Text, string Connect, ref string Ex )
{
return ExecuteNonQuery( 'UPDATE ' + Text, Connect, ref Ex );
}
/// <summary>
/// 执行DELETE
/// </summary>
/// <param name='Text'>DELETE后的文本</param>
/// <param name='Connect'>连接字符串</param>
/// <param name='Ex'>异常信息</param>
/// <returns>返回行数</returns>
public static int Delete( string Text, string Connect, ref string Ex )
{
return ExecuteNonQuery( 'DELETE ' + Text, Connect, ref Ex );
}
/// <summary>
/// 执行某SQL语句(非SELECT子句)
/// </summary>
/// <param name='Text'>SQL文本</param>
/// <param name='Connect'>连接字符串</param>
/// <param name='Ex'>异常信息</param>
/// <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
}
}
