evelopers constantly face problems brought on by changing technologies. When Microsoft releases a new version of a data provider, or a customer decides to switch databases from Oracle to SQL Server, you may have to rework code you’ve already written. But you can avoid much of this rework if you take the time to plan and code for such changes. One recommended way to do this is to develop components that take advantage of the Provider Model.

A provider is a class or a component that provides specific functionality to an application. However, the Provider class used will not be known until run time. In this article, you will see how to create a data provider that lets you change from using SQL Server to a generic OLE DB provider with no code changes! You will just have to change a setting in a configuration file. Using the Provider Model gives you a great amount of flexibility and upgradeability to your source code.

Microsoft provides a set of Provider Model Templates that you can download from MSDN. The difference between their model and the one explained in this article is that Microsoft’s templates are designed for use with web applications; in contrast, you’ll see a UI-agnostic version here; you can use the same techniques for Windows Forms, ASP.NET, Windows services, web services, etc.

Before seeing how to implement a data provider, here’s a look at three items that help you create a provider.

The Configuration Manager Class
You use the ConfigurationManager class from System.Configuration.dll to retrieve application settings from a configuration file. The configuration file can be a Windows Forms configuration file or a Web.config file in an ASP.NET Web application. ConfigurationManager replaces the old ConfigurationSettings class from .NET 1.1.

The ConfigurationManager class contains two properties that are designed specifically for retrieving values from two built-in sections in .NET 2.0 configuration files: appSettings and connectionStrings. For example, suppose you have the following appSettings entry in a configuration file:

You can use the following code to retrieve the StateCode value:

   // C#   ConfigurationManager.AppSettings["StateCode"];         ' Visual Basic   ConfigurationManager.AppSettings("StateCode")

Similarly, you can retrieve entries from the connectionStrings section, such as the “Northwind” entry below:

You can use the following code to retrieve the Northwind connection string.

   // C#   ConfigurationManager.     ConnectionStrings["Northwind"].ConnectString;         ' Visual Basic   ConfigurationManager. _     ConnectionStrings("Northwind").ConnectString

Abstract Base Class or Interface
You use an abstract base class when you have a class that can implement some or most of the functionality of the classes that will be inheriting from it, but the inheriting class must provide the actual implementation. In other words, the class that inherits from the abstract base class will do some of the work and the abstract base class will do some of the work.

You use an interface when there is no common code that could be put into a base class. In this case, you use an interface so each class has a list of standard methods and properties that whatever consumes that class can rely on being there and being implemented.

System.Activator Class
Sometimes in an application you do not know what class to load until run time. This is normally due to a data-driven scenario where the name of the class is placed into a database table or in a configuration file as a string. Your application then needs to use this at run time to create an actual instance of a class. To do this, you can use the System.Activator class to build an object from a string. The example below shows how to dynamically create an instance of an object at run time:

   // C#   IDataClass cust;   Type typ;   typ = Type.GetType("Customer");   x = (IDataClass)Activator.CreateInstance(typ);   MessageBox.Show(cust.GetData());         ' Visual Basic   Dim cust As IDataClass   Dim typ As Type   typ = Type.GetType("Customer")   cust = CType(Activator.CreateInstance(typ), _    IDataClass)   MessageBox.Show(cust.GetData())

The code above creates an instance of a Customer class. This code assumes that the Customer class either inherits from an abstract base class or implements an Interface named IDataClass.

Building a Data Provider
To illustrate the points outlined so far in this article, you’ll see how to create a data provider that can use SQL Server, OLE DB, or the Oracle native providers based on settings in a configuration file. The advantage of this approach is your user interface layer will make calls only to the DataLayer class for all DataSets, DataReaders, commands, etc. The DataLayer class will ensure that the appropriate provider is used based on settings in the Configuration file (see Figure 1).

? Figure 1. Structure of the Sample Application: The DataLayer class handles loading and calls to the provider, insulating the application from direct interaction with the data store.

? Figure 2. Provider Sample: Here’s a screenshot from the sample application showing retrieved data.

To test out this model, create a sample Windows Form application with a GridView control on a form that will load the Customers table from the Northwind database (see Figure 2).

Loading the Data
In the Form_Load event procedure you will call a method named GridLoad, which in turn will be responsible for calling the GetDataSet method in the DataLayer class:

   // C#   private void frmMain_Load(object sender,      EventArgs e)   {      GridLoad();   }   private void GridLoad()   {      string SQL = "SELECT * FROM Customers";      grdCust.DataSource =          DataLayer.GetDataSet(SQL,          AppConfig.ConnectString).Tables[0];   }         ' Visual Basic   Private Sub frmMain_Load( _       ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles MyBase.Load      GridLoad()   End Sub      Private Sub GridLoad()      Dim SQL As String = "SELECT * FROM Customers"      grdCust.DataSource = _         DataLayer.GetDataSet(SQL, _         AppConfig.ConnectString).Tables(0)   End Sub

