Building common database access classes by using Factory mode and single-piece mode __ Database

Access to the database is important in the design of the application, and we often need to centralize access to the database to ensure good encapsulation and maintainability. In. NET, database access, for Microsoft's own SQL Server and other databases (supporting OLE DB), uses different access methods, which are distributed in the System.Data.SqlClient and System.Data.OleDb namespaces, respectively. Microsoft later launched a class library dedicated to accessing Oracle databases. We hope that when the application system is written, not because of so many different classes are affected, can try to do database-independent, when the background database changes, do not need to change the client code.

Sometimes, for performance and other reasons, we also want to provide caching for database access, especially for database connections. Although Microsoft has built a database cache for us, it can undoubtedly provide greater flexibility and efficiency by controlling the cache itself.

This requires us to encapsulate these database access classes once in the actual development process. In this paper, we introduce an implementation strategy which has a very good effect in practical application. Factory and Silgleton Design patterns are the primary methods used.

Let's take a look at the meaning of factory: Define an interface for creating objects, and let subclasses decide which class to instantiate. Factory method causes the instantiation of a class to be deferred to its subclasses. We may be dealing with a variety of databases here, so we need to first define an interface that manipulates the database, and then, depending on the database, the class factory decides which class to instantiate.

Next, we first define this provider. To help illustrate the problem, we've defined fewer methods for this class, and other methods are easy to reference. Also note that I use abstract class here to define this access interface, not interface, for reasons that can be seen later.

