Use design patterns to build a common database access classes Author: SUN Ya-min This article taken from: CCID March 20, 2003 in the design of the application, access to the database is very important, we will usually need to access the database together, To ensure good encapsulation and maintainability. In .NET, database access, for Microsoft's own SQLServer and other databases (support OLEDB), which are distributed in system.data.sqlclient and System.Data.OLDB namespaces. Microsoft launched a class library specifically for accessing the Oracle database. We hope that when writing the application system, it is not impact on such a variety of classes, and it is possible to do the database without correlation. When the background database changes, the client's code does not need to change the code. Sometimes, for performance and other reasons, we also want to provide a cache for database access, especially database connections. Although Microsoft gives us a built-in database buffer, it controls the cache, which can undoubtedly provide greater flexibility and efficiency. This requires us to access the classes in the actual development process to make a package. Here, a practical policy that has been very good in practical applications is introduced. Factory and Silgleton design patterns are the main ways to use. Let's take a look at the meaning of Factory: Define an interface for creating an object, let the subclass determine which class. Factory Method delays a class of instantiation to their subclasses. We may handle the operation of multiple databases, so we need to first define an interface of the database, and then according to the database, which is determined which class is determined by the class factory. Below, we first define this access interface. For the convenience of explanation, we define less methods for this class, and other methods are easy to refer to the added. At the same time, I use the Abstract Class here to define this access interface, not Interface, the reason can be seen later.
Public Abstract Class Dboperator
{
Public Abstract IdbConnection Connection {get;} // Get database connection
Public abstract void open (); // Open database connection
Public abstract void close (); // Turns off database connection
Public Abstract void begintrans (); // Start a transaction
Public Abstract void committrans (); // Submit a transaction
Public abstract void rollbacktrans (); // roll back a transaction
Public Abstract Void Exesql (String Strsql, String [] strparams, object [] objvalues);
// Execute SQL statement, no return value
Public Abstract DataSet EXESQLFORDATSET (STRING QueryString); // Execute SQL, return to DataSet
}
Then, we write the specific implementation classes of two data access for SQL Server and OLEDB databases: SQL Server database access class:
INTERNAL CLASS SQLDBOPERATOR: DBOPERATOR
{
Private SqlConnection Conn; // Database Connection Private Sqltransaction Trans; // Transaction Treatment Class
Private bool Intractions = false; // Indicates whether the current is in a transaction
Public Override IDBConnection Connection
{
Get {returnim.
}
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 (IntraSction)
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 cmd.Parameters.Add (Strparams [i], Strvalues [i]); } cmd.executenonquery (); } Public Override DataSet ExesqlFordataset (String QueryString) { SQLCommand cmd = new sqlcommand (); CMD.Connection = this.conn; IF (IntraSction) Cmd.Transaction = Trans; DataSet DS = New DataSet (); SqlDataAdapter ad = new sqldataadapter (); cmd.commandtext = querystring; Ad.selectcommand = cmd; Ad.fill (DS); Return DS; } } The class of OLEDB database operations is very similar to the SQL Server database operation, just replacing the corresponding SQL class into an OLEDB class. It should be noted that because the parameter transfer mode of OLEDB and SQL Server is inconsistent, so you need to do a small conversion, convert the "@ parameter name" type of parameter into "?", I hope the reader can notice. 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 {returnim. } 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 OLEDBCommand (); CMD.Connection = this.conn; IF (IntraSction) 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 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 we have completed the features you want, below, we need to create a Factory class to implement automatic database switching. This class is very simple, the main function is based on the database connection string, determines what database used, and then returns the appropriate database manipulation. Here, the method of judging is simple, just determined according to the difference between the two database connection strings. In practice, with the increase in database class, the method of judging may vary, and readers should adjust 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); } } } Ok, now, everything is completed, the client is called when the code is called, it may be the following form: DBOPERATOR DB = dboperatorfactory.getdboperator (strConnection) db.open (); DB. needed db.close (); or: DBOPERATOR DB = dboperatorfactory.getdboperator (strConnection) db.open (); db.begintrans (); Try { DB. needed db.committrans (); } Catch { db.rollbacktrans (); } db.close (); When the database changes, DBOPERATORFAActory automatically calls the corresponding class according to the database connection string, and the client will not feel changes, nor do you have to care. In this way, good encapsulation is achieved. Of course, the premise is that when you write a program, there is no feature of a specific database, for example, the dedicated function of SQL Server. In fact, Factory mode can also be implemented without using the Factory class, but allow interface abstraction classes to manage, which can be called self-managed Factory, a deformerization of Factory mode. The benefits of doing this are to avoid a Factory class to make the code more concise. Do this, we need to make some changes to the Dboperator class, add an instance method. This is also why DBOPERATOR uses an abstract class rather than an interface (the method of interface cannot be realized), the code is as follows: Public Static Dboperator Instance (String StrConnection) { IF (strconnection.indexof ("provider =") <0) // SQLServer { Return New SqldBoperator (STRCONNECTION); } Else // Other Database { Return New Oledboperator (STRCONNECTION); } } Then, the client code may be similar to the following form: DBOPERATOR DB = dboperator.instance (StrConnection) db.open (); DB. needed db.close (); Let's take a look at the connection of the connection pool, the method is Singleton. Look at the classic meaning of the Singleton mode: Ensure that a class has only one instance and provides a global access point access to it. It can be easily extended. When we need to accurately control the number of instances in the system, you can use the Singleton mode. Now, we need to build a buffer pool, save an instance of the database class, just when the Singleton mode works. We still let the DBoperator class manage this buffer pool. In order to achieve this, we need to make some changes to the DBOPERATOR class: First, add two variables: Static dboperator [] connectionpool = new DBOPERATOR [INT.PARS (ConfigurationSettings.AppSettings ["PoolCount"])]; Static int currentposition = -1; Then, do 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 [CURRENTPSITION] = 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); } }
