SQLHelper助手类【代码】

川长思鸟来 2022-06-05 05:49 219阅读 0赞

SqlHelper是一个基于.NET Framework的数据库操作组件，组件中包含数据库操作方法。SqlHelper用于简化你重复的去写那些数据库连接SqlConnection，SqlCommand,SqlDataReader等等。SqlHelper封装过后通常是只需要给方法传入一些参数如数据库连接字符串、SQL参数等，就可以访问数据库了，很方便。

public class SQLHelper
        {
            private SqlConnection conn = null;
            private SqlCommand cmd = null;
            private SqlDataReader sdr = null;
            public SQLHelper()
            {
                string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
                conn = new SqlConnection(connStr);
            }
    
            /// <summary>
            /// 获取数据库连接
            /// </summary>
            /// <returns></returns>
            private SqlConnection GetConn()
            {
                if (conn.State ==ConnectionState .Closed )
                {
                    conn.Open();
                }          
                return conn;
            }
    
            /// <summary>
            /// 该方法执行不带参数的增删改SQL语句或存储过程
            /// </summary>
            /// <param name="cmdText">增删改SQL语句或者存储过程</param>
            /// <param name="ct">命令类型</param>
            /// <returns></returns>
            public int ExecuteNonQuery(string cmdText,CommandType ct)
            {
                int res ;
                try
                {
                    cmd = new SqlCommand(cmdText , GetConn ());
                    cmd.CommandType = ct;
                    res = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn.State ==ConnectionState .Open)
                    {
                        conn.Close();
                    }              
                }
                return res;
            }
    
            /// <summary>
            /// 执行带参数的增删改SQL语句或存储过程
            /// </summary>
            /// <param name="cmdText">增删改SQL语句或存储过程</param>
            /// <param name="paras">参数</param>
            /// <param name="ct">命令类型</param>
            /// <returns></returns>
            public int ExecuteNonQuery(string cmdText,SqlParameter [] paras,CommandType ct)
            {
                int res;
                using (cmd=new SqlCommand (cmdText ,GetConn ()))
                {
                    cmd.CommandType = ct;
                    cmd.Parameters.AddRange(paras);              
                    res = cmd.ExecuteNonQuery();
                }
                return res;
            }
    
            /// <summary>
            /// 执行查询SQL语句或存储过程
            /// </summary>
            /// <param name="cmdText">查询SQL语句或存储过程</param>
            /// <param name="ct">命令类型</param>
            /// <returns></returns>
            public DataTable ExecuteQuery(string cmdText,CommandType ct)
            {
                DataTable dt = new DataTable();
                cmd = new SqlCommand(cmdText , GetConn ());
                cmd.CommandType = ct;
                using (sdr=cmd .ExecuteReader (CommandBehavior .CloseConnection ))
                {
                    dt.Load(sdr);
                }
                return dt;
            }
    
            /// <summary>
            /// 执行带参数的查询SQL语句或存储过程
            /// </summary>
            /// <param name="cmdText">查询SQL语句或存储过程</param>
            /// <param name="paras">参数</param>
            /// <param name="ct">命令类型</param>
            /// <returns></returns>
            public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)
            {
                DataTable dt = new DataTable();
                cmd = new SqlCommand(cmdText, GetConn());
                cmd.CommandType = ct;
                using (sdr=cmd.ExecuteReader (CommandBehavior .CloseConnection ))
                {
                    dt.Load(sdr);
                }
                return dt;
            }
    
        }