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
}
}
