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 4

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.

Using a Provider Factory
After getting an instance of the required provider factory, you can use it to create instances of the provider-specific data access classes you'll need. The DbProviderFactory class exposes a series of methods that return these class instances, as listed in Table 1.

Table 1. DbProviderFactory Methods: The table lists the methods of the DbProviderFactory that return provider-dependent class instances.
Method Description
CreateConnectionStringBuilder() Returns an instance of a DbConnectionStringBuilder that you can use to create connection strings dynamically.
CreateConnection() Returns an instance of a DbConnection that you can use to connect to a data store. The DbConnection class exposes a method CreateCommand() that returns a new DbCommand instance. You can use this instead of the DbProviderFactory.CreateCommand() method to create a command for that connection.
CreateCommand() Returns an instance of a DbCommand that you can use to execute SQL statements and stored procedures. The DbCommand class exposes a method CreateParameter() that returns a new DbParameter instance. You can use this instead of the DbProviderFactory.CreateParameter() method to create parameters for that command.
CreateParameter() Returns an instance of a DbParameter that you can use to pass values into and out of SQL statements and stored procedures.
CreateCommandBuilder() Returns an instance of a DbCommandBuilder that you can use to create the UPDATE, INSERT and DELETE SQL statements for a DataAdapter automatically.
CreateDataAdapter() Returns an instance of a DbDataAdapter that you can use to fill or update a DataSet or DataTable.
CreateDataSourceEnumerator() Returns an instance of a DbDataSourceEnumerator that you can use to examine the data sources available through this DbProviderFactory instance.
CreatePermission(PermissionState) Takes a value from the PermissionState enumeration and returns an instance of a CodeAccessPermission that you can use to ensure that callers have been granted appropriate permission for all the objects they require access to.

You'll see how to use some of these methods in the following example. Note, however, that there is no method to create instances of a DbDataReader. This is still an abstract class, and (as in ADO.NET version 1.x) you can create an instance only by executing the ExecuteReader method of a Command object. To declare a variable to reference a DbDataReader you use the IDataReader interface. You'll see how this works in the final example in this article.

Specifying Connection Strings and Parameter Names
Unfortunately, there is another issue to contend with when writing provider-independent data access code. Although the code can automatically adjust to create the required provider-specific data access class instances, these classes have other dependencies. The data store connection strings differ for each provider, and the way that parameter naming behaves can also depend on the provider type. To assist you in creating the appropriate connection string, ADO.NET includes a new class in the System.Data.Common namespace named DbConnectionStringBuilder.

DbConnectionStringBuilder is rather like a cross between the StringBuilder and Dictionary classes. It exposes an Add method that takes two parameters, a key and a value. So, you can add the individual "name/value" pairs that are required in a connection string one at a time. Then you extract the complete connection string from the ConnectionString property of the DbConnectionStringBuilder. The following code creates a connection string suitable for use with the SqlClient classes, and then sets the ConnectionString property of a new DbConnection instance:

   ' create and populate a DbConnectionStringBuilder
   Dim csb As DbConnectionStringBuilder = _
   csb.Add("Data Source", "localhost")
   csb.Add("Initial Catalog", "AdventureWorks")
   csb.Add("Integrated Security", "SSPI")
   ' create a connection using this connection string
   Dim conn As DbConnection = df.CreateConnection() 
   conn.ConnectionString = csb.ConnectionString
Author's Note: There's a bug in the DbConnectionStringBuilder in Beta 1 that incorrectly creates the section around the curly brackets that are required when specifying Driver={SQL Server} for an ODBC connection string. The example code used here contains a temporary work-around for this bug.

The second area where your code must cope with the variations in the behavior of the different data providers is in the names of parameters. Most providers use a "question-mark" (?) character as the placeholder for a parameter in a SQL statement. Parameters are passed by position—which means that the names of the individual parameters are irrelevant. However, the classes in the SqlClient namespace pass parameters by name, so you must ensure that your code can adapt to this.

The examples you see here use a Select Case statement (a switch statement in C#) that examines the invariant name of the selected provider and then—based on this—builds the correct connection string and a parameter name to suit the SQL statement that will be used later on. You can see all this in the next code fragment. As the machine used for this article does not have access to an Oracle database, and the standard Beta 1 installation has problems with the SqlServerCe provider, the code handles those two selections by simply displaying a message and exiting from the routine:

     ' create a builder for the connection string
     Dim csb As DbConnectionStringBuilder = _
     ' fill in appropriate connection string details
     ' and set parameter name (which is provider-specific)
     Dim paramName As String = String.Empty
     Select Case sInvariant
       Case "System.Data.SqlClient"
         csb.Add("Data Source", "localhost")
         csb.Add("Initial Catalog", "AdventureWorks")
         csb.Add("Integrated Security", "SSPI")
         paramName = "@discount"
       Case "System.Data.OleDb"
         csb.Add("Provider", "SQLOLEDB")
         csb.Add("Data Source", "localhost")
         csb.Add("Initial Catalog", "AdventureWorks")
         csb.Add("Integrated Security", "SSPI")
         paramName = "?"
       Case "System.Data.Odbc"
         csb.Add("Driver", "{SQL Server}")
         csb.Add("Server", "localhost")
         csb.Add("Database", "AdventureWorks")
         csb.Add("Trusted_Connection", "yes")
         paramName = "?"
       Case Else
         ' cannot connect to other data sources so exit
         output.Text = _
            "Sorry, cannot extract rows using this provider."
         Exit Sub
     End Select

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