一个自定义的C#数据库操作基础类 SqlHelper

浏览数：27 / 时间：2015年06月12日

SqlHelper其实是我们自己编写的一个类，使用这个类目的就是让使用者更方便、更安全的对数据库的操作，既
是除了在SqlHelper类以外的所有类将不用引用对数据库操作的任何类与语句，无须担心数据库的连接与关闭的问题。
转自：http://blog.csdn.net/dandanzmc/article/details/9345937

{  
    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;  
        }  
    }  
}
//该代码片段来自于: http://www.sharejs.com/codes/csharp/8134