Factories and ADO.NET in Whidbey

I’ve been playing around with the alpha Whidbey release of VS .NET in preparation for doing some writing and speaking on the subject this spring. One of the first things about ADO.NET in Whidbey that caught my attention is the inclusion of a provider factory much like the one I created and included in our Atomic .NET course and made available on atomic.quilogy.com and discussed in my book on ADO.NET.

The goal of the ProviderFactory that I created was to allow developers to write .NET Data Provider independent code by providing a factory class that created the set of types necessary to work with a particular provider. My implementation followed the principle of the Abstract Factory Pattern documented in the GoF but instead of creating specific factory classes for each provider, relied instead on reflection to create the appropriate types at runtime. As a result my factory was a single class instead of a family of classes related through implementation inheritance. This had the effect of reducing the amount of code I had to write (just over 100 lines of code to support the OleDb, Odbc, and SqlClient providers) but dynamically creating the objects via reflection was slightly slower than providing concrete implementations (I did create a concrete implementation for the Compact Framework book I wrote with Jon Box to work with the SqlServerCe and SqlClient providers on the Compact Framework). My implementation then included the connection, command, parameter, and data adapter and returned interfaces from the factory methods. However, my approach ran into a few problems because the .NET Data Providers were based on interfaces rather than base classes. This manifested particularly when dealing with data adapters since the interface I was returning (IDataAdapter) didn’t include all of the members you would actually need to be able to work with a data adapter and the alternative (IDbDataAdapter) contained the command properties but not other members you would need. As a result, you had to cast from one interface to the other to get your code to work correctly.

I’m happy to report that Whidbey includes a DbProviderFactory base class and derived factory classes for each of the four .NET Data Providers that ship with the product. The DbProviderFactories class then includes a static GetFactory method that returns the appropriate factory class based on an invariant string passed to the method and typically read from the application configuration file in the appSettings element or using your own custom configuration section handler. For example, to use the factory to execute a command against SQL Server you could do the following:

DbProviderFactory pf = DbProviderFactories.GetFactory ("System.Data.SqlClient");
DbConnection con = pf.CreateConnection ();
con.ConnectionString = "server=.;database=Northwind;trusted_connection=yes";

DbCommand cmd = pf.CreateCommand ();
cmd.CommandText = "SELECT * FROM Products";
cmd.Connection = con;

You’ll notice that rather than return interfaces the CreateCommand and CreateConnection methods return the DbCommand and DbConnection classes respectively which now acts as the base class for all the provider command objects. Analogous base classes have been provided for other objects as well. In addition to the four standard objects the DbProviderFactory classes also return command builder, db table (more in a later post), and permission objects for the provider.

The invariant strings are configured in the machine.config file and read by the DbProviderConfigurationHandler class. For example, the providers are defined in the machine.config file like so:

<system.data>

        <DbProviderFactories>

           <add name="SqlClient Data Provider"

             invariant="System.Data.SqlClient" support="1FF"

             description=".Net Framework Data Provider for SqlServer"

             type="System.Data.SqlClient.SqlClientFactory,

             System.Data, Version=1.2.3400.0, Culture=neutral,

             PublicKeyToken=b77a5c561934e089"/>

        </DbProviderFactories>

</system.data>

You could also add your own entry and place it in the Web.config or application configuration file to use another provider.

A second way to create the factory is to use the GetFactoryClasses method, which populates a DataTable with all of the data from the DbProviderFactories configuration element. You can then return one of the factories based on the row in the DataTable like so:

DataTable dt = DbProviderFactories.GetFactoryClasses ();
DbProviderFactory pf = DbProviderFactories.GetFactory (dt.Rows[0]);
DbCommand cmd = pf.CreateCommand ();

Although I don’t know if this architecture will stick in the released version, I really like it and it makes it easy to use. I hope, however, that they add some overloads to the CreateXXX methods so you can pass in common properties such as the connection string, command text, command type, connection object etc. The current approach forces you to set the properties separately once the object is returned.