Skip to content

Instantly share code, notes, and snippets.

@nzhul
Created May 21, 2020 17:19
Show Gist options
  • Save nzhul/fad6ffc471cd3d08152b7173701044b4 to your computer and use it in GitHub Desktop.
Save nzhul/fad6ffc471cd3d08152b7173701044b4 to your computer and use it in GitHub Desktop.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Threading.Tasks;
namespace Common.SqlServer.BulkOperations
{
public class BulkService<T> : IBulkService<T>
{
private readonly string _connectionString;
private readonly string _tableName;
private readonly DataTable _dataTable;
private IList<PropertyInfo> _properties;
public BulkService(string connectionString)
{
_connectionString = connectionString;
_tableName = this.GetTableName();
_dataTable = this.InitDataTable();
}
public BulkService(string connectionString, string tableName)
{
_connectionString = connectionString;
_tableName = tableName;
_dataTable = this.InitDataTable();
}
public async Task InsertAsync(ICollection<T> items)
{
this.PopulateDataTable(items);
using (var sqlBulk = new SqlBulkCopy(_connectionString))
{
sqlBulk.DestinationTableName = _tableName;
await sqlBulk.WriteToServerAsync(_dataTable);
}
}
private void PopulateDataTable(ICollection<T> items)
{
_dataTable.Rows.Clear();
foreach (var item in items)
{
var dr = _dataTable.NewRow();
foreach (var p in _properties)
{
var value = p.GetValue(item);
dr[p.Name] = value ?? DBNull.Value;
}
_dataTable.Rows.Add(dr);
}
}
private DataTable InitDataTable()
{
Type itemType = typeof(T);
var properties = itemType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
_properties = new List<PropertyInfo>();
var dataTable = new DataTable();
foreach (var p in properties)
{
_properties.Add(p);
var propertyType = p.PropertyType;
if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
propertyType = Nullable.GetUnderlyingType(propertyType);
}
dataTable.Columns.Add(this.GetColumnName(p), propertyType);
}
return dataTable;
}
private string GetColumnName(PropertyInfo p)
{
string columnName = string.Empty;
var columnAttribute = p.GetCustomAttribute(typeof(ColumnAttribute)) as ColumnAttribute;
if (columnAttribute != null && !string.IsNullOrEmpty(columnAttribute.Name))
{
columnName = columnAttribute.Name;
}
else
{
columnName = p.Name;
}
return columnName;
}
private string GetTableName()
{
string tableName = string.Empty;
var tableAttribute = typeof(T).GetCustomAttribute(typeof(TableAttribute)) as TableAttribute;
if (tableAttribute != null)
{
return tableAttribute.Name;
}
string className = typeof(T).Name;
if (className.IndexOf("Record") > -1)
{
tableName = className.Replace("Record", "s");
}
else
{
tableName = className + "s";
}
return tableName;
}
}
}
using System.Collections.Generic;
using System.Threading.Tasks;
namespace Common.SqlServer.BulkOperations
{
public interface IBulkService<T>
{
Task InsertAsync(ICollection<T> items);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment