Learn how to leverage the new data provider factory. Whether a database belongs to SQL Server, Oracle, Access or something else, you can employ one solution that enables
you to connect to them all: a data provider factory.
Tested with Microsoft Visual Studio Version 8.0.50727.42; Microsoft .NET Framework Version 2.0.50727
Using the New Data Provider Independent Model in .Net 2.0
The purpose of a data factory is to provide you with a dynamic way to connect to any (almost) type of database.
Whether a database belongs to SQL Server, Oracle, Access or other, you can employ one solution that enables
you to connect to them all: a data factory. This article describes how you can accomplish such a task.
Microsoft calls this model the Provider Independent Model, and is it's based on the
Factory design pattern.
The new data provider factory model in .Net 2.0 is easier to use than the model(s) in the previous versions of .Net
as it doesn't make use of implementation-lacking interfaces. Rather, the new model makes use of abstract classes that
contain the functionality you need based on the type of data provider you want to employ.
For example, if you wanted your application to
retrieve data from an Oracle database, you would obtain a data factory from the new model by passing it the
"System.Data.OracleClient" invariant provider name. In return, the factory returned would be an
abstract DbProviderFactory class which could be cast to an OracleClientFactory class (via the
System.Data.OracleClient namespace). In most cases, however,
this type of casting will be unnecessary as you should be able to do everything you need to via the abstract
DbProviderFactory class itself.
Similarly, if you wanted your application to
retrieve data from an SQL Server database, you would obtain a data factory from the new model by passing it the
"System.Data.SqlClient" invariant provider name. In return, the factory returned would be an
abstract DbProviderFactory class which could be cast to an SqlClientFactory class (via the
System.Data.SqlClient namespace).
Determining what providers are available to ADO.NET with GetFactoryClasses
The DbProviderFactories (in the System.Data.Common namespace) class contains a static method called GetFactoryClasses()
that returns a DataTable object that contains all of the available data providers available to ADO.NET. The providers
are retreived from the local machine's machine.config file. Below is an example of how to retrieve this information:
DataTable tbl = DbProviderFactories.GetFactoryClasses();
this.dataGrid1.DataSource = tbl;
The results:
Creating a Data Factory based on the Provider's Invariant Name
It's the invariant name of the data provider that determines what type of factory object is used. The various
invariant names can be obtained as discussed in the preceeding section. To obtain a data factory based on a specified
provider, we must call the DbProviderFactories class' GetFactory method.
// Determine the invariant provider name somehow.
// We'll set it to a specific one here for testing purposes:
String strInvariantProviderName = "System.Data.SqlClient";
DbProviderFactory factory =
DbProviderFactories.GetFactory(strInvariantProviderName);
The object returned in this example is an instance of the SqlClientFactory, but in the form of the base abstract class
DbProviderFactory. Therefore, it contains all of the implementation code necessary to connect to and manipulate
SQL Server data.
After obtaining a factory object, we can obtain a connection object (as well as other objects related to that specific
provider. In this case, SQL Server).
// Create a new connection object from the factory
DbConnection conn = factory.CreateConnection();
The base abstract data factory classes do not take any method parameters. Therefore, the objects that the factory
creates and returns are not passed any constructor parameters. Therefore, we must set properties on instances
of those classes after they have been created.
// Set the connection string's connection string:
String strConn = this.GetConnectionString(strInvariantProviderName);
conn.ConnectionString = strConn;
I'll continue with this example by creating a command object for use with a data adapter and then filling a DataSet
with the data retrieved from an SQL Server database.
// Create a new command object from the factory:
DbCommand cmd = factory.CreateCommand();
// Set the command's connection:
cmd.Connection = conn;
// Set the command's query string:
cmd.CommandText = "SELECT * FROM TBLMYTABLE";
// Assign the command to the adapter's select command property:
adapter.SelectCommand = cmd;
// Create a DataSet object for use with a DataGrid control:
DataSet dsData = new DataSet();
// Fill the DataSet with data from the data provider:
adapter.Fill(dsData, "TBLMYTABLE");
// Set the DataGrid's data properties:
this.dataGrid1.DataSource = dsData;
this.dataGrid1.DataMember = "TBLMYTABLE";
Here's the method that determines what connection string to use (based on the invariant provider name):
private String GetConnectionString(String InvariantProviderName)
{
String strConn = null;
switch (InvariantProviderName)
{
case "System.Data.Odbc":
strConn = @"Driver={Microsoft Access Driver (*.mdb)};" +
"DBQ=c:\bin\Northwind.mdb";
/*
Possible value examples:
"Driver={SQL Server};Server=(local);Trusted_Connection=Yes;" +
"Database=AdventureWorks;"
"Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;" +
"Persist Security Info=False;Trusted_Connection=Yes"
"Driver={Microsoft Access Driver (*.mdb)};" +
"DBQ=c:\bin\Northwind.mdb"
"Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls"
"Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\bin"
"DSN=dsnname"
*/
break;
case "System.Data.OleDb":
strConn = @"Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=c:\bin\LocalAccess40.mdb";
/*
Possible value examples:
"Provider=MSDAORA; Data Source=ORACLE8i7;" +
"Persist Security Info=False;Integrated Security=Yes"
"Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=c:\bin\LocalAccess40.mdb"
"Provider=SQLOLEDB;Data Source=(local);Integrated Security=SSPI"
*/
break;
case "System.Data.OracleClient":
strConn = "Data Source=MyOracleServer;Integrated Security=yes";
break;
case "System.Data.SqlClient":
strConn = "Data Source=(local); Initial Catalog=pubs; " +
"Integrated Security=SSPI;";
break;
case "Microsoft.SqlServerCe.Client":
// get CE conn. string
break;
}
return strConn;
}
Current .Net Version: 2.0.50727
