Sunday, April 02, 2006

Implementing a DataProvider Independant ADO.NET DataAdapter

Okey... sometime ago... (in ADO.NET 1.x times) I wondered why data access for Odbc, for OleDb, for SQLServer or for Oracle had to be that different...
So i read Implementing a .NET Framework Data Provider
and I read ADO.NET: Building a Custom Data Provider for Use with the .NET Data Access Framework and I understood the processes that has to be followed to build a Data Provider... but I found something strange...

First... since ADO.NET 1.x it was possible to create a connection, and encapsulate that creation inside a factory that read the class type from a config file (you had to do-it-yourself but it was possible)... after that, using only interfaces and abstract classes common to all the Data Providers... it was easy to create a Command with the IDbConnection.CreateCommand.

But then... the problem arised... how to create the correct DataAdapter? (an IDbConnection couldn't create a IDbDataAdapter... an IDbCommand couldn't create an IDbDataAdapter... an IDataReader couldn't create an IDbDataAdapter...)
So... What is the difference between the DataAdapters and everything else? Why was it excluded from the "factory chain" (IDbConnection to IDbCommand to IDataReader)

With ADO.NET 2.0 that problem is solved, thanks to the new DbProviderFactory.CreateDataAdapter... but back then when only ADO.NET 1.x existed I began to think that maybe there was something really different between the IDbDataAdapter/DbDataAdapter and everything else... and I started looking for the difference... and the results were startling... there were absolutely NO difference... take a look at this example code from Microsoft... and now tell me why this "TemplateDataAdapter" couldn't have been included in the .NET Framework... perhaps as "DataProviderIndependantDataAdapter" or take a look at... for example... the SqlDataAdapter or the OleDbDataAdapter..., or the OdbcDataAdapter and tell me... exactly... what do the do that it is specific for SQLServer... or for OleDb... or for Odbc?

I you copy& paste the example code from Microsoft to one of your projects... and then in every place where you use the SqlDataAdapter you change it to the TemplateDataAdapter... what functionality do you miss? (if any) and... in the remote case that you do find something missing (I couldn't find anything)... is that functionality really database provider dependent?

Okey, okey, you want to know what is my point... well my point is:
  1. if the job for a DataAdapter is to represent a set of data commands and a database connection that are used to fill the DataSet and update a database (and I didn't invent that, Microsoft wrote in in the documentation for the SqlDataAdapter)
  2. and the DataSet is data provider agnostic...
  3. and it is possible to update the database using only interfaces like IDbConnection, IDbCommand, IDataReader... (and the base class DbDataAdapter already implements most of the logic needed to do that) then...

Why in .NET we don't have a DataProviderIndependantDataAdapter ? (as I said before it is so easy to build one, you just have to do a search an replace form the code of the TemplateDataAdapter in the example code from Microsoft but that doesn't answer the question).
Well, you might think... maybe it is just not worth the effort... maybe it is just a much better idea to have different DataAdapter classes... one for each DataProvider.... wrong.. that is simply not true...

Even with the new and improved DbProviderFactory.CreateDataAdapter there is a problem and the problem are the DataAdapter events

 adapter.RowUpdating += new SqlRowUpdatingEventHandler( OnRowUpdating );
adapter.RowUpdated += new SqlRowUpdatedEventHandler( OnRowUpdated );

Have you already seen the problem?

Even if you use the great new DbProviderFactory.CreateDataAdapter if you need to trigger provider independent logic using the the adapter.RowUpdating or the adapter.RowUpdated events... what do you do? there is no easy answer... (or maybe there is... to create a DataProviderIndependantDataAdapter )

First... I don't see why we have to use an "SqlRowUpdatedEventHandler" ? why ADO.NET does not include an "DataProviderIndependantRowUpdatedEventHandler" ? AFAIK the SqlRowUpdatedEventHandler has no special difference from the OleDbRowUpdatedEventHandler ... or do you see any? (of course, there is one you might say, the SqlRowUpdatedEventHandler uses SqlRowUpdatedEventArgs and the OleDbRowUpdatedEventHandler uses OleDbRowUpdatedEventArgs) so, the question now is... why we do not have a DataProviderIndependantUpdatedEventArgs ? is there an important difference between the SqlRowUpdatedEventArgs and the OleDbRowUpdatedEventArgs... well... there is none... they both inherit from RowUpdatedEventArgs... and add nothing... but they do create a problem... if you have a code like this:

 DbProviderFactory dataFactory =
DbDataAdapter adapter = dataFactory.CreateDataAdapter();
DbCommandBuilder builder = dataFactory.CreateCommandBuilder();
builder.DataAdapter = adapter

// Create and fill DataSet (select only first 5 rows)
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, 0, 5, "Table");

// Modify DataSet
DataTable table = dataSet.Tables["Table"];
table.Rows[0][1] = "new product";

// now... how do I add handlers?

if (adapter is SqlDataAdapter){
((SqlDataAdapter)adapter).RowUpdating += new SqlRowUpdatingEventHandler( OnRowUpdating );
((SqlDataAdapter)adapter).RowUpdated += new SqlRowUpdatedEventHandler( OnRowUpdated );

// now... that WAS NOT a DataProvider independent code...

Maybe we are missing something... lets take a loot at the guide for "Writing Provider Independent Code in ADO.NET"... mmm... no, the examples in Retrieving Data with a DbDataAdapter just do not cover the case when one needs to use the RowUpdating or the RowUpdated events

Of course that could have been easily fixed.. if we had a
DataProviderIndependantAdapter.. with its DataProviderIndependantUpdatedEventHandler and its DataProviderIndependantUpdatedEventArgs... so... why ADO.NET 2.0 does NOT provide one... don't ask me... but if you want to write code that is REALLY DataProvider Independant then you just have to create your own DataProviderIndependantAdapter.

No comments: