Skyserver
.NET 2.0: FtpWebRequest

.NET 2.0: Database Independent ADO.NET

.NET 2 has new factories that makes it easier to write data-programs independent of the databsae.

With .NET 1.0, we could program independent of the database using interfaces:

IDbConnection connection = new SqlConnection(connectionString);
IDbCommand command = connection.CreateCommand();
command.CommandText = "select * from customers";
connection.Open();
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
   //...

The connection object can also be created with the Activator class - to make it independent of the database. What about creating a SqlDataAdapter? This needs to be done similar to the connection. A custom factory class is very useful with 1.0.

.NET 2.0 offers database-independent factory DBProviderFactory:

DbProviderFactory provider = DbProviderFactories.GetFactory ("System.Data.SqlClient");
DbConnection connection = provider.CreateConnection ();
DbDataAdapter adapter = provider.CreateDataAdapter ();

The available providers can be found in the configuration file.

Christian

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

Paul Wilson

Excellent feature. I was actually just fixing to post the following version when I saw yours: uses System.Data.Common; DbProviderFactory factory = DbProviderFactories.GetFactory (System.Data.SqlClient); DbConnection connect = factory.CreateConnection (); connect.ConnectionString = ...; DbCommand command = connect.CreateCommand (); command.CommandText = ...; connect.Open (); DbDataReader reader = command.ExecuteReader (CommandBehavior.CloseConnection);

Frans Bouma

But.. do they also have implemented a common exception hierarchy for databases? (I don't have my whidbey install available at the moment, so I can't check). Because that's a very important point about generic databases. Another point is that Oracle providers want the parameters in the parameter collection in the same order as they are provided in the query string. SqlServer doesn't need this. This can be a problem too. I appreciate the effort, but I think there is still a long way to go before generic database access is a reality...

Christian

Frans, With the exception hierarchy we just have the common base class SystemException. Of course the factories can't solve all problems. Different providers can support completely different commands.

brady gaster

just wrote an article about this - see a href=http://www.michaelrichardson.org/articles/Using%20Interfaces%20to%20Develop%20a%20Generic%20Data%20Access%20Layer.pdfhere/a

Holger Schwichtenberg

The available providers can not only be found in the configuration file, but also be enumerated with code: DataTable providers = System.Data.Common.DbProviderFactories.GetFactoryClasses(); foreach (DataRow provider in providers.Rows) { foreach (DataColumn c in providers.Columns) Console.WriteLine(c.ColumnName + : + provider[c]); Console.WriteLine(---); }

Christian

Holger, Of course it is possible to get enumerate all providers with code. GetFactoryClasses() gets the information from the config file, too. With the code I'm using to access the database usually I'm not enumerating all available providers (maybe just from an application configuration program), but instead configuring the provider that should be used.

Li Chen's Weblog

AUTHOR: Li Chen's Weblog
IP: 000.000.000.000
DATE: 02/11/2004 07:21:00 PM

David Goldstein

If they don't FIX the missing common root of the database exception hierarchy, this really screws up thw whole thing. How are we supposed to distinguish database exceptions in a vendor-agnostic way? We cannot!

CCube Blog

Introduces solutions to generic data access in ADO.NET 1.0/1.1 and 2.0.

The comments to this entry are closed.