Section A.1. Data Provider Enumeration and Factories

A.1. Data Provider Enumeration and Factories

Data providers in ADO.NET 1.0 and 1.1 are a set of provider-specific classes that implemented generic interfaces. These interfaces can be used to write code that is data provider independent. For example, the data connection classes in the Microsoft SQL Server data provider (SqlConnection) and the Microsoft Oracle data provider (OracleConnection) both implement the IDbConnection interface. Code based on the IDbConnection interface that is common to both classes, rather than a database-specific instance of a data provider, is independent of the data provider and therefore not dependent on the underlying database. The disadvantage of the interface approach is that you cannot use the interface to access any database-specific features implemented as members of the data provider class but not defined as part of the interfacethe ChangeDatabase( ) method of the Oracle data provider, for example.

ADO.NET 2.0 introduces the Common Model, based on the Factory design pattern, which uses a single API to access databases having different providers. Data provider factories let your code work with multiple data providers without choosing a specific provider. The factory class creates and returns a strongly typed, provider-specific object based on information in the request. This lets you write data provider-independent code and select the provider at runtime. Using the Common Model, it becomes easier to write an application to support multiple databases.

The DbProviderFactories class in the System.Data.Common namespace lets you retrieve information about installed .NET data providers. The static GetFactoryClasses( ) method returns a DataTable object containing information about the installed data providers that implement the abstract base class DbProviderFactory, with the schema described in Table A-1.

Table A-1. DataTable schema for GetFactoryClasses( ) method results

Column name	Description
Name	Data provider name.
Description	Data provider description.
InvariantName	A unique identifier for a data provider registered in machine.config in the <system.data><DbProviderFactories> element. For example, the invariant name for SQL Server is System.Data.SqlClient. The invariant name is used to programmatically refer to the data provider.
AssemblyQualifiedName	Fully qualified name of the data provider factory classenough information to instantiate the object.

The following console application uses the DbProviderFactories class to get information about the installed data providers:

 using System; using System.Data; using System.Data.Common; class Program {     static void Main(string[] args)     {         DataTable dt = DbProviderFactories.GetFactoryClasses(  );         foreach (DataRow row in dt.Rows)             Console.WriteLine("{0}\n\r  {1}\n\r  {2}\n\r  {3}\n\r",             row["Name"], row["Description"], row["InvariantName"],             row["AssemblyQualifiedName"]);         Console.WriteLine("Press any key to continue.");         Console.ReadKey(  );     } } 

The output is similar to that shown in Figure A-1.

The providers listed in Figure A-1 correspond to the DbProviderFactories element in machine.config, shown in the following excerpt:

 <system.data>   <DbProviderFactories>     <add name="Odbc Data Provider" invariant="System.Data.Odbc"       description=".Net Framework Data Provider for Odbc"       type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0,       Culture=neutral, PublicKeyToken=b77a5c561934e089" />     <add name="OleDb Data Provider" invariant="System.Data.OleDb"       description=".Net Framework Data Provider for OleDb"       type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0,       Culture=neutral, PublicKeyToken=b77a5c561934e089" />     <add name="OracleClient Data Provider" invariant="System.Data.OracleClient"       description=".Net Framework Data Provider for Oracle"       type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient,       Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />     <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" />     <add name="SQL Server CE Data Provider"       invariant="Microsoft.SqlServerCe.Client" support="3F7"       description=".NET Framework Data Provider for Microsoft SQL Server 2005       Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory,       Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral,       PublicKeyToken=89845dcd8080cc91" />   </DbProviderFactories> </system.data> 

Figure A-1. Information about installed data providers

The static GetFactory( ) method of the DbProviderFactories class takes a single argumenteither a DataRow object from the table returned by the GetFactoryClasses( ) method or a string containing the invariant name of the providerand returns a DbProviderFactory instance for that data provider.

The DbProviderFactory class is an abstract base class that every ADO.NET 2.0 data provider must implement. DbProviderFactory is a data provider-independent class that provides a strongly typed object based on information supplied at runtime. The provider-specific classes derived from DbProviderFactory installed with .NET Framework 2.0 are listed in Table A-2.

Table A-2. Provider-specific classes derived from DbProviderFactory installed with .NET Framework 2.0

Factory class	Description
System.Data.Odbc.OdbcFactory	Used to create instances of ODBC provider classes
System.Data.OleDb.OleDbFactory	Used to create instances of OLE DB provider classes
System.Data.OracleClient.OracleClientFactory	Used to create instances of Oracle provider classes
System.Data.SqlClient.SqlClientFactory	Use to create instances of SQL Server provider classes

The DbProviderFactory class has public methods, listed in Table A-3, that are used to create the provider-specific class instances.

Table A-3. DbProviderFactory class public methods

