Weird IDbCommand.Parameters[] design?

[Massimo]

I'm trying to build a DB-independent application using Visual Studio 2005
and the .NET Framework 2.0; I'm not using stored procedures, so I'd like the
application to not rely upon a specific DBMS.

I created a Factory class which instantiates DB-specific classes, and
returns them using IDbX interfaces:

public static IDbConnection CreateConnection();
public static IDbCommand CreateCommand();
public static IDbDataParameter CreateParameter(string name,DbType type);

The problem is with the last one; I pre-build commands to employ later,
using the following code:

---
IDbConnection connection = Factory.CreateConnection();

IDbCommand command = Factory.CreateCommand();

command.CommandText = "INSERT INTO Table VALUES (@Date,@Name,@Value)";

command.Parameters.Add(Factory.CreateParameter("Date",DbType.Date));
command.Parameters.Add(Factory.CreateParameter("Name",DbType.AnsiString));
command.Parameters.Add(Factory.CreateParameter("Value",DbType.Int32));

command.connection = connection;
---


Now, what I want to do is to be able to populate the parameters and run the
command when needed; but, when I recover the stored parameters, they're
returned as Objects instead of IDbDataParameters (or IDataParameters).

So, instead of writing code like

command.Parameters["Name"].Value = "john";

I need to use one of the the rather cumberstome casts:

(command.Parameters["Name"] as IDbDataParameter).Value = "John";
((IDbDataParameter) command.Parameters["Name"]).Value = "John";


I think this is really too much upcasting. Only something that implements
IDataParameter (including IDbDataParameter) can be placed in a
IDataParameterCollection, so why returning it as a plain Object instead of
using the the latest common interface?

If find this being really a nuisance. I'd like to know if there are design
reasons for this, of it was only a misdesign (which I hope can be fixed,
then).

Thanks


Massimo

 

[Cor Ligthert [MVP]]

Massimo,

I think that in this sample on our website is everything that you ask.

http://www.vb-tips.com/default.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

I hope this helps,

Cor

 

[Massimo]

Massimo,

I think that in this sample on our website is everything that you ask.

http://www.vb-tips.com/default.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

I hope this helps,

It does; I didn't know .NET 2.0 had a DbProviderFactory, and now I think my
error was using IDbX interfaces instead of the new DbX classes. Also, it
seems the DbCommand.Parameters[] method actually returns a DbParameter,
which is much better than the Object returned by IDbCommand.Parameters[]

I'll study it and try rewriting some code, thanks.


Massimo

P.S.
Anyway, even if IDbCommand shouldn't be used directly, I still think it's
quite strange for IDbCommand.Parameters[] to return an Object...