Access | Data summary: Learn how to write smart applications that transparently use different data sources, from Microsoft Access to SQL Server and Oracle RDBMS.
Content of this page
Introduction
Using common data access methods
Using basic interfaces
Write a dedicated data access layer
Using data access classes from other tiers
Some of the possible improvements
Conclusion
Introduction
Over the past 6 years in charge of consulting, I've heard a lot about data access and operations, and it's always bothering users: "How do you write applications so that you can use database servers x, y, and z with minimal changes or changes?" "Knowing that the data access layer is still the most critical part of modern applications and usually the number one enemy of inexperienced developers, my first reaction has always been: it's impossible!"
Facing people's restless faces and "What about using the common data access method that Microsoft provides in ADO?" "I decided to provide a more detailed description of the problem and suggested solutions to this issue."
The problem is that if the application is a smaller prototype, or if there are fewer concurrent users and the data access logic is simpler, you will not experience any problems even if you choose the following simplest methods: 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, often hide complex interactions between applications and specific data sources. However, when the number of users increases so that concurrent operational problems must be resolved, many performance problems occur because of the frequent use of dynamic recordsets, server-side cursors, and unnecessary locking policies. The design and code changes that must be made to the system to achieve the user's goal will take a significant amount of time because you have not considered the issue at the outset.
Using common data access methods
Microsoft's use of universal data access culminated in the reliable incorporation of ADO into MDAC (Microsoft Data Access Components version 2.1). The idea is to show developers that by using a Simple object model (connection, command, and Recordset), you can write applications that connect to a variety of different data sources, whether they are relational data sources or non relational data sources. The documentation (and most of the articles and examples at the time) is not usually mentioned, even with the same data access technology, the programmability and characteristics of various data sources vary widely.
As a result, in applications that need to get data from multiple data sources, the simplest approach is to use the "common ground" of the functionality provided by all data sources, but will lose the benefit of using the data source-specific options that provide the best way to access and manipulate information in various RDBMS.
My persistent suspicion of this approach is that after more detailed analysis with my customers, we generally agree that interacting with the data source is only a small part of the application compared to other parts of the application that handle the display and business logic. With a well-designed modular design, RDBMS-specific code can be isolated in some easily interchangeable modules, thereby avoiding the use of a "generic" approach to data access. However, we can use very specific data access codes (stored procedures, command batches, and other features, depending on the data source) without touching most of the other application code. This always reminds you that the right design is the key to writing portable, valid code.
Ado.net introduces some important changes into the field of data access coding, such as the concept of a dedicated. NET data provider. With a specific provider, you can bypass the numerous, but sometimes unnecessary, series of software interfaces and services that are inserted between the data access code and the database server by the OLE DB and ODBC layers, and thus connect to the data source in an optimal manner. But each data source still has different characteristics and characteristics (with different SQL dialect), and writing efficient applications still has to use these specific features rather than "commonalities". From a portability standpoint, managed and unmanaged data access technologies are still very similar.
In addition to "leveraging unique characteristics of a data source," the other rules necessary to write a good data access layer are typically the same for each data source:
• Use the connection pooling mechanism where possible.
• Save limited resources using the database server.
• Pay attention to the round-trip of the network.
• Increase execution of planned reuse and avoid duplicate compilation, where appropriate.
• Manage concurrency with the appropriate locking model.
From my personal experience with modular design methods, the total number of code used to process a particular data source in the entire application is not more than 10% of the amount. Obviously, this is more complicated than just changing the connection string in the configuration file, but I think it will yield performance gains, so this is an acceptable compromise.
Using basic interfaces
The goal here is to use abstractions and encapsulate code specific to a particular data source in the class layer, making other parts of the application independent of or unaffected by the back-end database server.
The. NET Framework's object-oriented feature will help us in this process, enabling us to choose the level of abstraction to use. One option is to use the basic interfaces that each. NET data provider must implement (IDbConnection, IDbCommand, IDataReader, and so on). Another option is to create a set of classes (data access tiers) that manage all of the application's data access logic (for example, using the CRUD paradigm). To examine both possibilities, we start with an Order entry application sample based on the Northwind database, and then insert and retrieve information from different data sources.
The data provider basic interface identifies typical behaviors that an application typically requires to interact with a data source:
• Define the connection string.
• Open and close the physical connection to the data source.
• Define commands and related parameters.
• Perform different kinds of commands that can be created.
• Returns a set of data.
• Returns a scalar value.
• Performs operations on data but does not return any content.
• Provides forward-only and read-only access to the returned dataset.
• Define a set of actions that are required to keep the data set synchronized with the contents of the data source (data adapter).
But in fact, if you would retrieve, the various operations required to insert, UPDATE, and delete information from different data sources (using different data providers) are encapsulated in the data access layer and expose only the members of the basic interface, and you can implement the first level of abstraction-at least from the point of view of the data provider. Let's take a look at the following 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.OracleClient;
Namespace DAL
{
public enum DatabaseType
{
Access,
Sql server
Oracle
Any other data source type
}
public enum ParameterType
{
Integer,
Char,
VarChar
Defining common parameter type sets
}
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 to 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 purpose of this class is to hide details about the creation of an instance of a particular type (from a particular data provider) at a higher level of the application, and the application can now interact with the data source using the generic behavior exposed through the basic interface.
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.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 getcustomerorders (string CustomerID)
{
Pending
return null;
}
Public Customerslist Getcustomersbycountry
(String CountryCode)
{
Pending
return null;
}
public bool InsertCustomer ()
{
Pending
return false;
}
}
}
In the GetCustomers () method of the CustomerData class, we can see by reading the information in the configuration file. You can use the DataFactory class to create a xxxconnection instance from a specific connection string and write the rest of the code section that has no specific dependencies on the underlying data source.
An example of a business layer class interacting with the data tier might look something like the following:
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 getcustomerorders ()
{
Pending
return null;
}
}
}
So what's wrong with this approach? The problem here is that there is only one important detail that binds the code to a specific data source: The SQL syntax for the command string! In fact, if you write an application this way, the only way to make it portable is to take a basic SQL syntax that can be interpreted by any data source, but it may lose the opportunity to benefit from specific features of a particular data source. This can be a minor problem if your application only makes simple and standard operations on the data, and if you do not want to use advanced features in a particular data source, such as XML support. However, this approach typically leads to performance degradation because you cannot use the best features of each data source.
Write a dedicated data access layer
Therefore, using only basic interfaces is not sufficient to provide an acceptable level of abstraction through different data sources. In this case, a good solution is to raise this level of abstraction, that is, create a set of classes, such as Customer, order, and so on, to encapsulate the use of a particular data provider and exchange information with other levels of the application, in relation to a particular data source, a typed dataset, an object collection, and so on.
You can create specialized classes of this layer within a particular assembly (one for each supported data source), and you can load them from the application as described in the configuration file, as needed. This way, if you want to add a completely new data source to your application, the only thing to do is to implement a new set of classes for contracts defined in a set of common interface groups.
Let's look at a practical example: if you want to support Microsoft®sql Server™ and microsoft®access as a data source, you should create two different projects in Microsoft®visual studio®.net, with each data Source to create a separate one.
The project created for SQL Server will resemble 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 cmdstring = "Select CustomerID," +
"Companyname,contactname" +
"From Customers";
SqlCommand cmd =
New SqlCommand (cmdstring, CNN);
SqlDataAdapter da = new SqlDataAdapter (cmd);
DataTable dt = new DataTable ("Customers");
Da. Fill (DT);
return DT;
}
}
Public DataTable getcustomerorders (string CustomerID)
{
Pending
return null;
}
Public DataTable Getcustomersbycountry
(String CountryCode)
{
Pending
return null;
}
public bool InsertCustomer ()
{
Pending
return false;
}
}
}
The code for data retrieval 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 Customers";
OleDbCommand cmd =
New OleDbCommand (cmdstring, CNN);
OleDbDataAdapter da = new
OleDbDataAdapter (CMD);
DataTable dt = new DataTable ("Customers");
Da. Fill (DT);
return DT;
}
}
Public DataTable getcustomerorders (string CustomerID)
{
Pending
return null;
}
Public DataTable Getcustomersbycountry
(String CountryCode)
{
Pending
return null;
}
public bool InsertCustomer ()
{
Pending
return false;
}
}
}
The CustomersData class implements the Idbcustomers interface. When you need to support a new data source, you can create only one new class that implements the interface.
This type of interface can resemble 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 private assemblies or shared assemblies to encapsulate these data access classes, in the first case, the assembly loader will search for the assembly that we specify in the configuration file of the AppBase folder, or use a typical probing rule to search within the subdirectory. If we have to share these classes with other applications, you can place these assemblies in the global assembly cache.
Using data access classes from other tiers
The two almost identical customersdata classes are contained within two different assemblies that will be used by the rest of the application. With the following configuration file, we can now specify the assembly to load and the data source to target.
The possible configuration file example will resemble the following:
<?xml version= "1.0" encoding= "Utf-8"?>
<configuration>
<appSettings>
<add key= "ConnectionString"
Value= "server= (local);D atabase=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 messages within this file. The first message is the canonical connection string (used to provide an opportunity for the change), such as the server name or some other parameters for the connection. The second piece of information is the fully qualified name of the assembly, and the upper layer of the application dynamically loads the assembly to find classes to use with a particular data source:
Let's take a look at this part of the code again:
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) =
Assembly.Load (AssemblyName).
CreateInstance (MyType);
DataTable dt = CD. GetCustomers ();
return DT;
}
Public DataSet getcustomerorders ()
{
Pending
return null;
}
}
}
As you can see, the assembly is loaded with the name read from the configuration file and creates and uses an instance of the CustomersData class.
Some of the possible improvements
For an example of the method I suggested, check out the NET Pet Shop v3.0 sample application. It is recommended that you download this sample and learn more about it, not only to address portability issues, but also to address other related issues such as caching and performance tuning.
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 the example in this article, I use only a common DataTable instance; In a production environment, you might want to consider different solutions based on the type of data that must be represented (you must handle the hierarchy, and so on). Here, I don't want to start from scratch, we recommend that you review the designing data Tier components and passing data through Tiers Guide, which details the different scenarios and the benefits of the proposed solution.
As I mentioned in the introduction, you should consider the specific features that your target data source exposes and the overall data access in the design phase. This should cover such things as stored procedures, XML serialization, and so on. For Microsoft®sql server™2000, you can find an introduction to how to optimize the use of these features in the URL below:. NET Data Access Architecture Guide. It is highly recommended that you read the guide.
I always get a lot of requests about Data Access Application Block and how it relates to parameters (as described in this article). These. NET classes act as layers of abstraction above the SQL Server. NET data provider and enable you to write more good code to interact with the database server. Here's a piece of code that demonstrates a workable operation:
DataSet ds = SqlHelper.ExecuteDataset (
ConnectionString,
CommandType.StoredProcedure,
"getProductsByCategory",
New SqlParameter ("@CategoryID", CategoryID));
This method also has an extension that you can find in the open source Data Access block 3.0 (Abstract Factory Implementation) example on GotDotNet. This version implements the same abstract factory pattern and enables you to use different data sources based on the available. NET data providers.
Conclusion
You should now be able to build business logic classes that don't need to be modified based on the specific data source you choose, and you can get better results with the unique characteristics of a given data source. This comes at a price: we have to implement several sets of classes to encapsulate low-level operations for a particular data source and all the programmable objects that can be built for each particular data source (stored procedure, function, and so on). If you want to get high performance and high portability, you have to pay that price. According to my practical experience, it is entirely worth it!
