SQLHelper

数据库帮助类基本每个程序都会用到,我上网找了很多个,并进行对比,找到一个比较喜欢的,据说是来自一个叫牛腩新闻系统的开源项目,我对其进行了一些小修改

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
namespace CSJWebApp.BLL
{
    public class SQLHelper
    {
        #region 0.1 声明数据数据库连接所需要的对象
        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataReader sdr = null;
        #endregion
        #region 0.2 构造函数,初始化了连接对象  SQLHelper()
        /// <summary>
        /// 将连接数据对象封装到构造函数中
        /// </summary>
        public SQLHelper()
        {
            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            conn = new SqlConnection(connStr);
        }
        #endregion
        #region 0.3 获取连接对象  GetConn()
        /// <summary>
        /// 得到连接数据库对象，抽象连接的过程
        /// </summary>
        /// <returns>返回一个连接数据对象</returns>
        private SqlConnection GetConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }
        #endregion
        #region 1.1 执行增/删/改语句,返回受影响行数  int ExecuteNonQuery(string strsql, CommandType cmdtype, SqlParameter[] paras)
        /// <summary>
        /// (带参数)执行增/删/改语句,返回受影响行数
        /// </summary>
        /// <param name="strsql">SQL语句</param>
        /// <param name="paras">参数数组</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteNonQuery(string sql, CommandType cmdtype, SqlParameter[] paras)
        {
            int res = 0;
            using (cmd = new SqlCommand(sql, GetConn()))
            {
                try
                {
                    cmd.CommandType = cmdtype;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    res = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    throw ex;
                }
            }
            return res;
        }
        #endregion
        #region 2.1 执行查询语句或存储过程,返回DataTable类型的数据  DataTable ExecuteQuery(string sql, CommandType cmdtype, SqlParameter[] paras)
        /// <summary>
        /// 执行查询语句或存储过程,返回DataTable类型的数据
        /// </summary>
        /// <param name="sql">SQL语句或者存储过程</param>
        /// <param name="cmdtype">命令类型</param>
        /// <param name="paras">参数数组</param>
        /// <returns>返回DataTable类型的数据</returns>
        public DataTable ExecuteQuery(string sql, CommandType cmdtype, SqlParameter[] paras)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(sql, GetConn());
            cmd.CommandType = cmdtype;
            if (paras != null) cmd.Parameters.AddRange(paras);
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
        #endregion
        #region 2.2 执行查询语句或存储过程,返回第一行第一列的值  DataTable ExecuteScalar(string sql, CommandType cmdtype, SqlParameter[] paras)
        /// <summary>
        /// 执行查询语句或存储过程,返回第一行第一列的值
        /// </summary>
        /// <param name="sql">SQL语句或者存储过程</param>
        /// <param name="cmdtype">命令类型</param>
        /// <param name="paras">参数数组</param>
        /// <returns>返回第一行第一列的值</returns>
        public object ExecuteScalar(string sql, CommandType cmdtype, SqlParameter[] paras)
        {
            object res = null;
            using (cmd = new SqlCommand(sql, GetConn()))
            {
                cmd.CommandType = cmdtype;
                try
                {
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    res = cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    throw ex;
                }
            }
            return res;
        }
        #endregion
    }
}