Public abstract class Dboperator {public abstract idbconnection connection{get;} Get the database connection public abstract void Open (); Open the database connection public abstract void Close (); Closes the database connection public abstract void BeginTrans (); Start a transaction public abstract void CommitTrans (); Commits a transaction public abstract void RollbackTrans (); Rollback a transaction public abstract void Exesql (string strsql,string[] strparams,object[] objvalues); Execute SQL statement with no return value public abstract DataSet Exesqlfordataset (string querystring);//Execute SQL, return DataSet }

Then, we write two specific implementation classes for SQL Server and OLE DB databases for data access:

Database access classes for SQL Server:

Internal class Sqldboperator:dboperator {private SqlConnection conn; Database connection private SqlTransaction trans; Transaction processing class private bool intransaction=false; Indicates whether the current is in the transaction public override IDbConnection Connection {Get{return this.conn;} Public Sqldboperator (String strconnection) {this.conn= new SqlConnection (Strconnection); public override void Open () {if (conn). State.tostring (). ToUpper ()!= "OPEN") This.conn.Open (); public override void Close () {if (conn). State.tostring (). ToUpper () = = "OPEN") this.conn.Close (); public override void BeginTrans () {trans=conn. 　　　　BeginTransaction (); Intransaction=true; 　　　　public override void CommitTrans () {trans.commit (); Intransaction=false; public override void RollbackTrans () {trans. 　　　　Rollback (); Intransaction=false; public override void Exesql (string strsql,string[] strparams,object[] strvalues) { 　　SqlCommand cmd=new SqlCommand (); Cmd. 　　Connection=this.conn; if (intransaction) cmd. 　　Transaction=trans; if ((strparams!=null) && (strparams.length!=strvalues.length)) throw new Paramvaluenotmatchexception (" Query parameters and values do not correspond! "); 　　Cmd.commandtext=strsql; if (Strparams!=null) {for (int i=0;i<strparams.length;i++) cmd. Parameters.Add (Strparams[i],strvalues[i]); } cmd. ExecuteNonQuery (); public override DataSet Exesqlfordataset (string querystring) {SqlCommand cmd=new SqlCommand (); Cmd. 　　Connection=this.conn; if (intransaction) cmd. 　　Transaction=trans; 　　DataSet ds = new DataSet (); 　　SqlDataAdapter ad = new SqlDataAdapter (); 　　cmd.commandtext=querystring; Ad. 　　SelectCommand =cmd; Ad. 　　Fill (DS); 　　return DS; } 　　　　}

OLE DB database operations have a similar class of SQL Server database operations, except that the corresponding SQL classes are replaced with OLE DB classes. It's important to note that because OLE DB and SQL Server have inconsistent parameter passing, there's a little bit of a transformation that converts arguments of the "@ parameter name" type to ". This detail is expected to be noticed by the reader. The code is as follows:

Internal class Oledboperator:dboperator {private OleDbConnection conn; Private OleDbTransaction Trans; private bool Intransaction=false; Public Oledboperator (String strconnection) {this.conn= new OleDbConnection (strconnection); public override IDbConnection Connection {Get{return this.conn;} public override void Open () {if (conn). State.tostring (). ToUpper ()!= "OPEN") This.conn.Open (); public override void Close () {if (conn). State.tostring (). ToUpper () = = "OPEN") this.conn.Close (); public override void BeginTrans () {trans=conn. 　　　BeginTransaction (); Intransaction=true; 　　public override void CommitTrans () {trans.commit (); Intransaction=false; public override void RollbackTrans () {trans. 　　Rollback (); Intransaction=false; public override void Exesql (string strsql,string[] strparams,object[] strvalues) {OleDbCommand cmd=new Oledbcomm 　 and (); Cmd. Connection=this.conn; if (intransaction) cmd. Transaction=trans; if ((strparams!=null) && (strparams.length!=strvalues.length)) throw new Paramvaluenotmatchexception (" Query parameters and values do not correspond! "); Cmd.commandtext=this.changequerystring (strSQL); if (Strparams!=null) {for (int i=0;i<strparams.length;i++) cmd. Parameters.Add (Strparams[i],strvalues[i]); } cmd. ExecuteNonQuery (); public override DataSet Exesqlfordataset (string querystring) {OleDbCommand cmd=new OleDbCommand (); Cmd. 　　Connection=this.conn; if (intransaction) cmd. 　　Transaction=trans; 　　DataSet ds = new DataSet (); OleDbDataAdapter ad = new OleDbDataAdapter (); 　　cmd.commandtext=querystring; Ad. 　　SelectCommand =cmd; Ad. 　　Fill (DS); 　　return DS; } }

Now that we have finished the function we want, we need to create a factory class to implement the management of automatic database switching. This class is simple, and the main function is to determine what database to use based on the database connection string, and then return to the appropriate database manipulation class. Here, the method of judgment is simple, based on the differences between the two database connection strings. In practice, as the database class increases, the method of judgment may change, and the readers should make corresponding adjustment according to their own actual situation.

public class Dboperatorfactory {public static Dboperator getdboperator (string strconnection) { if (　　Strconnection.indexof ("provider=") <0)//sqlserver {return new Sqldboperator (strconnection); } else //other database {return new Oledboperator (strconnection); } }}

Well, now that everything is done, the client may be using the following form when the code is called:

Dboperator db=dboperatorfactory.getdboperator (strconnection) db. Open (); db. Required operations DB. Close (); Or: dboperator db=dboperatorfactory.getdboperator (strconnection) db. Open ();d B. BeginTrans (); Try { db. Required Operation Db.committrans (); } Catch { db. RollbackTrans (); } Db. Close ();

When the database changes, Dboperatorfactory will automatically invoke the corresponding class according to the database connection string, and the client will not feel the change or care. In this way, a good encapsulation is achieved. Of course, if you're writing a program, you're not using specific database features, such as SQL Server's proprietary functions.

In fact, the factory pattern can be implemented without the factory class, but rather let the interface abstract class manage itself, which can be called a self managed factory, a variant of the factory pattern. The advantage of doing so is that you can dispense with a factory class to make your code more concise. In doing so, we need to make some changes to the Dboperator class and add a instance method. This is also the reason for Dboperator to take an abstract class rather than an interface (the method of an interface cannot have an implementation), and the code is as follows:

public static Dboperator Instance (string strconnection) { if (Strconnection.indexof ("provider=") <0)// SQL Server {return new Sqldboperator (strconnection); } else //other database {return new Oledboperator (strconnection); } }

The client code can then be similar to the following form:

Dboperator db= dboperator.instance (strconnection) db. Open (); db. Required operations DB. Close ();

Let's take a look at the connection pool approach by Singleton.

First look at the classic meaning of the singleton pattern: Ensure that a class has only one instance and provide a global access point to access it. By extension, we can use the singleton mode when we need to precisely control the number of instances of a class in the system. Now, we need to build a buffer pool to hold an instance of the database class, just when the singleton mode works.

We still have the Dboperator class manage this buffer pool ourselves, and to do this we need to make some changes to the Dboperator class:

First, add two variables:

Static dboperator[] Connectionpool=new dboperator[int.　Parse (configurationsettings.appsettings["Poolcount"])]; static int currentposition=-1;

Then, make some changes to the instance method:

public static Dboperator Instance (string strconnection) { if ( APPLICATIONCONFIGURATION.POOLEDCONNECTIONCOUNT<1) //No buffer {return createnewdboperator ( strconnection); } else { currentposition++; if (currentposition==applicationconfiguration.pooledconnectioncount) currentposition=0; if (connectionpool[currentposition]==null) { Connectionpool[currentposition]=createnewdboperator ( strconnection); } return connectionpool[currentposition]; } private static Dboperator Createnewdboperator (string strconnection) { if (Strconnection.indexof ("Provider = ") <0)//sqlserver {return new Sqldboperator (strconnection); } else //other database {return new Oledboperator (strconnection); } }

The algorithm used here is simple, just to be able to clearly explain the problem, the reader should be able to use the actual process to achieve better algorithms.

Above, introduced a general database operation class Realization design method, hoped can have the inspiration to everybody. When the author design Websharp middleware, the above method is adopted in the database processing layer, and the result is very good. If you need complete source code, you can contact the author.

Author: Sun Yamin, graduated from Nanjing University, China University of Science and Technology in the postgraduate computer technology, familiar with Java and. NET platform, Suzhou, a software company technical director, based on the. NET platform of Websharp middleware designers. Can be contacted by sunny_y_m@163.com.