The GridLoad method must read the appropriate connection string for the application from the configuration file. You’ll create an AppConfig class for that purpose that returns the appropriate connection string. The code in the UI layer is very generic and does not rely on any specific data provider to retrieve the data.

Configuration Settings
The next code snippet shows the configuration settings that provide not only the connection string, but also the provider class to use for retrieving data. In the element you will need a key called ProviderName. The value for the ProviderName will correspond to another key in the element that has the fully qualified namespace and class name for the data provider class. In addition, the ProviderName value will also be the same as the name key in the element, which stores the appropriate connection string for the data provider:

AppConfig Class
To retrieve the appropriate connection string from the configuration file you need to create the following static/Shared property in the AppConfig class. Notice that you have to read from the configuration file twice: once to get the ProviderName value, and again to retrieve the connection string from the element:

   // C#   public class AppConfig   {      public static string ConnectString      {         get         {            string ProviderName;            // Get Provider Name            ProviderName = ConfigurationManager.               AppSettings["ProviderName"];            // Get Connect String            return ConfigurationManager.ConnectionStrings[               ProviderName]. ConnectionString;         }      }   }         ' Visual Basic   Public Class AppConfig      Public Shared ReadOnly Property _         ConnectString() As String         Get            Dim ProviderName As String            ' Get Provider Name            ProviderName = ConfigurationManager. _                AppSettings("ProviderName")            ' Get Connect String            Return ConfigurationManager.ConnectionStrings( _               ProviderName).ConnectionString         End Get      End Property   End Class

The IDataProvider Interface
As mentioned earlier, when you use the Provider Model you will need to create either an abstract base class or an interface that each provider class must inherit from or implement. This example uses an interface called IDataProvider.

Because data provider class implementations vary widely, an interface is the logical choice. There is no common code between the different data providers, so you can’t use an abstract base class in this particular case. You can see the interface class in the code below:

   //  C#   interface IDataProvider   {      IDbConnection CreateConnection();      IDbCommand CreateCommand();      IDbDataAdapter CreateDataAdapter();   }         ' Visual Basic   Public Interface IDataProvider      Function CreateConnection() As IDbConnection      Function CreateCommand() As IDbCommand      Function CreateDataAdapter() As IDbDataAdapter   End Interface

DataLayer.GetDataSet Method
Going back to the sample Windows Form code (reiterated below) you will see a call to the DataLayer.GetDataSet method. You call this method by passing in a SQL statement and a connection string. This method contains fairly standard ADO.NET code that creates a DataSet instance and uses a DataAdapter to fill that DataSet. It returns the filled DataSet, which is assigned to the DataSource property of the grid control:

   // C#   private void frmMain_Load(object sender,       EventArgs e)   {      GridLoad();   }      private void GridLoad()   {      string SQL = "SELECT * FROM Customers";      grdCust.DataSource =          DataLayer.GetDataSet(SQL,          AppConfig.ConnectString).Tables[0];   }   ' Visual Basic   Private Sub frmMain_Load( _       ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles MyBase.Load      GridLoad()   End Sub      Private Sub GridLoad()      Dim SQL As String = "SELECT * FROM Customers"      grdCust.DataSource = _         DataLayer.GetDataSet(SQL, _          AppConfig.ConnectString).Tables(0)   End Sub

As you can see, by looking only at the form code you cannot tell which data provider is used to retrieve the code. It could be SQL Server, Oracle, or some OLE DB data provider. This works because the DataLayer class abstracts the specific code away from the UI layer. It’s worth taking a look at the GetDataSet method in the DataLayer to see how it does its job.

The GetDataSet method itself does not use a specific provider such as SqlDataAdapter or OleDbDataAdapter. Instead it uses the interface IDbDataAdapter. The IDbDataAdapter is a .NET interface that anyone who writes a .NET native provider must implement when creating a DataAdapter class. You will find interface classes for each of the specific ADO.NET provider classes such as IDbConnection and IDbCommand:

   // C#   public static DataSet GetDataSet(      string SQL, string ConnectString)   {      DataSet ds = new DataSet();      IDbDataAdapter da;      da = CreateDataAdapter(SQL, ConnectString);      da.Fill(ds);      return ds;   }   ' Visual Basic   Public Shared Function GetDataSet( _       ByVal SQL As String, _      ByVal ConnectString As String) As DataSet      Dim ds As New DataSet      Dim da As IDbDataAdapter      ' Create Data Adapter      da = CreateDataAdapter(SQL, ConnectString)      da.Fill(ds)      Return ds   End Function

Instead of creating a specific instance of a data adapter, the preceding code calls the CreateDataAdapter method to perform this function. CreateDataAdapter (also contained within the DataLayer), loads the appropriate data provider class.