Method	Description
CreateCommand	Returns a DbCommand instancethe base class for strongly typed command objects
CreateCommandBuilder	Returns a DbCommandBuilder instancethe base class for strongly typed command builder objects
CreateConnection	Returns a DbConnection instancethe base class for strongly typed connection objects
CreateConnectionStringBuilder	Returns a DbConnectionStringBuilder instancethe base class for strongly typed connection string builder objects
CreateDataAdapter	Returns a DbDataAdapter instancethe base class for strongly typed data adapter objects
CreateDataSourceEnumerator	Returns a DbDataSourceEnumerator instancethe base class for strongly typed data source enumerator objects
CreateParameter	Returns a DbParameter instancethe base class for strongly typed parameter objects
CreatePermission	Returns a CodeAccessPermission instancethe base class for strongly typed code access permission objects

The following console application shows how to create an instance of the SqlClientFactory class and use it to output the top 10 rows from the Person.Contact table in AdventureWorks:

 using System; using System.Data; using System.Data.Common; class Program {     static void Main(string[] args)     {         // create factory using the invariant name         DbProviderFactory f =             DbProviderFactories.GetFactory("System.Data.SqlClient");         DbConnection conn = f.CreateConnection(  );         conn.ConnectionString = "Data Source=localhost;" +             "Integrated Security=SSPI;Initial Catalog=AdventureWorks";         DbCommand selectCommand = conn.CreateCommand(  );         selectCommand.CommandText = "SELECT TOP 10 " +             "FirstName, LastName, EmailAddress " +             "FROM Person.Contact ORDER BY LastName";         DataTable dt = new DataTable(  );         DbDataAdapter da = f.CreateDataAdapter(  );         da.SelectCommand = selectCommand;         da.Fill(dt);         foreach (DataRow row in dt.Rows)             Console.WriteLine(row[0] + ", " + row[1] + ", " + row[2]);         Console.WriteLine(Environment.NewLine + "Press any key to continue.");         Console.ReadKey(  );     } } 

Results are shown in Figure A-2.

Figure A-2. Results for SqlClientFactory example

The code is database-independent, with the exception of the invariant name of the provider and the connection string, highlighted in the preceding example. These arguments would normally be retrieved from a configuration file or similar mechanism rather than hardcoded, to make the application truly database-independent.

The ConfigurationManager class in the System.Configuration namespace provides access to application configuration information. The ConnectionStrings( ) method returns a ConnectionStringSettingsCollection instance containing the connection strings for the application, each one corresponding to a named connection string in the <connectionStrings> section of the application configuration file.

This example shows how to retrieve a connection string from the configuration file. First create a new console application project in Visual Studio .NET. Select Add New Item Application Configuration File to add a new application configuration file named App.config. Add a connection string to the filethe following snippet shows the completed configuration file with the connection string named MyConnection highlighted:

 <?xml version="1.0" encoding="utf-8" ?> <configuration>   <connectionStrings>     <add name="MyConnection" connectionString="Data Source=localhost;       Integrated Security=SSPI;Initial Catalog=AdventureWorks"       providerName="System.Data.SqlClient" />   </connectionStrings> </configuration> 

The following code retrieves the connection string from the configuration file. You need to add a reference to the System.Configuration assembly to compile and execute this example.

 using System; using System.Collections; using System.Data.SqlClient; using System.Configuration; class Program {     static void Main(string[] args)     {         //// get the configuration string from the config file         Configuration c =             ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);         ConnectionStringsSection css = c.ConnectionStrings;         for (int i = 0; i < css.ConnectionStrings.Count; i++)         {             Console.WriteLine(css.ConnectionStrings[i].Name);             Console.WriteLine("  " + css.ConnectionStrings[i]);             Console.WriteLine(  );         }         Console.WriteLine("Press any key to continue.");         Console.ReadKey(  );     } } 

Results are shown in Figure A-3.

Figure A-3. Results for retrieving configuration strings from application configuration file example

Two connection strings are retrieved. The first is the default string defined in the Machine.config file, as shown in the excerpt that follows:

   <connectionStrings>     <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;       Integrated Security=SSPI;       AttachDBFilename=|DataDirectory|aspnetdb.mdf;       User Instance=true" providerName="System.Data.SqlClient" />   </connectionStrings> 

DbConnectionStringBuilder is a helper class used to construct provider-specific connection strings. You supply the connection string name-value pairs to the Add( ) method and retrieve the connection string using the ConnectionString property. You could change the previous example so that it constructs the connection string using the connection string builder, and then assign it to the ConnectionString property of the connection with the following code:

 using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; class Program {     static void Main(string[] args)     {         // build the connection string         DbConnectionStringBuilder csb = new DbConnectionStringBuilder(  );         csb["Data Source"] = "localhost";         csb["Integrated Security"] = "SSPI";         csb["Initial Catalog"] = "AdventureWorks";         // create a connection using the connection string         SqlConnection conn = new SqlConnection(  );         conn.ConnectionString = csb.ConnectionString;         // output the connection string         Console.WriteLine(csb.ConnectionString);         Console.WriteLine("Press any key to continue.");         Console.ReadKey(  );     } } 

Results are shown in Figure A-4.

Figure A-4. Results for DbConnectionStringBuilder example