Obtaining a DbProviderFactory (ADO.NET)

The process of obtaining a DbProviderFactory involves passing information about a data provider to the DbProviderFactories class. Based on this information, the GetFactory method creates a strongly typed provider factory. For example, to create a SqlClientFactory, you can pass GetFactory a string with the provider name specified as "System.Data.SqlClient". The other overload of GetFactory takes a DataRow. Once you create the provider factory, you can then use its methods to create additional objects. Some of the methods of a SqlClientFactory include CreateConnection, CreateCommand, and CreateDataAdapter.

Registering DbProviderFactories

Each .NET Framework data provider that supports a factory-based class registers configuration information in the DbProviderFactories section of the machine.config file on the local computer. The following configuration file fragment shows the syntax and format for System.Data.SqlClient.

<system.data>
  <DbProviderFactories>
    <add name="SqlClient Data Provider"
     invariant="System.Data.SqlClient" 
     description=".Net Framework Data Provider for SqlServer" 
     type="System.Data.SqlClient.SqlClientFactory, System.Data, 
     Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    />
  </DbProviderFactories>
</system.data>

The invariant attribute identifies the underlying data provider. This three-part naming syntax is also used when creating a new factory and for identifying the provider in an application configuration file so that the provider name, along with its associated connection string, can be retrieved at run time.

Retrieving Provider Information

You can retrieve information about all of the data providers installed on the local computer by using the GetFactoryClasses method. It returns a DataTable named DbProviderFactories that contains the columns described in the following table.

This DataTable can be used to enable a user to select a DataRow at run time. The selected DataRow can then be passed to the GetFactory method to create a strongly typed DbProviderFactory. A selected DataRow can be passed to the GetFactory method to create the desired DbProviderFactory object.

Listing the Installed Provider Factory Classes

This example demonstrates how to use the GetFactoryClasses method to return a DataTable containing information about the installed providers. The code iterates through each row in the DataTable, displaying information for each installed provider in the console window.

' This example assumes a reference to System.Data.Common.
Private Shared Function GetProviderFactoryClasses() As DataTable

    ' Retrieve the installed providers and factories.
    Dim table As DataTable = DbProviderFactories.GetFactoryClasses()

    ' Display each row and column value.
    Dim row As DataRow
    Dim column As DataColumn
    For Each row In table.Rows
        For Each column In table.Columns
            Console.WriteLine(row(column))
        Next
    Next

    Return table
End Function

// This example assumes a reference to System.Data.Common.
static DataTable GetProviderFactoryClasses()
{
    // Retrieve the installed providers and factories.
    DataTable table = DbProviderFactories.GetFactoryClasses();

    // Display each row and column value.
    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn column in table.Columns)
        {
            Console.WriteLine(row[column]);
        }
    }
    return table;
}

Using Application Configuration Files to Store Factory Information

The design pattern used for working with factories entails storing provider and connection string information in an application configuration file, such as app.config for a Windows application, and web.config for an ASP.NET application.

The following configuration file fragment demonstrates how to save two named connection strings, "NorthwindSQL" for a connection to the Northwind database in SQL Server, and "NorthwindAccess" for a connection to the Northwind database in Access/Jet. The invariant name is used for the providerName attribute.

<configuration>
  <connectionStrings>
    <clear/>
    <add name="NorthwindSQL" 
     providerName="System.Data.SqlClient" 
     connectionString=
     "Data Source=MSSQL1;Initial Catalog=Northwind;Integrated Security=true"
    />

    <add name="NorthwindAccess" 
     providerName="System.Data.OleDb" 
     connectionString=
     "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Northwind.mdb;"
    />
  </connectionStrings>
</configuration>

Retrieving a Connection String by Provider Name

