数据库操作类(源代码)

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

namespace PeterZhu.DbOperator
{
    public class DbOperator : IDisposable
    {
        public DbOperator()
        {
        }

        /// <summary>
        /// 用于数据库连接
        /// </summary>
        protected SqlConnection conn;
        /// <summary>
        /// 用于执行命令
        /// </summary>
        protected SqlCommand cmd;

        /// <summary>
        /// 数据库事务
        /// </summary>
        protected SqlTransaction trans;
        /// <summary>
        /// 当前连接是否正处在事务中
        /// </summary>
        protected bool _IsInTrans;

        /// <summary>
        /// 数据库连接字符串节的名称
        /// </summary>
        private string _ConnStrSectionName;

        /// <summary>
        /// 指示是否已经执行过清理
        /// </summary>
        private bool disposed;

        #region 设置连接字符串

        /// <summary>
        /// 公开属性，用于设置数据库连接字符串节的名称。连接字符串位于connectionStrings节
        /// </summary>
        public string ConnStrSectionName
        {
            set
            {
                if (String.IsNullOrEmpty(value))
                    throw new Exception("数据库连接字符串节的名称不能为空！");

                _ConnStrSectionName = value;
            }
        }

        /// <summary>
        /// 私有方法，取得web.config文件中的连接字符串节
        /// </summary>
        /// <returns></returns>
        private string GetConnString()
        {
            if (String.IsNullOrEmpty(_ConnStrSectionName))
            {
                throw new Exception("数据库连接字符串节的名称不能为空！");
            }

            return System.Web.Configuration.WebConfigurationManager.ConnectionStrings[_ConnStrSectionName].ConnectionString;
        }

        #endregion

        #region 数据库连接
        /// <summary>
        /// 私有方法，用于打开数据库连接
        /// </summary>
        private void OpenConn()
        {
            if (conn == null || conn.State == ConnectionState.Closed)
            {
                if (conn == null) conn = new SqlConnection();
                conn.ConnectionString = GetConnString();
                conn.Open();
            }
        }

        /// <summary>
        /// 用于关闭数据库连接
        /// </summary>
        private void CloseConn()
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
        #endregion

        #region 释放资源
        /// <summary>
        /// 释放类所占用的资源
        /// </summary>
        public void Dispose()
        {
            if (!this.disposed)
            {
                this.Free();
            }

            disposed = true;
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// 析构函数，执行最后的清理工作，以防止未手工调用Dispose方法
        /// </summary>
        ~DbOperator()
        {
            Free();
        }

        /// <summary>
        ///私有方法， 释放类所占用的资源
        /// </summary>
        private void Free()
        {
            if (conn != null)
            {
                conn.Dispose();
            }
            if (cmd != null)
            {
                cmd.Dispose();
            }
            if (trans != null)
            {
                trans.Dispose();
            }
        }
        #endregion

        #region 事务处理
        /// <summary>
        /// 公开属性，判断当前连接是否正处在事务中
        /// </summary>
        public bool IsInTrans
        {
            get { return _IsInTrans; }
        }

        /// <summary>
        /// 开始一个事务处理
        /// </summary>
        /// <param name="transName">事务的名称</param>
        public void BeginTrans(string transName)
        {
            if (String.IsNullOrEmpty(transName))
                throw new ArgumentNullException("transName", "事务名称不能为空！");
            OpenConn();
            trans = conn.BeginTransaction(transName);
            _IsInTrans = true;
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTrans()
        {
            if (_IsInTrans == true)
            {
                if (trans.Connection != null)
                {
                    trans.Commit();
                    _IsInTrans = false;
                }
                else
                {
                    throw new Exception("该事务已经失效，提交事务失败！");
                }
            }
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollBackTrans()
        {
            if (_IsInTrans == true && trans.Connection != null)
            {
                trans.Rollback();
                _IsInTrans = false;
            }
        }
        #endregion

        #region 创建SqlCommand对象
        /// <summary>
        /// 创建SqlCommand对象
        /// </summary>
        /// <param name="cmdText">要执行的Sql语句</param>
        /// <returns>SqlCommand</returns>
        public SqlCommand CreateCommand(string cmdText)
        {
            return CreateCommand(cmdText, CommandType.Text);
        }

        /// <summary>
        /// 创建SqlCommand对象
        /// </summary>
        /// <param name="cmdText">要执行的CommandText</param>
        /// <param name="cmdType">执行命令的CommandType</param>
        /// <returns>SqlCommand</returns>
        public SqlCommand CreateCommand(string cmdText, CommandType cmdType)
        {
            OpenConn();
            SqlCommand myCmd = new SqlCommand();
            myCmd.Connection = conn;
            myCmd.CommandText = cmdText;
            myCmd.CommandType = cmdType;

            return myCmd;
        }
        #endregion

        #region 为SqlCommand执行命令准备参数
        /// <summary>
        /// 为SqlCommand执行命令准备参数
        /// </summary>
        /// <param name="cmdText">CommandText</param>
        /// <param name="cmdType">CommandType</param>
        /// <param name="cmdParms">参数数组</param>
        protected void PrepareCommand(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
        {
            cmd = CreateCommand(cmdText, cmdType);
            if (_IsInTrans)
            {
                cmd.Transaction = trans;
            }

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
        #endregion

        #region 基础数据操作

        #region ExecuteNonQuery操作
        /// <summary>
        /// 执行一个带参数的命令，返回受影响的条数
        /// </summary>
        /// <param name="cmdText">CommandText</param>
        /// <param name="cmdType">CommandType</param>
        /// <param name="cmdParms">参数数组</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
        {
            PrepareCommand(cmdText, cmdType, cmdParms);
            int result = 0;
            result = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            return result;
        }

        /// <summary>
        /// 执行一个不带参数的命令，返回受影响的条数
        /// </summary>
        /// <param name="cmdText">要执行的CommandText命令</param>
        /// <param name="cmdType">要执行命令的CommandType</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, CommandType cmdType)
        {
            int result = ExecuteNonQuery(cmdText, cmdType, null);

            return result;
        }

        #endregion

        #region ExecuteReader操作
        /// <summary>
        /// 执行一个带参数的命令，返回SqlDataReader。使用后应当及时关闭DataReader
        /// </summary>
        /// <param name="cmdText">要执行的CommandText</param>
        /// <param name="cmdType">要执行命令的CommandType</param>
        /// <param name="cmdParms">参数数组</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
        {
            PrepareCommand(cmdText, cmdType, cmdParms);
            try
            {
                SqlDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();

                return r;
            }
            catch
            {
                CloseConn();
                throw;
            }
        }

        /// <summary>
        /// 执行一个不带参数的命令，返回SqlDataReader对象
        /// </summary>
        /// <param name="cmdText">要执行的CommandText</param>
        /// <param name="cmdType">要执行命令的CommandType</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string cmdText, CommandType cmdType)
        {
            SqlDataReader r = ExecuteReader(cmdText, cmdType, null);

            return r;
        }
        #endregion

        #region ExecuteScalar操作

        /// <summary>
        /// 返回第一条记录第一列
        /// </summary>
        /// <param name="cmdText">要执行的命令CommandText</param>
        /// <param name="cmdType">要执行命令的CommandType</param>
        /// <param name="cmdParms">参数数组</param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
        {
            PrepareCommand(cmdText, cmdType, cmdParms);

            object result = cmd.ExecuteScalar();
            cmd.Parameters.Clear();

            return result;
        }

        #endregion

        #endregion
    }
}