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

Creating Connections and Commands
The next step is to create the data access object instances required. First you create the connection by calling the CreateConnection method of the DbProviderFactory. You obtain the appropriate connection string from the ConnectionString property of the ConnectionStringBuilder. But, because of the problem with the ConnectionStringBuilder in Beta 1 (as mentioned earlier), the code then implements a work-around by replacing the connection string if the user selected the ODBC provider:

     ...
     ' create a connection using the new connection string
     Dim conn As DbConnection = df.CreateConnection()
     conn.ConnectionString = csb.ConnectionString
   
     '*******************************************
     ' fix for bug in ConnectionStringBuilder in Beta 1 -- 
     ' fixed in Beta 2
     If sInvariant = "System.Data.Odbc" Then
       conn.ConnectionString = _
       "Driver={SQL Server};Server=localhost;" & _
       "Database=AdventureWorks;Trusted_Connection=yes"
     End If
     '*******************************************
     ...

After displaying the connection string so that you can see what has been created, the code creates a command of the correct type using the CreateCommand method—but this time calls the method on the DbConnection that was previously created. This automatically uses the current connection, without having to specify it separately. Then the code specifies the parameterized SQL statement for the CommandText property, and sets the CommandType to CommandType.Text (although this is the default). Notice that the SQL statement uses the correct parameter name, as created in the Select Case statement in the previous sections of code:

     ...
     ' display connection string
     output.Text &= "Connection String: " & _
        conn.ConnectionString & "<p />"
   
     ' create a command from the connection and set properties
     Dim cmd As DbCommand = conn.CreateCommand()
     cmd.CommandText = "SELECT Description " & _
        "FROM [Sales].[SpecialOffer]" & _
        "WHERE DiscountPct > " & paramName
     cmd.CommandType = CommandType.Text
     ...

Next, you create the parameter itself. Here, the code uses the CreateParameter method of the current DbCommand instance, and sets the properties. This includes the parameter name, which is required if the selected provider is SqlClient (the name has no effect for other providers that pass parameters by position rather than by name). Even though the DBCommand creates the parameter, you must still remember to add it to the ParametersCollection of the command.

     ...
     ' create a parameter and add to command
     ' parameter name is only important for SqlClient
     Dim param As DbParameter = cmd.CreateParameter()
     param.ParameterName = "@discount"
     param.DbType = DbType.Double
     param.Value = "0.25"
     cmd.Parameters.Add(param)
     ...

Creating a DataAdapter and Retrieving Rows
Finally, the code creates a DbDataAdapter instance using the DbProviderFactory.CreateDataAdapter method, and sets the command just created as the SelectCommand property. Then, after creating a new DataSet to hold the incoming rows, it calls the Fill method of the DbDataAdapter and displays the rows returned from the database:

Figure 4. Using the System.Data.OleDb Provider: The figure shows the page generated by selecting the OleDB provider, including the connection string, the parameterized SQL query, and the query results.

     ...
     ' create DataAdapter and DataSet, and
     ' fetch some rows using selected provider
     Dim da As DbDataAdapter = df.CreateDataAdapter()
     da.SelectCommand = cmd
     Dim ds As New DataSet()
     Try
   
       da.Fill(ds, "Sales.SpecialOffer")
   
       ' iterate through table rows and display the results
       Dim disc As Double = _
          Convert.ToDouble(cmd.Parameters("@discount").Value)
       output.Text &= 
          "Products on sale with discount greater than " _
          & disc.ToString("P") & ":<br />"
       For Each dr As DataRow In ds.Tables(0).Rows
         output.Text &= " - " & dr("Description") & "<br />"
       Next
   
     Catch ex As Exception
       output.Text &= "<br />* ERROR: " & ex.Message
     End Try

Figure 4 shows the results of selecting the OleDb provider. You can see that the invariant name is System.Data.OleDb, and that the code creates the appropriate connection string for this provider (note that the connection string is displayed only when running the page on the local machine—it is not displayed when you access the page remotely). The page also shows the SQL statement containing the "?" parameter placeholder, followed by the results of executing the statement.

Figure 5. Using the System.Data.SqlClient Provider: The figure shows the page generated by selecting the SqlClient provider, including the connection string, the parameterized SQL query (including the parameter name as required by this provider), and the query results.

Figure 5 shows the results of selecting the SqlClient provider. In this case, the invariant name is System.Data.SqlClient, and the connection string uses the correct format for this provider. You can also see that the SQL statement now contains the name of the parameter, as required when using this provider.

Creating and Using a DataReader
As discussed earlier, you cannot create instances of a DbDataReader directly, as it is an abstract class. The technique for reading data through a DataReader, when using provider-independent code, is to declare the variable as of type IDataReader. The following code extract shows how you can modify the previous example to use a DataReader instead of a DataAdapter and DataSet:

   ' fetch some rows using selected provider and a DataReader. 
   ' Assumes that the command and parameters have already been 
   ' created, as in the previous example
   Try
     conn.Open()
     Using reader As IDataReader = _
        cmd.ExecuteReader(CommandBehavior.CloseConnection)
       ' iterate through table rows and display the results
       Dim disc As Double = _ 
          Convert.ToDouble(cmd.Parameters("@discount").Value)
       output.Text &= "Products on sale with discount " & _
          "greater than " & disc.ToString("P") & ":<br />"
       While reader.Read()
         output.Text &= " - " & reader("Description") & "<br />"
       End While
     End Using
   Catch ex As Exception
     output.Text &= "<br />* ERROR: " & ex.Message
   End Try

Author's Note: In version 2.0 of the .NET Framework, VB.NET supports the Using construct that was available only to C# in earlier versions. This is the preferred way of working with classes that implement the IDispose interface, and ensures that resources are promptly and correctly disposed of. The code shown here uses this approach with the DataReader.

Alternatively, instead of the Using construct, you could use the more traditional approach—but remember to destroy the reader when the process completes:

   Dim reader As IDataReader = _
      cmd.ExecuteReader(CommandBehavior.CloseConnection)

When Should I Use Provider-Independent Code?
To end this article, it's appropriate to discuss when and where to consider using provider-independent code. Remember that it is only an option, and is not the way that you should approach all your data access requirements from now on. It is primarily designed as a feature for tool and IDE suppliers, rather than regular data access applications. However, you may want to consider it if your application needs to be configurable to run against different database systems on a regular basis.

Remember that, although you can dynamically choose the data access provider type, you also have to contend with the differences between the connection strings and parameter name requirements. However, the good news is that the performance of provider-independent code is pretty much equal to code that uses the equivalent provider-specific classes (such as SqlClient or OleDb). You do have to create the provider factory, but you should do this once and subsequently reuse the factory. After doing that, there is no performance overhead, because the factory object returns the base class itself.

Author's Note: The code described in this article is based on the Beta 1 release of the .NET Framework v 2.0. In addition to the downloadable code on this site, you can download the code or run the examples online from http://www.daveandal.net/articles/provider-independent/.

Previous Page

---

• Download The Code for this Article

---