Generic Code in ADO.net 2.0

A majority of applications today use a database at the back-end. In application software, database systems have become indispensable. As there are many open and closed source database systems available, the development team has to make a choice as to the database system that they will use in their application. This crucial decision depends on various factors such as the list of features required in the application, cost, speed and reliability of the DBMS, etc.

A good number of applications end up with using only one DBMS in their life cycle while in other applications the DBMS may change more than once during their life cycle. There are an equally good numbers of applications which support multiple database systems at the same time. The choice of database system is completely in the hands of the end-user who may be using the application with many DBMS at the same time. Although it may appear that it is good to include support for many database systems in the application, it could mean a lot of other things too. The development team has to put in more efforts for developing and maintaining the application, which means more development and maintenance time and thus more costs. This also increases the size of the application. Though size is not an issue today, as storage has become cheaper, it is still a consideration for many. The main consideration would be the additional development efforts, since this could increase the time of development life cycle considerably in case of complex applications.

The best way to make an application run on multiple DBMS is to make the code very generic. We all know that there are many differences in the various DBMS available and it is very hard for developers to run the same code on different DBMS. So in the yesteryears we used to separate the Data Access code from the User Interface and Business Logic code. User Interface, Business Logic and Data Access formed separate layers in the application. All the differences between the DBMS were handled in the Data Access layer. Although it worked well for large applications, for small and medium applications it meant a lot of overhead code.

Earlier, DBMS vendors used to write tools which allowed developers to access the database. These were commonly known as Drivers but with .net the term has changed to Managed Providers. In ADO.net, which is the primary data access model for .net-based applications, Microsoft has kept the API very generic and the provider writers were given space to write plug-ins for their DBMS. Provider writers implemented a set of generic classes to write their DBMS-specific classes and exposed their generic functionality through interfaces. As such, applications which were packed to support multiple databases could be written using the generic interfaces. Although coding became a bit easy, loads of conditional statements had to be written to use DBMS-specific providers. Let us write a simple code snippet in VB.net to understand how we could do it in ADO.net 1.1. Refer code 1.

Public Function MyGetConnection (ByVal strProvider as String) as IDbConnection

Dim Con as IDbConnection

Select Case strProvider.toLower 
Case "sql"
Con = new SqlConnection()
Case "oracle"
Con = new OracleConnection() 
End Select
Return Con 

End Function

IDbConnection is an interface for connection class in ADO.net, which has been implemented as SqlConnection and OracleConnection in SqlClient and OracleClient, respectively. In ADO.net 1.0 Oracle data was accessible using only the generic OLE-DB providers, but performance lagged behind that of SQL Server’s Managed Provider. Recognizing the need for an Oracle-specific provider, Microsoft released the .net Managed Provider for Oracle. 

Let us take a look at the interfaces and their provider-specific classes in ADO.net 1.1. Refer figure 1.




In ADO.net 1.1 the only problem was that one always had to check for the provider and then create the instance of DBMS-specific class. With ADO.net 2.0, you don’t have this problem. Everything has been codified so that we can write completely generic codes for our applications. In fact, the whole story for data access has changed in ADO.net 2.0. Now we can write applications running on the user’s choice of database with minimum code.

In ADO.net 2.0, some new classes have been added in the System.Data.Common namespace which directly enable the implementation of provider-independent data access classes in the form of Factory Method Patterns. Instead of using the provider-specific classes in our code, we use the new base classes instead. These base classes can be mapped to the different provider-specific classes using the DbProviderFactory class. 
The System.Data.Common namespace has the following base classes. See figure 2:



Now let us rewrite the above code snippet to see how it will change in ADO.net 2.0
Dim df as DbProviderFactory = DbProviderFactories.GetFactory(strProviderName)
Dim Con as DbConnection= df.CreateConnection()


This looks quite tidy and the same applies to other data objects too. Once we create a DbProviderFactory class for the specific provider, we can use its methods to create all the specific objects of that specific provider. Let’s take another example of the Command object.

In ADO.net 1.1, we write the code as (See code 2):

Public Function MyGetCommand (ByVal strProvider as String) as IDbCommand

Dim Com as IDbCommand

Select Case strProvider.toLower 
Case "sql"
Com = new SqlCommand()
Case "oracle"
Com = new OracleCommand () 
End Select
Return Com

End Function

In ADO.net 2.0 this can be done with just one statement.

Dim Com as DbCommand = df.CreateCommand()

Following is the list of DbProviderFactory class methods which can be used to create provider-specific classes in the generic code. Refer figure 3.

As in earlier versions of ADO.net, we did not use the New operator directly to create an instance of the DataReader, instead it was created by the ExecuteReader method of the Command object like SqlCommand.ExecuteReader() or OracleCommand.ExecuteReader(). Similarly there is no method in the DbProviderFactory class to create the DataReader, instead it will be created by the DbCommand.ExecuteReader() method.

Another goodie in ADO.net 2.0 is that we can load the dataTable using DataReader and create a Reader from dataTable itself. Let us write a few lines of code to understand this better.


Dim dr As DbDataReader = Com.ExecuteReader(CommandBehavior.CloseConnection)
Dim dt As New DataTable("Contacts")
dt.Load(dr)


To Wrap Up it, we will write an example code that displays all the contacts in the dataGrid. Depending on the user’s selection, data can come from either Microsoft SQL Server or Oracle database.

When the user chooses a provider in the application, we assign values to the following two String variables:

a) strProviderName
b) strConnectionString

strProviderName can take any of the following two values:

a) “System.Data.SqlClient”
b) “System.Data.OracleClient”

The value of strConnectionString depends on the Server name, Database name, User name and Password, which will vary from client to client.

Now let us write the main lines which use the DbProviderFactories of ADO.net 2.0 to create the provider-independent code. See code 3.

Dim df As DbProviderFactory = DbProviderFactories.GetFactory(strProviderName)
Dim Con As DbConnection = df.CreateConnection()

With Con
.ConnectionString = strConnectionString
.Open
End With

Dim Com As DbCommand = df.CreateCommand()
Dim dr As DbDataReader

With Comm
.CommandText = "Select * from Contacts"
.Connection = Con
dr = .ExecuteReader(CommandBehavior.CloseConnection)
End With

Dim dt As New DataTable("Contacts")
dt.Load(dr)
dataGrid.dataSource = dt

This completes our example code. To sum it up in a line, we can say that, although ADO.net 2.0 is not revolutionary it is evolving in the right direction, by providing better support for generic code. 

Sajad Deyargaroo is a developer with BQE Software Inc., LA. Presently working in Offshore Development Centre at STPI, Kashmir,
he can be reached at sajad@programmer.net