Here is something that I’ve run into a few times and have not been able to resolve generically. ADO.Net support generic interfaces for most of the data access components to allow writing generic front end code against specific data providers. The idea is that you can cast down any of the specific providers to a generic interface and then use the generic versions in your front end code.

I make use of this extensively in my Business Object layer which talks to a data layer. The data layer can then use any of the supported backend providers (SqlClient, OleDbClient, OdbcClient, MySqlClient etc.) interchangeably and not worry about a specific implementation.

Since this happens in the business layer this also means that any customizations should use that approach. It works well for the most part, with the exception of parameter objects, which is also one of the most common scenarios. Here’s an example of a business object method accessing the Data access layer (which is wrapped in the This.Execute, this.CreateCommand etc) :

public bool LoadByInvNo(string InvNo)

{

      this.SetError();

      if (!this.Open())

            return false;

      IDbCommand Command =

               this.CreateCommand("select pk from wws_invoice where invno=@InvNo");

      // *** Note SQL Specific code

      SqlParameterCollection Parms = (SqlParameterCollection) Command.Parameters;

      Parms.Add("@InvNo",InvNo);

      if (this.Execute(Command,"TInvNoPk") < 1)

      {

            return false;

      }

      int Pk = (int) this.DataSet.Tables["TInvNoPk"].Rows[0][0];

      return this.Load(Pk);

}

Notice the IDbCommand instead of the SqlCommand object returned from the data layer (and wrappered in the local CreateCommand method of the bus object). But once you have this Command object there’s no way to directly retrieve a parameter object.

The code above explicitly uses a SqlParameterCollection object, but this actually hardcodes the above code to SQL Server. This is hardly ideal.

I did this because the following does not work  (as the docs suggest):

int index = Command.Parms.Add("@InvNo");

IDbDataParameter Parm = (IDbDataParameter) Command.Parms[index];

Parm.Value = InvNo;

Following the docs on what’s required for the generic Add is futile. What’s actually required as a parameter is not a parameter name or value (what MSDN says) but an IDbDataParameter object. The problem is there’s no way to directly get a IDbDataParameter out of the Parameters collection, which is too bad.

You can do this with the provider specific parameter collections though, as shown in the first sample, by doing Command.Parameters.Add() which returns you a reference to a new parameter object:

SqlParameterCollection Parms = (SqlParameterCollection) Command.Parameters;

IDbDataParameter Parm = Parms.Add("@InvNo",InvNo);

Parm.SourceColumn = "InvNo";

But this of course is provider specifc – this only works with SQL Server or OleDb or Oracle depending on which type of Parameter collection you’re referencing.

My workaround is to provide a parameter option from the Data Access layer.

IDbDataParameter Parm = this.Data.CreateParameter("@InvNo");

Parm.Value = InvNo;

Command.Parameters.Add(Parm);

But it seems silly that you have to do this since the Command object already contains the Parameters collection of the right type.

This works fine in the above scenario since I have a Data layer that feeds me these objects via Factory methods anyway. But this problem actually arose yesterday because I was working on a generic DataRow update routine that bypasses the DataAdapter for updates. I created a static method UpdataDataRow() into which I pass a DataRow, a IDbCommand object and a few options. Since it’s static it can’t use a Factory method to retrieve a parameter object which ruins the whole idea of having a generic static method for this.

Maybe there’s some way to do this and I’m just missing it – for now the above works.