C#自用DBHelper类

C#自用DBHelper类

2016-09-08 查询DataTable和DateSet加入元数据信息, 类实现IDisposable接口
DBHelper类代码:

    public class DBHelper: IDisposable
    {
        #region 实现IDisposable接口
        public void Dispose()
       {         
            Dispose(true);
            GC.SuppressFinalize(this);
       }

        protected virtual void Dispose(bool disposing)
        {

            if (!m_disposed)
            {
                if (disposing)
                {
                    con.Dispose();
                    if (sqlTran != null)
                    {
                        sqlTran.Dispose();
                    }
                }
                m_disposed = true;
            }
        }

        ~DBHelper()
        {
            Dispose(false);
        }

        private bool m_disposed;
        #endregion

       /// <summary>
        /// 初始化DBHelper
        /// </summary>
        /// <param name="source">服务器</param>
        /// <param name="data">数据库</param>
        /// <param name="user">用户名</param>
        /// <param name="password">密码</param>
        public DBHelper(string source, string data, string user, string password)
        {
            string conStr = "Data Source=" + source +";";
            conStr += "Initial Catalog=" + data +";";
            conStr += "User ID=" + user +";";
            conStr += "Password=" + password +";";
            con = new SqlConnection(conStr);
            Open();
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            con.Close();
            return;
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            con.Open();
            return;
        }

        SqlConnection con;

        SqlTransaction sqlTran;

        #region 事务
        /// <summary>
        /// 开启事务
        /// </summary>
        public void OpenTran()
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            sqlTran = con.BeginTransaction();
            return;
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public void CmtTran()
        {
            sqlTran.Commit();
            return;
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RockTran()
        {
            sqlTran.Rollback();
            return;
        }
        /// <summary>
        /// 关闭事务
        /// </summary>
        public void CloseTran()
        {
            sqlTran.Dispose();
            sqlTran = null;
            return;
        }

        #endregion

        #region 执行查询, 返回DataTable

        /// <summary>
        /// 执行查询,返回DataTable
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public DataTable GetTable(string strSQL)
        {
            return GetTable(strSQL, null);
        }

        /// <summary>
        /// 执行查询,返回DataTable
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public DataTable GetTable(string strSQL, SqlParameter[] pas)
        {
            return GetTable(strSQL, pas, CommandType.Text);
        }

        /// <summary>
        /// 执行查询,返回DataTable
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="pas"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public DataTable GetTable(string strSQL, SqlParameter[] pas, CommandType cmdType)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(strSQL, con);
            da.SelectCommand.CommandType = cmdType;
            //判断是否开启事务
            if (sqlTran != null)
            {
                da.SelectCommand.Transaction = sqlTran;
            }
            if (pas != null)
            {
                da.SelectCommand.Parameters.AddRange(pas);
            }
            da.FillSchema(dt, SchemaType.Mapped);
            da.Fill(dt);        
            da.SelectCommand.Parameters.Clear();
            return dt;
        }
        #endregion

        #region 执行查询, 返回DataSet

        /// <summary>
        /// 执行查询, 返回DataSet
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="pas"></param>
        /// <param name="cmdtype"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSQL, SqlParameter[] pas, CommandType cmdtype)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(strSQL, con);
            //判断是否开启事务
            if (sqlTran != null)
            {
                da.SelectCommand.Transaction = sqlTran;
            }
            da.SelectCommand.CommandType = cmdtype;
            if (pas != null)
            {
                da.SelectCommand.Parameters.AddRange(pas);
            }
            da.FillSchema(ds, SchemaType.Mapped);
            da.Fill(ds);
            da.SelectCommand.Parameters.Clear();
            return ds;
        }

        /// <summary>
        /// 执行查询, 返回DataSet
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSQL, SqlParameter[] pas)
        {
            return GetDataSet(strSQL, pas, CommandType.Text);
        }

        /// <summary>
        /// 执行查询, 返回DataSet
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSQL)
        {
            return GetDataSet(strSQL, null);
        }
        #endregion

        #region 执行非查询语句

        /// <summary>
        /// 执行非查询语句,返回受影响行数
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="pas"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public int ExcuteSQL(string strSQL, SqlParameter[] pas, CommandType cmdType)
        {
            int i = 0;
            if (con.State != ConnectionState.Open)
            {
                Open();
            }
            SqlCommand cmd = new SqlCommand(strSQL, con);
            //判断是否开启事务
            if (sqlTran != null)
            {
                cmd.Transaction = sqlTran;
            }
            cmd.CommandType = cmdType;
            if (pas != null)
            {
                cmd.Parameters.AddRange(pas);
            }
            i = cmd.ExecuteNonQuery();;
            return i;
        }

        /// <summary>
        /// 执行非查询语句,返回受影响行数
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public int ExcuteSQL(string strSQL, SqlParameter[] pas)
        {
            return ExcuteSQL(strSQL, pas, CommandType.Text);
        }

        /// <summary>
        /// 执行非查询语句,返回受影响行数
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public int ExcuteSQL(string strSQL)
        {
            return ExcuteSQL(strSQL, null);
        }

        #endregion

        #region 执行存储过程

        /// <summary>
        /// 执行非查询存储过程,返回受影响行数
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public int ExcuteProc(string procName, SqlParameter[] pas)
        {
            return ExcuteSQL(procName, pas, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行非查询存储过程,返回受影响行数
        /// </summary>
        /// <param name="procName"></param>
        /// <returns>返回受影响行数</returns>
        public int ExcuteProc(string procName)
        {
            return ExcuteProc(procName, null);
        }

        #endregion

    }