|
Overview
ADO.NET is equipped with many providers -- OLEDB, SqlClient, OracleClient and OdbcClient. Database vendors also supply providers, i.e Oracle's ODP.NET. Applications usually target a specific database with hard-coded provider-specific ADO.NET calls. However, there are many cases where it would be ideal to write a single reusable class that utilizes a variety of ADO.NET providers without code duplication. In these cases, the code knows which ADO.NET provider to use at runtime. Writing a single ADO.NET class that can support any ADO.NET provider at runtime is the focus of this article.
download source code
ADO.NET Providers
ADO.NET provides a core set of interfaces that define the functionality and structure of ADO.NET. When a specific provider implements ADO.NET interfaces, the class names contain the provider prefix and end with the ADO.NET function suffix. For example, the Adapter class in the SQL Server provider is SqlDataAdapter. In the OLEDB provider it is OleDbDataAdapter. This consistency makes it easy to change the code from the SqlClient provider to OleDb; simply search and replace "Sql" with "OleDb". But this is only one way to deal with multiple providers. Of course this option is not elegant, supports only one provider at any time, and requires re-compilation each time the provider changes.
ADO.NET Interface Programming
ADO.NET providers implement interfaces defined by ADO.NET. The trick to providing a single codebase is using ADO.NET interfaces rather than using actual provider class names. Of course there are a few special cases where ADO.NET provider-specific classes need to be instantiated. With a few tricks and reflection this can be achieved.
Here are a few important ADO.NET interfaces:
Connection: System.Data.IDbConnection
Command: System.Data.IDbCommand
Transaction: System.Data.IDbTransaction
DataAdapter: System.Data.IDataAdapter
DataReader: System.Data.IdataReader
Here's the function to get the Connection class based on the ADO.NET provider:
// START OF CODE
BLOCK
protected IDbConnection GetConnection()
{
IDbConnection
oReturn = null;
switch(this.PROVIDER)
{
case
PROVIDER_TYPE.PROVIDER_SQLCLIENT:
oReturn = new
SqlConnection();
break;
case
PROVIDER_TYPE.PROVIDER_OLEDB:
oReturn = new
OleDbConnection();
break;
case
PROVIDER_TYPE.PROVIDER_ODBC:
oReturn =
new OdbcConnection();
break;
case
PROVIDER_TYPE.PROVIDER_ORACLE:
oReturn =
new OracleConnection();
break;
case
PROVIDER_TYPE.PROVIDER_OTHER:
oReturn = (IDbConnection)
GetADONETProviderObject(ProviderAssemblyName, ProviderCommandBuilderClassName,
null);
break;
default:
throw(new Exception("Invalid provider type"));
}
if(oReturn == null)
throw(new Exception("Failed to get ADONET Connection
object [IDbConnection]"));
return oReturn;
}
// END OF CODE BLOCK
Retrieving the ADO.NET Command or Transaction object based on the Connection Interface IdbConnection is as follows:
// Provider specific command object
IDbCommand oCmd = IDbConnection.CreateCommand();
// Provider specific transactionobject
IDbTransaction oTran = IDbConnection.BeginTransaction();
Save Coding with Useful ADO.NET Functions
In cases of stored procedures that take many parameters, writing code to add each parameter and setting up correct parameter direction is not elegant and is also error prone. Think about stored procedures that take more than 20 parameters. The ADO.NET CommandBuilder class supports the static method 'DeriveParameters'. This is a very useful method and is supported in all available providers. If you decide to use this call, please be aware that it requires an additional roundtrip to the database.
Here's a function to read parameter info based on the ADO.NET provider class:
// START OF CODE
BLOCK
public
IDataParameterCollection DeriveParameters(string
sSql, CommandType oType)
{
ValidateConnection();
ClearParameters();
m_oCommand.CommandText = sSql;
m_oCommand.CommandType = oType;
switch(this.PROVIDER)
{
case PROVIDER_TYPE.PROVIDER_SQLCLIENT:
SqlCommandBuilder.DeriveParameters((SqlCommand)m_oCommand);
break;
case PROVIDER_TYPE.PROVIDER_OLEDB:
OleDbCommandBuilder.DeriveParameters((OleDbCommand)m_oCommand);
break;
case PROVIDER_TYPE.PROVIDER_ODBC:
OdbcCommandBuilder.DeriveParameters((OdbcCommand)m_oCommand);
break;
case PROVIDER_TYPE.PROVIDER_ORACLE:
OracleCommandBuilder.DeriveParameters((OracleCommand)m_oCommand);
break;
case PROVIDER_TYPE.PROVIDER_OTHER:
{
Type oCmdBuilderType = Type.GetType(ProviderCommandBuilderClassName);
MethodInfo oMth =
oCmdBuilderType.GetMethod("DeriveParameters");
if(oMth == null)
throw(new
Exception("DeriveParameters method is not suppored by the selected
provider"));
object[]
oParams = new
Object[1];
oParams[0] = m_oCommand;
// DeriveParameters is static
method
oMth.Invoke(null,oParams);
break;
}
default:
throw(new
Exception("Invalid provider type"));
}
return
m_oCommand.Parameters;
}
//
END OF CODE BLOCK
Sample Code: Generic Database Wrapper and Testing Tool
The sample database wrapper code for this article contains most of the functionality required for real-world projects. The testing tool that is provided with the sample tests the features of the database wrapper class.
NOTE: The sample code was created with .NET Framework 1.1 Visual Studio 2002. I did this because I don't have the latest version of Visual Studio installed at home. Visual Studio 2003 will convert the project for you. Otherwise please make sure you have "TEST_ADONETWrapper.exe.config" file in your testing tool directory.
Hint: Look for following entry in "TEST_ADONETWrapper.exe.config" file:
<startup>
<requiredRuntime version="v1.1.4322" safemode="true"/>
</startup>
.NET Generics
It is elegant and easier to develop reusable code using .NET Generics. The sample database wrapper code does not use them, as this functionality is not available in .NET Framework 1.1. In case you are working with the next version (2.0) of the .NET Framework, you can use this feature and simplify the database wrapper code even more. Please see Rob Chartier's 15Seconds article titled "Introducing .NET Generics" for more information.
A Few Important Database Wrapper Class Properties
a. Connection retries:
Although it is not directly related to developing a generic ADO.NET wrapper, it is useful to support retry count. Retry count specifies the number of database connection attempts in cases of failure. Most of the time connections fail for simple reasons, and it can be recovered with the next attempt. Built-in recovery using the default retry count is a good idea.
b. Command Timeout:
If you are executing SQL queries that take a long time and you don't override the default command timeout value, your query will timeout in three minutes (default). Placing this property in your database wrapper class provides timeout flexibility.
c. Clean up:
Implementing the IDisposable interface is a good way to allow clean up even if the caller forgets it. The sample wrapper code implements this interface.
d. Error handling:
The provided database wrapper code does not have its own error handling. All exceptions are passed to the caller (not handled in the wrapper object). This is intentionally left out.
Windows Form Based Testing Tool
What good it is if there is no easy way to test the technology we have discussed. The sample code comes with a testing tool to execute queries using different providers. The tool shows stored procedure information using the 'DeriveParameters' call if Command Type is selected as 'Stored procedure'.
Testing and Quality of Code
Most of the functionality is tested with Oracle, OleDb (Sybase, SQL Server) and SqlClient (With SQL Server). The code is provided for educational purposes. Developers can use it as is or change it as per their requirements. Author takes no responsibility for the code.
Using .NET Reflection for Creating Provider-Specific Classes
.NET Framework 1.1 supports OracleClient, Odbc, SqlClient and OleDb providers. So for these providers, the code does not use reflection to create provider-specific classes. If you have an ADO.NET provider that is not listed above (Example: ODP.NET from Oracle), you can try using reflection code. This part of the code is not tested, and you may have to fix a few things. The sample code shows how to use reflection to support any future ADO.NET providers without any code changes.
Here's the function to create a provider-specific object at runtime:
[Use following code only for third-party ADO.NET providers]
// START OF CODE
BLOCK
protected object
GetADONETProviderObject(string sAssembly, string sClass, object[]
oArgs)
{
if(sAssembly == null
|| sAssembly.Trim().Length == 0)
throw(new Exception("Invalid provider assembly
name"));
if(sClass == null ||
sClass.Trim().Length == 0)
throw(new Exception("Invalid provider connection class
name"));
Assembly
oSrc =
Assembly.LoadWithPartialName(sAssembly);
if(oArgs == null)
{
return
oSrc.CreateInstance(sClass,true);
}
else
{
Type oType =
oSrc.GetType(sClass,true,true);
Type[] arTypes = new Type[oArgs.Length];
for(int i=0; i < oArgs.Length; i++)
{
arTypes[i] = oArgs[0].GetType();
}
ConstructorInfo oConstr =
oType.GetConstructor(arTypes);
return
oConstr.Invoke(oArgs);
}
}
// END OF CODE BLOCK
Conclusion
In these days of building reusable infrastructure and applications, keeping data access layer as transparent as possible from underlying database server is important. ADO.NET should be seen as a database access technology and separate from a specific database server. The current implementation of ADO.NET attempts to achieve this to a major extent with DataSet, DataAdapter, DataReader classes. This article and code sample attempt to demonstrate reusability with a few tricks and required workarounds.
References
IDisposable interface:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemidisposableclassdisposetopic.asp
.NET Reflection:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemactivatorclasscreateinstancetopic.asp
http://www.15seconds.com/issue/031024.htm
ADO.NET Best practices:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/adonetbest.asp
About the Author
Jayram Tallamraju is working as an Architect engineer for a financial company based in Boston. He is MCP of .NET, MCSD (Microsoft Certified Solution Developer) and SCJD (Sun Certified Java Developer). Jay holds an MS in Electronics and has been working in the software industry for over 10 years. He is focused more in building server architecture and in building reusable business components. His current area of expertise is in Microsoft technologies such as .NET, C#, Web services, ASP.NET, VC++/VB, COM/DCOM, ASP/IIS. HE can be reached at tjayram@yahoo.com.
Other articles from same author:
* "XSLT Support in the .NET Framework"
* "Presenting Hierarchical Data in ASP.NET"
* "Microsoft Excel for Data Analysis and Reporting in ASP.NET"
* "Multi-Column Sort Using the ASP.NET DataGrid"
* ".NET Buzzword Reality"
* "Building a .NET Application Configuration Editor"
* "How to Share Session/Application State Across Different ASP.NET Web Applications"
* "Data Import Functionality Using SQL Server"
* "Distributed Processing"
|
