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;
}
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;
}
Comments
Post a Comment