C # Implementation of DBHelper in a lightweight ORM framework (III)

What has been done is usually a bit simple. Some new users may hear the word "Framework" and think it is "advanced". In short, orm encapsulates ado. in the first blog post about this framework, DalBase has introduced the design idea. The DBHelper object in this article is also used for dalBase. It can be said that all the definition objects of the framework are for it. the name here is DBHelper, because I also started to write SQLHelper. DBHelper is only the encapsulation of all types of methods for ado operations. Therefore, this blog post is intended for beginners of c, or it is helpful to have a vague understanding of ado.net. first, DBHelper is defined as abstract, because I don't know which database DalBase is going to access, but I know that no matter which database has a link string, I will ask, A connection string is required. public DbHelperBase (string connStr) {_ ConnStr = connStr;} DbHelper is abstract. Personnel must not have specific objects. define commonly used ado.net objects. (If you are a beginner, I suggest you define which object to use. Otherwise, you do not know what to define it later.) copy the Code protected abstract DbConnection DBConnectionObj {get ;} protected abstract DbCommand DbCommandObj {get;} protected abstract DbDataAdapter DbDataAdapterObj {get;} protected DbTransaction DbTransObj; public DbConnection CurrentConnection {get {return DBConnectionObj, why is the transaction object not abstract, because the transaction object always has DbConnec I don't need to know what type it is. the DbConnection object public also exists to extend other functions. A variable that defines whether a transaction is performed. All ado operations will judge whether the transaction is currently in the mark. bool _ IsTrans = false; beginner. net friends, there should be a SQLHelper class, I have seen it all the same, and there is generally no transaction implementation, if you shot, then congratulations, you are about to change your sqlHelper implementation. the following code: copy the code /// <summary> /// execute an SQL statement of the specified command type (SQL statement or stored procedure, returned Number of affected rows // </summary> public int ExecNonQuery (string sqlText, CommandType limit type, params DbParameter [] param) {using (SqlConnection Conn = new SqlConnection (_ ConnStr) {using (SqlCommand cmd = new SqlCommand (sqlText, conn) {cmd. CommandType = partition type; if (param! = Null) cmd. parameters. addRange (param); conn. open (); return cmd. executeNonQuery () ;}} it looks okay to copy the code above, but if you want to enable the transaction, can you implement it? If the code for adding a transaction is modified, the statement is as follows: SqlTransaction tran = conn. beginTransaction (); cmd. transaction = tran; this is obviously wrong, because we usually call the ExecNonQuery () method multiple times in multiple execution statements. the encapsulation of such an ado method is obviously unreasonable. if multiple addition, deletion, modification, and query (note: the query may also be in a transaction) methods, in a transaction, it must be a database Connection) this is one of the reasons for defining the three objects to the outside. The most important reason is that I need the subclass to rewrite it. next let's take a look at my implementation: copy the code /// <summary> /// open the connection. If the connection is already opened, nothing is executed /// </summary> void OpenConnection () {if (DBConnectionObj. state! = ConnectionState. open) {DBConnectionObj. connectionString = _ ConnStr; DBConnectionObj. open () ;}} copy the code to copy the code /// <summary> /// assign a value to the current DbCommand object and OpenConnection (); /// </summary> void SetCommandAndOpenConnect (string sqlText, CommandType primitive type, params DbParameter [] param) {// assign values to Connection and CommandType, which are DbCommandObj without multiple values. commandType = commantype; DbCommandObj. connection = DBConnectionObj; DbCommandO Bj. Parameters. Clear (); if (param! = Null) {DbCommandObj. parameters. addRange (param);} DbCommandObj. commandText = sqlText; OpenConnection ();} copy the code and copy the code. // <summary> // execute an SQL statement of the specified command type (SQL statement or stored procedure, returns the number of affected rows /// </summary> public int ExecNonQuery (string sqlText, CommandType struct type, params DbParameter [] param) {try {SetCommandAndOpenConnect (sqlText, struct type, param ); return DbCommandObj. executeNonQuery ();} catch (Exception ex) {t Hrow ex ;} finally {CloseConnect () ;}} when you copy the code, you may be confused about the three methods. If you don't see any transaction-related code, you can go through a circle, now imagine what to do if a transaction is added? First, let's take a theoretical look at the transaction processing process 1. specify the Connection of the transaction. 2. the transaction object of the Command is specified to this transaction. 3. open () 4. submit or roll back (I can write Chinese characters without pinyin) 5. close the connection. let's continue with these methods. Why do we define the SetCommandAndOpenConnect and OpenConnection methods to minimize repeated code. that's all. since both Connection and Command have been defined outside the method, that is to say, I only need to execute Command again. assign values to the ExecuteNonQuery () method. that is, to start a transaction, you only need to assign values to the two objects. The related code of the transaction is as follows: copy the code /// <summary> /// start to execute the transaction // </summary> public void TransStart () {OpenConnection (); DbTransObj = DBConnectionObj. beginTransaction (); DbCommandObj. transaction = DbTransObj; _ IsTrans = true;} // <summary> // Transaction commit // </summary> public void TransCommit () {_ IsTrans = false; dbTransObj. commit (); CloseConnect () ;}/// <summary> /// transaction rollback /// </summary> public void TransRollback () {_ IsTrans = false; dbTransObj. rollback (); CloseConnect ();} copies the Code. This is the transaction method. the last CloseConnect () method almost forgot to copy the code. // <su Mmary> // close the connection. if the transaction is not started or the connection is opened, close it. // </summary> void CloseConnect () {if (! _ IsTrans) {if (DBConnectionObj. state = ConnectionState. open) {DBConnectionObj. close (); DBConnectionObj. dispose () ;}} copy the code. When the transaction starts, the connection cannot be closed. the current connection is disconnected only when the connection is submitted or rolled back. in fact, the DbHelper design is basically complete. I will post several query methods, which are similar to the execution and will not be explained here. copy the code /// <summary> /// obtain the first column of the First line // </summary> public object GetScalar (string sqlText, CommandType parameter type, params DbParameter [] param) {try {SetCommandAndOpenConnect (sqlText, plain type, param ); Return DbCommandObj. executeScalar ();} catch (Exception ex) {throw ex;} finally {CloseConnect ();}} /// <summary> /// execute an SQL statement to return the DataSet object // </summary> public DataSet GetDataSet (string sqlText, CommandType parameter type, params DbParameter [] param) {try {SetCommandAndOpenConnect (sqlText, partition type, param); DbDataAdapterObj. selectCommand = DbCommandObj; DataSet ds = new DataSet (); DbDataAdapterObj. Fill (ds); return ds;} catch (Exception ex) {throw ex;} finally {CloseConnect ();}} /// <summary> /// obtain the DataReader object // </summary> public DbDataReader GetDataReader (string sqlText, CommandType primitive type, params DbParameter [] param) {try {SetCommandAndOpenConnect (sqlText, partition type, param); CommandBehavior describehavior = CommandBehavior. closeConnection; if (_ IsTrans) {describehavior = CommandBehavi Or. default;} DbDataReader dbReader = DbCommandObj. executeReader (describehavior); return dbReader;} catch (Exception ex) {throw ex;} finally {// DataReader uses the dbReader object to close // CloseConnect ();}} copy the code. Note that the Connect and describehavior values cannot be disabled when the DataReader object is returned. now, the DBHelper design and core code have all been implemented. imagine what I need to do to implement SQLServerHelper now? Of course, you only need to implement the abstract attributes of the parent class. the Code is as follows: 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 ;}} copy the code OracleHelper, oledbhelper, and SQLiteHelper.