Converting between DbType and System.Type

[James Moore]

Attempting to convert from a System.Type to a DbType:

public static System.Data.DbType ToDbType (System.Type type)

{

object dummyInstance = Activator.CreateInstance (type);

// Note that we don't care what type of XXParameter we use; the

// parameter is going to get thrown away. Basically, we're

// creating a pseudointerface to code hidden inside the XXParameter

// constructor.

MySql.Data.MySqlClient.MySqlParameter dummyParameter = new MySqlParameter
("dummyname", dummyInstance);

// This also does something sensible:
//
// System.Data.SqlClient.SqlParameter p = new
System.Data.SqlClient.SqlParameter ("dummyname", dummyInstance);
//
// However, the ODBC and OLE versions bite the wax tadpole and usually just
return String as the DbType


return dummyParameter.DbType;

}

However, when I plug in different parameter types (OleDbParameter,
OdbcParameter),
I get surprising results. Specifically, the DbType field of the
dummyParameter
object is usually String, no matter what gets passed in as the object value.
System.Data.SqlClient.SqlParameter behaves sensibly; I get back DbTypes that
make sense.

I'm not sure if it's a bug, or if ODBC and OLE just don't really care, so
they
don't return useful values. If all you're planning to do is something like

escape_all_sql_special_characters (dummyParameter.Value.ToString ())

in the driver, then the "type" of the parameter is probably irrelevant to
you.

OK, just took another look at the doc; OLE and ODBC have different ways of
doing things.
Specifically, the two-argument (string, object) version of the constructor
for OleDbParameter expects

string - the name of the parameter
value - "When you specify an Object in the value parameter, the OleDbType is
inferred from the .NET Framework type of the Object."

That's what the MySQL version does as well.

ODBC is weird:

string - the same (the name of the parameter)
value - An OdbcParameter object.

Huh? I can't see how initializing a parameter with another parameter as its
value makes sense; either I'm very confused or the
doc is wrong. The wording of the rest of the OdbcParameter (string, object)
documentation is so similar to the Ole and Sql version
that I'm tempted to think the doc is wrong.

- James
(e-mail address removed)