C# SqlHelper資料庫的通用存取碼

using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Collections;using System.Data.SqlClient;/// <summary>/// 資料庫的通用存取碼/// 此類為抽象類別，不允許執行個體化，在應用時直接調用即可/// </summary>public abstract class SqlHelper{    //擷取資料庫連接字串，其屬於靜態變數且唯讀，項目中所有文檔可以直接使用，但不能修改    public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["pubsConnectionString"].ConnectionString;    // 雜湊表用來儲存緩衝的參數資訊，雜湊表可以儲存任意類型的參數。    private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());    /// <summary>    ///執行一個不需要傳回值的SqlCommand命令，通過指定專用的連接字串。    /// 使用參數數組形式提供參數列表     /// </summary>    /// <remarks>    /// 使用樣本：    ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));    /// </remarks>    /// <param name="connectionString">一個有效資料庫連接字串</param>    /// <param name="commandType">SqlCommand命令類型 (預存程序， T-SQL語句， 等等。)</param>    /// <param name="commandText">預存程序的名字或者 T-SQL 陳述式</param>    /// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>    /// <returns>返回一個數值表示此SqlCommand命令執行後影響的行數</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方法將參數逐個加入到SqlCommand的參數集合中            PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);            int val = cmd.ExecuteNonQuery();            //清空SqlCommand中的參數列表            cmd.Parameters.Clear();            return val;        }    }        /// <summary>    ///執行一條不返回結果的SqlCommand，通過一個已經存在的資料庫連接     /// 使用參數數組提供參數    /// </summary>    /// <remarks>    /// 使用樣本：      ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));    /// </remarks>    /// <param name="conn">一個現有的資料庫連接</param>    /// <param name="commandType">SqlCommand命令類型 (預存程序， T-SQL語句， 等等。)</param>    /// <param name="commandText">預存程序的名字或者 T-SQL 陳述式</param>    /// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>    /// <returns>返回一個數值表示此SqlCommand命令執行後影響的行數</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>    /// 執行一條不返回結果的SqlCommand，通過一個已經存在的資料庫事物處理     /// 使用參數數組提供參數    /// </summary>    /// <remarks>    /// 使用樣本：     ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));    /// </remarks>    /// <param name="trans">一個存在的 sql 事物處理</param>    /// <param name="commandType">SqlCommand命令類型 (預存程序， T-SQL語句， 等等。)</param>    /// <param name="commandText">預存程序的名字或者 T-SQL 陳述式</param>    /// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>    /// <returns>返回一個數值表示此SqlCommand命令執行後影響的行數</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>    /// 執行一條返回結果集的SqlCommand命令，通過專用的連接字串。    /// 使用參數數組提供參數    /// </summary>    /// <remarks>    /// 使用樣本：      ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));    /// </remarks>    /// <param name="connectionString">一個有效資料庫連接字串</param>    /// <param name="commandType">SqlCommand命令類型 (預存程序， T-SQL語句， 等等。)</param>    /// <param name="commandText">預存程序的名字或者 T-SQL 陳述式</param>    /// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>    /// <returns>返回一個包含結果的SqlDataReader</returns>    public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)    {        SqlCommand cmd = new SqlCommand();        SqlConnection conn = new SqlConnection(connectionString);        // 在這裡使用try/catch處理是因為如果方法出現異常，則SqlDataReader就不存在，        //CommandBehavior.CloseConnection的語句就不會執行，觸發的異常由catch捕獲。        //關閉資料庫連接，並通過throw再次引發捕捉到的異常。        try        {            PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);            cmd.Parameters.Clear();            return rdr;        }        catch        {            conn.Close();            throw;        }    }    /// <summary>    /// 執行一條返回第一條記錄第一列的SqlCommand命令，通過專用的連接字串。     /// 使用參數數組提供參數    /// </summary>    /// <remarks>    /// 使用樣本：      ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));    /// </remarks>    /// <param name="connectionString">一個有效資料庫連接字串</param>    /// <param name="commandType">SqlCommand命令類型 (預存程序， T-SQL語句， 等等。)</param>    /// <param name="commandText">預存程序的名字或者 T-SQL 陳述式</param>    /// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>    /// <returns>返回一個object類型的資料，可以通過 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>    /// 執行一條返回第一條記錄第一列的SqlCommand命令，通過已經存在的資料庫連接。    /// 使用參數數組提供參數    /// </summary>    /// <remarks>    /// 使用樣本：     ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));    /// </remarks>    /// <param name="conn">一個已經存在的資料庫連接</param>    /// <param name="commandType">SqlCommand命令類型 (預存程序， T-SQL語句， 等等。)</param>    /// <param name="commandText">預存程序的名字或者 T-SQL 陳述式</param>    /// <param name="commandParameters">以數組形式提供SqlCommand命令中用到的參數列表</param>    /// <returns>返回一個object類型的資料，可以通過 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="cacheKey">參數緩衝的索引值</param>    /// <param name="cmdParms">被緩衝的參數列表</param>    public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)    {        parmCache[cacheKey] = commandParameters;    }    /// <summary>    /// 擷取被緩衝的參數    /// </summary>    /// <param name="cacheKey">用於尋找參數的KEY值</param>    /// <returns>返回緩衝的參數數組</returns>    public static SqlParameter[] GetCachedParameters(string cacheKey)    {        SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];        if (cachedParms == null)            return null;        //建立一個參數的複製列表        SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];        //通過迴圈為複製參數列表賦值        for (int i = 0, j = cachedParms.Length; i < j; i++)            //使用clone方法複製參數列表中的參數            clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();        return clonedParms;    }    /// <summary>    /// 為執行命令準備參數    /// </summary>    /// <param name="cmd">SqlCommand 命令</param>    /// <param name="conn">已經存在的資料庫連接</param>    /// <param name="trans">資料庫事物處理</param>    /// <param name="cmdType">SqlCommand命令類型 (預存程序， T-SQL語句， 等等。)</param>    /// <param name="cmdText">Command text，T-SQL語句 例如 Select * from Products</param>    /// <param name="cmdParms">返回帶參數的命令</param>    private 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);        }    }    /// <summary>    /// 執行查詢，返回結果集中的第一行第一列的值，忽略其他行列    /// </summary>    /// <param name="sql"></param>    /// <returns></returns>    public static object ExcuteScalar(string sql)    {        using (SqlConnection con = new SqlConnection(ConnectionStringLocalTransaction))        {            con.Open();            SqlCommand cmd = new SqlCommand(sql, con);            con.Close();            return cmd.ExecuteScalar();                   }    }    /// <summary>    /// 執行查詢    /// </summary>    /// <param name="sql">有效sql語句</param>    /// <param name="param">返回DataReader</param>    /// <returns>返回DataReader</returns>    public static SqlDataReader ExcuteReader(string sql, SqlParameter[] param)    {        SqlConnection con = new SqlConnection(ConnectionStringLocalTransaction);        con.Open();        SqlCommand cmd = new SqlCommand(sql, con);        cmd.Parameters.AddRange(param);        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);        cmd.Parameters.Clear();        return reader;    }    /// <summary>    /// 執行查詢    /// </summary>    /// <param name="sql">有效sql語句</param>    /// <returns>返回DataReader</returns>    public static SqlDataReader ExcuteReader(string sql)    {        SqlConnection con = new SqlConnection(ConnectionStringLocalTransaction);        con.Open();        SqlCommand cmd = new SqlCommand(sql, con);        return cmd.ExecuteReader(CommandBehavior.CloseConnection);    }    /// <summary>    /// 執行查詢的基方法    /// </summary>    /// <param name="sql">有效sql語句</param>    /// <returns>返回DataTable</returns>    public static DataTable ExcuteDataQuery(string sql)    {        using (SqlConnection con = new SqlConnection(ConnectionStringLocalTransaction))        {            con.Open();            SqlDataAdapter sda = new SqlDataAdapter(sql, con);            DataTable table = new DataTable();            sda.Fill(table);            con.Close();            return table;        }    }    /// <summary>    /// 執行增，刪，改的基方法    /// </summary>    /// <param name="sql">有效sql語句</param>    /// <param name="param">參數集合</param>    /// <returns>影響的行數</returns>    public static int ExcuteNonQuery(string sql, SqlParameter[] param)    {        using (SqlConnection con = new SqlConnection(ConnectionStringLocalTransaction))        {            con.Open();            SqlCommand cmd = new SqlCommand(sql, con);            if (param != null)            {                cmd.Parameters.AddRange(param);            }            int count = cmd.ExecuteNonQuery();            cmd.Parameters.Clear();            con.Close();            return count;        }    }    //每頁顯示5條資料    static int pageSize = 10;    /// <summary>    /// 查詢共有多少行，然後直接返回總頁碼    /// </summary>    /// <returns></returns>    public static int GetAllBookCount()    {        int num = 0;        int pageCount = 0;        string sql = "select count(0) from TB_BookInfo";        num = Convert.ToInt32(ExcuteScalar(sql));        pageCount = num % pageSize != 0 ? (num / pageSize) + 1 : num / pageSize;        return pageCount;    }    /// <summary>    /// 準備命令    /// </summary>    /// <param name="con"></param>    /// <param name="cmd"></param>    /// <param name="textcmd"></param>    /// <param name="cmdType"></param>    /// <param name="param"></param>    public static void PreparedCommd(SqlConnection con, SqlCommand cmd, string textcmd, CommandType cmdType, SqlParameter[] param)    {        try        {            if (con.State != ConnectionState.Open)            {                con.Open();            }            cmd.Connection = con;            cmd.CommandText = textcmd;            cmd.CommandType = cmdType;            if (param != null)            {                foreach (SqlParameter p in param)                {                    cmd.Parameters.Add(p);                }            }        }        catch (Exception ex)        {            throw new Exception(ex.Message);        }    }    /// <summary>    /// 執行增、刪、改    /// </summary>    /// <param name="textcmd">sql語句或者預存程序</param>    /// <param name="cmdType">類型</param>    /// <param name="param">參數</param>    /// <returns>返回int類型的資料</returns>    public static int ExecuteNonQuery(string textcmd,SqlParameter[] param, CommandType cmdType)    {        using (SqlConnection con = new SqlConnection(ConnectionStringLocalTransaction))        {            SqlCommand cmd = new SqlCommand();            PreparedCommd(con, cmd, textcmd, cmdType, param);            int num = cmd.ExecuteNonQuery();            return num;        }    }    /// <summary>    /// 讀取一行一列的資料    /// </summary>    /// <param name="textmd"></param>    /// <param name="cmdType"></param>    /// <param name="param"></param>    /// <returns></returns>    public static object ExecuteScalar(string textmd, CommandType cmdType, SqlParameter[] param)    {        using (SqlConnection con = new SqlConnection(ConnectionStringLocalTransaction))        {            SqlCommand cmd = new SqlCommand();            PreparedCommd(con, cmd, textmd, cmdType, param);            return cmd.ExecuteScalar();        }    }    /// <summary>    /// 讀取一行一列的資料    /// </summary>    /// <param name="textmd"></param>    /// <param name="cmdType"></param>    /// <param name="param"></param>    /// <returns></returns>    public static object ExecuteScalar(string SQL)    {        using (SqlConnection con = new SqlConnection(ConnectionStringLocalTransaction))        {            con.Open();            SqlCommand cmd = new SqlCommand(SQL, con);            return cmd.ExecuteScalar();        }    }    /// <summary>    /// 查詢    /// </summary>    /// <param name="textcmd"></param>    /// <param name="cmdType"></param>    /// <param name="param"></param>    /// <returns></returns>    public static SqlDataReader ExecuteReader(string textcmd, CommandType cmdType, SqlParameter[] param)    {        SqlConnection con = new SqlConnection(ConnectionStringLocalTransaction);        SqlCommand cmd = new SqlCommand();        try        {            //PreparedCommd(con, cmd, textcmd, cmdType, param);            SqlDataReader read = cmd.ExecuteReader(CommandBehavior.CloseConnection);            return read;        }        catch (Exception ex)        {            con.Close();            throw new Exception(ex.Message);        }    }    /// <summary>    /// 查詢返回DataTable    /// </summary>    /// <param name="sql"></param>    /// <returns></returns>    public static DataTable ExecuteReader(string sql)    {        SqlConnection con = new SqlConnection(ConnectionStringLocalTransaction);        DataTable dt = new DataTable();        try        {            SqlDataAdapter da = new SqlDataAdapter(sql, con);            da.Fill(dt);        }        catch (Exception)        {            throw;        }        return dt;    }    /// <summary>    /// 使用者登入    /// </summary>    /// <param name="UserName"></param>    /// <param name="UserPwd"></param>    /// <returns></returns>    public static int CheckLogin(string UserName, string UserPwd)    {        int num = 0;        try        {            string sql = "select * from TB_UserInfo  where [user_Name]='" + UserName + "' and user_pwd='" + UserPwd + "'";            num = Convert.ToInt32(ExecuteScalar(sql));        }        catch (Exception ex)        {            throw ex;        }        return num;    }   }