DBHelper implementation of C # Lightweight ORM Framework (III)

The weekend is more leisurely, the ORM framework of their own to organize the next, open source.

What has been done usually feels simple, some novice may hear the word "frame" feel some "advanced", in short, ORM is the encapsulation of ADO.

In the introduction of this framework of the first blog post, has introduced dalbase design ideas, the DBHelper object of this article is also for dalbase to use, it can be said that all the framework of the definition of the object is for it.

Here is called DBHelper, because I also started from writing SqlHelper, DBHelper is just all types of ADO operation of the various methods of encapsulation, so this blog post hope to give a new C #, or a vague understanding of ADO some help.

The first dbhelper definition is abstract, because I don't know what kind of database dalbase is going to access, but I know that no matter which database has a link string, I would ask that you have a connection string.

 Public Dbhelperbase (string  connstr)        {            = connstr;        }

View Code

DBHelper is an abstraction, and its members must not have specific objects.

Then define the objects that are commonly used by ADO. (If you are a beginner, my suggestion is to use which object to define, otherwise I do not know what to define it to do later)

        protected AbstractDbConnection Dbconnectionobj {Get; } protected AbstractDbCommand Dbcommandobj {Get; } protected AbstractDbDataAdapter Dbdataadapterobj {Get; } protecteddbtransaction dbtransobj;  PublicDbConnection currentconnection {Get            {                returnDbconnectionobj; }        }  

Explain why a transactional object is not abstract, because a transactional object is always created with dbconnection, and I don't have to know what type it is.

The DbConnection object public is also available to extend other functions.

Defines whether a transaction is a variable, and all ADO operations determine whether the tag is currently in the transaction.

BOOL false;

Beginners. NET friends, should have a SqlHelper class, I have seen all the same, and generally do not have the realization of affairs, if you are shot, then congratulations, you will change your sqlhelper realization.

The following code:

 // <summary>        ///executes an SQL statement that specifies the type of command (SQL statement or stored procedure, etc.) that returns the number of rows affected/// </summary>         Public intExecnonquery (stringSQLText, CommandType Cmdtype,paramsdbparameter[] param) {            using(SqlConnection conn =NewSqlConnection (_CONNSTR)) {                using(SqlCommand cmd =NewSqlCommand (SQLTEXT, conn)) {Cmd.commandtype=Cmdtype; if(param! =NULL) cmd.                    Parameters.addrange (param); Conn.                    Open (); returncmd.                ExecuteNonQuery (); }            }        }

The above code seems to be fine, but if you want to enable the transaction, think about whether it can be implemented?

If you're going to change the code for the business.

As follows:

SqlTransaction Tran == Tran;   

This is obviously wrong, because we tend to have multiple execution statements that are often called execnonquery () methods in a number of times.

The encapsulation of such an ADO method is obviously unreasonable.

If multiple additions and deletions (note: The query may also be in the transaction) method, in a transaction, then must be a database connection (Connection)

This is one of the reasons why the three objects are defined outside, and the most important reason is that I need subclasses to rewrite it.

Here's a look at my implementation:

        /// <summary>        /// Open the connection, and if it's already open, nothing         is done. /// </summary>        void OpenConnection ()        {            if (dbconnectionobj.state! = ConnectionState.Open            )                {= _connstr;                Dbconnectionobj.open ();            }        }

        /// <summary>        ///assigns a value to the current DbCommand object, and OpenConnection (); /// </summary>        voidSetcommandandopenconnect (stringSQLText, CommandType Cmdtype,paramsdbparameter[] param) {            //It is not necessary to assign a value Connection,commandtypeDbcommandobj.commandtype =Cmdtype; Dbcommandobj.connection=Dbconnectionobj;            DbCommandObj.Parameters.Clear (); if(param! =NULL) {DbCommandObj.Parameters.AddRange (param); } Dbcommandobj.commandtext=SQLText;        OpenConnection (); }

        /// <summary>        ///executes an SQL statement that specifies the type of command (SQL statement or stored procedure, etc.) that returns the number of rows affected/// </summary>         Public intExecnonquery (stringSQLText, CommandType Cmdtype,paramsdbparameter[] param) {            Try{setcommandandopenconnect (sqltext, Cmdtype, param); returnDbcommandobj.executenonquery (); }            Catch(Exception ex) {Throwex; }            finally{closeconnect (); }        }

See these three methods may be confused for beginners, do not see anything about the transaction code, take a circle, now imagine what to do if you add a transaction?

Let's start with a theoretical understanding of the process of transaction processing

1. Specify which connection the transaction is

A transaction object of 2.Command is specified to the transaction.

3.Open ()

