Christian Nagel's OneNotes

.NET Training, Consulting, Coaching - C#, Web Services, Enterprise Services, ASP.NET, Whidbey, Longhorn and More!

Sponsors

Affiliations

Books I've written

INETA UG Leaders

My Blogroll

.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

Posted: Oct 30 2003, 05:10 PM by CNagel | with 15 comment(s)
Filed under: ,

Comments

Paul Wilson said:

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);
# October 30, 2003 1:30 PM

Frans Bouma said:

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...
# October 30, 2003 3:38 PM

Christian said:

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.
# October 30, 2003 4:09 PM

brady gaster said:

just wrote an article about this - see <a href="http://www.michaelrichardson.org/articles/Using%20Interfaces%20to%20Develop%20a%20Generic%20Data%20Access%20Layer.pdf">here</a>
# October 30, 2003 4:18 PM

TrackBack said:

Scott Hanselman's Thoughts
# October 31, 2003 11:56 PM

TrackBack said:

Peter Provost's Geek Noise
# November 6, 2003 7:02 AM

TrackBack said:

mscorlib.com
# November 14, 2003 12:52 AM

Holger Schwichtenberg said:

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("---");
}
# January 2, 2004 5:35 AM

Christian said:

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.
# January 2, 2004 6:45 AM

TrackBack said:

# February 11, 2004 1:21 PM

TrackBack said:

TheServerSide.NET Discussions - Database and data provider independence in ADO.NET
# April 9, 2004 9:33 AM

David Goldstein said:

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!
# May 31, 2004 4:21 PM

TrackBack said:

Introduces solutions to generic data access in ADO.NET 1.0/1.1 and 2.0.
# July 22, 2004 8:30 PM

Problemi di validazione campo in form ASPX | hilpers said:

Pingback from  Problemi di validazione campo in form ASPX | hilpers

# January 17, 2009 3:52 PM

JXL99 said:

This is classic fragmented identity management, operationalized   in a real-world technical system. ,

# October 22, 2009 6:25 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)