Is Your Data Access Code Portable?

By KENT SHARKEY on 5/27/2008 3:44:30 PM

While not every application needs some degree of database flexibility, the need to change your database does happen some times. You may be attempting to create a reusable data access framework that may be used to connect to multiple different databases in different applications, or you may have a change in plans, leading to the requirement of connecting to some new database (or database version). When that happens, do you:

  • Rewrite all your code from scratch
  • Go through your data access code, changing it as needed for the new database provider
  • Make a change in your configuration file and continue without code changes

Avoiding code changes is the ideal scenario, and there are two main ways you can achieve it in your code.

The first – and arguably better – solution is to use an existing framework that provides database portability. One simple, but flexible, alternative is to use the Data Access Application Block (DAAB). The DAAB is available as part of the Enterprise Library created by the Patterns and Practices group at Microsoft. In addition to integrating with the other components of the Enterprise Library, the DAAB provides both database-agnostic and database-specific wrappers. These provide easy methods to query and update databases without explicitly identifying the needed database. In addition, they make common scenarios (like calling a stored procedure requiring parameters) much easier than the default ADO.NET code.

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("GetProductsByCategory");

// Retrieve products from category 7.
db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, 7);
DataSet productDataSet = db.ExecuteDataSet(dbCommand);

If you do need a database specific feature, those are also available through classes provided in the DAAB. The Enterprise Library is an excellent choice as a starting point when building your code framework, and the DAAB is a strong part of that functionality.

You can also create your own database provider-agnostic framework by using the classes added in .NET Framework 2.0. These classes are provided in the System.Data.Common namespace. They are an example of the Factory pattern. In this pattern, rather than explicitly loading classes, you use the methods of the factory class to create the correct objects. The factory class reads the configuration file to determine the needed classes. While the code below only retrieves and displays a simple set of values, the provider-agnostic classes are also capable of updating data as well.

Dim fact As DbProviderFactory
Dim prov As String = My.Settings.productsProvider
fact = DbProviderFactories.GetFactory(prov)
Using conn As DbConnection = fact.CreateConnection()
conn.ConnectionString = My.Settings.productsConnectionString
conn.Open()
Using cmd As DbCommand = fact.CreateCommand
With cmd
.CommandText = "SELECT ProductName, UnitPrice FROM Products"
.CommandType = CommandType.Text
.Connection = conn
Using reader As DbDataReader = cmd.ExecuteReader
While reader.Read
Console.WriteLine("{0}: {1:c}", _
reader.GetString(0), _
reader.GetDecimal(1))
End While
End Using
End With
End Using
End Using

Whether you use either of these two methods, or select another method to achieve portability, you will be grateful when the time comes (and it will) when you need to switch out your existing database for your latest storage mechanism.

Application Development
Let Apptius save you time and money by adding functionality to your existing programs.
Microsoft Outlook
Apptius offers professional development services for custom Microsoft Outlook solutions
Microsoft Outlook Logo