Use design patterns to build a common database access classes Author: SUN Ya-min www.ASPCool.com Time: 2003-7-30 9:37:57 Views: 6698
In the design of the application, database access is very important, we usually need to focus on the database's access 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 the database connection public abstract void Open (); // Open Database Connectivity public abstract void Close (); // close the connection public abstract void BeginTrans (); / / Start a transaction public abstract void committrans (); // Submit a transaction public abstract voidbacktrans (); // roll back a transaction public Abstract void exSQL (String strsql, string [] strparams, object [] objvalues); // Execute the SQL statement, no return value public abstract DataSet ExesqlFordataset (String queryString); // Execute SQL, return to DataSet} then, we write two data access to both data access to SQL Server and OLEDB database: SQL Server database Access classes : internal class SqlDBOperator: DBOperator {private SqlConnection conn; // database connection private SqlTransaction trans; // transaction based private bool inTransaction = false; // indicates whether the current transaction is in the 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 (); intractions = true;} public override void commits () {Trans.commit (); IntraSaction = 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 paramatuenotmatChexception ("Query parameters and values do not correspond! "); Cmd.commandtext = strsql; if (strparams! = Null) {for (int i = 0; i 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 () {ix (conn.state.tostring (). TouPper ()! = "open") this.conn.open ();} public override void close ()} (conn) .State.Tostring () .oupper () == "open") this.conn.close ();} public override void begintrans (); IntraSaction = true;} public override void CommitTrans (); inTransaction = true; ) {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; (strparams! = null) && (strparams.length! = strvalues.length)) throw new paramatuenotmatChexception ("Query parameters and No value! "); Cmd.commandtext = this.changeQueryString (strsql); if (strparams! = Null) {for (int i = 0; i DataSet DS = New Dataset (); OLEDBDataAdapter Ad = New OLEDBDataAdapter (); cmd.comMandText = queryString; ad.selectcommand = cmd; ad.fill (ds); return ds;} Now we have completed the function, 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: DBOPERATOR DB = DBOPERATORFAACTORY.GETDBOPERATOR (STRCONNECTION) DB.Open (); db. Needed DB .Close (); or: dboperator db = dboperatorfactory.getdboperator () DB.Open (); db.begintrans (); try {db. Requires 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 why the DBOPERATOR is an abstract class rather than an interface (the method of the interface cannot be implemented), 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, client code may be similar to the following form: DBOPERATOR DB = dboperator.instance (strconnection) DB.Open (); DB. What you need DB.Close (); look at the connection pool practice, 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.
