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

c#-4.0 mapping sqlbulkcopy

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 :(

1
0
3/12/2013 6:00:26 AM

Accepted Answer

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.

0
3/13/2013 6:08:28 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow