利用数据集实现对数据库的操作(查询|SP)(未测试)

///

1<summary>   
2/// 说明: 本类主要实现对数据库的操作(查询|SP)   
3/// 建立者: 黄宗银   
4/// 建立时间: 2004-12-4   
5/// </summary>

public class DB
{
///

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="strEx"/>

异常信息
///

1<returns>返回查询结果</returns>

public static DataView GetDBData( string Columns, string Target, string PKColumn, string PKValue, bool Q, ref string strEx )
{
string strCondition = "[" + PKColumn + "] = ";
if( Q )
{
strCondition += "'" + PKValue + "'";
}
else
{
strCondition += PKValue;
}

DataSet ds = GetDBData( Columns, Target, strCondition, ref strEx );

if( strEx != string.Empty )
{
return null;
}

return ds.Tables[0].DefaultView;
}

///

1<summary>   
2/// 从数据库查询数据   
3/// </summary>

///

1<param name="Columns"/>

查询列名
///

1<param name="Target"/>

查询目标
///

1<param name="Condition"/>

查询条件
///

1<param name="strEx"/>

异常信息
///

1<returns>查询结果</returns>

public static DataSet GetDBData( string Columns, string Target, string Condition, ref string strEx )
{
strEx = string.Empty;
string strConnect = ConfigurationSettings.AppSettings["ConnectString"];
SqlConnection sqlConnection = new SqlConnection( strConnect );
try
{
string strQuery = "SELECT " + Columns + "FROM [" + Target + "]";

if( Condition.Trim() != string.Empty )
{
strQuery += "WHERE [" + Condition;
}

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter( strQuery, sqlConnection );

DataSet ds = new DataSet();
sqlDataAdapter.Fill( ds );

sqlConnection.Close();
return ds;
}
catch( SqlException ex )
{
sqlConnection.Close();
strEx = ex.Message;
return null;
}
}

///

1<summary>   
2/// 执行某个存储过程   
3/// </summary>

///

1<param name="SP"/>

存储过程名
///

1<param name="xsd"/>

数据集对象
///

1<param name="strEx"/>

异常信息
///

1<returns>执行结果</returns>

public static void ExecSP( string SP, DataSet xsd, ref string strEx )
{
string strConnect = ConfigurationSettings.AppSettings["ConnectString"];
SqlConnection sqlConnection = new SqlConnection( strConnect );

try
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter( SP, sqlConnection );
sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

for( int i = 0; i < xsd.Tables["IN"].Columns.Count; i++ )
{
string strColumnName = xsd.Tables["IN"].Columns[i].ColumnName;
sqlDataAdapter.SelectCommand.Parameters.Add( "@" + strColumnName, (xsd.Tables["IN"].Rows[0])[i] );
}

for( int j = 0; j < xsd.Tables["OUT"].Columns.Count; j++ )
{
string strColumnName = xsd.Tables["OUT"].Columns[j].ColumnName;
sqlDataAdapter.SelectCommand.Parameters.Add( "@" + strColumnName, xsd.Tables["OUT"].Columns[j].DataType );
sqlDataAdapter.SelectCommand.Parameters["@" + strColumnName].Direction = ParameterDirection.Output;
}

sqlDataAdapter.Fill( xsd, "OUT" );
}
catch( SqlException ex )
{
strEx = ex.Message;
}
finally
{
sqlConnection.Close();
}
}
}