DbType vs. OleDbType vs. SqlDbType

[Tom Winter]

I'm trying to put together a generic Command object that my business objects
can use to access my applications database. We're starting with an MDB and
then maybe moving to SQL Server and then maybe SQL Mobile.

So my code in the business objects is working with the "generic"
IDbConnection, IDbCommand, IDataParameter interfaces, instead of directly
with OleDbCommand or SqlCommand. The problem is, with
IDbCommand.Parameters.Add, you need pass it a fully formed Parameter object.
It doesn't have an AddWithValue method like OleDbCommand.Parameters. The big
question here is what to set the DbType to for an IDataParameter object that
I get from IDbCommand.CreateParameter(). IDataParameter.DbType wants a
System.Data.DbType. OleDb appears to use OleDbType and SQL uses SqlDbType.
It doesn't look like the values map to each other. What should I use here?
Can I just not fill in the Type property and let the system figure it out?

(I know I can use the System.Data.Common objects, but those still have the
same issue.)

Thanks for your help!

Tom Winter
(e-mail address removed)

 

[Robert Simpson]

Well, what do you have at the time you create the parameter? Do you have an
OleDbType value or a SqlDbType value that you want to convert to a DbType?

If so, you can perform this magic with a little bit of reflection.

// -- Cut Here --
using System;
using System.Data;
using System.Data.Common;
using System.Reflection;
// ... etc etc...

static void Main()
{
// The following two lines are just for demonstration purposes.
// In your app you'll already have them I hope
IDbCommand cmd = new System.Data.SqlClient.SqlCommand();
SqlDbType providerSpecificType = SqlDbType.BigInt;

// Setting multiple parameters
PropertyInfo providerProperty = null;
IDataParameter parameter;
for (int n = 0; n < 10; n++)
{
parameter = cmd.CreateParameter();
SetProviderSpecificType(providerProperty, parameter,
providerSpecificType);

cmd.Parameters.Add(parameter);
}
}


static void SetProviderSpecificType(
ref PropertyInfo providerProperty,
IDataParameter param,
object providerType)
{
if (providerProperty == null)
providerProperty = GetProviderProperty(param);

if (providerProperty != null)
providerProperty.SetValue(param, providerType, null);
}

static System.Reflection.PropertyInfo GetProviderProperty(
IDataParameter param)
{
Type parameterType = param.GetType();
PropertyInfo[] propertyInfo;

propertyInfo = parameterType.GetProperties();
for (int n = 0; n < propertyInfo.Length; n++)
{
if (propertyInfo[n].GetCustomAttributes(
typeof(DbProviderSpecificTypePropertyAttribute), false).Length > 0)
{
return propertyInfo[n];
}
}
return null;
}
// -- End of code --

The first time SetProviderSpecificType() is called, you pass in a reference
to the PropertyInfo variable, initially set to null. The function will
fetch it and store it so you can make repeated calls to the function without
incurring the lookup overhead.

Robert Simpson
Programmer at Large

 

[Matt Noonan]

Have you seen the Enterprise Library?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/entlib.asp

Their support for OleDb is limited, but they have already solved the DbType
problem you are asking about.