Write a portable data access layer

Write a portable data access layer. Release Date: 8/25/2004 | updated: 8/25/2004

Silvano coriani

Microsoft Corporation

Applicable:

Microsoft Visual Studio. NET 2003

Microsoft. NET Framework 1.1

Ado. net

Various RDBMS

Summary: Learn How to Write smart applications that transparently use different data sources (from Microsoft Access to SQL Server and Oracle RDBMS.

Content on this page

	Introduction
	Use common data access methods
	Use Basic Interfaces
	Write a dedicated data access layer
	Use the data pipeline class from other layers
	Possible improvements
	Conclusion

Introduction

During the past six years in charge of consulting, I have heard many times about Data Access and Operation Issues, which plague users: "How to Write Applications, so that you can use the database servers x, y, and z with little or no changes to it?" Because I know that the data access layer is still the most critical part of modern applications and is usually the number one enemy of experienced developers, my first response is always: I cannot do it!

In the face of uneasiness and "what is the general data access method provided by Microsoft in ado ?" For such a problem, I decided to provide more detailed instructions and recommended solutions for the problem.

The problem is that if the application is a small prototype, or if there are few concurrent users and the data access logic is relatively simple, even if you choose the following simplest method, there will be no problems either: Using RAD tools (such as data environment in Microsoft Visual Basic 6.0), or some "package" solutions (such as ActiveX Data Control and other third-party components ), these solutions usually hide complex interactions between applications and specific data sources. However, when the number of users increases and concurrent operations must be solved, many performance problems may occur due to frequent use of dynamic record sets, server-side cursors, and unnecessary locking policies. The Design and code changes you have to make to achieve your goals will take a lot of time, because you have not considered this issue from the beginning.

Back to Top

Use common data access methods

After the reliable integration of ADO into MDAC (Microsoft Data Access Components 2.1), Microsoft set off the use of universal data access. The main idea is to demonstrate to developers that by using simple object models ("connection", "command", and "record set "), you can compile applications that can be connected to different data sources, including relational data sources and non-relational data sources. What is not mentioned in the document (and most of the articles and examples at the time) is that even with the same data access technology, the programmability and features of various data sources vary significantly.

The result is that, in applications that need to obtain data from multiple data sources, the simplest way is to use the "commonalities" of the functions provided by all data sources ", however, the benefits of using specific data sources are lost, which provides the best way to access and operate information in various RDBMS databases.

My persistent suspicion of this method is that, after more detailed analysis with my customers, we usually agree that it is compared with other parts of the application that process display and business logic, interacting with the data source is only a small part of the application. Through careful modular design, specific RDBMS code can be isolated in some easily interchangeable modules, thus avoiding the use of "common" methods for data access. However, we can use very specific data access code (using stored procedures, command batch processing, and other features based on different data sources) without touching most other application code. This always reminds everyone that correct design is the key to writing portable effective code.

ADO. NET introduces some important changes to the field of data access encoding, such as the concept of dedicated. NET data providers. Use specific providers, it can bypass a large number of software interfaces and services that are sometimes unnecessary (they are the content inserted between the data access code and the database server at the ole db and ODBC layers ), connect to the data source in the best way. However, each data source still has different characteristics and features (with different SQL dialect), and efficient applications must still use these specific features rather than "common ". From the perspective of portability, managed and unmanaged data access technologies are still very similar.

Except for "using the unique features of a data source", other rules required to write a good data access layer are usually the same for each data source:

•	Use the connection pool mechanism whenever possible.
•	Saves limited resources on database servers.
•	Pay attention to the round-trip of the network.
•	When appropriate, enhance the execution plan's repeated usage and avoid repeated compilation.
•	Use the appropriate locking model to manage concurrency.

From my personal experience using modular design methods, the amount of code used by the entire application to process specific data sources does not exceed 10% of the total amount. Obviously, this is more complicated than simply modifying the connection strings in the configuration file, but I think this will get performance benefits, so this is an acceptable compromise.

Back to Top

Use Basic Interfaces

The purpose here is to use abstraction and encapsulate code specific to special data sources in the class layer, so that other parts of the application are independent of the back-end database server or are not affected.

The. NET Framework object-oriented feature will help us in this process, so that we can select the abstraction level to use. One option is the basic interface that must be implemented by every. NET data provider (Idbconnection,Idbcommand,Idatareader). Another option is to create a group of classes (data access layer) for managing all the data access logic of an application (for example, using the crud example ). To check the two possibilities, we first start with the order input application example based on the northwind database, and then insert and retrieve information from different data sources.

The basic interface of the data provider identifies the typical behavior required for the application to interact with the Data source:

•	Define the connection string.
•	Open and Close the physical connection to the data source.
•	Define commands and related parameters.
•	Execute different types of commands that can be created. • Returns a group of data. • Returns the scalar value. • Perform operations on data without returning any content.
•	Only forward access and read-only access are provided to the returned dataset.
•	Defines a set of operations required to synchronize the content of a dataset with a data source (data adapter.

But in fact, if you want to encapsulate the operations required to retrieve, insert, update, and delete information from different data sources (using different data providers) in the data access layer, and only publish the members of the basic interface, the first level of abstraction can be implemented-at least from the perspective of the data provider. Let's take a look at the following code to demonstrate this design idea:

Using system; using system. data; using system. data. common; using system. data. sqlclient; using system. data. oledb; using system. data. oracleclient; namespace Dal {public Enum databasetype {access, sqlserver, Oracle // any other data source type} public Enum parametertype {INTEGER, Char, varchar // defines public parameter type set} public class DataFactory {private DataFactory () {} public static idbconnection createconnection (string connectionstring, databasetype dbtype) {idbconnection CNN; Switch (dbtype) {Case databasetype. access: CNN = new oledbconnection (connectionstring); break; Case databasetype. sqlserver: CNN = new sqlconnection (connectionstring); break; Case databasetype. ORACLE: CNN = new oracleconnection (connectionstring); break; default: CNN = new sqlconnection (connectionstring); break;} return CNN;} public static idbcommand createcommand (string commandtext, databasetype dbtype, idbconnection CNN) {idbcommand cmd; Switch (dbtype) {Case databasetype. access: cmd = new oledbcommand (commandtext, (oledbconnection) CNN); break; Case databasetype. sqlserver: cmd = new sqlcommand (commandtext, (sqlconnection) CNN); break; Case databasetype. ORACLE: cmd = new oraclecommand (commandtext, (oracleconnection) CNN); break; default: cmd = new sqlcommand (commandtext, (sqlconnection) CNN); break;} return cmd ;} public static dbdataadapter createadapter (idbcommand cmd, databasetype dbtype) {dbdataadapter da; Switch (dbtype) {Case databasetype. access: da = new oledbdataadapter (oledbcommand) cmd); break; Case databasetype. sqlserver: da = new sqldataadapter (sqlcommand) cmd); break; Case databasetype. ORACLE: da = new oracledataadapter (oraclecommand) cmd); break; default: da = new sqldataadapter (sqlcommand) cmd); break;} return da ;}}}

This class is used to hide details related to creating instances of specific types (from specific data providers) to a higher level of applications, applications can now interact with data sources using common behaviors exposed through basic interfaces.

Let's take a look at how to use this class from other parts of the application:

Using system; using system. data; using system. data. common; using system. configuration; namespace Dal {public class customersdata {public datatable getcustomers () {string connectionstring = configurationsettings. appsetetype ["connectionstring"]; databasetype dbtype = (databasetype) enum. parse (typeof (databasetype), configurationsettings. appsettings ["databasetype"]); idbconnection CNN = DataFactory. createconnection (connectionstring, dbtype); string connector string = "select customerid" + ", companyName, contactname from MERs"; idbcommand cmd = DataFactory. createcommand (Response string, dbtype, CNN); dbdataadapter da = DataFactory. createadapter (CMD, dbtype); datatable dt = new datatable ("MERs"); DA. fill (DT); Return DT;} public customersds getcustomerorders (string customerid) {// tbreturn NULL;} public customerslist getcustomersbycountry (string countrycode) {// tbreturn NULL ;} public bool insertcustomer () {// tbreturn false ;}}}

InCustomerdataClassGetcustomers ()Method, we can see that by reading information in the configuration file. AvailableDataFactoryClass is created through a specific connection stringXxxconnectionInstance, and write other code parts that do not have specific dependencies with the basic data source.

An example of a business-layer class that interacts with the data layer may look similar to the following:

Using system; using system. data; using Dal; namespace BLL {public class MERs {public datatable getallcustomers () {customersdata Cd = new customersdata (); datatable dt = CD. getcustomers (); Return DT;} public dataset getcustomerorders () {// to be determined return NULL ;}}}

In this case, what is the problem with this method? The problem here is that only one important detail binds the code to a specific data source: SQL syntax of the command string! In fact, if you write an application in this way, the only way to make it portable is to use the basic SQL syntax that can be interpreted by any data source, however, this may lose the opportunity to benefit from the specific functions of a specific data source. If the application only performs simple and standard operations on data, and you do not want to use advanced features in a specific data source (such as XML support), this may be a small problem. However, this method usually causes performance degradation because you cannot use the best features of each data source.

Back to Top

Write a dedicated data access layer

Therefore, using only basic interfaces is not sufficient to provide acceptable abstraction levels through different data sources. In this case, a good solution is to improve the abstraction level, that is, to create a group of classes (suchCustomer,OrderTo encapsulate the use of specific data providers, and exchange information with other application levels through data structures unrelated to specific data sources, typed "datasets", object sets, and so on.

You can create a dedicated class for this layer within a specific assembly (create a dedicated class for each supported data source ), you can load the application according to the instructions in the configuration file as needed. In this way, if you want to add a new data source to the application, the only thing you need to do is implement a new set of classes for the contract defined in a group of common interface groups.

Let's take a look at the actual example: if you want to use Microsoft SQL Server and Microsoft Access as the data source to provide support for it, you should go to Microsoft Visual Studio. create two different projects, one for each data source.

Projects created for SQL Server are similar to the following:

Using system; using system. data; using system. data. common; using system. data. sqlclient; using system. configuration; using common; namespace Dal {public class customersdata: idbcustomers {public datatable getcustomers () {string connectionstring = configurationsettings. appsettings ["connectionstring"]; using (sqlconnection CNN = new sqlconnection (connectionstring) {string comment string = "select customerid," + "companyName, contactname" + "from MERs "; sqlcommand cmd = new sqlcommand (canonical string, CNN); sqldataadapter da = new sqldataadapter (CMD); datatable dt = new datatable ("MERs"); DA. fill (DT); Return DT ;}} public datatable getcustomerorders (string customerid) {// tbreturn NULL;} public datatable getcustomersbycountry (string countrycode) {// tbreturn NULL ;} public bool insertcustomer () {// tbreturn false ;}}}

The code for retrieving data from Microsoft Access is similar to the following:

Using system; using system. data; using system. data. common; using system. data. oledb; using system. configuration; using common; namespace Dal {public class customersdata: idbcustomers {public datatable getcustomers () {string connectionstring = configurationsettings. appsettings ["connectionstring"]; using (oledbconnection CNN = new oledbconnection (connectionstring) {string comment string = "select customerid," + "companyName, contactname" + "from MERs "; oledbcommand cmd = new oledbcommand (Response string, CNN); oledbdataadapter da = new oledbdataadapter (CMD); datatable dt = new datatable ("MERs"); DA. fill (DT); Return DT ;}} public datatable getcustomerorders (string customerid) {// tbreturn NULL;} public datatable getcustomersbycountry (string countrycode) {// tbreturn NULL ;} public bool insertcustomer () {// tbreturn false ;}}}

CustomersdataClass implementationIdbcustomersInterface. To support new data sources, you can create only one new class to implement this interface.

This type of interface can be similar to the following:

using System;using System.Data; namespace Common{    public interface IDbCustomers    {        DataTable GetCustomers();        DataTable GetCustomerOrders(string CustomerID);        DataTable GetCustomersByCountry(string CountryCode);        bool InsertCustomer();    }}

We can create a dedicated assembly or shared assembly to encapsulate these data assembly classes. In the first case, the Assembly loader searches for the Assembly specified in the configuration file of the appbase folder, you can also use typical detection rules to search in sub-directories. If we must share these classes with other applications, we can place these sets in the global assembly cache.

Back to Top

Use the data pipeline class from other layers

These two almost identicalCustomersdataThe class is included in two different assemblies that will be used by the rest of the application. Through the following configuration file, we can now specify the assembly to be loaded and the target data source.

Possible configuration file examples are as follows:

<?xml version="1.0" encoding="utf-8" ?>    <configuration>    <appSettings>        <add key="ConnectionString"             value="Server=(local);Database=Northwind;            User ID=UserDemo;Pwd=UserDemo" />        <add key="DALAssembly" value="DALAccess,                   version=1.0.0.0, PublicKeyToken=F5CD5666253D6082" /><!--   <add key="ConnectionString"             value="Provider=Microsoft.Jet.OLEDB.4.0;             Data Source=../../../Northwind.mdb" />-->                    </appSettings></configuration>

We must specify two pieces of information in this file. The first information is a standard connection string (used to provide opportunities for changes), such as a server name or other parameters used for connection. The second information is the fully qualified name of the Assembly. The upper layer of the application dynamically loads the Assembly to find the class used with the specific data source:

Let's take a look at this part of the Code:

Using system; using system. data; using system. configuration; using system. reflection; using common; namespace BLL {public class MERs {public datatable getallcustomers () {string assemblyname = configurationsettings. appsettings ["dalassembly"]; string typename = "Dal. customersdata "; idbcustomers Cd = // (idbcustomers) = assembly. load (assemblyname ). createinstance (mytype); datatable dt = CD. getcustomers (); Return DT;} public dataset getcustomerorders () {// to be determined return NULL ;}}}

You can see that the Assembly is loaded with the name read from the configuration file, and is created and usedCustomersdataClass.

Back to Top

Possible improvements

For more information about the sample method, see the net Pet Shop V3.0 sample application. We recommend that you download this example and learn more about it, not only to solve portability problems, but also to solve other related problems (such as cache and performance optimization ).

In the process of designing a data access layer for a portable application, an important issue to note is how to communicate with other layers. In this example, I only use a commonDatatableInstances; in the production environment, you may want to consider different solutions based on the data types that must be represented (you must handle the hierarchy, etc. Here, I do not want to start from scratch. It is recommended that you refer to the designing data tier components and passing data through tiers guide, which details different situations and advantages of the proposed solution.

As described in my introduction, in the design phase, you should consider the specific features exposed by your target data source and the overall data access. This should cover stored procedures, XML serialization, and other matters. For details about Microsoft SQL Server 2000, refer to the following URL for how to optimize the use of these features:. NET data access architecture guide. We strongly recommend that you read this guide.

I always receive many requests about Data Access Application Block and how it is associated with parameters (as described in this article. These. Net Classes Act as the abstraction layer on top of the SQL Server. NET data provider, and enable you to write more excellent code to interact with the database server. The following is a piece of code to demonstrate feasible operations:

DataSet ds = SqlHelper.ExecuteDataset(       connectionString,      CommandType.StoredProcedure,      "getProductsByCategory",      new SqlParameter("@CategoryID", categoryID));

This method also has an extension that you can find in the example of the open source data access block 3.0 (Abstract Factory implementation) on gotdotnet. This version implements the same Abstract Factory mode and enables you to use different data sources based on available. NET data providers.

Back to Top

Conclusion

You should be able to build business logic classes that do not need to be modified based on the selected data source, and you can use the unique features of the given data source for better results. This is costly: we must implement multiple groups of classes to encapsulate low-level operations on specific data sources and all programmable objects that can be built for each specific data source (stored procedure, function, etc. If you want high performance and high portability, you have to pay this price. Based on my actual experience, this is completely worthwhile!