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 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.
Note
The OracleClientFactory, OdbcFactory, and OleDbFactory classes also provide similar functionality.
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.
| Column ordinal | Column name | Example output | Description |
|---|---|---|---|
| 0 | Name | SqlClient Data Provider | Readable name for the data provider |
| 1 | Description | .Net Framework Data Provider for SqlServer | Readable description of the data provider |
| 2 | InvariantName | System.Data.SqlClient | Name that can be used programmatically to refer to the data provider |
| 3 | AssemblyQualifiedName | System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 | Fully qualified name of the factory class, which contains enough information to instantiate the object |
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.
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;
}
' 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
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>
Important
Microsoft recommends that you use the most secure authentication flow available. If you're connecting to Azure SQL, Managed Identities for Azure resources is the recommended authentication method.
Retrieve 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.
Note
A reference to System.Configuration.dll is required in order for the code to run.
// 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 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 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;
}
' 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