Provider Enumeration in Whidbey

I have been wanting to post for a long time now, and have finally decided to FIGHT my slackness. I will continue with my views on some of the new features in ADO.Net for Whidbey. I will start with talking about a new feature in Whidbey – Provider Enumeration.

Pre-Whidbey there was no way to know if a particular provider is available on your computer. In addition there was no API from ADO.Net that allowed you to choose a provider at execution time (by some cool looking UI) and then run the Application, written in a provider-independent way with the selected provider. There could be scenarios in which getting this list of providers seamlessly from the API could be useful.

In Whidbey, there is a way to enumerate the providers on the computer. Here is a sample program that does just that:

using System.Data;
using System.Data.Common;
using System;

public class Repro
  public static int Main(string[] args)
      DataTable datatable1 = DbProviderFactories.GetFactoryClasses(); // DbProviderFactories
      foreach (DataRow row in datatable1.Rows) {
         Console.WriteLine(“Provider Name:”+row[“Name”]);
         Console.WriteLine(“Provider Description:”+row[“Description”]);
         Console.WriteLine(“Provider Invariant Name:”+ row[“InvariantName”]);
      return 1;

Output:Provider Name:Odbc Data Provider
Provider Description:.Net Framework Data Provider for Odbc
Provider Invariant Name:System.Data.Odbc
Provider Name:OleDb Data Provider
Provider Description:.Net Framework Data Provider for OleDb
Provider Invariant Name:System.Data.OleDb
Provider Name:OracleClient Data Provider
Provider Description:.Net Framework Data Provider for Oracle
Provider Invariant Name:System.Data.OracleClient
Provider Name:SqlClient Data Provider
Provider Description:.Net Framework Data Provider for SqlServer
Provider Invariant Name:System.Data.SqlClient

What did I just do? The DbProviderFactories has a static method GetFactoryClasses that enumerates through all the providers that are on my system and returns it as a DataTable. Then I iterate throw all the rows in the DataTable in the foreach statement and print the output. The returned table has 5 columns with the following Column Names

  • Name: this has the Name of the provider 
  • Description: describes what the provider does
  • InVariantName: returns the invariant name of the assembly
  • AssemblyQualifiedName: a fully qualified assembly name
  • Supported classes: Gets the value of DbProviderSupportedClasses, which tells the set of of classes (Connection, Command, DataReader, Adapter) that this provider supports.

Q: Where is the information about the provider got from?
A: All this information about the provider is received from the Configuration file. (This could be any of the config file- machine-level, app-level or user-level)

Q: Why doesn’t a third party provider does not feature in the list even when the provider is installed on my machine?
A: There is no standard way to know if a 3rd party provider is installed on the system. Since the enumeration shows only the information available in the configuration file, to make it appear in the list the information pertaining to the provider should be mentioned in the config file.

In the next blog, I will post how you can use the Factory classes to build provider independent code.

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

Comments (3)

  1. Kirk Marple says:

    What does ADO.NET key off of to know which provider to load at runtime? It is as simple as just looking at the connection string?

    I’ve been doing some work lately with pluggable protocols and the WebClient class, and they key off of the URI scheme (file, http, etc.).

  2. Sushil says:

    Kirk, ADO.Net looks at the AssemblyQualifiedName field from the config file and creates the Factory class via Reflection. After the provider-specifc factory class is created it then returns its specific component objects.Example, SqlClientFactory then returns SqlCommand,SqlConnection,SqlDataAdapter and so on.