Export (0) Print
Expand All

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.

NoteNote:

The .NET Framework OracleClientFactory, OdbcFactory, and OleDbFactory classes also provide similar functionality.

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.

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.

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

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).

NoteNote:

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

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

Community Additions

ADD
Show:
© 2014 Microsoft