C# 操作資料庫就的那點代碼

標籤：

操作資料庫的那點代碼,別在費勁每個資料庫都寫一遍SQLHelper,SQLiteHelper,OleDbHelper,了,這裡都有了.

介面不發了,自己抽取定義就行了.

public abstract class DbHelperBase : IDbHelper    {        /// <summary>        /// 直接獲得連接字串        /// </summary>        /// <param name="connStr"></param>        public DbHelperBase(string connStr)        {            _ConnStr = connStr;            _LockExecNonQuery = new object();            _LockGetDataReader = new object();            _LockGetScalar = new object();            _LockGetDataSet = new object();        }        /// <summary>        /// 連接字串        /// </summary>        protected string _ConnStr;        /// <summary>        /// 是否事務        /// </summary>        bool _IsTrans = false;        object _LockExecNonQuery;        object _LockGetDataReader;        object _LockGetScalar;        object _LockGetDataSet;        protected abstract DbConnection DBConnectionObj { get; }        protected abstract DbCommand DbCommandObj { get; }        protected abstract DbDataAdapter DbDataAdapterObj { get; }        protected DbTransaction DbTransObj;        /// <summary>        /// 當前串連        /// </summary>        public DbConnection CurrentConnection        {            get            {                return DBConnectionObj;            }        }        /// <summary>        /// 執行sql問錯誤寫日誌方法        /// </summary>        protected virtual void WriteErrLog(Exception ex, string sqlText, params DbParameter[] param)        {            StringPlus logs = new StringPlus();            logs.AppendLine("SQL文本:");            logs.AppendLine(sqlText);            logs.AppendLine("Sql文參數:");            if (param != null)            {                foreach (DbParameter item in param)                {                    logs.AppendSpaceLine(1, "參數名:{0},參數值:{1}", item.ParameterName, item.Value);                }            }            logs.AppendLine("原始錯誤:");            logs.AppendLine(ex.Message);            LogHelper.WriteLog("DbErrorLog", logs.ToString());        }        /// <summary>        /// 開啟串連,如果已經開啟則什麼都不執行了        /// </summary>        protected virtual void OpenConnection()        {            if (DBConnectionObj.State != ConnectionState.Open)            {                DBConnectionObj.ConnectionString = _ConnStr;                DBConnectionObj.Open();            }        }        /// <summary>        /// 關閉串連,如果沒有開始事務或串連開啟時才關閉        /// </summary>        void CloseConnect()        {            if (!_IsTrans)            {                if (DBConnectionObj.State == ConnectionState.Open)                {                    DBConnectionObj.Close();                    DBConnectionObj.Dispose();                }            }        }        /// <summary>        /// 給當前DbCommand對象賦值,並且OpenConnection();        /// </summary>        void SetCommandAndOpenConnect(string sqlText, CommandType cmdType, params DbParameter[] param)        {            //按說賦值Connection,CommandType,是不用多次賦值的            DbCommandObj.CommandType = cmdType;            DbCommandObj.Connection = DBConnectionObj;            DbCommandObj.Parameters.Clear();            if (param != null)            {                DbCommandObj.Parameters.AddRange(param);            }            DbCommandObj.CommandText = sqlText;            OpenConnection();        }        /// <summary>        /// 開始執行事務        /// </summary>        public virtual void TransStart()        {            OpenConnection();            DbTransObj = DBConnectionObj.BeginTransaction();            DbCommandObj.Transaction = DbTransObj;            _IsTrans = true;        }        /// <summary>        /// 事務提交        /// </summary>        public virtual void TransCommit()        {            _IsTrans = false;            DbTransObj.Commit();            CloseConnect();        }        /// <summary>        /// 交易回復        /// </summary>        public virtual void TransRollback()        {            _IsTrans = false;            DbTransObj.Rollback();            CloseConnect();        }        /// <summary>        /// 執行一條指定命令類型(SQL語句或預存程序等)的SQL語句,返回所影響行數        /// </summary>        public virtual int ExecNonQuery(string sqlText, CommandType cmdType, params DbParameter[] param)        {            lock (_LockExecNonQuery)            {                try                {                    SetCommandAndOpenConnect(sqlText, cmdType, param);                    return DbCommandObj.ExecuteNonQuery();                }                catch (Exception ex)                {                    WriteErrLog(ex, sqlText, param);                    throw new DbDataException();                }                finally                {                    CloseConnect();                }            }        }        /// <summary>        /// 執行一條普通SQL語句的命令,返回所影響行數        /// </summary>        public virtual int ExecNonQuery(string sqlText, params DbParameter[] param)        {            return ExecNonQuery(sqlText, CommandType.Text, param);        }        /// <summary>                /// 獲得DataReader對象        /// </summary>        public virtual DbDataReader GetDataReader(string sqlText, CommandType cmdType, CommandBehavior cmdBehavior, params DbParameter[] param)        {            lock (_LockGetDataReader)            {                try                {                    SetCommandAndOpenConnect(sqlText, cmdType, param);                    DbDataReader dbReader = DbCommandObj.ExecuteReader(cmdBehavior);                    return dbReader;                }                catch (Exception ex)                {                    WriteErrLog(ex, sqlText, param);                    throw new DbDataException();                }                finally                {                    //DataReader用dbReader對象來關閉,即使非事務也是,不要把注釋取消                    //CloseConnect();                }            }        }        /// <summary>        /// 獲得DataReader對象        /// </summary>        public virtual DbDataReader GetDataReader(string sqlText, CommandType cmdType, params DbParameter[] param)        {            CommandBehavior cmdBehavior;            if (_IsTrans)            {                cmdBehavior = CommandBehavior.Default;            }            else            {                //非事務時,關閉DataReader則關閉當前串連                cmdBehavior = CommandBehavior.CloseConnection;            }            return GetDataReader(sqlText, cmdType, cmdBehavior, param);        }        /// <summary>        /// 執行sql語句返回DataReader對象        /// </summary>        public virtual DbDataReader GetDataReader(string sqlText, params DbParameter[] param)        {            return GetDataReader(sqlText, CommandType.Text, param);        }        /// <summary>        /// 獲得首行首列        /// </summary>        public virtual object GetScalar(string sqlText, CommandType cmdType, params DbParameter[] param)        {            lock (_LockGetScalar)            {                try                {                    SetCommandAndOpenConnect(sqlText, cmdType, param);                    return DbCommandObj.ExecuteScalar();                }                catch (Exception ex)                {                    WriteErrLog(ex, sqlText, param);                    throw new DbDataException();                }                finally                {                    CloseConnect();                }            }        }        /// <summary>        /// 執行SQL語句,返回首行首列        /// </summary>        public virtual object GetScalar(string sqlText, params DbParameter[] param)        {            return GetScalar(sqlText, CommandType.Text, param);        }        /// <summary>        /// 執行一條SQL語句返回DataSet對象        /// </summary>        public virtual DataSet GetDataSet(string sqlText, CommandType cmdType, params DbParameter[] param)        {            lock (_LockGetDataSet)            {                try                {                    SetCommandAndOpenConnect(sqlText, cmdType, param);                    DbDataAdapterObj.SelectCommand = DbCommandObj;                    DataSet ds = new DataSet();                    DbDataAdapterObj.Fill(ds);                    return ds;                }                catch (Exception ex)                {                    WriteErrLog(ex, sqlText, param);                    throw new DbDataException();                }                finally                {                    CloseConnect();                }            }        }        /// <summary>        /// 執行一條SQL語句返回DataSet對象        /// </summary>                public virtual DataSet GetDataSet(string sqlText, params DbParameter[] param)        {            return GetDataSet(sqlText, CommandType.Text, param);        }        /// <summary>        /// 執行一條SQL語句返回DataTable對象(調用GetDataSet)        /// </summary>                public virtual DataTable GetDataTable(string sqlText, params DbParameter[] param)        {            return GetDataTable(sqlText, CommandType.Text, param);        }        /// <summary>        /// 執行一條SQL語句返回DataTable對象(調用GetDataSet)        /// </summary>        public virtual DataTable GetDataTable(string sqlText, CommandType cmdType, params DbParameter[] param)        {            return (GetDataSet(sqlText, cmdType, param)).Tables[0];        }    }

　　

下面是一個sqlserver的實現

