Summary: Understand how to write transparent use of smart applications using different data sources (from Microsoft Access to SQL Server, and Oracle RDBMS).
This page
Introduction Using General Data Access Methods Use the basic interface to write a specialized data access layer from other layers to use data access to some possible improvement conclusions
introduction
In the past six years responsible for consulting, I have repeatedly heard about data access and operational issues. It always plasted users: "How to write applications so that only requires little changes or not Changes you can use database server x, y, and z? "Since the data access layer is still the most critical part of the modern application, and is usually the number one enemy of experienced developers, my first reaction is always: fundamental Can't do it!
In the face of people's uneasy faces and "how to use the general data access method provided in Ado?" I decide to provide a more detailed description and suggested solutions for this issue.
The problem is that if the application is a smaller prototype, or if it is less user, if the data access logic is simple, even if you choose the simpler method below, you will not encounter any problems: use the RAD tool (such as Data Environment In Microsoft® Visual Basic® 6.0), or some "one package" solution (such as ActiveX® Data Control and other third-party components), these solutions typically hide complex interactions between applications and specific data sources. However, when the number of users increases, when the concurrent operation problem must be solved, many performance issues have occurred due to frequent dynamic record sets, server-side cursors, and unnecessary lock strategies. In order to achieve the user's goal, you must spend a lot of time, because you have not considered this problem since you start.
Use universal data access method
After the ADO is reliably incorporated into the MDAC (Microsoft Data Access Components 2.1), Microsoft set off a climax of general data access. Its dominant thinking is to show developers, by using a simple object model ("Connect", "Command", "Record"), can prepare a variety of different data sources (whether it is relational data source or non-relational Data Source) The application is connected. The documentation (and most of the articles and examples at the time) generally has not been mentioned, even if the same data access technology is used, the programmability and characteristics of various data sources are different.
As a result, the easiest way to obtain data from multiple data sources is to use the "common" of the functions provided by all data sources, but therefore will lose the benefits of using the specific option of the data source, namely Provide the best way to access and operate information in various RDBMs.
I have always existed the method that after more detailed analysis with my customers, we usually agree that interact with the data source compared to other parts of the application in the application, is only an application. Some part of it. By performing a well-modular design, RDBMS specific code can be isolated in some ever-interchangeable modules, thereby avoiding access to data access using an universal method. However, we can use very specific data access code (depending on the data source, use the stored procedure, command batch, and other features), and do not touch many other application code. This always reminds everyone: The correct design is the key to writing portable valid code.
ADO.NET introduces some important changes into the data access coding field, such as the concept of dedicated .NET data provider. Using a specific provider, you can bypass a series of software interfaces and services (they are inserted between the OLE DB and ODBC layers between the data access code and the database server), thereby connecting Go to the data source. However, each data source still has different features and features (with different SQL DIALECT), and writing efficient applications must still use these specific features rather than "common". From a portable view, managed and unmanaged data access technologies are still very similar. In addition to "unique features of the data source", other rules necessary to write a good data access layer are usually the same for each data source:
• Use the connection pool mechanism where possible. • Save limited resources to use the database server. • Pay attention to the round trip of the network. • In appropriate, enhance the repeated usage rate of the program and avoid repeated compilation. • Manage concurrency using the appropriate lock model.
From the personal experience of using modular design methods, the amount of code dedicated to handling specific data sources will not exceed 10% of the total amount. Obviously, this is more complicated than only the connection string in the configuration file, but I think so will get performance benefits, so this is an acceptable compromise.
Use basic interface
The goal here is to use abstractions and package the code-specific data source in the class layer, so that other parts of the application are independent of the backend database server or from it.
Object-oriented object-oriented objects will help us in the process so that we can choose the abstract level to use. One of the options is the basic interface (IDBConnection, IDBCommand, iDataReader, etc.) that must be implemented using each .NET data provider. Another option is to create a class (data access layer) for managing all data access logic of the application (for example, using crudous examples). To check these two possibilities, we first enter an application example in an order based on a Northwind database, then insert and retrieve information in different data sources.
The data provider basic interface identifies the typical behavior required to interact with the data source:
• Define the connection string. • Turn and close the physical connection to the data source. • Define commands and related parameters. • Perform different types of commands that can be created.
• Returns a set of data. • Return the scalar value. • Data execution operation but does not return anything. • Provide only forward access and read-only access to the returned dataset. • Define a set of operations required to keep the content of the data set to the content of the data source (data adapter).
But in fact, if you retrieve, insert, update, and delete different data sources (using different data providers) in the data access layer, only the members of the basic interface can be disclosed, Realize the first level abstraction - at least from the perspective of the data provider. Let's take a look at the code that demonstrates the design idea:
Using system;
Using system.data;
Using system.data.common;
Using system.data.sqlclient;
Using system.data.oledb;
Using system.data.oraclient;
Namespace DAL
{
Public Enum DataBaseType PUBLIC ENUM
{
Access,
SQL Server,
Oracle
// any other data source type
}
Public Enum ParameterType
{
Integer,
CHAR,
VARCHAR
// Define the utility parameter type set
}
Public Class DataFactory
{
PRIVATE DATAFAACTORY () {} 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;
}
}
}
The role of this class is to hide the application's higher levels related to the details related to the instance of a specific type (from a specific data provider), and the application can now interact with the data source using the general behavior disclosed by the basic interface.
Let us know how to use this class from other parts of the app:
Using system;
Using system.data;
Using system.data.common;
Using system.configuration; namespace dal
{
Public Class Customersdata
{
Public DataTable getCustomers ()
{
String connectionString =
ConfigurationSettings.Appsettings
["Connectionstring"];
DatabaseType DBTYPE =
(DatabaseType) Enum.Parse
(TypeOf (DatabaseType),
ConfigurationSettings.Appsettings
["DatabaseType"]);
IDBCONNECTION CNN =
DataFactory.createConnection
Connectionstring, DBTYPE;
String cmdstring = "Select Customerid"
", CompanyName, ContactName from Customers";
IDBCOMMAND CMD =
DataFactory.createCommand
CMDString, DBTYPE, CNN);
DBDataAdapter Da =
DataFactory.createAdapter (CMD, DBTYPE);
DataTable DT = New DataTable ("Customers");
Da.fill (DT);
Return DT;
}
Public Customersds GetCustomerRorders (String Customerid)
{
// to be determined
Return NULL;
}
Public Customerslist getCustomersbyCountry
(String Countrycode)
{
// to be determined
Return NULL;
}
Public bool insertcustomer ()
{
// to be determined
Return False;
}
}
}
In the getCustomers () method of the CustomerData class, we can see the information in the configuration file. You can use the DataFactory class to create an XXXConnection instance through a specific connection string, and write the remaining code parts that do not have specific dependencies with the basic data source.
A business layer example of interaction with the data layer looks like this:
Using system;
Using system.data;
Using dal;
Namespace BLL
{
Public Class Customers
{
Public DataTable getAllCustomers ()
{
Customersdata CD = New Customersdata ();
DataTable dt = cd.getcustomers ();
Return DT;
}
Public Dataset getCustomerRorders ()
{
// to be determined
Return NULL;
}
}
}
In this way, what is wrong with this method? The problem here is that there is only one important detail to bind the code to a specific data source: the SQL syntax of the command string! In fact, if the application is written in this manner, it is the only way to be portable to use the basic SQL syntax that can be interpreted by any data source, but this may lose the specific function of a particular data source. opportunity. This may be a small problem if the application is only simple and standard for data, and if you do not want to use advanced features in a specific data source (such as XML support). But usually this method will result in performance reduction because you cannot use the best features of each data source. Write a specialized data access layer
Therefore, only the basic interface is insufficient to provide acceptable levels of abstraction through different data sources. In this case, a good solution is to improve the level of this abstraction, create a set of classes (such as Customer, Order et al.) To encapsulate the use of a specific data provider, and type "data that types with specific data sources. Set ", object collection, etc. independent data structure and other levels of information on the application.
You can create a dedicated class for this layer within a particular assembly (a dedicated class is created for each supported data source), and can be loaded from the application in accordance with the instructions in the configuration file. This way, if you want to add a new data source to your application, the only thing to do is a new class for the "contract" defined in a set of universal interface groups.
Let us look at an actual example: If you want to provide Microsoft® SQL ServerTM and Microsoft® Access as a data source to provide support, you should create two different items in Microsoft® Visual Studio® .NET, each data source is created separately. One.
The items created for SQL Server will look like this:
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 cmdstring = "Select Customerid,"
"CompanyName, ContactName"
"From customer";
SQLCOMMAND CMD =
New SQLCommand (cmdstring, cnn);
SqlDataAdapter Da = New SqlDataAdapter (CMD);
DataTable DT = New DataTable ("Customers");
Da.fill (DT);
Return DT;
}
}
Public DataTable getCustomerRorders (String Customerid)
{
// to be determined
Return NULL;
}
Public DataTable getCustomersBycountry
(String Countrycode)
{
// to be determined
Return NULL;
}
Public bool insertcustomer ()
{
// to be determined
Return False;
}
}
}
The code 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 cmdstring = "Select Customerid,"
"CompanyName, ContactName"
"From customer";
OLEDBCOMMAND CMD =
New OLEDBCommand (cmdstring, cnn);
OLEDBDataAdapter Da = New
OLEDBDataAdapter (CMD);
DataTable DT = New DataTable ("Customers");
Da.fill (DT);
Return DT;
}
}
Public DataTable getCustomerRorders (String Customerid)
{
// to be determined
Return NULL;
}
Public DataTable getCustomersBycountry
(String Countrycode)
{
// to be determined
Return NULL;
}
Public bool insertcustomer ()
{
// to be determined
Return False;
}
}
}
The CustomersData class implements the IDBCustomers interface. You need to support new data sources, you can only create a new class that implements the interface.
This type of interface can be similar to the following:
Using system;
Using system.data;
Namespace Common
{
Public Interface IDBCustomers
{
DataTable getcustomers ();
DataTable getCustomerRorders (String Customerid);
DataTable getCustomersBycountry (String countrycode);
BOOL INSERTCUSTOMER ();
}
}
We can create a private assembly or shared assembly to encapsulate these data access classes. In the first case, the assembly loader searches for the assembly we specified within the configuration file of the AppBase folder, or uses a typical probe rule Search within the sub-directory. If we must share these classes with other applications, you can set these assembly caches.
Use data access classes from other layers
These two almost the same CustomersData class contain two different assembles that will be used in the rest of the application. Through the following configuration file, we can now specify the assembly to load and the orientated data source.
Possible configuration file examples will be similar to:
Value = "Server = (local); Database = Northwind; User ID = UserDemo; PWD = USERDEMO "/>
Value = "provider = microsoft.jet.oledb.4.0; Data Source = .. / .. / .. / northwind.mdb "/> -> We must specify two information in this file. The first information is a standardized connection string (for chances to make changes), such as server names, or other parameters for connection. The second information is a fully qualified name of the assembly. The previous layer of the application will dynamically load this assembly to find classes that use the specific data source: Let's take a look at this part of this code: Using system; Using system.data; Using system.configuration; Using system.reflection; USING COMMON; Namespace BLL { Public Class Customers { Public DataTable getAllCustomers () { String assemblyname = ConfigurationSettings.Appsettings ["DALASSEMBLY"]; String Typename = "Dal.customersdata"; IDBCUSTOMERS CD = // (idbcustomers) = AskMBLY.LOAD (AssemblyName). CreateInstance (MyType); DataTable dt = cd.getcustomers (); Return DT; } Public Dataset getCustomerRorders () { // to be determined Return NULL; } } } You can see that the assembly is loaded using the name read from the configuration file and creates and uses the instance of the CustomersData class. Some possible improvements To understand the examples of what I suggested, check the NET PET SHOP V3.0 sample application. It is recommended that you download this example and understand it, not only to solve the portability problem, but also to address other related issues (such as cache and performance optimization). In the process of designing data access layers for portable applications, an important issue that needs attention is how to communicate with other layers. In the example of this article, I only use a normal DataTable instance; in the production environment, you may want to consider different solutions based on the data type (you must handle hierarchism, etc.). Here, I don't want to start with my head, I suggest you check the Designing Data Tier Components and Passing Data Through Tiers Guide, which describes the advantages of different situations and the suggested solution. As described in me, in the design phase, you should consider the specific characteristics of your target data source and overall data access. This should cover the stored procedure, XML serialization. With regard to Microsoft® SQL ServerTM 2000, you can find an introduction to how to optimize these features in the following URL: .NET Data Access Architecture Guide. I strongly recommend that you read this guide. I always receive a number of requests for Data Access Application Block and how it associated with parameters (as described herein). These .NET classes act as abstraction layers above the SQL Server .NET data provider and enable you to write more excellent code interact with the database server. Below is a code that demonstrates the feasible operation: DataSet DS = SQLHELPER.EXECUtedataSet CONNECTIONSTRING, CommandType.StoredProcedure, "getProductSBycategory", New Sqlparameter ("@ categoryid", categoryid); This approach has an extension that you can find in the open source Data Access Block 3.0 (Abstract Factory Implementation) sample on the GotdotNET. This version implements the same abstract factory model and allows you to use different data sources based on available .NET data providers. in conclusion You should now be able to build business logic classes that need to be modified according to the selected specific data source, and you can use the unique features of a given data source to get a better effect. This is cost: we must implement multiple groups in order to encapsulate low-level operations of a particular data source, and all programmable objects that can be built for each specific data source (stored procedure, function, etc.). If you want to achieve high performance and high-portability, you must pay such a price. According to my actual experience, this is completely worthwhile!
