C# 数据库共通类

 很多时候项目中操作数据库都需要写一数据共通类。下面是我自己写的一个。供大家参考。

在使用时候Sub_ConnectOpen()方法里面去的数据库连接的方法自己修改。

internal class DBBase
    {
        #region 　全局变量定义
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string m_strConnectString = "";

        /// <summary>
        /// SqlServer数据库连接定义
        /// </summary>
        private SqlConnection m_SqlConn = null;

        /// <summary>
        /// SqlServer事务定义
        /// </summary>
        private SqlTransaction m_SqlTran = null;

        /// <summary>
        /// 类的构造函数区别
        /// </summary>
        private bool m_bFlag = true;

        /// <summary>
        /// 执行超时时间
        /// </summary>
        private int m_TimeOut = 3000;

        /// <summary>
        /// 执行超时时间
        /// </summary>
        public int TimeOut
        {
            get { return m_TimeOut; }
            set { m_TimeOut = value; }
        }
        #endregion

        #region 　构造函数
        /// <summary>
        /// 无参数类构造
        /// </summary>
        public DBBase()
        {
            m_bFlag = true;
        }

        /// <summary>
        /// 无参数类构造
        /// </summary>
        /// <param name="p_strConnectString">连接字符串</param>
        /// <param name="p_strType">数据库类型</param>
        public DBBase(string p_strConnectString)
        {
            m_strConnectString = p_strConnectString;
            m_bFlag = false;
        }
        #endregion

        #region　数据库连接连接打开
        /// <summary>
        /// 数据库连接函数，连接打开
        /// </summary>
        public void Sub_ConnectOpen()
        {
            try
            {
                if (m_SqlConn == null)
                {
                    if (m_bFlag)
                    {
                        //连接字符串读取
                        //m_strConnectString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnDB"].ToString();
                    }
                    //数据库连接初始化
                    m_SqlConn = new SqlConnection(m_strConnectString);
                }

                if (m_SqlConn != null && m_SqlConn.State == ConnectionState.Closed)
                {
                    //数据库连接连接打开
                    m_SqlConn.Open();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region　数据库连接连接关闭
        /// <summary>
        ///　数据库连接连接关闭
        /// </summary>
        /// <remarks>
        ///　数据库连接连接关闭
        /// </remarks>
        /// <returns>
        ///　
        /// </returns>
        /// <exception>
        ///　
        /// </exception>
        public void Sub_Disconnect()
        {
            try
            {
                if (m_SqlConn != null && m_SqlConn.State == ConnectionState.Open)
                {
                    //数据库连接连接关闭
                    m_SqlConn.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region　事务打开
        /// <summary>
        ///　事务打开
        /// </summary>
        public void Sub_TransactionBegin()
        {
            try
            {
                if (m_SqlConn != null && m_SqlTran == null && m_SqlConn.State == ConnectionState.Open)
                {
                    //事务打开
                    m_SqlTran = m_SqlConn.BeginTransaction();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region　事务提交处理
        /// <summary>
        ///　事务提交处理
        /// </summary>
        public void Sub_TransactionCommit()
        {
            try
            {
                if (m_SqlConn != null && m_SqlConn.State == ConnectionState.Open)
                {
                    if (m_SqlTran != null)
                    {
                        //事务提交
                        m_SqlTran.Commit();
                        m_SqlTran = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region　事务回滚
        /// <summary>
        ///　事务回滚
        /// </summary>
        /// <remarks>
        public void Sub_TransactionRollback()
        {
            try
            {
                if (m_SqlConn != null && m_SqlConn.State == ConnectionState.Open)
                {
                    if (m_SqlTran != null)
                    {
                        //事务回滚
                        m_SqlTran.Rollback();
                        m_SqlTran = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region　插入、更新、删除函数
        /// <summary>
        /// 插入、更新、删除函数
        /// </summary>
        /// <param name="p_objCmdType"></param>
        /// <param name="p_strCmdText"></param>
        /// <param name="p_lstParam"></param>
        /// <param name="p_dbType"></param>
        /// <param name="p_lstValue"></param>
        /// <returns></returns>
        public int Func_ExecuteNonQuery(
            CommandType p_objCmdType,
            string p_strCmdText,
            SqlParameter[] p_param)
        {
            int lntResult = 0;

            try
            {
                //使用后资源回收
                using (SqlCommand cmd = new SqlCommand())
                {
                    mSub_PrepareCommandSql(cmd, m_SqlConn, m_SqlTran, p_objCmdType, p_strCmdText, p_param);

                    lntResult = cmd.ExecuteNonQuery();

                    cmd.Parameters.Clear();
                }

                return lntResult;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region　查询DATESET
        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_objCmdType"></param>
        /// <param name="p_strCmdText"></param>
        /// <param name="p_lstParam"></param>
        /// <param name="p_dbType"></param>
        /// <param name="p_lstValue"></param>
        /// <returns></returns>
        public DataSet Func_ExecuteDataset(
            CommandType p_objCmdType,
            string p_strCmdText,
            SqlParameter[] p_param)
        {
            DataSet ds = null;
            try
            {
                //使用后资源回收
                using (SqlCommand cmd = new SqlCommand())
                {
                    mSub_PrepareCommandSql(cmd, m_SqlConn, m_SqlTran, p_objCmdType, p_strCmdText, p_param);

                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    ds = new DataSet();

                    da.Fill(ds);

                    cmd.Parameters.Clear();
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region　查询第一行第一列的值返回
        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_objCmdType"></param>
        /// <param name="p_strCmdText"></param>
        /// <param name="p_lstParam"></param>
        /// <param name="p_dbType"></param>
        /// <param name="p_lstValue"></param>
        /// <returns></returns>
        public Object Func_ExecuteScalar(
            CommandType p_objCmdType,
            string p_strCmdText,
            SqlParameter[] p_param)
        {
            object resultVal = null;
            try
            {
                //使用后资源回收
                using (SqlCommand cmd = new SqlCommand())
                {
                    mSub_PrepareCommandSql(cmd, m_SqlConn, m_SqlTran, p_objCmdType, p_strCmdText, p_param);

                    resultVal = cmd.ExecuteScalar();

                    cmd.Parameters.Clear();
                }

                return resultVal;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region　SQL命令准备
        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_command"></param>
        /// <param name="p_connection"></param>
        /// <param name="p_transaction"></param>
        /// <param name="p_commandType"></param>
        /// <param name="p_commandText"></param>
        /// <param name="p_lstParam"></param>
        /// <param name="p_dbType"></param>
        /// <param name="p_lstValue"></param>
        private void mSub_PrepareCommandSql(
            SqlCommand p_command,
            SqlConnection p_connection,
            SqlTransaction p_transaction,
            CommandType p_commandType,
            string p_commandText,
            SqlParameter[] p_param)
        {
            try
            {
                p_command.Connection = p_connection;

                //数据库的命令存储过程还是SQL语句
                p_command.CommandText = p_commandText;

                p_command.CommandTimeout = m_TimeOut;

                if (p_transaction != null)
                {
                    //有事务的话打开事务
                    p_command.Transaction = p_transaction;
                }

                p_command.CommandType = p_commandType;

                if (p_param != null && p_param.Length > 0)
                {
                    //有参数进行传参数
                    for (int i = 0; i < p_param.Length; i++)
                    {
                        p_command.Parameters.Add(p_param[i]);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }