Creating Data Access Layer Using DbProviderFactories and DbProviderFactory
by David Hayden ( Florida .NET Developer ), Filed: ADO.NET 2.0
I know I have mentioned DbProviderFactories and DbProviderFactory in ADO.NET 2.0 before, but they were new to some developers this weekend so I thought I would reiterate their use in a database agnostic data access layer in your winform and ASP.NET Web Applications.
If you look at the connection string in your App.Config or Web.Config:
<add
name="ConnectionString"
connectionString="server=.;Initial Catalog=Northwind;..."
providerName="System.Data.SqlClient"
/>
you will notice the providerName Property, which represents the unique name for the .NET Framework data provider used to connect to an underlying data source.
In this case we are connecting to SQL Server, but we don't really need to know that. We can use that providerName to create various connection, command, and other database classes without knowing what type of database we are connecting to by using the DbProviderFactories and DbProviderFactory Classes.
First, let's get the actual connectionString and providerName that make up the database we are connecting to using System.Configuration.ConfigurationManager:
string connectionString =
ConfigurationManager.ConnectionStrings
["ConnectionString"].ConnectionString;
string providerName =
ConfigurationManager.ConnectionStrings
["ConnectionString"].ProviderName;
Once we have the providerName, we can create a DbProviderFactory class for the database using the DbProviderFactories Class:
DbProviderFactory provider =
DbProviderFactories.GetFactory(providerName);
The DbProviderFactory Class exposes several methods that allow us to create classes to access the database:
- CreateConnection()
- CreateCommand()
- CreateParameter()
- CreateDataReader()
- CreateDataAdapter()
Again, keep in mind that it does not matter what database we are using at this point - could be SQL Server, Oracle, Microsoft Access, DB2, Firebird, etc. Pick your poison.
Assuming the database supports stored procedures, we can call a stored procedure in a database agnostic way as follows:
using (DbConnection cn = provider.CreateConnection())
{
cn.ConnectionString = connectionString
using (DbCommand command = cn.CreateCommand())
{
command.CommandText = "GetAllCustomers";
command.CommandType = CommandType.StoredProcedure; cn.Open();
using (DbDataReader dr = command.ExecuteReader())
{
// Do Something...
}
}
}
This is an awesome way to target multiple database with your application.
Don't forget you can do some really cool stored procedure parameter discovery as well:
All good O/R Mappers and Code Generators for the Data Access Layer handle all of this data access layer database agnostic code for you, but it is still good to know what is happening behind the scenes.
Hope this helps!
Similar ADO.NET Tutorials
by David Hayden ( Florida .NET Developer ), Filed: ADO.NET 2.0