在编写一些代码的时候我们在数据库访问层也就是service层经常要对数据库进行增删改查的操作， 所以我们可以把一些经常写的代码，可以封转到一个通用的类中！这就是经常说的DBHelper类 ；

具体的实现如下：

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace eBookModels
{
    public static class DBHlper
    {

      ///封装的一个Connection

        private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {///最好把这个连接字符串写道配置文件里在读取出来， 这样有利于维护
                string connectionString = "Data Source=.;Initial Catalog=MyBookShop;User ID=sa";
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }

       //这是通用的增删改的方法不带参数的

        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }

     //这是通用的增删改的方法带参数的

        public static int ExecuteCommand(string sql, SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }

    //这是通用的增删改的方法只带一个参数的

        public static int ExecuteCommand(string sql, SqlParameter value)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.Add(value);
            int result = cmd.ExecuteNonQuery();
            return result;
        }

   //执行返回首行首列不带参数的
        public static int ExecuteScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = (int)cmd.ExecuteScalar();
            return result;
        }

///执行返回首行首列带参数的
 

        public static int ExecuteScalar(string sql, SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = (int)cmd.ExecuteScalar();
            return result;
        }

        public static int ExecuteScalar(string sql, SqlParameter value)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.Add(value);
            int result = (int)cmd.ExecuteScalar();
            return result;
        }

        /// <summary>
        /// 执行无参数的sql语句
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

       /// <summary>
       /// 执行有参数的sql语句
       /// </summary>
       /// <param name="sql"></param>
       /// <param name="value"></param>
       /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter value)
        {

            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.Add(value);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 这个主要就是为了集合对象多服务的
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
    }
}

这里虽然我也封装了SqlDataReader 这个方法但是这个用的时候要注意， 就是涉及到主外键关系的时候你最好还是不要用这个方法， 因为会出现一些连接未关闭的的异常，因为一次读取中在查外键的时候还要读取一次， 这样一来连接闭合就成了问题（能解决）；根据我写代码的经验我一般在涉及到外键查询的时候用GetDataSet这个方法， 当然所有的查询都可以用这个方法！呵呵。