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

/// <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();
   }
  }
 }

本文地址：http://com.8s8s.com/it/it42488.htm