Obtaining a DbProviderFactory

The process of obtaining a DbProviderFactory involves passing information about a data provider to the DbProviderFactories class. Based on this information, the GetFactory method returns a strongly typed provider factory. For example, after you register SqlClientFactory, pass GetFactory the provider name Microsoft.Data.SqlClient. Another overload of GetFactory takes a DataRow. Once you obtain 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

Install the Microsoft.Data.SqlClient NuGet package before you register its factory.

On .NET Framework, add the following system.data section inside the configuration element of your application's app.config or web.config file. The remove element avoids duplicate registrations inherited from another configuration file.

<system.data>
  <DbProviderFactories>
    <remove invariant="Microsoft.Data.SqlClient" />
    <add name="Microsoft SqlClient Data Provider"
     invariant="Microsoft.Data.SqlClient"
     description="Microsoft SqlClient Data Provider for SQL Server"
     type="Microsoft.Data.SqlClient.SqlClientFactory, Microsoft.Data.SqlClient"
    />
  </DbProviderFactories>
</system.data>

On modern .NET, configuration files don't register provider factories. Before you call GetFactory, call RegisterFactory(String, DbProviderFactory) with Microsoft.Data.SqlClient as the provider name and Instance as the factory instance. RegisterFactory isn't available on .NET Framework.

The following examples register the factory in code for modern .NET. For .NET Framework, use the previous configuration entry and omit any RegisterFactory call.

The invariant provider name identifies the underlying data provider. Use the same name for factory lookups and the providerName attribute of a connection string.

Retrieving Provider Information

You can retrieve information about registered data providers by using the GetFactoryClasses method. It returns a DataTable named DbProviderFactories that contains the columns described in the following table. The example values correspond to the .NET Framework configuration entry shown previously; assembly version details depend on the installed package.

This DataTable can be used to enable a user to select a DataRow at runtime. 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.
    static DataTable GetProviderFactoryClasses()
    {
#if !NETFRAMEWORK
        DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance);
#endif

        // 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;
    }

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

#If Not NETFRAMEWORK Then
        DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance)
#End If

        ' 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

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="Microsoft.Data.SqlClient"
     connectionString=
     "Data Source=&lt;server&gt;;Initial Catalog=Northwind;Integrated Security=true;Encrypt=true"
    />

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

Retrieve a Connection String by Provider Name

To create a connection through a provider factory, 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 invariant provider name, such as Microsoft.Data.SqlClient. The code iterates through the ConnectionStringSettingsCollection. It returns the matching connection string 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.

// Retrieve a connection string by specifying the providerName.
// Assumes one connection string per provider in the config file.
static string? GetConnectionStringByProvider(string providerName)
{
    // 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)
            {
                return cs.ConnectionString;
            }
        }
    }
    return null;
}

' 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

Creating the DbProviderFactory and DbConnection

This example demonstrates how to create a DbProviderFactory and DbConnection object by passing it the invariant provider name, such as Microsoft.Data.SqlClient, 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 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
            {
#if !NETFRAMEWORK
                if (providerName == "Microsoft.Data.SqlClient")
                {
                    DbProviderFactories.RegisterFactory(providerName, SqlClientFactory.Instance);
                }
#endif

                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;
    }

    ' 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
#If Not NETFRAMEWORK Then
                DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance)
#End If
                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

See also

• DbProviderFactories
• Connection Strings
• Using the Configuration Classes
• ADO.NET Overview