下面給出了一個(gè)C#操作MS SQL Server 數(shù)據(jù)庫的通用類，通過該類可以對(duì)數(shù)據(jù)庫進(jìn)行任何操作，包括執(zhí)行SQL語句、執(zhí)行存儲(chǔ)過程。以下是其詳細(xì)實(shí)現(xiàn)過程，希望大家共同修改優(yōu)化之。稍后將介紹如何使用它實(shí)現(xiàn)N層的程序設(shè)計(jì)。

配置web.config文件的鏈接參數(shù)

<appSettings>
    <!--
  connStr參數(shù)設(shè)置，事例說明：
  （1）Sql server數(shù)據(jù)庫，例如“server=local;database=test;uid=sa;pwd=;”
  （2）Access數(shù)據(jù)庫，例如“data\ex.mdb; user id='admin';Jet OLEDB:database password='admin';”
 -->
    <add key="connStr" value="server=127.0.0.1;database=DbName;uid=sa;pwd=;" />
  </appSettings>

C#代碼

using System;
using System.Data;
using System.Data.SqlClient;

namespace Com.LXJ.Database
{
 /// <summary>
 /// ConnDB 的摘要說明。
 /// </summary>
 public class ConnDB
 {
  protected SqlConnection Connection;
  private string connectionString;

  /// <summary>
  /// 默認(rèn)構(gòu)造函數(shù)
  /// </summary>
  public ConnDB()
  {
   string connStr;
   connStr = System.Configuration.ConfigurationSettings.AppSettings["connStr"].ToString();

   connectionString = connStr;
   Connection = new SqlConnection(connectionString);
  }

  /// <summary>
  /// 帶參數(shù)的構(gòu)造函數(shù)
  /// </summary>
  /// <param name="newConnectionString">數(shù)據(jù)庫聯(lián)接字符串</param>
  public ConnDB(string newConnectionString)
  {
   connectionString = newConnectionString;
   Connection = new SqlConnection(connectionString);
  }

  /// <summary>
  /// 完成SqlCommand對(duì)象的實(shí)例化
  /// </summary>
  /// <param name="storedProcName"></param>
  /// <param name="parameters"></param>
  /// <returns></returns>
  private SqlCommand BuildCommand(string storedProcName,IDataParameter[] parameters)
  {
   SqlCommand command = BuildQueryCommand(storedProcName,parameters);
   command.Parameters.Add(new SqlParameter("ReturnValue",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));
   return command;
  }

  /// <summary>
  /// 創(chuàng)建新的SQL命令對(duì)象(存儲(chǔ)過程)
  /// </summary>
  /// <param name="storedProcName"></param>
  /// <param name="parameters"></param>
  /// <returns></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>
  /// 執(zhí)行存儲(chǔ)過程,無返回值
  /// </summary>
  /// <param name="storedProcName"></param>
  /// <param name="parameters"></param>
  public void ExecuteProcedure(string storedProcName,IDataParameter[] parameters)
  {
   Connection.Open();
   SqlCommand command;
   command=BuildQueryCommand(storedProcName,parameters);
   command.ExecuteNonQuery();
   Connection.Close();
  }

  /// <summary>
  /// 執(zhí)行存儲(chǔ)過程，返回執(zhí)行操作影響的行數(shù)目
  /// </summary>
  /// <param name="storedProcName"></param>
  /// <param name="parameters"></param>
  /// <param name="rowsAffected"></param>
  /// <returns></returns>
  public int RunProcedure(string storedProcName,IDataParameter[] parameters,out int rowsAffected)
  {
   int result;
   Connection.Open();
   SqlCommand command = BuildCommand(storedProcName,parameters);
   rowsAffected = command.ExecuteNonQuery();
   result = (int)command.Parameters["ReturnValue"].Value;
   Connection.Close();

   return result;
  }
  

  /// <summary>
  /// 重載RunProcedure把執(zhí)行存儲(chǔ)過程的結(jié)果放在SqlDataReader中
  /// </summary>
  /// <param name="storedProcName"></param>
  /// <param name="parameters"></param>
  /// <returns></returns>
  public SqlDataReader RunProcedure(string storedProcName,IDataParameter[] parameters)
  {
   SqlDataReader returnReader;
   Connection.Open();
   SqlCommand command = BuildQueryCommand(storedProcName,parameters);
   command.CommandType = CommandType.StoredProcedure;
   returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
   return returnReader;
  }

  /// <summary>
  /// 重載RunProcedure把執(zhí)行存儲(chǔ)過程的結(jié)果存儲(chǔ)在DataSet中和表tableName為可選參數(shù)
  /// </summary>
  /// <param name="storedProcName"></param>
  /// <param name="parameters"></param>
  /// <param name="tableName"></param>
  /// <returns></returns>
  public DataSet RunProcedure(string storedProcName,IDataParameter[] parameters,params string[] tableName)
  {
   DataSet dataSet = new DataSet();
   Connection.Open();
   SqlDataAdapter sqlDA = new SqlDataAdapter();
   sqlDA.SelectCommand = BuildQueryCommand(storedProcName,parameters);
   string flag;
   flag = "";
   for(int i=0;i<tableName.Length;i++)
    flag = tableName[i];
   if (flag!="")
    sqlDA.Fill(dataSet,tableName[0]);
   else
    sqlDA.Fill(dataSet);
   Connection.Close();
   return dataSet;
  }

  
  /// <summary>
  /// 執(zhí)行SQL語句，返回?cái)?shù)據(jù)到DataSet中
  /// </summary>
  /// <param name="sql"></param>
  /// <returns></returns>
  public DataSet ReturnDataSet(string sql)
  {
   DataSet dataSet=new DataSet();
   Connection.Open();
   SqlDataAdapter sqlDA=new SqlDataAdapter(sql,Connection);
   sqlDA.Fill(dataSet,"objDataSet");
   Connection.Close();
   return dataSet;
  }
  

  /// <summary>
  /// 執(zhí)行SQL語句，返回 DataReader
  /// </summary>
  /// <param name="sql"></param>
  /// <returns></returns>
  public SqlDataReader ReturnDataReader(String sql)
  {
   Connection.Open();
   SqlCommand command = new SqlCommand(sql,Connection);
   SqlDataReader dataReader = command.ExecuteReader();

   return dataReader;
  }

  /// <summary>
  /// 執(zhí)行SQL語句，返回記錄數(shù)
  /// </summary>
  /// <param name="sql"></param>
  /// <returns></returns>
  public int ReturnRecordCount(string sql)
  {
   int recordCount = 0;

   Connection.Open();
   SqlCommand command = new SqlCommand(sql,Connection);
   SqlDataReader dataReader = command.ExecuteReader();

   while(dataReader.Read())
   {
    recordCount++;
   }
   dataReader.Close();
   Connection.Close();

   return recordCount;
  }

  /// <summary>
  /// 執(zhí)行SQL語句
  /// </summary>
  /// <param name="sql"></param>
  /// <returns></returns>
  public bool EditDatabase(string sql)
  {
   bool successState = false;

   Connection.Open();
   SqlTransaction myTrans = Connection.BeginTransaction();
   SqlCommand command = new SqlCommand(sql,Connection,myTrans);
   try
   {
    command.ExecuteNonQuery();
    myTrans.Commit();
    successState = true;
   }
   catch
   {
    myTrans.Rollback();
   }
   finally
   {
    Connection.Close();
   }

   return successState;
  }

  /// <summary>
  /// 關(guān)閉數(shù)據(jù)庫聯(lián)接
  /// </summary>
  public void Close()
  {
   Connection.Close();
  }

 }//end class
}//end namespace