Wednesday, 2 August 2017

Load data into a SQL Database using SqlBulkCopy, Entity Framework and reflection

I recently had the requirement to bulk load data into a SQL database. Easy enough, but since I was using the Entity Framework, most of the work has already been done for me.

The plan is as follows:
Use SqlBulkCopy with a DataTable as an input. In order to do this, I will need to create some headers and then add the required data as rows before blasting them into the SQL Database.

ConcurrentQueue<MyObject> items = GetMyItems(); // method not included

Type t = (new MyObject()).GetType();
var dt = SetHeader(t);
AddItems(dt, items);
Load(dt, "MyTable");

// Add the properties as columns to the datatable
private DataTable SetHeader(Type t)
{
var dt = new DataTable();
PropertyInfo[] pi = t.GetProperties();

foreach (var p in pi)
{
if (string.Compare(p.PropertyType.Name, typeof(Guid).Name, true) == 0)
{
dt.Columns.Add(p.Name, typeof(Guid));
}
else
{
dt.Columns.Add(p.Name);
}
}

return dt;
}

// add each row as a CSV to the DataTable
private bool AddItems(DataTable dt, ConcurrentQueue<MyObject> items)
{
object table = new object();
Parallel.ForEach(items, item =>
{
object[] values = ObjectToArray(item);
lock (table)
{
dt.Rows.Add(values);
}
});

return true;
}

private bool Load(DataTable dt, string tableName)
{
var copy = new SqlBulkCopy(Constants.DATABASE_CNN);
copy.DestinationTableName = tableName;
copy.WriteToServer(dt);
return true;
}

// convert the object to a string array
private string[] ObjectToArray(object obj)
{
if (obj == null)
{
throw new ArgumentNullException("obj", "Value can not be null or Nothing!");
}

Type t = obj.GetType();
PropertyInfo[] pi = t.GetProperties();

var returnValue = new string[pi.Length] ;

for (int index = 0; index < pi.Length; index++)
{
if (pi[index].GetValue(obj) == null)
{
returnValue[index] = "";
}
else
{
returnValue[index] = pi[index].GetValue(obj).ToString();
}
}

return returnValue;
}

No comments:

Post a comment