/// <summary> /// 说明: 本类主要实现对数据库的操作(查询|SP) /// 建立者: 黄宗银 /// 建立时间: 2004-12-4 /// </summary> public class DB { /// <summary> /// 从数据库查询数据 /// </summary> /// <param name="Columns">查询列名</param> /// <param name="Target">查询目标</param> /// <param name="PKColumn">主键列名</param> /// <param name="PKValue">主键值</param> /// <param name="Q">是否加引号</param> /// <param name="strEx">异常信息</param> /// <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; }
/// <summary> /// 从数据库查询数据 /// </summary> /// <param name="Columns">查询列名</param> /// <param name="Target">查询目标</param> /// <param name="Condition">查询条件</param> /// <param name="strEx">异常信息</param> /// <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; } }
/// <summary> /// 执行某个存储过程 /// </summary> /// <param name="SP">存储过程名</param> /// <param name="xsd">数据集对象</param> /// <param name="strEx">异常信息</param> /// <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(); } } } 
|