Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SqlBulkCopy Wrapper
public class SqlBulkCopier<T>
{
private readonly string tableName;
private readonly Dictionary<PropertyInfo, PropertyMapping> mappings;
public SqlBulkCopier(string tableName)
{
if (String.IsNullOrWhiteSpace(tableName))
{
throw new ArgumentNullException("tableName");
}
this.tableName = tableName;
this.mappings = new Dictionary<PropertyInfo, PropertyMapping>();
}
public void Map<TProp>(Expression<Func<T, TProp>> accessor, string columnName)
{
PropertyInfo property = getProperty(accessor);
if (String.IsNullOrWhiteSpace(columnName))
{
throw new ArgumentException("The destination column name cannot be null.", "columnName");
}
PropertyMapping mapping = new PropertyMapping();
mapping.Name = property.Name;
mapping.PropertyType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
Func<T, TProp> getter = accessor.Compile();
mapping.Getter = x => getter(x);
mapping.ColumnName = columnName;
mappings.Add(property, mapping);
}
private static PropertyInfo getProperty<TProp>(Expression<Func<T, TProp>> accessor)
{
if (accessor == null)
{
throw new ArgumentNullException("accessor");
}
MemberExpression member = accessor.Body as MemberExpression;
if (member == null)
{
throw new ArgumentException("The property selector did not select a property.", "accessor");
}
PropertyInfo propertyInfo = member.Member as PropertyInfo;
if (propertyInfo == null)
{
throw new ArgumentException("The property selector did not select a property.", "accessor");
}
if (!propertyInfo.DeclaringType.GetTypeInfo().IsAssignableFrom(typeof(T)))
{
throw new ArgumentException("The property selector did not select a property.", "accessor");
}
return propertyInfo;
}
public void Copy(SqlConnection connection, IEnumerable<T> data, SqlTransaction transaction = null)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (data == null)
{
throw new ArgumentNullException("data");
}
using (new ConnectionLifetimeManager(connection))
using (SqlBulkCopy copier = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
copier.DestinationTableName = tableName;
copier.BulkCopyTimeout = 0;
foreach (PropertyInfo property in mappings.Keys)
{
PropertyMapping mapping = mappings[property];
copier.ColumnMappings.Add(property.Name, mapping.ColumnName);
}
DataTable table = getDataTable(data);
copier.WriteToServer(table);
}
}
private DataTable getDataTable(IEnumerable<T> items)
{
string typeName = typeof(T).Name;
DataTable table = new DataTable(typeName);
foreach (PropertyInfo property in mappings.Keys)
{
PropertyMapping mapping = mappings[property];
table.Columns.Add(mapping.Name, mapping.PropertyType);
}
foreach (T item in items.Where(x => x != null))
{
DataRow row = table.NewRow();
foreach (PropertyInfo property in mappings.Keys)
{
PropertyMapping mapping = mappings[property];
row[property.Name] = mapping.Getter(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
table.AcceptChanges();
return table;
}
private class PropertyMapping
{
public string Name { get; set; }
public Type PropertyType { get; set; }
public Func<T, object> Getter { get; set; }
public string ColumnName { get; set; }
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.