 public class SQLHelper : DbHelperBase    {        public SQLHelper(string connStr)            : base(connStr)        { }        SqlConnection _DBConnectionObj;        SqlCommand _DbCommandObj;        SqlDataAdapter _DbDataAdapterObj;        protected override DbConnection DBConnectionObj        {            get            {                //SqlBulkCopy aa = new SqlBulkCopy(new SqlConnection());                if (_DBConnectionObj == null)                {                    _DBConnectionObj = new SqlConnection(_ConnStr);                }                return _DBConnectionObj;            }        }        protected override DbCommand DbCommandObj        {            get            {                if (_DbCommandObj == null)                {                    _DbCommandObj = new SqlCommand();                }                return _DbCommandObj;            }        }        protected override DbDataAdapter DbDataAdapterObj        {            get            {                if (_DbDataAdapterObj == null)                {                    _DbDataAdapterObj = new SqlDataAdapter();                }                return _DbDataAdapterObj;            }        }    }

　　

使用樣本:

            IDbHelper sql = new SQLHelper("連接字串");            try            {                sql.TransStart(); //事務開始                sql.ExecNonQuery("執行sql語句1"); //執行(增刪改查)一條sql語句                sql.ExecNonQuery("執行sql語句2"); //執行(增刪改查)一條sql語句                DataTable dt = sql.GetDataTable("查詢sql語句1"); //擷取一些資料                sql.TransCommit(); //提交事務            }            catch (Exception ex)            {                sql.TransRollback(); //交易回復                throw;            }

　　

 

C# 操作資料庫就的那點代碼