0

I'm attempting to write a generic function for populating compound keys in a few tables of my database.

Unfortunately, I'm limited to using an OleDbConnection so can't make use of many of the niceties of the SqlConnection (and related) classes.

As part of this effort, I need to be able to convert generic system types (which will always be primitive string, int etc) to their OleDbType enum counterparts.

I've viewed this question which has several suggestions in it: primarily, to use a class map. This approach would work fine however, it strikes me as strange that there would be no pre-built and pre-tested component of the .NET Framework that would do this significantly better than any code I would write would. I also dislike reinventing the wheel. The second approach suggested was to utilise the Parameter.ConvertTypeToDbType method in System.Web namespace (a stupid place to put it IMHO but I digress). This appears to work fine however, it yields a DbType enum rather than an OleDbType which I fear may cause compatibility issues.

My current attempt to utilise this approach looks like this:

OleDbParameter searchValueOne = new OleDbParameter("@searchValueOne", System.Web.UI.WebControls.Parameter.ConvertTypeCodeToDbType(Type.GetTypeCode(typeof(T1))));

So my question would be, is there a similar built in function that provides an OleDbType as opposed to a DbType or is going with a TypeMap simply going to be the best approach?

ScottishTapWater
  • 3,656
  • 4
  • 38
  • 81
  • `((int)DbType.Date)==((int)OleDbType.Double)` so you'd be correct that using your current code would be problematic. I don't think there's a built-in function since the preferred mapping may depend on the specific database you're connecting to whereas these enums are meant to be DB agnostic. – Damien_The_Unbeliever Sep 11 '17 at 10:47

1 Answers1

1

After reading comments and doing some further research it appears that a TypeMap is indeed the best option available.

Given that there are many cases where the different DB types map to the same Type (see VarChar and BStr amongst others), there are many different options available and which you take will depend on your database structure.

That being said, here's the class that I've come up with to perform this mapping, it should be a good starting point for anybody who wishes to try this in the future.

static class OleDbTypeMap
{
    private static readonly Dictionary<Type,OleDbType> TypeMap = new Dictionary<Type, OleDbType> {
        {typeof(string), OleDbType.VarChar },
        {typeof(long), OleDbType.BigInt },
        {typeof(byte[]), OleDbType.Binary },
        {typeof(bool), OleDbType.Boolean },
        {typeof(decimal), OleDbType.Decimal },
        {typeof(DateTime), OleDbType.Date },
        {typeof(TimeSpan), OleDbType.DBTime },
        {typeof(double), OleDbType.Double },
        {typeof(Exception),OleDbType.Error },
        {typeof(Guid), OleDbType.Guid },
        {typeof(int), OleDbType.Integer },
        {typeof(float), OleDbType.Single },
        {typeof(short), OleDbType.SmallInt },
        {typeof(sbyte), OleDbType.TinyInt },
        {typeof(ulong), OleDbType.UnsignedBigInt },
        {typeof(uint), OleDbType.UnsignedInt },
        {typeof(ushort), OleDbType.UnsignedSmallInt },
        {typeof(byte), OleDbType.UnsignedTinyInt }
    };
    public static OleDbType GetType(Type type) => TypeMap[type];
}

This could be modified with a GetType(OleDbType type) overload which accesses a different dictionary containing the inverse of the first to make this bi-directional, however, I don't need this functionality so haven't implemented it myself.

My OleDbParameter line now looks like this:

OleDbParameter searchValueOne = new OleDbParameter("@searchValueOne", OleDbTypeMap.GetType(typeof(T1)));

Which is much neater than before.

ScottishTapWater
  • 3,656
  • 4
  • 38
  • 81