4. Commit or rollback (I am a place where I can write Chinese characters and never write pinyin)

5. Close the connection.

Continue to say these methods, why define Setcommandandopenconnect and OpenConnection these two methods, in line with the principle of minimizing duplication of code. That's all.

Now that connection and command have been defined outside of the method, it means that I just need to assign a value to the Command.executenonquery () method before executing it.

That is, starting a transaction only needs to assign a value to the two object.

The relevant code for the transaction is as follows:

        /// <summary>        ///start the execution of a transaction/// </summary>         Public voidTransstart () {openconnection (); Dbtransobj=dbconnectionobj.begintransaction (); Dbcommandobj.transaction=Dbtransobj; _istrans=true; }        /// <summary>        ///Transaction Commit/// </summary>         Public voidTranscommit () {_istrans=false;            Dbtransobj.commit ();        Closeconnect (); }        /// <summary>        ///Transaction Rollback/// </summary>         Public voidTransrollback () {_istrans=false;            Dbtransobj.rollback ();        Closeconnect (); }

This is the way of the business.

The last Closeconnect () method, almost forgot it.

        /// <summary>        /// Close the connection if no transaction is started or if the connection is        open /// </summary>        void Closeconnect ()        {            if (!  _istrans)            {                if (dbconnectionobj.state = = ConnectionState.Open)                {                    Dbconnectionobj.close ();                    Dbconnectionobj.dispose ();         }}}

When a transaction is started, the connection cannot be closed. Only the current connection will be broken if it is submitted or rolled back.

In fact, the design of the dbhelper is basically complete, and then paste a few methods about the query, and the implementation of similar does not explain.

 // <summary>        ///get first line column/// </summary>         Public ObjectGetscalar (stringSQLText, CommandType Cmdtype,paramsdbparameter[] param) {            Try{setcommandandopenconnect (sqltext, Cmdtype, param); returndbcommandobj.executescalar (); }            Catch(Exception ex) {Throwex; }            finally{closeconnect (); }}/// <summary>        ///executes an SQL statement to return a DataSet object/// </summary>         PublicDataSet GetDataSet (stringSQLText, CommandType Cmdtype,paramsdbparameter[] param) {            Try{setcommandandopenconnect (sqltext, Cmdtype, param); Dbdataadapterobj.selectcommand=Dbcommandobj; DataSet DS=NewDataSet ();                Dbdataadapterobj.fill (DS); returnds; }            Catch(Exception ex) {Throwex; }            finally{closeconnect (); }}/// <summary>        ///get DataReader Object/// </summary>         PublicDbDataReader Getdatareader (stringSQLText, CommandType Cmdtype,paramsdbparameter[] param) {            Try{setcommandandopenconnect (sqltext, Cmdtype, param); CommandBehavior Cmdbehavior=commandbehavior.closeconnection; if(_istrans) {Cmdbehavior=Commandbehavior.default; } DbDataReader dbreader=Dbcommandobj.executereader (Cmdbehavior); returnDbreader; }            Catch(Exception ex) {Throwex; }            finally            {                //DataReader use Dbreader object to close//Closeconnect ();            }        }

It is important to note that you cannot turn off connect, and the assignment of Cmdbehavior when returning a DataReader object.

Well, to this dbhelper the design and the core code have all been implemented.

Just imagine what I need to do now to complete the sqlserverhelper implementation.

Of course, just implement the few abstract attributes of the parent class.

The code is as follows:

  Public classSqlhelper:dbhelperbase { PublicSQLHelper (stringconnstr):Base(connstr) {} SqlConnection _dbconnectionobj;        SqlCommand _dbcommandobj;        SqlDataAdapter _dbdataadapterobj; protected OverrideDbConnection dbconnectionobj {Get            {                //SqlBulkCopy AA = new SqlBulkCopy (new SqlConnection ());                if(_dbconnectionobj = =NULL) {_dbconnectionobj=NewSqlConnection (_CONNSTR); }                return_dbconnectionobj; }        }        protected OverrideDbCommand dbcommandobj {Get            {                if(_dbcommandobj = =NULL) {_dbcommandobj=NewSqlCommand (); }                return_dbcommandobj; }        }        protected OverrideDbDataAdapter dbdataadapterobj {Get            {                if(_dbdataadapterobj = =NULL) {_dbdataadapterobj=NewSqlDataAdapter (); }                return_dbdataadapterobj; }        }    }

View Code

Oraclehelper,oledbhelper,sqlitehelper, no code is posted.

Reprint suggestion to mark the source (I never force others to do things I can not control).

The source code for the entire framework is in the first post of the introduction framework.

Welcome to Spit Groove, praise, suggestions, criticism, correction, plagiarism.