Building a common database access class using design patterns

Access | design | data | Database Use design pattern to build common database access classes

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.

This requires us to encapsulate these database access classes once in the actual development process. This package can not only achieve the above goals, but also reduce the operation of the database steps, reduce the amount of code written. In this regard, Microsoft provides us with Application block, but unfortunately, only SQL Server is currently supported. In this paper, we introduce an implementation strategy which has a very good effect in practical application--the data access structure in the WEBSHARP framework written by the author. The factory design pattern is the main method 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 only list fewer methods here, and other methods are easy to reference.

public interface DataAccess

{

DatabaseType Databasetype{get}//Database type

IDbConnection Dbconnection{get}//Get database connection

void Open (); Open a database connection

void Close (); To close a database connection

IDbTransaction BeginTransaction (); Start a transaction

int ExecuteNonQuery (string commandtext); Execute SQL statement

DataSet ExecuteDataset (string commandtext);//Execute SQL, return dataset

}




Because there are some common approaches to the DataAccess implementation class, we first implement an abstract Abstractdataaccess class from dataaccess, including some common methods. We then write three specific implementation classes for SQL Server, Oracle, and OLE DB databases for data access:

public sealed class Mssqldataaccess:abstractdataaccess

{

....//Implement code concretely.

}



public class Oledbdataaccess:abstractdataaccess

{

....//Implement code concretely.

}



public class Oracledataaccess:abstractdataaccess

{

....//Implement code concretely.

}




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 return the appropriate database manipulation classes based on the database type.

public sealed class Dataaccessfactory

{

Private Dataaccessfactory () {}

private static Persistenceproperty Defaultpersistenceproperty;

public static Persistenceproperty Defaultpersistenceproperty

{

Get{return Defaultpersistenceproperty;}

Set{defaultpersistenceproperty=value;}

}

public static DataAccess createdataaccess (Persistenceproperty pp)

{

DataAccess dataaccess;

Switch (pp. DatabaseType)

{

Case (Databasetype.mssqlserver):

DataAccess = new Mssqldataaccess (pp. ConnectionString);

Break

Case (Databasetype.oracle):

DataAccess = new Oracledataaccess (pp. ConnectionString);

Break

Case (databasetype.oledbsupported):

DataAccess = new Oledbdataaccess (pp. ConnectionString);

Break

Default

Dataaccess=new mssqldataaccess (pp. ConnectionString);

Break

}

return dataaccess;

}

public static DataAccess createdataaccess ()

{

Return createdataaccess (Defaultpersistenceproperty);

}

}




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

Persistenceproperty pp = new Persistenceproperty ();

Pp. ConnectionString = "server=127.0.0.1;uid=sa;pwd=;d atabase=northwind;";

Pp. DatabaseType = DatabaseType. MSSQLServer;

Pp. UserID = "sa";

Pp. Password = "";

DataAccess db= dataaccessfactory.createdataaccess (PP)

Db. Open ();

...//db. Required operation

Db. Close ();



Alternatively, if you set the Dataaccessfactory defaultpersistenceproperty attribute beforehand, you can use the

DataAccess db= dataaccessfactory.createdataaccess ()

method to create a DataAccess instance.




When the database changes, only need to modify the value of Persistenceproperty, the client will not feel the change, do not 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.

Above, introduced a general database operation class Realization design method, hoped can have the inspiration to everybody. All source code, can be downloaded from www.websharp.org, or to http://www.uml.org.cn/dvbbs6.0.0/index.asp for discussion. In addition to the data access source code, you can also download to the full Websharp source code.