RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Writing Provider-Independent Data Access Code with ADO.NET 2.0 : Page 3

When you need to write an application that allows users to select the database provider, the application code itself needs to be completely provider-independent. ADO.NET 2.0 helps you create and deliver database applications even when you don't know what database your clients are using.

Selecting a Provider Factory
To select and retrieve an instance of a specific provider factory, from which you can create instances of the individual classes that you'll use to perform the data access, you use the static GetFactory method. This method has two overloads:

  • GetFactory(String) takes an invariant name, such as "System.Data.SqlClient".
  • GetFactory(DataRow) takes a reference to a row in the DataTable returned by the GetFactoryClasses method.
You can use either method. The choice probably depends on whether you are enumerating the providers first, in which case you will already have a reference to the DataTable you need, or just using a value from—for example—a configuration file and instantiating the specified provider factory directly.

Configuring a Specific Provider Type
If you only want your code to be configurable to a specified provider when installed, or when the database in use is changed, you can store the appropriate invariant name in the configuration information for the application. The most obvious place for ASP.NET is in a Web.config file, in the <appSettings> section:

       <add key="provider-type" value="System.Data.SqlClient" />
Then you can create an instance of the required data factory using the following code. Notice that because you only need one instance of the data factory no matter how many provider-dependent class instances you will be creating, it's a good idea to hold on to the single instance after you create it. You can do that by declaring the factory variable as a page-level or module-level variable, and then populating it using the GetFactory method within the Page_Load event handler or some other routine as appropriate for your application:

   ' declare a variable to hold the single instance that
   ' is required of the specific provider factory class
   Dim df As DbProviderFactory 
   Sub Page_Load() 
     ' get an instance of the specified provider factory class
     Dim sInvariant As String = _
     df = DbProviderFactories.GetFactory(sInvariant)
Checking Whether a Provider Factory Is Installed
If you allow users to change the provider factory using a configuration setting, it's a good idea to check that the invariant name they provide is legal and corresponds to an installed provider before you attempt to create the provider factory. Doing so prevents your code from raising an exception when the GetFactory method is called, and can more clearly indicate the source of the error to the user. All you need to do is ensure that the invariant name provided does exist in one of the rows of the DataTable returned by the GetFactoryClasses method. The easiest way is to try and select that row in the DataTable. The Select method returns an array of matching DataRow instances; if no matching row exists the array will have a Length property value of zero:

   ' check that the value supplied is a valid provider
   ' invariant name
   If DbProviderFactories.GetFactoryClasses.Select _
      ("InvariantName='" & sInvariant & "'").Length = 0 Then
      output.Text = 
         "Invariant Name in Web.config file is invalid."
      Exit Sub
   End If
Creating a Provider Factory from a DataTable Row
If your application needs to enumerate the installed providers, and provide a list from which the user can select, you will already have a DataTable containing all the installed provider factory details. You can then access a specific provider factory, and create an instance of it, by passing a reference to the appropriate row in the DataTable to the GetFactory method. For example, the code below creates a GridView that displays just the invariant name and description, together with a Select button in each row:

   <asp:GridView id="grid1" runat="server"
        BorderWidth="1px" ForeColor="Black" BackColor="White"
        CellPadding="4" BorderStyle="None" BorderColor="#dedfde"
        GridLines="Vertical" AutoGenerateColumns="False"
       <HeaderStyle ForeColor="White" Font-Bold="True" 
          BackColor="#6b696b" />
       <RowStyle BackColor="#f7f7de" />
       <AlternatingRowStyle BackColor="White" />
       <SelectedRowStyle ForeColor="White" Font-Bold="True" 
          BackColor="#CE5D5A" />
         <asp:CommandField ShowSelectButton="True" 
             ButtonType="Button" />
         <asp:BoundField DataField="InvariantName" 
             HeaderText="Invariant Name" />
         <asp:BoundField DataField="Description" 
             HeaderText="Description" />
Figure 3. Selecting a Data Provider at Runtime: The page provides the invariant name and description of the five installed data provider factories, along with a Select button so users can choose the provider.
The Page_Load event handler populates this GridView when the page first loads, though not in each postback that occurs when the user clicks one of the Select buttons. However, the code (shown in the following code) still populates the DataTable containing the list of installed data provider factories because this is needed to demonstrate the use of the GetFactory(DataRow) method later on. Figure 3 shows the page with the invariant name and description of the five installed providers displayed.

   ' declare a variable to hold the single instance that
   ' is required of the specific provider factory class
   Dim df As DbProviderFactory
   ' declare a variable to hold the DataTable containing
   ' the list of installed providers
   Dim dt As DataTable
   Sub Page_Load()
     ' get DataTable containing list of installed providers
     dt = DbProviderFactories.GetFactoryClasses()
     If Not Page.IsPostback Then
       ' bind to GridView control
       grid1.DataSource = dt
     End If
   End Sub
Author's Note: Obviously, in an ASP.NET application, you could avoid recreating the DataTable on each postback. The code could cache the DataTable between requests (it could be stored in the Application or in the ASP.NET Cache, as it is the same for all users of the application). Alternatively, you could use the invariant name in the GetFactory method, as described earlier, by extracting it from the selected row. However, the GetFactory method is not particularly resource-intensive, and so caching the DataTable it returns is probably not worthwhile.

Each Select button executes the routine named GetSomeDataRows, which is specified for the OnRowCommand attribute of the GridView control. The RowCommand event receives an instance of a GridViewCommandEventArgs class, whose CommandArgument property contains the index of the row in the GridView containing the Select button that was clicked. This value is first used to select the row in the GridView so that it is highlighted. Then it is used to specify the row in the DataTable that contains details of the selected provider factory. From this row, the code can extract the invariant name, which is displayed in the page and used later in the code. The next code fragment shows the relevant parts of the GetSomeDataRows routine:

   Sub GetSomeDataRows(s As Object, e As GridViewCommandEventArgs)
     ' get the row index within the DataTable from the GridView
     Dim iIndex As Integer = Convert.ToInt32(e.CommandArgument)
     ' select this row in the GridView control
     grid1.SelectedIndex = iIndex
     ' get an instance of the specified provider factory class
     df = DbProviderFactories.GetFactory(dt.Rows(iIndex))
     ' get the Invariant Name from the selected DataRow
     Dim sInvariant As String = dt.Rows(iIndex)("InvariantName")

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date