一個自定義的C#數據庫操作基礎類 SqlHelper

SqlHelper其實是我們自己編寫的一個類，使用這個類目的就是讓使用者更方便、更安全的對數據庫的操作，既
是除了在SqlHelper類以外的所有類將不用引用對數據庫操作的任何類與語句，無須擔心數據庫的連接與關閉的問題。

{ 
    public class SQLHelper 
    { 
        private string strConn = null; 
        private SqlConnection Conn = null; 
        private SqlCommand Cmd = null; 
        private SqlDataReader Sqlsdr = null; 
        public SQLHelper() 
        { 
            //利用反射連接數據庫 
            strConn = ConfigurationManager.ConnectionStrings["strConn"].ConnectionString; 
            //strConn = "data source=192.168.24.83;initial catalog=NewsSystem;user id=sa;password=123456"; 
            Conn = new SqlConnection(strConn); 
        }


    /// <summary> 
    /// 狀態 
    /// </summary> 
    /// <returns>SqlConnection</returns> 
    private SqlConnection GetConn() 
    { 
        if (Conn.State == ConnectionState.Closed) 
        { 
            Conn.Open(); 
        } 
        return Conn; 
    } 

    /// <summary> 
    /// 執行不帶參數的增刪改操作 
    /// </summary> 
    /// <param name="CmdText">執行的SQL語句或存儲過程</param> 
    /// <param name="CmdType">類型</param> 
    /// <returns>增刪改的操作數</returns> 

    public int ExecuteNonQuery(string CmdText, CommandType CmdType) 
    { 
        //數據庫連接語句 
        int res; 
        try 
        { 
            Cmd = new SqlCommand(CmdText, GetConn()); 
            Cmd.CommandType = CmdType; 
            res = Cmd.ExecuteNonQuery(); 

        } 
        catch (Exception ex) 
        { 

            throw ex; 
        } 
        finally 
        { 
            if (Conn.State == ConnectionState.Open) 
            { 
                Conn.Close(); 
            } 
        } 
        return res; 
    } 

    /// <summary> 
    /// 執行帶參數的增刪改操作 
    /// </summary> 
    /// <param name="CmdText">執行的SQL語句或存儲過程</param> 
    /// <param name="Parameters">參數</param> 
    /// <param name="CmdType">類型（SQL語句或存儲過程）</param> 
    /// <returns>增刪改的操作數</returns> 
    public int ExecuteNonQuery(string CmdText, SqlParameter[] Parameters, CommandType CmdType) 
    { 
        int res; 
        try 
        { 
            Cmd.Parameters.AddRange(Parameters); 
            Cmd = new SqlCommand(CmdText, GetConn()); 
            Cmd.CommandType = CmdType; 
            res = Cmd.ExecuteNonQuery(); 
        } 
        catch (Exception ex) 
        { 

            throw ex; 
        } 
        finally 
        { 
            if (Conn.State == ConnectionState.Open) 
            { 
                Conn.Close(); 
            } 
        } 
        return res; 
    } 


    /// <summary> 
    /// 不帶參數的查詢 
    /// </summary> 
    /// <param name="CmdText">執行的SQL語句或存儲過程</param> 
    /// <param name="CmdType">類型</param> 
    /// <returns>查詢的數據</returns> 
    public DataTable ExecuteQuery(string CmdText, CommandType CmdType) 
    { 
        DataTable dt = new DataTable(); 

        using (Sqlsdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection)) 
        { 
            Cmd = new SqlCommand(CmdText, GetConn()); 
            Cmd.CommandType = CmdType; 
            dt.Load(Sqlsdr); 
        } 
        return dt; 
    } 

    /// <summary> 
    /// 帶參數的查詢 
    /// </summary> 
    /// <param name="CmdText">SQL語句或存儲過程</param> 
    /// <param name="Parameters">參數</param> 
    /// <param name="CmdType">類型</param> 
    /// <returns>查詢的數據</returns> 
    public DataTable ExecuteQuery(string CmdText, SqlParameter[] Parameters, CommandType CmdType) 
    { 
        DataTable dt = new DataTable(); 

        using (Sqlsdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection)) 
        { 
            Cmd = new SqlCommand(CmdText, GetConn()); 
            Cmd.CommandType = CmdType; 
            Cmd.Parameters.AddRange(Parameters); 
            dt.Load(Sqlsdr); 
        } 
        return dt; 
    } 
} 

}</pre>