DataLayer.CreateDataAdapter Method
The CreateDataAdapter method must do a couple of things to create an instance of a specific data adapter. First it initializes the appropriate provider based on the information in the configuration file. The InitProvider method is responsible for this and will be discussed in the next section. After the appropriate DataProvider class is loaded the code calls the CreateDataAdapter method on that specific provider. That creates a specific adapter, such as SqlDataAdapter, or OleDbDataAdapter, or the OracleDataAdapter:

   // C#   public static IDbDataAdapter CreateDataAdapter(      string SQL, string ConnectString)   {      IDbDataAdapter da;      // Make sure provider is created      InitProvider();      da = DataProvider.CreateDataAdapter();      da.SelectCommand = CreateCommand(SQL,          ConnectString, false);      return da;   }         ' Visual Basic   Public Shared Function CreateDataAdapter( _       ByVal SQL As String, _      ByVal ConnectString As String) As IDbDataAdapter      Dim da As IDbDataAdapter      ' Make sure provider is created      InitProvider()      da = DataProvider.CreateDataAdapter()      da.SelectCommand = CreateCommand(SQL, _        ConnectString, False)      Return da   End Function

The DataLayer.InitProvider Method
The InitProvider method is responsible for creating the actual provider object that will be used. To do this you first need a field/member variable to hold that data provider. Create a variable named DataProvider of type IDataProvider. Remember that IDataProvider is the interface that each specific DataProvider that you create must implement.

The first time the InitProvider method is called it loads the provider name by reading the value from the configuration file, and then uses the System.Activator class to create a new instance of that provider. The DLL containing the appropriate provider class must already be referenced by your project for this to work:

   // C#   private static IDataProvider DataProvider = null;   private static void InitProvider()   {      string TypeName;      string ProviderName;      if(DataProvider == null)      {         // Get provider name         ProviderName = ConfigurationManager.            AppSettings["ProviderName"];         // Get type to create         TypeName = ConfigurationManager.          AppSettings[ProviderName];         // Create new DataProvider         DataProvider = (IDataProvider)            Activator.CreateInstance(            Type.GetType(TypeName));      }   }         ' Visual Basic   Private Shared DataProvider As       IDataProvider = Nothing      Private Shared Sub InitProvider()      Dim TypeName As String      Dim ProviderName As String      If DataProvider Is Nothing Then         ' Get Provider Name         ProviderName = ConfigurationManager. _             AppSettings("ProviderName")         ' Get Type to Create         TypeName = ConfigurationManager. _             AppSettings(ProviderName)         ' Create new DataProvider         DataProvider = CType(Activator.CreateInstance( _             Type.GetType(TypeName)), IDataProvider)      End If   End Sub

DataProvider.CreateDataAdapter Method
Now you can finally look at the DataProvider class and its specific implementation of the CreateDataAdapter method. Look at the snippet below to see the class that uses the SqlClient.SqlDataAdapter:

   // C#   class SqlDataProvider : IDataProvider   {      public IDbDataAdapter CreateDataAdapter()      {         SqlDataAdapter da = new SqlDataAdapter();         return da;      }   }         ' Visual Basic   Public Class SqlDataProvider      Implements IDataProvider      Public Function CreateDataAdapter() _         As IDbDataAdapter Implements _         IDataProvider.CreateDataAdapter            Dim da As New SqlDataAdapter         Return da      End Function   End Class

While this is a very simple provider method to write, it is necessary to implement it this way to provide the maximum flexibility and reusability. This becomes more apparent when you look at the other Provider class that uses the OLE DB namespace to create instances of OleDbDataAdapters.

OLEDB DataProvider.CreateDataAdapter Method
Below is another DataProvider class that uses the OleDb native provider. Notice that this code is almost exactly the same as the SqlClient?they differ only in the provider used:

   // C#   class OleDbDataProvider : IDataProvider   {      public IDbDataAdapter CreateDataAdapter()      {         OleDbDataAdapter da = new OleDbDataAdapter();         return da;      }   }   ' Visual Basic

   Public Class OleDbDataProvider      Implements IDataProvider         Public Function CreateDataAdapter() _         As IDbDataAdapter _         Implements IDataProvider.CreateDataAdapter            Dim da As New OleDbDataAdapter         Return da      End Function   End Class

Try it Out
Using a Provider Model will make the code you write much more generic, easier to maintain, and easier to upgrade as Microsoft (and other companies) introduce new technology. Other areas where you should use the Provider Model include Exception Management?to determine where to publish exceptions. You could also use the Provider Model to determine where to read configuration settings from. As an exercise you could create additional providers that implement the OracleClient or any other native provider you might be using. You could have providers that read configuration settings from an XML file, the registry, a database table, or even a Web service. With a little imagination you can apply the concepts presented in this article to many areas of your application development process.