Using Provider Factories

There are situation in real world; when you dont want to write code, dependent on just one of the managed provider. This also helps to easily move from using one provider to another if the code design changes in the future. Pre-Whidbey, the only way to do this was to write your own wrapper classes for the providers. In whidbey this can be done usign ProviderFactories. Here is a sample code:

using System.Data.Common;

using System.Data;

using System;

using System.Data.SqlClient;


namespace DataViewer.Repro


      public class Repro


            public static int Main(string[] args)


                  string conString = "ConnectionString here";

                  string cmdText = "CommandText here";


                  //Select SQL Client factory - Can change to use any provider later

                  DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");


                  //Create Connection from the factory

                  DbConnection cn = factory.CreateConnection();

                  cn.ConnectionString = conString;



                  //Create Command from the factory

                  DbCommand cmd = factory.CreateCommand();


                  //Execute a command from the conneciton  

                  cmd.Connection = cn;

                  cmd.CommandText = cmdText;

                  DbDataReader reader = cmd.ExecuteReader();

                  while (reader.Read())




                  return 1;




After the provider is selected, there is no reference to any of the managed providers in the above code. The above code infact uses the SqlClient MP. In similar way, you can then use the factories to create Parameter, ConnectionStringBuilder, DataSourceEnumerator, Adapter, CommandBuilder and Permissions classes.

1. Generalization might also cause an extra overhead.
2. When writing genralized code, you will not be able to use features that are provider specific. You would have to special case them.

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Skip to main content