基於SQLServer的資料層基類C#源碼

server|sqlserver|資料

/*****************************************************
 * 文 件 名：DBObject.cs
 * 功能描述：定義資料層基類。
 * 創 建 人：夏春濤 xchuntao@163.com qq:23106676
 * 建立時間：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>
  /// 資料連線對象（唯讀）
  /// </summary>  
  public SqlConnection Connection
  {
   get
   {
    return connection;
   }
   set
   {
    connection = value;
   }
  }  

  //-----------------------------------------------------------------------------------------
  //以下是從《ASP.Net Web網站進階編程》中Copy的（夏春濤）------------------------------------
  //-----------------------------------------------------------------------------------------
  /// <summary>
  /// 建立一個SqlCommand對象，用於擷取預存程序的傳回值
  /// </summary>
  /// <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;
  }

  /// <summary>
  /// 建立一個SqlCommand對象，用於產生SqlDataReader
  /// </summary>
  /// <param name="storedProcName">預存程序名稱</param>
  /// <param name="parameters">預存程序的參數對象列表（數組）</param>
  /// <returns>SqlCommand對象</returns>
  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 );
   }

   return command;

  }

  /// <summary>
  /// 運行預存程序，擷取影響數，返回預存程序運行結果
  /// </summary>
  /// <param name="storedProcName">預存程序名稱</param>
  /// <param name="parameters">預存程序的參數對象列表（數組）</param>
  /// <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;
  }

  /// <summary>
  /// 運行預存程序，返回產生的SqlDataReader對象
  /// </summary>
  /// <param name="storedProcName">預存程序名稱</param>
  /// <param name="parameters">預存程序的參數對象列表（數組）</param>
  /// <returns>SqlDataReader對象</returns>
  public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
  {
   SqlDataReader returnReader;

   connection.Open();
   SqlCommand command = BuildQueryCommand( storedProcName, parameters );
   command.CommandType = CommandType.StoredProcedure;

   returnReader = command.ExecuteReader();
   //connection.Close();
   return returnReader;
  }

  /// <summary>
  /// 運行預存程序，建立一個DataSet對象，
  /// 將運行結果存入指定的DataTable中，返回DataSet對象
  /// </summary>
  /// <param name="storedProcName">預存程序名稱</param>
  /// <param name="parameters">預存程序的參數對象列表（數組）</param>
  /// <param name="tableName">資料表名稱</param>
  /// <returns>DataSet對象</returns>
  public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
  {
   DataSet dataSet = new DataSet();
   connection.Open();
   SqlDataAdapter sqlDA = new SqlDataAdapter();
   sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );
   sqlDA.Fill( dataSet, tableName );
   connection.Close();

   return dataSet;
  }

  /// <summary>
  /// 運行預存程序，將運行結果存入已有DataSet對象的指定表中，無傳回值
  /// </summary>  
  /// <param name="storedProcName">預存程序名稱</param>
  /// <param name="parameters">預存程序的參數對象列表（數組）</param>
  /// <param name="dataSet">DataSet對象</param>
  /// <param name="tableName">資料表名稱</param>
  public void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName )
  {
   connection.Open();
   SqlDataAdapter sqlDA = new SqlDataAdapter();
   sqlDA.SelectCommand = BuildIntCommand( storedProcName, parameters );
   sqlDA.Fill( dataSet, tableName );
   connection.Close();   
  }

  //-----------------------------------------------------------------------------------------
  //以下是自建的（夏春濤）-------------------------------------------------------------------
  //-----------------------------------------------------------------------------------------
  /// <summary>
  /// 運行與寫資料庫相關的SQL語句，返回影響行數**********************************************
  /// </summary>  
  /// <param name="sqlString">SQL語句</param>
  /// <returns>影響行數</returns>
  public int ExeNonQuery(string sqlString)
  {
   int RowAffected;
   if(connection.State.ToString() == "Closed")
    connection.Open();
   SqlCommand command = new SqlCommand( sqlString, connection );
   RowAffected = command.ExecuteNonQuery();
   //connection.Close();
 
   return RowAffected;

  }

  /// <summary>
  /// 運行SQL語句，返回SqlDataReader對象
  /// </summary>
  /// <param name="sqlString">SQL語句</param>
  /// <returns>SqlDataReader對象</returns>
  public SqlDataReader ExeSqlString(string sqlString)
  {
   SqlDataReader returnReader;

   if(connection.State.ToString() == "Closed")
    connection.Open();
   SqlCommand command = new SqlCommand( sqlString, connection );
   returnReader = command.ExecuteReader();
   //connection.Close();

   return returnReader;
  }

  /// <summary>
  /// 運行SQL語句，返回DataSet對象
  /// </summary>
  /// <param name="string">SQL語句</param>
  /// <param name="tableName">資料表名稱</param>
  /// <returns>DataSet對象</returns>
  public DataSet ExeSqlString(string sqlString, string tableName )
  {
   DataSet dataSet = new DataSet();
   if (connection.State.ToString() == "Closed")
                connection.Open();
   SqlDataAdapter sqlDA = new SqlDataAdapter();
   sqlDA.SelectCommand = new SqlCommand( sqlString, connection );
   sqlDA.Fill( dataSet, tableName );
   connection.Close();

   return dataSet;
  }

  /// <summary>
  /// 運行SQL語句，將運行結果存入已有DataSet對象的指定表中，無傳回值
  /// </summary>  
  /// <param name="sqlString">SQL語句</param>
  /// <param name="dataSet">DataSet對象</param>
  /// <param name="tableName">資料表名稱</param>
  public void ExeSqlString(string sqlString, DataSet dataSet, string tableName )
  {
   if (connection.State.ToString() == "Closed")
    connection.Open();
   SqlDataAdapter sqlDA = new SqlDataAdapter();
   sqlDA.SelectCommand = new SqlCommand( sqlString, connection );
   sqlDA.Fill( dataSet, tableName );
   connection.Close();  
  }

  
  /// <summary>
  /// 運行SQL語句，返回查詢結果的第一行的第一列，忽略其它行或列
  /// </summary>  
  /// <param name="sqlString">SQL語句</param>
  /// <returns>影響行數</returns>
  public object ExeScalar(string sqlString)
  {
   object returnScalar;
   if (connection.State.ToString() == "Closed")
    connection.Open();
   SqlCommand command = new SqlCommand( sqlString, connection );
   returnScalar = command.ExecuteScalar();
   //connection.Close();
   
   return returnScalar;
  }
  
  ~DBObject()
  {
   if(connection.State.ToString() == "Open")
                connection.Close();
   connection.Dispose();
  }

 }
}