Is it possible to do data type conversion on SQLBulkUpload from IDataReader?

Question

I need to grab a large amount of data from one set of tables and SQLBulkInsert into another set...unfortunately the source tables are ALL varchar(max) and I would like the destination to be the correct type. Some tables are in the millions of rows...and (for far too pointless policital reasons to go into) we can't use SSIS.

On top of that, some "bool" values are stored as "Y/N", some "0/1", some "T/F" some "true/false" and finally some "on/off".

Is there a way to overload IDataReader to perform type conversion? Would need to be on a per-column basis I guess?

An alternative (and might be the best solution) is to put a mapper in place (perhaps AutoMapper or custom) and use EF to load from one object and map into the other? This would provoide a lot of control but also require a lot of boilerplate code for every property :(

BTW: as an aside - is there any reason NOT to use both SqlConnection/Transaction and EF in the same general code? e.g. For some things its simpler just to call SQLCommand, and for others use EF model? — BlueChippy, Mar 12 '13 at 06:20
I had a similar problem - look to the answer here, it helped me http://stackoverflow.com/q/4750653/532498 — Pleun, Mar 12 '13 at 21:29

score 0 · Accepted Answer · answered Mar 13 '13 at 06:08

In the end I wrote a base wrapper class to hold the SQLDataReader, and implementing the IDataReader methods just to call the SQLDataReader method.

Then inherit from the base class and override GetValue on a per-case basis, looking for the column names that need translating:

public override object GetValue(int i)
{
  var landingColumn = GetName(i);
  string landingValue = base.GetValue(i).ToString();

  object stagingValue = null;
  switch (landingColumn)
    {
    case "D4DTE": stagingValue = landingValue.FromStringDate(); break;
    case "D4BRAR": stagingValue = landingValue.ToDecimal(); break;

    default:
        stagingValue = landingValue;
        break;
    }
  return stagingValue;
}

Works well, is extensible, and very fast thanks to SQLBulkUpload. OK, so there's a small maintenance overhead, but since the source columns will very rarely change, this doesn't really affect anything.

Is it possible to do data type conversion on SQLBulkUpload from IDataReader?

1 Answers1