一个数据访问公共类

using System;
using System.Data;
using System.Data.SqlClient;

namespace DBUtility
{
    /// <summary>
    /// SqlHelper 的摘要说明
    /// 数据访问公共类
    /// 1.使用静态方法，便于调用
    /// 2.每个方法都新建一个连接对象、命令对象以支持多线程操作
    /// </summary>
    public class SqlHelper
    {
        //数据库连接串
        private static readonly string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

        #region 数据库连接对象操作
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private static SqlConnection OpenConnection()
        {
            try
            {
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();
                return conn;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 关闭数据库连接释放资源
        /// </summary>
        /// <param name="Conn">数据库连接对象</param>
        public static void DisposeConnection(SqlConnection Conn)
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
        }

        #endregion

        #region SQL操作语句

        /// <summary>
        /// 执行Sql查询语句
        /// </summary>
        /// <param name="strSQL">传入的Sql语句</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteSql(string strSQL)
        {
            try
            {
                SqlConnection conn = OpenConnection();
                SqlCommand comm = new SqlCommand(strSQL, conn);
                int val = comm.ExecuteNonQuery();

                DisposeConnection(conn);
                return val;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 执行Sql查询语句,同时进行事务处理
        /// </summary>
        /// <param name="strSQL">传入的Sql语句</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteSqlWithTransaction(string strSQL)
        {
            SqlTransaction trans;
            SqlConnection conn = OpenConnection();
            SqlCommand comm = new SqlCommand(strSQL, conn);

            trans = conn.BeginTransaction();
            comm.Transaction = trans;

            try
            {
                int val = comm.ExecuteNonQuery();
                trans.Commit();

                DisposeConnection(conn);
                trans.Dispose();
                return val;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="coll">参数集合</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecutePorcedure(string procName, SqlParameter[] parameters)
        {
            try
            {
                SqlConnection conn = OpenConnection();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                for (int i = 0; i < parameters.Length; i++)
                {
                    comm.Parameters.Add(parameters[i]);
                }
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = procName;
                int val = comm.ExecuteNonQuery();

                DisposeConnection(conn);
                return val;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        #endregion

        #region 数据查询

        /// <summary>
        /// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox
        /// 例如string userName = (string)ExecuteScalar("select userName from users");
        /// </summary>
        /// <param name="strSQL">传入的Sql语句</param>
        /// <returns>object 返回值</returns>
        public static object ExecuteScalar(string strSQL)
        {
            object obj = new object();
            try
            {
                SqlConnection conn = OpenConnection();
                SqlCommand comm = new SqlCommand(strSQL, conn);
                obj = comm.ExecuteScalar();

                DisposeConnection(conn);
                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }

        /// <summary>
        /// 执行SQL语句返回第一行第一列的值
        /// </summary>
        /// <param name="strSQL">传入的SQL语句</param>
        /// <returns></returns>
        public static string getValue(string strSQL)
        {
            string strReturn = "";
            SqlDataReader dr = null;
            SqlConnection conn = OpenConnection();
            SqlCommand comm = new SqlCommand(strSQL, conn);
            try
            {
                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
                while (dr.Read())
                {
                    if (dr[0].ToString().Length > 0)
                        strReturn = dr[0].ToString();
                }
                return strReturn;

            }
            catch (Exception ex)
            {
                if (dr != null && !dr.IsClosed)
                    dr.Close();
                DisposeConnection(conn);
                throw new Exception(ex.Message);
            }

        }

        /// <summary>
        /// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接
        /// </summary>
        /// <param name="strSQL">传入的Sql语句</param>
        /// <returns>SqlDataReader对象</returns>
        public static SqlDataReader getDataReader(string strSQL)
        {
            SqlDataReader dr = null;
            SqlConnection conn = OpenConnection();
            try
            {

                SqlCommand comm = new SqlCommand(strSQL, conn);
                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                return dr;
            }
            catch (Exception ex)
            {
                if (dr != null && !dr.IsClosed)
                    dr.Close();
                DisposeConnection(conn);
                throw new Exception(ex.Message);
            }

        }

        /// <summary>
        /// 返回指定Sql语句的DataTable
        /// </summary>
        /// <param name="strSQL">传入的Sql语句</param>
        /// <returns>DataTable</returns>
        public static DataTable getDataTable(string strSQL)
        {
            try
            {
                SqlConnection conn = OpenConnection();
                SqlCommand comm = new SqlCommand(strSQL, conn);
                SqlDataAdapter da = new SqlDataAdapter(comm);
                DataTable table = new DataTable();
                da.Fill(table);

                DisposeConnection(conn);
                return table;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 执行带参数存储过程并返回数据表
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">SqlParameterCollection 输入参数</param>
        /// <returns>dataTable</returns>
        public static DataTable getDataTable(string procName, SqlParameter[] parameters)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable datatable = new DataTable();
            try
            {
                SqlConnection conn = OpenConnection();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.Parameters.Clear();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = procName;

                for (int i = 0; i < parameters.Length; i++)
                {
                    comm.Parameters.Add(parameters[i]);
                }

                da.SelectCommand = comm;
                da.Fill(datatable);

                DisposeConnection(conn);
                return datatable;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 根据DataReader生成DataTable
        /// </summary>
        /// <param name="strSQL">传入的Sql语句</param>
        /// <returns>DataTable</returns>
        public static DataTable getDataTableFromDataReader(string strSQL)
        {
            SqlDataReader dr = null;
            try
            {
                SqlConnection conn = OpenConnection();
                SqlCommand comm = new SqlCommand(strSQL, conn);
                dr = comm.ExecuteReader();

                DataTable datatable = new DataTable();
                DataTable schemaTable = dr.GetSchemaTable();

                //生成列
                foreach (DataRow row in schemaTable.Rows)
                {
                    DataColumn column = new DataColumn(row["ColumnName"].ToString(), System.Type.GetType(row["DataType"].ToString()));
                    datatable.Columns.Add(column);
                }

                //添加数据
                while (dr.Read())
                {
                    DataRow row = datatable.NewRow();
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        row[i] = dr[i];
                    }
                    datatable.Rows.Add(row);
                    row = null;
                }

                schemaTable = null;

                dr.Close();

                return datatable;
            }
            catch (Exception ex)
            {
                if (!dr.IsClosed)
                    dr.Close();
                throw new Exception(ex.Message);
            }

        }

        /// <summary>
        /// 返回指定Sql语句的DataSet
        /// </summary>
        /// <param name="strSQL">传入的Sql语句</param>
        /// <returns>DataSet</returns>
        public static DataSet getDataSet(string strSQL)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                SqlConnection conn = OpenConnection();
                SqlCommand comm = new SqlCommand(strSQL, conn);
                comm.CommandType = CommandType.Text;
                da.SelectCommand = comm;
                da.Fill(ds);

                DisposeConnection(conn);
                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 是否存在值
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static bool isExists(string strSQL)
        {
            try
            {
                SqlConnection conn = OpenConnection();
                SqlCommand comm = new SqlCommand(strSQL, conn);
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.HasRows) return true;

                DisposeConnection(conn);
                return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }

        #endregion

    }
}