Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • JavaScript • Angular
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
West Wind WebSurge - Rest Client and Http Load Testing for Windows

IDbCommand and generic Parameters


:P
On this page:

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.

?>


The Voices of Reason


 

Josche MacDonnell
June 17, 2004

# re: IDbCommand and generic Parameters

I've ran into this problem too recently. As of yet I haven't been able to find a solution. I was researching the possibility of design time reflection (Had seen it once a month or two ago but having trouble finding where to do it) What that should accomplish is: Determine at design time what possible overrides the returned variable (in this case would be a descendent of IDataParameterCollection) would have. I would have just settled it Microsoft had implemented the Add(string parametername, object value) in the interface. That would have been enough to keep me going. Until then thanks for your input and at least it should keep me going.

du_d_u@yahoo.com.cn
November 29, 2004

# re: IDbCommand and generic Parameters

string sql = " select * from tableA where aColumn=@columnA";
IDbCommand cmd = Database.Connection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
IDbDataParameter p = cmd.CreateParameter();
p.ParemeterName="@columnA";
p.DbType = DbType.String;
p.Value = "column value";
cmd.Parameters.Add(p);
IDataReader reader = cmd.ExxcuteReader();
while(reader.Read()){
Console.Write(reader.GetString(0));
}
reader.Close();

If it works for u, let me know pls.
du_d_u@yahoo.com.cn

These segments work for me.

BTW, Database class create provider specific connection, but return IDbConnection.

Rick Strahl
November 30, 2004

# re: IDbCommand and generic Parameters

Damn... How did I miss that? Command.CreateParameter() is the ticket!!!

Philipp Sumi
March 12, 2005

# re: IDbCommand and generic Parameters

I don't think its a workaround anyway to delegate the creation of your IDataParameter objects to the data layer - it's all about responsibility :-)

And if you don't want to access the layer for every parameter, you still could use something like this:

NameValueCollection params = new NameValueCollectoin();
params.Add("ParamName1", value1);
params.Add("ParamName2", value2);

and feed both to the data layer

IDbCommand cmd = CommandBuilder.Create(sql, params)

Cheers,
Philipp

Brendan Wright
April 20, 2005

# re: IDbCommand and generic Parameters

One wierd thing is the MSDN documentation does not have those Properties listed, all I see are <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataidbdataparametermemberstopic.asp>:

Precision
Scale
Size

What am I missing?

RAHMAN MAHMOODI
January 02, 2009

# Mr

Hi,

DbParameter should be provider independent based on the current factory used. eg

        Dim loCmd As DbCommand = GenericFactoryHelper.Command 'generic based on current facotry
        Dim loPram As DbParameter = loCmd.CreateParameter     'generic based on current factory
        loPram.ParameterName = GenericFactoryHelper.FormatParameter("MyPram") ' this is the trick, format it dynamically
        loPram.Value = "something"
        loCmd.Parameters.Add(loPram)

    ' Format the DB parameters based on the provider format eg SQL is @
    ' or what ever way you want to read the ParameterMarkerFormat
    private Shared Function FormatParameter(ByVal ParameterName As String) As String
        Dim ParameterMarkerFormat As String
        Dim loCnn As DbConnection = OpenConnection()
        ParameterMarkerFormat = loCnn.GetSchema("DataSourceInformation").Rows(0)("ParameterMarkerFormat").ToString
        Return String.Format(ParameterMarkerFormat, ParameterName)
    End Function


This will work ok as I am not using '@MyPram' or :MyPram ...


rahman

Rick Strahl
January 02, 2009

# re: IDbCommand and generic Parameters

Well in .NET 2.0 all of this is addressed with the much more generic DbProviderFactory which allows creation of all the objects needed to interact with ADO.NET. The above questions and solutions are appropriate though for .NET 1.x.

RT
March 21, 2010

# re: IDbCommand and generic Parameters

Hi Rick,

I'm glad you found that .net 2.0 provides all the generic classes for ADO.net.

Would you care to illustrate with an example to your original problem?

Thanks.

frustratedCSharpUser
November 04, 2010

# re: IDbCommand and generic Parameters

This worked for me:
using (var connection = new SQLiteConnection("data source=:memory:;"))
{
    connection.Open();
    using (IDbCommand command = connection.CreateCommand())
    {
        command.CommandText = "DROP TABLE IF EXISTS Table1;" +
            "create table IF NOT EXISTS Table1(Value1 INTEGER not null,Value2 INTEGER not null);";
        command.ExecuteNonQuery();
    }
    
    using (IDbTransaction transaction = connection.BeginTransaction())
    {
        using (IDbCommand command = connection.CreateCommand())
        {
            command.CommandText = "INSERT INTO Table1 values (@Param1,@Param2)";
            IDbDataParameter tempParam1 = command.CreateParameter();
            tempParam1.ParameterName = "Param1";
            command.Parameters.Add(tempParam1);
            IDbDataParameter tempParam2 = command.CreateParameter();
            tempParam2.ParameterName = "Param2";
            command.Parameters.Add(tempParam2);
            
            
            for(int i = 1; i <= 12; i++)
            {
                ((IDbDataParameter)command.Parameters["Param1"]).Value = i;
                ((IDbDataParameter)command.Parameters["Param2"]).Value = i * i;
                command.ExecuteNonQuery();
            }
        
            transaction.Commit();
        }
    }
    
    connection.Close();
}



The key is the cast:
Does anyone know why IDbCommand.Parameters[] returns an object?
((IDbDataParameter)command.Parameters["Param1"]).Value = i;

Firegarden
July 25, 2022

# re: IDbCommand and generic Parameters

Hi Rick, Have you considered the option of just making your own objects out of the interfaces?

Thank you, Rob

public class DbDataParameter : DataParameter, IDbDataParameter
{
    public byte Precision { get; set; }
    public byte Scale { get; set; }
    public int Size { get; set; }
}

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2022