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

After the weekend, I spent some time working out my own orm framework and made it open-source.

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 ;}View Code

 

DbHelper is abstract, so its members cannot have specific objects.

Define the commonly used objects in ado.net)

        protected abstract DbConnection DBConnectionObj { get; }        protected abstract DbCommand DbCommandObj { get; }        protected abstract DbDataAdapter DbDataAdapterObj { get; }        protected DbTransaction DbTransObj;        public DbConnection CurrentConnection        {            get            {                return DBConnectionObj;            }        }  

Explain why the transaction object is not abstract, because the transaction object is always created with DbConnection, and 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:

/// <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 ();}}}

The above Code seems to be okay, but if you want to enable the transaction, can you implement it?

If you just need to modify the code with the transaction

As follows:

 SqlTransaction tran = conn.BeginTransaction(); cmd.Transaction = tran;   

This is obviously wrong, because we usually call the ExecNonQuery () method multiple times.

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.

Let's take a look at my implementation:

/// <Summary> /// open the connection. if the connection has been enabled, nothing is executed. // </summary> void OpenConnection () {if (DBConnectionObj. State! = ConnectionState. Open) {DBConnectionObj. ConnectionString = _ ConnStr; DBConnectionObj. Open ();}}

/// <Summary> /// assign a value to the current DbCommand object and OpenConnection (); // </summary> void SetCommandAndOpenConnect (string sqlText, CommandType specify type, params DbParameter [] param) {// assign a value to Connection and CommandType, Which is DbCommandObj without multiple values. commandType = commantype; DbCommandObj. connection = DBConnectionObj; DbCommandObj. parameters. clear (); if (param! = Null) {DbCommandObj. Parameters. AddRange (param);} DbCommandObj. CommandText = sqlText; OpenConnection ();}

/// <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) {throw ex;} finally {CloseConnect ();}}

It may be confusing for beginners to see these three methods. If they don't see any code about transactions, let's take a look. 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 for the transaction

2. The transaction object of the Command is specified to the 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.

Since both the Connection and Command have been defined outside the method, that is to say, I only need to assign values to them before executing the Command. ExecuteNonQuery () method.

That is, to start a transaction, you only need to assign values to the two objects.

The transaction code is as follows:

/// <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 ();}

This is the transaction method.

The last CloseConnect () method almost forgot it.

/// <Summary> /// close the connection. if the transaction is not started or the connection is opened, close the connection. /// </summary> void CloseConnect () {if (! _ IsTrans) {if (DBConnectionObj. State = ConnectionState. Open) {DBConnectionObj. Close (); DBConnectionObj. Dispose ();}}}

When starting a transaction, the connection cannot be closed. The current connection will be disconnected only after the transaction is committed 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.

/// <Summary> /// obtain the first column of the First line // </summary> public object GetScalar (string sqlText, CommandType parameter type, params DbParameter [] param) {try {SetCommandAndOpenConnect (sqlText, partition 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 = CommandBehavior. default;} DbDataReader dbReader = DbCommandObj. executeReader (describehavior); return dbReader;} catch (Exception ex) {throw ex;} finally {// DataReader uses the dbReader object to close // CloseConnect ();}}

Note that when returning the DataReader object, Connect and describehavior values cannot be closed.

 

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 ;}}}View Code

OracleHelper, oledbhelper, and SQLiteHelper.

 

Repost the suggestion to indicate the source (I never force others to do what I can't do ).

The source code of the entire framework is included in the first blog post about the framework.

You are welcome to speak out, like, suggest, criticize, correct, and copy.