In order to create a provider factory, you must supply a connection string as well as the provider name. This example demonstrates how to retrieve a connection string from an application configuration file by passing the provider name in the invariant format "System.Data.ProviderName". The code iterates through the ConnectionStringSettingsCollection. It returns the ProviderName on success; otherwise null (Nothing in Visual Basic). If there are multiple entries for a provider, the first one found is returned. For more information and examples of retrieving connection strings from configuration files, see Connection Strings and Configuration Files (ADO.NET).

' Retrieve a connection string by specifying the providerName.
' Assumes one connection string per provider in the config file.
Private Shared Function GetConnectionStringByProvider( _
    ByVal providerName As String) As String

    'Return Nothing on failure.
    Dim returnValue As String = Nothing

    ' Get the collection of connection strings.
    Dim settings As ConnectionStringSettingsCollection = _
        ConfigurationManager.ConnectionStrings

    ' Walk through the collection and return the first 
    ' connection string matching the providerName.
    If Not settings Is Nothing Then
        For Each cs As ConnectionStringSettings In settings
            If cs.ProviderName = providerName Then
                returnValue = cs.ConnectionString
                Exit For
            End If
        Next
    End If

    Return returnValue
End Function

// Retrieve a connection string by specifying the providerName.
// Assumes one connection string per provider in the config file.
static string GetConnectionStringByProvider(string providerName)
{
    // Return null on failure.
    string returnValue = null;

    // Get the collection of connection strings.
    ConnectionStringSettingsCollection settings =
        ConfigurationManager.ConnectionStrings;

    // Walk through the collection and return the first 
    // connection string matching the providerName.
    if (settings != null)
    {
        foreach (ConnectionStringSettings cs in settings)
        {
            if (cs.ProviderName == providerName)
                returnValue = cs.ConnectionString;
            break;
        }
    }
    return returnValue;
}

Creating the DbProviderFactory and DbConnection

This example demonstrates how to create a DbProviderFactory and DbConnection object by passing it the provider name in the format "System.Data.ProviderName" and a connection string. A DbConnection object is returned on success; null (Nothing in Visual Basic) on any error.

The code obtains the DbProviderFactory by calling GetFactory. Then the CreateConnection method creates the DbConnection object and the ConnectionString property is set to the connection string.

' Given a provider, create a DbProviderFactory and DbConnection.
' Returns a DbConnection on success; Nothing on failure.
Private Shared Function CreateDbConnection( _
    ByVal providerName As String, ByVal connectionString As String) _
    As DbConnection

    ' Assume failure.
    Dim connection As DbConnection = Nothing

    ' Create the DbProviderFactory and DbConnection.
    If Not connectionString Is Nothing Then
        Try
            Dim factory As DbProviderFactory = _
               DbProviderFactories.GetFactory(providerName)

            connection = factory.CreateConnection()
            connection.ConnectionString = connectionString

        Catch ex As Exception
            ' Set the connection to Nothing if it was created.
            If Not connection Is Nothing Then
                connection = Nothing
            End If
            Console.WriteLine(ex.Message)
        End Try
    End If

    ' Return the connection.
    Return connection
End Function

// Given a provider name and connection string, 
// create the DbProviderFactory and DbConnection.
// Returns a DbConnection on success; null on failure.
static DbConnection CreateDbConnection(
    string providerName, string connectionString)
{
    // Assume failure.
    DbConnection connection = null;

    // Create the DbProviderFactory and DbConnection.
    if (connectionString != null)
    {
        try
        {
            DbProviderFactory factory =
                DbProviderFactories.GetFactory(providerName);

            connection = factory.CreateConnection();
            connection.ConnectionString = connectionString;
        }
        catch (Exception ex)
        {
            // Set the connection to null if it was created.
            if (connection != null)
            {
                connection = null;
            }
            Console.WriteLine(ex.Message);
        }
    }
    // Return the connection.
    return connection;
}

See Also

Concepts

DbProviderFactories (ADO.NET)

Using the Configuration Classes

Other Resources

Connection Strings (ADO.NET)

ADO.NET Managed Providers and DataSet Developer Center