SqlHelper改造版本

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

??? /// <summary>
??? /// SqlHelper類是專門提供給廣大用戶用于高性能、可升級和最佳練習的sql數據操作
??? /// </summary>
??? public abstract class SqlHelper
??? {

?
??????? // 用于緩存參數的HASH表
??????? // public static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
??????? /// <summary>
??????? /// 返回一個數據表(Fill)1
??????? /// </summary>
??????? /// <param name="connectionString">連接字符串</param>
??????? /// <param name="cmdType">類型</param>
??????? /// <param name="cmdText">命令</param>
??????? /// <param name="TableName">DataTable表名</param>
??????? /// <returns>DataTable</returns>
??????? public static DataTable SelectTable(string connectionString, CommandType cmdType, string cmdText,string TableName)
??????? {

??????????? SqlCommand cmd = new SqlCommand();
??????????? SqlDataAdapter dpt = new SqlDataAdapter();
??????????? using (SqlConnection Conn = new SqlConnection(connectionString))
??????????? {
??????????????? cmd.CommandType = cmdType;
??????????????? cmd.CommandText = cmdText;
??????????????? dpt.SelectCommand = cmd;
??????????????? DataSet DsResoult = new DataSet();
??????????????? dpt.Fill(DsResoult, "TempTable");
??????????????? return DsResoult.Tables["TempTable"];
??????????? }
??????? }
?????????? /// <summary>
??????? /// 返回一個數據表(Fill)3
??????? /// </summary>
??????? /// <param name="connectionString">連接字符串</param>
??????? /// <param name="cmdType">類型</param>
??????? /// <param name="cmdText">命令</param>
??????? /// <param name="commandParameters">執行命令所用參數的集合</param>
??????? /// <returns>DataTable</returns>
??????? public static DataTable SelectTable(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
??????? {

??????????? SqlCommand cmd = new SqlCommand();
??????????? SqlDataAdapter dpt = new SqlDataAdapter();
??????????? using (SqlConnection Conn = new SqlConnection(connectionString))
??????????? {
??????????????? PrepareCommand(cmd, Conn, null, cmdType, cmdText, commandParameters);
??????????????? cmd.CommandType = cmdType;
??????????????? cmd.CommandText = cmdText;
??????????????? dpt.SelectCommand = cmd;
??????????????? DataSet DsResoult = new DataSet();
??????????????? dpt.Fill(DsResoult, "TableName");
??????????????? cmd.Parameters.Clear();
??????????????? return DsResoult.Tables["TableName"];
??????????? }
??????? }
??????? /// <summary>
??????? /// 返回一個數據表(Fill)4
??????? /// </summary>
??????? /// <param name="connectionString">連接字符串</param>
??????? /// <param name="cmdType">類型</param>
??????? /// <param name="cmdText">命令</param>
??????? /// <param name="commandParameters">執行命令所用參數的集合</param>
??????? /// <returns>DataTable</returns>
??????? public static DataTable SelectTable(string connectionString, CommandType cmdType, string cmdText, string TableName, params SqlParameter[] commandParameters)
??????? {

??????????? SqlCommand cmd = new SqlCommand();
??????????? SqlDataAdapter dpt = new SqlDataAdapter();
??????????? using (SqlConnection Conn = new SqlConnection(connectionString))
??????????? {
??????????????? PrepareCommand(cmd, Conn, null, cmdType, cmdText, commandParameters);
??????????????? cmd.CommandType = cmdType;
??????????????? cmd.CommandText = cmdText;
??????????????? dpt.SelectCommand = cmd;
??????????????? DataSet DsResoult = new DataSet();
??????????????? dpt.Fill(DsResoult, TableName);
??????????????? cmd.Parameters.Clear();
??????????????? return DsResoult.Tables[TableName];
??????????? }
??????? }
??????? /// <summary>
??????? ///? 給定連接的數據庫用假設參數執行一個sql命令（不返回數據集）
??????? /// </summary>
??????? /// <param name="connectionString">一個有效的連接字符串</param>
??????? /// <param name="commandType">命令類型(存儲過程, 文本, 等等)</param>
??????? /// <param name="commandText">存儲過程名稱或者sql命令語句</param>
??????? /// <param name="commandParameters">執行命令所用參數的集合</param>
??????? /// <returns>執行命令所影響的行數</returns>
??????? public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
??????? {

??????????? SqlCommand cmd = new SqlCommand();

??????????? using (SqlConnection conn = new SqlConnection(connectionString))
??????????? {
??????????????? PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
??????????????? int val = cmd.ExecuteNonQuery();
??????????????? cmd.Parameters.Clear();
??????????????? return val;
??????????? }
??????? }

??????? /// <summary>
??????? /// 用現有的數據庫連接執行一個sql命令（不返回數據集）
??????? /// </summary>
??????? /// <param name="conn">一個現有的數據庫連接</param>
??????? /// <param name="commandType">命令類型(存儲過程, 文本, 等等)</param>
??????? /// <param name="commandText">存儲過程名稱或者sql命令語句</param>
??????? /// <param name="commandParameters">執行命令所用參數的集合</param>
??????? /// <returns>執行命令所影響的行數</returns>
??????? public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
??????? {

??????????? SqlCommand cmd = new SqlCommand();
???
??????????? PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
??????????? int val = cmd.ExecuteNonQuery();
??????????? cmd.Parameters.Clear();
??????????? return val;
??????? }

??????? /// <summary>
??????? ///使用現有的SQL事務執行一個sql命令（不返回數據集）
??????? /// </summary>
??????? /// <remarks>
??????? ///舉例:?
??????? ///? int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
??????? /// </remarks>
??????? /// <param name="trans">一個現有的事務</param>
??????? /// <param name="commandType">命令類型(存儲過程, 文本, 等等)</param>
??????? /// <param name="commandText">存儲過程名稱或者sql命令語句</param>
??????? /// <param name="commandParameters">執行命令所用參數的集合</param>
??????? /// <returns>執行命令所影響的行數</returns>
??????? public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
??????? {
??????????? SqlCommand cmd = new SqlCommand();
??????????? PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
??????????? int val = cmd.ExecuteNonQuery();
??????????? cmd.Parameters.Clear();
??????????? return val;
??????? }

??????? /// <summary>
??????? /// 用執行的數據庫連接執行一個返回數據集的sql命令
??????? /// </summary>
??????? /// <remarks>
??????? /// 舉例:?
??????? ///? SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
??????? /// </remarks>
??????? /// <param name="connectionString">一個有效的連接字符串</param>
??????? /// <param name="commandType">命令類型(存儲過程, 文本, 等等)</param>
??????? /// <param name="commandText">存儲過程名稱或者sql命令語句</param>
??????? /// <param name="commandParameters">執行命令所用參數的集合</param>
??????? /// <returns>包含結果的讀取器</returns>
??????? public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
??????? {
??????????? //創建一個SqlCommand對象
??????????? SqlCommand cmd = new SqlCommand();
??????????? //創建一個SqlConnection對象
??????????? SqlConnection conn = new SqlConnection(connectionString);

??????????? //在這里我們用一個try/catch結構執行sql文本命令/存儲過程，因為如果這個方法產生一個異常我們要關閉連接，因為沒有讀取器存在，
??????????? //因此commandBehaviour.CloseConnection 就不會執行
??????????? try
??????????? {
??????????????? //調用 PrepareCommand 方法，對 SqlCommand 對象設置參數
??????????????? PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
??????????????? //調用 SqlCommand? 的 ExecuteReader 方法
??????????????? SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
??????????????? //清除參數
??????????????? cmd.Parameters.Clear();
??????????????? return reader;
??????????? }
??????????? catch
??????????? {
??????????????? //關閉連接，拋出異常
??????????????? conn.Close();
??????????????? throw;
??????????? }
??????? }

??????? /// <summary>
??????? /// 用指定的數據庫連接字符串執行一個命令并返回一個數據集的第一列
??????? /// </summary>
??????? /// <remarks>
??????? ///例如:?
??????? ///? Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
??????? /// </remarks>
??????? ///<param name="connectionString">一個有效的連接字符串</param>
??????? /// <param name="commandType">命令類型(存儲過程, 文本, 等等)</param>
??????? /// <param name="commandText">存儲過程名稱或者sql命令語句</param>
??????? /// <param name="commandParameters">執行命令所用參數的集合</param>
??????? /// <returns>用 Convert.To{Type}把類型轉換為想要的 </returns>
??????? public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
??????? {
??????????? SqlCommand cmd = new SqlCommand();

??????????? using (SqlConnection connection = new SqlConnection(connectionString))
??????????? {
??????????????? PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
??????????????? object val = cmd.ExecuteScalar();
??????????????? cmd.Parameters.Clear();
??????????????? return val;
??????????? }
??????? }

??????? /// <summary>
??????? /// 用指定的數據庫連接執行一個命令并返回一個數據集的第一列
??????? /// </summary>
??????? /// <remarks>
??????? /// 例如:?
??????? ///? Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
??????? /// </remarks>
??????? /// <param name="conn">一個存在的數據庫連接</param>
??????? /// <param name="commandType">命令類型(存儲過程, 文本, 等等)</param>
??????? /// <param name="commandText">存儲過程名稱或者sql命令語句</param>
??????? /// <param name="commandParameters">執行命令所用參數的集合</param>
??????? /// <returns>用 Convert.To{Type}把類型轉換為想要的 </returns>
??????? public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
??????? {

??????????? SqlCommand cmd = new SqlCommand();

??????????? PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
??????????? object val = cmd.ExecuteScalar();
??????????? cmd.Parameters.Clear();
??????????? return val;
??????? }

??????? /// <summary>
??????? /// 準備執行一個命令
??????? /// </summary>
??????? /// <param name="cmd">sql命令</param>
??????? /// <param name="conn">Sql連接</param>
??????? /// <param name="trans">Sql事務</param>
??????? /// <param name="cmdType">命令類型例如存儲過程或者文本</param>
??????? /// <param name="cmdText">命令文本,例如：Select * from Products</param>
??????? /// <param name="cmdParms">執行命令的參數</param>
??????? public static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
??????? {

??????????? if (conn.State != ConnectionState.Open)
??????????????? conn.Open();

??????????? cmd.Connection = conn;
??????????? cmd.CommandText = cmdText;

??????????? if (trans != null)
??????????????? cmd.Transaction = trans;

??????????? cmd.CommandType = cmdType;

??????????? if (cmdParms != null)
??????????? {
??????????????? foreach (SqlParameter parm in cmdParms)
??????????????????? cmd.Parameters.Add(parm);
??????????? }
??????? }
??? }