DBHelper类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;

namespace CRM.DAL
{
    public static class DBHelper
    {
        //获取当前Web应用程序Web.config配置文件中数据库连接字符串
        private static readonly string connectionString =
            WebConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        /// <summary>
        /// 准备参数的方法
        /// </summary>
        /// <param name="conn">连接对象</param>
        /// <param name="cmd">命令对象</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="values">参数集合</param>
        private static void PrepareParameter
            (
                SqlConnection conn,
                SqlCommand cmd,
                CommandType cmdType,
                string cmdText,
                params SqlParameter [] values
            )
        {
            cmd.Connection = conn;
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;
            if(values != null && values.Length != 0)
            {
                cmd.Parameters.AddRange(values);
            }
        }

        private static void CloseResource(SqlCommand cmd,SqlDataAdapter sda,SqlConnection conn)
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (sda != null)
            {
                sda.Dispose();
            }
            if (conn != null && conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
            }
               
        }
        /// <summary>
        /// 执行INSERT、UPDATE、DELETE SQL语句
        /// </summary>
        /// <param name="safeSql">T-SQL语句</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteCommand(string safeSql)
        {
            using(SqlConnection conn=new SqlConnection (connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareParameter(conn, cmd, CommandType.Text, safeSql, null);
                try
                {
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {

                    throw new Exception(ex.Message);
                }
                finally
                {
                    CloseResource(cmd,null,conn);
                }
            }
        
        }
        /// <summary>
        /// 执行INSERT、UPDATE、DELETE 带参数的T-SQL语句
        /// </summary>
        /// <param name="sql">带参数的T-SQL语句</param>
        /// <param name="values">参数数组</param>
        /// <returns>返回T-SQL语句受影响的行数</returns>
        public static int ExecuteCommand(string sql,params SqlParameter [] values)
        {
            using(SqlConnection conn=new SqlConnection (connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareParameter(conn,cmd,CommandType.Text,sql,values);

                try
                {
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {

                    throw new Exception(ex.Message);
                }
                finally
                {
                    CloseResource(cmd, null, conn);

                }
            }
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="safeSql">T-SQL语句</param>
        /// <returns>返回单列值</returns>
        public static int GetScalar(string safeSql)
        {
            using(SqlConnection conn=new SqlConnection (connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareParameter(conn, cmd, CommandType.Text, safeSql, null);

                try
                {
                    conn.Open();
                    return Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {

                    throw new Exception(ex.Message);
                }
                finally
                {
                    CloseResource(cmd, null, conn);
                }
            }
        }

        public static int GetScalar(string sql,params SqlParameter [] values)
        {
            using(SqlConnection conn=new SqlConnection (connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareParameter(conn,cmd,CommandType.Text,sql,values);

                try
                {
                    conn.Open();
                    return Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    CloseResource(cmd, null, conn);
                }
            }
        }
        /// <summary>
        /// 返回数据阅读器对象
        /// </summary>
        /// <param name="safeSql">T-SQL语句</param>
        /// <returns>返回数据阅读器对象</returns>
        public static SqlDataReader GetReader(string safeSql)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            PrepareParameter(conn,cmd,CommandType.Text,safeSql,null);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
          
        }
        public static SqlDataReader GetReader(string sql,params SqlParameter [] values)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            PrepareParameter(conn,cmd,CommandType.Text,sql,values);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
               
                throw new Exception(ex.Message);
            }
        }
        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            PrepareParameter(conn, cmd, CommandType.Text, safeSql, null);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            try
            {
                sda.Fill(ds);
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                CloseResource(cmd, sda, conn);
               
            }
            return ds.Tables[0];
        }
        public static DataTable GetDataSet(string sql,params SqlParameter [] values)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand();
            PrepareParameter(conn,cmd,CommandType.Text,sql,values);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            try
            {
                sda.Fill(ds);
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                CloseResource(cmd, sda, conn);

            }
            return ds.Tables[0];
        }
    }
}