[Mono-list] IDbParameter generic parameter handling

[Daniel Morgan]

Were you looking for the ProviderFactory or DataTools in
mcs/class/Mono.Data?  Brian Ritchie created the ProviderFactory and he may
able to help you.  Maybe you can enhance it to do what you describe - when
adding a generic parameter, use what is setup in
a config file.

The Microsoft OracleClient provider uses named parameters with a colon (:)
SELECT * FROM SOMETABLE WHERE COL1 = :parm1 AND COL2 = :parm2

The Microosft OleDb and Odbc providers use positional parameters with a
question mark (?).   However, when adding
a parameter, you can specify a name, but it will be ignored.
SELECT * FROM SOMETABLE WHERE COL1 = ? AND COL2 = ?

The Microsoft SqlClient provider uses named parameters with an at (@).
SELECT * FROM SOMETABLE WHERE COL1 = @parm1 AND COL2 = @parm2

Not only do you have to worry about the parameter character, but whether or
not you use it in the name when adding
the parameter to the parameter collection.

For instance, Oracle you do not include the colon when adding the parameter
pEmpNo.

OracleCommand myCommand =
   new OracleCommand("SELECT Ename, DeptNo FROM Emp WHERE EmpNo = :pEmpNo",
myConn);
OracleParameterCollection myParamCollection = myCommand.Parameters;
object myParm = new OracleParameter("pEmpNo", OracleType.Number);
int pIndex = myParamCollection.Add(myParm);

For SqlClient, you do need to include the @ symbol when adding paramter
@CategoryName.
myDataAdapter.SelectCommand.Parameters.Add("@CategoryName",
SqlDbType.VarChar, 80).Value = "toasters";

Microsoft didn't do this to confuse people; they did it for legacy reasons.
In Mono, we will try to implement the behavior that Microsoft uses for their
providers.

-----Original Message-----
From: mono-list-admin@ximian.com [mailto:mono-list-admin@ximian.com]On
Behalf Of Jaroslaw Kowalski
Sent: Wednesday, January 15, 2003 3:56 AM
To: mono-list@ximian.com
Subject: [Mono-list] IDbParameter generic parameter handling


I'm looking for a generic way to implement queries with parameters for my
application. There are IDbParameter, IParameter interfaces that seem to do
the job, but there's one confusion:

Some providers (like Sql, OleDb...) on MS.NET require the parameters to be
named as @param1, @param2, @param3. Some (like Odbc on MS.NET) require
parameters to be positional and marked as '?' in query text. Here are some
remarks from MSDN documentation on "OdbcParameter.ParameterName"

-8<-----------------------------------------------------
Instead of named parameters, the ODBC .NET Provider uses positional
parameters that are marked with a question mark (?) in the syntax of the
command text. Parameter objects in the OdbcParameterCollection and the
actual parameters accepted by the stored procedure or parameterized SQL
statement correspond to each other based on the order in which the
OdbcParameter objects are inserted into the collection rather than by
parameter name. Parameter names can be supplied, but will be ignored during
parameter object binding.
-8<-----------------------------------------------------

Mono used to have some form of generic, config-driven provider that seemed
to unify different providers. What happened to it? I cannot find it in CVS
anymore. Could it be used to provide some unification to this parameter
handling problem?

For now the only thing I can do is to try both methods for specifying
parameters, and whenever one fails on execute, revert to the other one and
make it a default. Can you do it better?

Jarek


_______________________________________________
Mono-list maillist  -  Mono-list@ximian.com
http://lists.ximian.com/mailman/listinfo/mono-list