Working with the Common Provider Model


In ADO.NET 1.x, you could either code to the provider-specific classes, such as SqlConnection, or the generic interfaces, such as IDbConnection. If there were a possibility that the database you were programming against would change during your project, or if you were creating a commercial package intended to support customers with different databases, then you had to use the generic interfaces. You can’t call a constructor on an interface, so most generic programs included code that accomplished the task of obtaining the original IDbConnection by means of their own factory method, such as a GetConnection method that would return a provider-specific instance of the IDbConnection interface.

ADO.NET 2.0 has a more elegant solution for getting the provider-specific connection. Each data provider registers a ProviderFactory class and a provider string in the .NET machine.config. A base ProviderFactory class (DbProviderFactory) and a System.Data.Common.ProviderFactories class can return a DataTable of information about different data providers registered in machine.config, and can return the correct ProviderFactory given the provider string (called ProviderInvariantName) or a DataRow from the DataTable. Instead of writing your own framework to build connections based on the name of the provider, ADO.NET 2.0 makes it much more straightforward, flexible, and easy to solve this problem.

Let’s look at an example of using the common provider model to connect to the pubs database and display some rows from the authors table. In the AdoNetFeaturesTest project, on the TestForm.vb form, the providerButton Button’s Click event handler shows this functionality. The code is broken down into six steps.

The first step is get the provider factory object based on a configuration value of the provider’s invariant name:

 Private Sub _providerButton_Click(ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles _providerButton.Click         ' 1. Factory         ' Create the provider factory from config value.         Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(ConfigurationSettings.AppSettings("providerInvariant Name"))

You are able to get the factory via the DbProviderFactories object’s GetFactory method and pass in the string name of the provider invariant that you are storing in the project’s app.config file. Here is the entry in the app.config file:

  <add key="providerInvariantName" value="System.Data.SqlClient" /> 

In this case, you are using the SQL Server Data Provider. Once you have the factory object, the next step is to use it to create a connection:

         ' 2. Connection         ' Create the connection from the factory.         Dim connection As DbConnection = factory.CreateConnection()         ' Get the connection string from config.         connection.ConnectionString = ConfigurationSettings.AppSettings("dbConnectionString")

The connection is created by calling the DbProviderFactory’s CreateConnection method. In this case, the factory is returning a SqlConnection, because you chose to use the System.Data.SqlClient provider invariant. To keep your code generic, you will not be directly programming against any of the classes in the System.Data.SqlClient namespace. Note how the connection class you declare is a DbConnection class, which is part of the System.Data namespace.

The next step is to create a Command object, so you can retrieve the data from the authors table:

  ' 3. Command  ' Create the command from the connection.  Dim command As DbCommand = connection.CreateCommand()  ' Set the type of the command to stored procedure.  command.CommandType = CommandType.StoredProcedure  ' Set the name of the stored procedure to execute.  command.CommandText = "usp_authors_Get_All" 

You begin by declaring a generic DbCommand class variable and then using the DbConnection’s CreateCommand method to create the DbCommand instance. Once you have done that, you set the command type to stored procedure and then set the stored procedure name.

This example uses a DbDataAdapter to fill a DataTable with the authors data. Here is how you create and initialize the DbDataAdapter:

  ' 4. Adapter ' Create the adapter from the factory. Dim adapter As DbDataAdapter = factory.CreateDataAdapter() ' Set the adapter's select command. adapter.SelectCommand = command 

Just as you did when you created your DbConnection instance, you use the factory to create your DbDataAdapter. After creating it, you then set the SelectCommand property’s value to the instance of the previously initialized DbCommand instance.

After finishing these steps, the next step is to create a DataTable and fill it using the DataAdapter:

  ' 5. DataTable ' Create a new DataTable. Dim authors As New DataTable("Authors") ' Use the adapter to fill the DataTable. adapter.Fill(authors) 

The final step is to bind the table to the form’s grid:

  ' 6.  Grid ' Populate the grid with the data. BindGrid(authors) 

You already looked at the BindGrid helper method in the asynchronous example earlier. In this example, you are simply reusing this generic method again:

 Private Sub BindGrid(ByVal table As DataTable)         ' Clear the grid.         Me._authorsGridView.DataSource = Nothing         ' Bind the grid to the DataTable.         Me._authorsGridView.DataSource = table End Sub

The main point to take away from this example is that you were able to easily write database-agnostic code with just a few short lines of code. ADO.NET 1.x required a lot of lines of code to create this functionality; you had to write your own abstract factory classes and factory methods in order to create instances of the generic database interfaces, such as IDbConnection, IDbCommand, and so on.




Professional VB 2005 with. NET 3. 0
Professional VB 2005 with .NET 3.0 (Programmer to Programmer)
ISBN: 0470124709
EAN: 2147483647
Year: 2004
Pages: 267

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net