Section 5.9. Write Database-Agnostic Code | Visual Basic 2005: A Developers Notebook

5.9. Write Database-Agnostic Code

In developing ADO.NET, Microsoft set out to create a new data access architecture that would be more flexible, better performing, and more easily extensible than its previous COM-based OLE DB and ADO architectures. They did this by creating a model where every data source must supply its own data provider: a set of managed classes that allow you to connect to a particular data source (e.g., SQL Server, Oracle), execute commands, and retrieve data. In order to ensure that these providers are consistent, each implements a standard set of interfaces. However, this approach creates major challenges for developers who want to write provider-agnostic codefor example, a basic database routine that can be used equally well with the SQL Server provider or the Oracle provider. Usually, you use provider-agnostic code because you aren't sure what type of database the final version of an application will use, or because you anticipate the need to migrate to a different database in the future.

Note: Want a way to write database code that isn't bound to a specific data source? This challenge becomes a whole lot easier in . NET 2.0.

.NET 2.0 takes major steps to facilitate generic database coding by introducing a new factory model. (A factory model is a pattern where one class has the exclusive responsibility for creating instances of other classes.) In this model, you can use a database provider factory to build the ADO.NET connections, commands, and many other types of objects required for a particular database. The factory automatically returns the type of object that you need for your data source (e.g., a SqlCommand or an OracleCommand), but when you write your code, you don't worry about these details. Instead, you write generic commands without regard to the particular details of the data source.

5.9.1. How do I do that?

In provider-agnostic code, you still use all the same strongly typed objects. However, your code manipulates these objects using common interfaces. For example, every command object, whether it's used for SQL Server or Oracle, implements the common IDbCommand interface, which guarantees a basic set of methods and properties.

Note: Because provider-agnostic code attempts to be as generic as possible, it's more difficult to properly optimize a database. As a result, this technique isn't suitable for most large-scale enterprise applications.

Provider-agnostic code is structured so that you specify the type of database you're using early on, usually by reading some information from a configuration file. You use this information to retrieve a DbProviderFactory for your database. Here's an example where the factory string is hardcoded:

Dim Factory As String = "System.Data.SqlClient" Dim Provider As DbProviderFactory Provider = DbProviderFactories.GetFactory(Factory)

In this example, the code uses the shared GetFactory( ) method of the System.Data.Common.DbProviderFactories class. It specifies a string that identifies the provider name. For example, if you use the string System.Data.SqlClient, the GetFactory() method returns a System.Data.SqlClient.SqlClientFactory object. The DbProviderFactories class can create factories for all the data providers included with .NET, because they are explicitly configured in the machine.config configuration file on the current computer. Essentially, the configuration record tells the DbProviderFactories class to create a SqlClientFactory when the programmer passes the exact string "System.Data.SqlClient." If you develop your own provider, you can also register it to work in this way (although that task is beyond the scope of this lab).

The SqlClientFactory object has the built-in smarts to create all the objects used by the SQL Server provider. However, your code can be completely generic. Instead of interacting with the specific SqlClientFactory class type, it should use the generic base class DbProviderFactory. That way, your code can work with any type of DbProviderFactory, and therefore support any database provider.

Once you have the DbProviderFactory, you can create other types of strongly typed ADO.NET objects using a set of common methods by using the CreateXxx() methods. These include:

CreateConnection( )

CreateCommand( )

CreateParameter( )

CreateDataAdapter( )

CreateCommandBuilder( )

All these methods create a provider-specific version of the object they name.

To get a better understanding of how generic database code works, it helps to try out a complete example that can switch from one data provider to another on the fly. First of all, you need to create an application configuration file that stores all the provider-specific details. To do this, create a console application and open the app.config file. Add the following three settings, which specify the factory name, the connection string for the database, and the query to perform:

<?xml version="1.0" encoding="utf-8" ?> <configuration>   <appSettings>     <add key="Factory" value="System.Data.SqlClient" />     <add key="Connection" value=  "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI" />     <add key="Query" value="SELECT * FROM Orders" />   </appSettings> </configuration>

This example uses the SQL Server provider to connect to the Northwind database and retrieve a list of all the records in the Orders table.

Now you can retrieve the configuration file information and use it with the DbProviderFactories class to create every ADO.NET provider object you need. In Example 5-9, the query is executed, a DataSet is filled, and a list of OrderID values is displayed in the console window.

Example 5-9. Using DbProviderFactories to write database-agnostic code

Imports System.Data.Common Imports System.Configuration      Module GenericDatabaseTest          Public Sub Main( )         ' Get all the information from the configuration file.         Dim Factory, Connection, Query As String         Factory = ConfigurationManager.AppSettings("Factory")         Connection = ConfigurationSettings.AppSettings("Connection")         Query = ConfigurationManager.AppSettings("Query")              ' Get the factory for this provider.         Dim Provider As DbProviderFactory         Provider = DbProviderFactories.GetFactory(Factory)              ' Use the factory to create a connection.         Dim con As DbConnection = Provider.CreateConnection( )         con.ConnectionString = Connection              ' Use the factory to create a data adapter         ' and fill a DataSet.         Dim Adapter As DbDataAdapter = Provider.CreateDataAdapter         Adapter.SelectCommand = Provider.CreateCommand( )         Adapter.SelectCommand.Connection = con         Adapter.SelectCommand.CommandText = Query         Dim ds As New DataSet         Adapter.Fill(ds, "Orders")              ' Display the retrieved information.         For Each Row As DataRow In ds.Tables("Orders").Rows             Console.WriteLine(Row("OrderID"))         Next     End Sub      End Module

Mostly, this is a fairly pedestrian piece of data access logic. The only exciting part is that you can switch from one provider to another without modifying any of the code or recompiling. You just need to modify the provider information and connection string in the configuration file. For example, make these changes to the configuration file to access the same table through the slower OLE DB provider interface:

<?xml version="1.0" encoding="utf-8" ?> <configuration>   <appSettings>     <add key="Factory" value="System.Data.OleDb" />     <add key="Connection" value=  "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI" />     <add key="Query" value="SELECT * FROM Orders" />   </appSettings> </configuration>

After saving the configuration file, you can run the application again. It will work just as well, displaying the same list of order records.

5.9.2. What about...

...the challenges you'll encounter in writing database-agnostic programs? The new factory approach is a giant leap forward for those who want to write provider-agnostic code. However, a slew of problems (some minor and some more significant) still remain. These include:

Handling errors: Every database provider has its own exception object (like SqlException and OracleException), and these objects don't derive from a common base class. That means there's no way to write an exception handler that catches database exceptions generically. All you can do is write exception handlers that catch the base Exception object.
Provider-specific functionality: Some features aren't exposed through the common interfaces. For example, SQL Server has the ability to execute FOR XML queries that return XML documents. To execute this type of query, you use the SqlCommand.ExecuteXmlReader( ) method. Unfortunately, this isn't a standard command method, so there's no way to access it through the IDbCommand interface.
Handling parameters: Some providers (like SQL Server) recognize command parameters by their name. Others (like OLE DB) recognize command parameters by the order of their appearance. Minor differences like this can thwart provider-agnostic programming.

5.9.3. Where can I learn more?

Unfortunately, there isn't much documentation yet in the MSDN Help about provider-agnostic coding. However, you can get a good overview with additional examples from the Microsoft whitepaper at http://msdn.microsoft.com/library/en-us/dnvs05/html/vsgenerics.asp.