基于SQLServer的数据层基类C#源码

/*****************************************************

 * 文 件 名：DBObject.cs

 * 功能描述：定义数据层基类。

 * 创建时间：2004-08-11 11：05

 *****************************************************/

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace XD.XLB.JBC.JBInfo.WebModules.Data

{

 /// <summary>

 /// 数据层基类，提供对底层数据的基本操作

 /// </summary>

 public class DBObject

 {  

  private SqlConnection connection;

  #region 构造函数

  /// <summary>

  /// 构造函数，初始化数据连接对象

  /// </summary>

  public DBObject()

  {

   string connectionString = ConfigurationSettings.AppSettings.Get("ConnectionString");//从Web.Config中取得的连接字符串

   connection = new SqlConnection(connectionString);

  }

  /// <summary><table style="font-size:12px">

  /// <tr><td><b>功能描述</b>：构造函数，根据指定的数据连接字符串，初始化数据连接对象</td></tr>

  /// <tr><td><b>创 建 人</b>：夏春涛 </td></tr>

  /// <tr><td><b>创建时间</b>：2005-05-28 </td></tr>

  /// </table></summary>

  /// <param name="newConnectionString">数据连接字符串</param>

  public DBObject( string newConnectionString )

   string connectionString = newConnectionString;

   connection = new SqlConnection( connectionString );

  #endregion

  /// 数据连接对象（只读）

  /// </summary>  

  public SqlConnection Connection

   get

   {

    return connection;

   }

   set

    connection = value;

  }  

  //-----------------------------------------------------------------------------------------

  //以下是从《ASP.Net Web站点高级编程》中Copy的（夏春涛）------------------------------------

  /// 创建一个SqlCommand对象，用于获取存储过程的返回值

  /// <param name="storedProcName">存储过程名称</param>

  /// <param name="parameters">存储过程的参数对象列表（数组）</param>

  /// <returns>SqlCommand对象</returns>

  private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)

   SqlCommand command = BuildQueryCommand( storedProcName, parameters );   

   command.Parameters.Add( new SqlParameter ( "ReturnValue",

    SqlDbType.Int,

    4, /* Size */

    ParameterDirection.ReturnValue,

    false, /* is nullable */

    0, /* byte precision */

    0, /* byte scale */

    string.Empty,

    DataRowVersion.Default,

    null ));

   return command;

  /// 创建一个SqlCommand对象，用于生成SqlDataReader

  private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)

   SqlCommand command = new SqlCommand( storedProcName, connection );

   command.CommandType = CommandType.StoredProcedure;

   foreach (SqlParameter parameter in parameters)

    command.Parameters.Add( parameter );

  /// 运行存储过程，获取影响数，返回存储过程运行结果

  /// <param name="rowsAffected">出参：执行存储过程所影响的记录行数</param>

  /// <returns>存储过程的运行结果</returns>

  public object RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )

   object result;

   if(connection.State.ToString() == "Closed")

    connection.Open();

   SqlCommand command = BuildIntCommand( storedProcName, parameters );

   rowsAffected = command.ExecuteNonQuery();

   result = command.Parameters["ReturnValue"].Value;

   connection.Close();

   return result;

  /// 运行存储过程，返回产生的SqlDataReader对象

  /// <returns>SqlDataReader对象</returns>

  public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )

   SqlDataReader returnReader;

   connection.Open();

   SqlCommand command = BuildQueryCommand( storedProcName, parameters );

   returnReader = command.ExecuteReader();

   //connection.Close();

   return returnReader;

  /// 运行存储过程，创建一个DataSet对象，

  /// 将运行结果存入指定的DataTable中，返回DataSet对象

  /// <param name="tableName">数据表名称</param>

  /// <returns>DataSet对象</returns>

  public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )

   DataSet dataSet = new DataSet();

   SqlDataAdapter sqlDA = new SqlDataAdapter();

   sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );

   sqlDA.Fill( dataSet, tableName );

   return dataSet;

  /// 运行存储过程，将运行结果存入已有DataSet对象的指定表中，无返回值

  /// <param name="dataSet">DataSet对象</param>

  public void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName )

   sqlDA.SelectCommand = BuildIntCommand( storedProcName, parameters );

   connection.Close();   

  //以下是自建的（夏春涛）-------------------------------------------------------------------

  /// 运行与写数据库相关的SQL语句，返回影响行数**********************************************

  /// <param name="sqlString">SQL语句</param>

  /// <returns>影响行数</returns>

  public int ExeNonQuery(string sqlString)

   int RowAffected;

   SqlCommand command = new SqlCommand( sqlString, connection );

   RowAffected = command.ExecuteNonQuery();

   return RowAffected;

  /// 运行SQL语句，返回SqlDataReader对象

  public SqlDataReader ExeSqlString(string sqlString)

  /// 运行SQL语句，返回DataSet对象

  /// <param name="string">SQL语句</param>

  public DataSet ExeSqlString(string sqlString, string tableName )

   if (connection.State.ToString() == "Closed")

                connection.Open();

   sqlDA.SelectCommand = new SqlCommand( sqlString, connection );

  /// 运行SQL语句，将运行结果存入已有DataSet对象的指定表中，无返回值

  public void ExeSqlString(string sqlString, DataSet dataSet, string tableName )

   connection.Close();  

  /// 运行SQL语句，返回查询结果的第一行的第一列，忽略其它行或列

  public object ExeScalar(string sqlString)

   object returnScalar;

   returnScalar = command.ExecuteScalar();

   return returnScalar;

  ~DBObject()

   if(connection.State.ToString() == "Open")

                connection.Close();

   connection.Dispose();

 }

}