本篇文章用于介绍怎么使用SQL Bulk Copy去提高批量插入数据的性能。
This function is to handle hundreds/thousands inserting of Entities, it has higher performance.
Currently it works only when C# properties match with DB columns names.
引入using Microsoft.Data.SqlClient;
目录
主代码
#region SqlBulkCopy
/// <summary>
/// This function is to handle hundreds/thousands inserting of Entities, it has higher performance.
/// Currently it works only when C# properties match with DB columns names.
/// </summary>
public static void BulkInsertBySqlBulkCopyFromEntities<T>(List<T> adoEntities) where T : EntityBase, IAdoEntity, new()
{
if (!adoEntities.Any()) return;
var mainDataTable = ConvertAdoEntitiesToDataTable(adoEntities);
TableBase<T> tableBase = new TableBase<T>();
var mainTableName = tableBase.GetDbTableName();
var connectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
TransactionOptions option = new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead,
Timeout = TimeSpan.FromMinutes(20)
};
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, option))
{
// Insert Associate AuditTrail
DataTable auditTrailDataTable = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
{
// Insert Main table Data
conn.Open();
var command = new SqlCommand($"SELECT IDENT_CURRENT('{mainTableName}');", conn);
var identityId = command.ExecuteScalar();
conn.Close();
BulkInsertBySqlBulkCopyFromDataTable(mainDataTable, mainTableName, sqlbulkCopy);
}
}
scope.Complete();
}
}
internal static void BulkInsertBySqlBulkCopyFromDataTable(DataTable dataTable, string tableName, SqlBulkCopy sqlbulkCopy)
{
sqlbulkCopy.DestinationTableName = tableName;
sqlbulkCopy.ColumnMappings.Clear();
for (int i = 0; i < dataTable.Columns.Count; i++)
{
sqlbulkCopy.ColumnMappings.Add(dataTable.Columns[i].ColumnName, dataTable.Columns[i].ColumnName);
}
sqlbulkCopy.WriteToServer(dataTable);
}
internal static DataTable ConvertAdoEntitiesToDataTable<T>(List<T> adoEntities) where T : EntityBase, IAdoEntity, new()
{
if (!adoEntities.Any()) return null;
DataTable dataTable = new DataTable();
adoEntities.First().GenerateDataTableColumns(dataTable);
adoEntities.ForEach(adoEntity =>
{
adoEntity.SetForeignKeys();
DataRow adoEntityRow = adoEntity.MapToDataRow(dataTable);
dataTable.Rows.Add(adoEntityRow);
});
return dataTable;
}
#endregionTableBase.cs
[DbTableConnectionString("Default")]
public class TableBase<T> : IAdoTable<T> where T : class, IAdoEntity, new()
{
#region Members
protected T Entity;
T IAdoTable<T>.Entity { get => Entity; set => Entity = value; }
IAdoEntity IAdoTable.Entity { get => Entity; set => Entity = (T)value; }
public string QueryString { get; set; }
public string Column { get; set; }
private Boolean? _isRowVersion { get; set; }
public Boolean IsRowVersionExist
{
get
{
if (_isRowVersion.HasValue == false)
{
_isRowVersion = Entity.GetType()
.GetProperty(SqlConstants.Columns.RowVersion) != null;
}
return this._isRowVersion.Value;
}
}
#endregion
#region Attributes
public string GetDbTableName()
{
DbTableNameAttribute tableNameAttribute;
tableNameAttribute =
typeof(T).GetCustomAttributes(typeof(DbTableNameAttribute), true).FirstOrDefault() as DbTableNameAttribute;
if (tableNameAttribute == null)
{
return string.Empty;
}
return tableNameAttribute.Value;
}
#endregion
#region SQL Stored Procedures
public string SELECT_COUNT
{
get
{
if (!string.IsNullOrEmpty(this.QueryString))
{
return string.Format(SqlConstants.StatementFormatting.SELECT_COUNT_FROM_WHERE, this.GetDbTableName() + " (nolock) ", this.QueryString);
}
return string.Format(SqlConstants.StatementFormatting.SELECT_COUNT_FROM, this.GetDbTableName() + " (nolock) ");
}
}
public string SELECT_COLUMN
{
get
{
if (!string.IsNullOrEmpty(this.QueryString))
{
return string.Format(SqlConstants.StatementFormatting.SELECT_COLUMN_FROM_WHERE, this.Column, this.GetDbTableName(), this.QueryString);
}
return string.Format(SqlConstants.StatementFormatting.SELECT_COLUMN_FROM, this.Column, this.GetDbTableName());
}
}
public string SELECT
{
get
{
if (!string.IsNullOrEmpty(this.QueryString))
{
return string.Format(SqlConstants.StatementFormatting.SELECT_FROM_WHERE, GetDbTableName(), this.QueryString);
}
return string.Format(SqlConstants.StatementFormatting.SELECT_FROM, GetDbTableName());
}
}
protected virtual void AddIdentitySqlParameter(SqlParameterCollection parameters)
{
SqlParameter identityParam = new SqlParameter();
identityParam.ParameterName = string.Format("@{0}", SqlConstants.Columns.IDENTITY);
identityParam.Size = 10;
identityParam.Direction = ParameterDirection.Output;
parameters.Add(identityParam);
}
protected virtual void AddInsertSqlParameters(SqlParameterCollection parameters)
{
//this.AddIdentitySqlParameter(parameters);
this.AddDefaultSqlParameters(parameters);
}
protected virtual void AddUpdateSqlParameters(SqlParameterCollection parameters)
{
this.AddDefaultSqlParameters(parameters);
if (!parameters.Contains(string.Format("@{0}", SqlConstants.Columns.IDENTITY)))
parameters.AddWithValue(string.Format("@{0}", SqlConstants.Columns.IDENTITY), this.Entity.Id);
}
protected virtual void AddDeleteSqlParameters(SqlParameterCollection parameters)
{
parameters.AddWithValue(string.Format("@{0}", SqlConstants.Columns.IDENTITY), this.Entity.Id);
if (this.IsRowVersionExist)
{
PropertyInfo rowversionProp = this.Entity.GetType().GetProperties()
.Single(e => e.Name == SqlConstants.Columns.RowVersion);
parameters.AddWithValue(string.Format("@{0}", SqlConstants.Columns.RowVersion), rowversionProp.GetValue(this.Entity));
}
}
public virtual void AddDefaultSqlParameters(SqlParameterCollection parameters)
{
T entity = this.Entity as T;
PropertyInfo[] propertyInfos = entity.GetType().GetProperties();
foreach (PropertyInfo propInfo in propertyInfos)
{
if (propInfo.IsDefined(typeof(DbIgnoreAttribute), true) || propInfo.IsDefined(typeof(DbReadOnlyAttribute), true))
{
continue;
}
string propertyName = propInfo.IsDefined(typeof(DbColumnNameAttribute), true)
? ((DbColumnNameAttribute)propInfo.GetCustomAttributes(typeof(DbColumnNameAttribute), true).First()).Value
: propInfo.Name;
if (propInfo.IsDefined(typeof(DbIgnoreAttribute), true) ||
propertyName == SqlConstants.Columns.IDENTITY && !propInfo.IsDefined(typeof(DbCompositeKeyAttribute), true))
{
continue;
}
object propertyValue = propInfo.GetValue(entity, null);
if (propertyValue == null)
{
if (propInfo.PropertyType == typeof(string))
propertyValue = string.Empty;
else
propertyValue = DBNull.Value;
}
if (propInfo.IsDefined(typeof(UTCField), true) && propertyValue != DBNull.Value)
{
propertyValue = ((DateTime)propertyValue).ToUniversalTime();
}
SqlParameter parameter = new SqlParameter($"@{propertyName}", propertyValue); // Create Parameter.
if (parameter.SqlDbType == SqlDbType.NVarChar) parameter.Size = -1;
if (propInfo.PropertyType.IsEnum)
{
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Size = -1;
}
try
{
var value = parameter.SqlValue; // Evaluate parameter value. If value is invalid, SqlValue will throw an exception.
// Running queries with bad parameter value abruptly terminates TransactionScope() causing partial information save in the system.
}
catch (Exception ex)
{
throw new Exception(string.Format("Invalid value for parameter '{0}: {1}' - {2}", propertyName, propertyValue, ex.Message)); // Throw custom exception with parameter details and errors.
}
parameters.Add(parameter); // Add parameter to parameters collections.
}
}
#endregion
#region SQL Statements
public string GetRowVersionStatement()
{
StringBuilder sb = new StringBuilder();
string selectColumncommand = string.Format(SqlConstants.StatementFormatting.SELECT_COLUMN_FROM
, SqlConstants.Columns.RowVersion
, this.GetDbTableName());
sb.Append(selectColumncommand);
sb.Append(' ');
sb.Append(string.Format(SqlConstants.StatementFormatting.WHERE_COLUMN_EQUALS_PARAMETER
, SqlConstants.Columns.IDENTITY
, SqlConstants.Columns.IDENTITY
));
return sb.ToString();
}
public string GetSqlStatement(SqlCommandTextEnum sqlCommandTextEnum)
{
StringBuilder sb = new StringBuilder();
sb.Append(this.GetSqlStatementAction(sqlCommandTextEnum));
switch (sqlCommandTextEnum)
{
case SqlCommandTextEnum.DELETE:
{
sb.Append(this.GetSqlStatementIdentityWhere());
break;
}
case SqlCommandTextEnum.INSERT:
{
sb.Append(this.GetSqlStatementInsertColumnsAndParameters());
sb.Append(this.GetSqlStatementIdentityScope());
break;
}
case SqlCommandTextEnum.UPDATE:
{
sb.Append(this.GetSqlStatementUpdateSetParameters());
sb.Append(this.GetSqlStatementIdentityWhere());
break;
}
default:
{
return string.Empty;
}
}
return sb.ToString();
}
#endregion
#region SQL Helpers
private string GetSqlStatementIdentityScope()
{
StringBuilder sb = new StringBuilder();
if (this.IsRowVersionExist)
{
sb.Append(SqlConstants.StatementAddons.RETURN_SCOPE_IDENTITY_AND_ROWVERSION);
}
else
{
sb.Append(SqlConstants.StatementAddons.RETURN_SCOPE_IDENTITY);
}
return sb.ToString();
}
private string GetSqlStatementUpdateSetParameters()
{
if (this.Entity == null)
{
return string.Empty;
}
StringBuilder sb = new StringBuilder();
sb.Append(" SET ");
T entity = this.Entity as T;
PropertyInfo[] propertyInfos = entity.GetType().GetProperties();
foreach (PropertyInfo propInfo in propertyInfos)
{
string propertyName = propInfo.IsDefined(typeof(DbColumnNameAttribute), true)
? ((DbColumnNameAttribute)propInfo.GetCustomAttributes(typeof(DbColumnNameAttribute), true).First()).Value
: propInfo.Name;
if (propInfo.IsDefined(typeof(DbIgnoreAttribute), true) || propInfo.IsDefined(typeof(DbReadOnlyAttribute), true) ||
(propertyName == SqlConstants.Columns.IDENTITY && !propInfo.IsDefined(typeof(DbCompositeKeyAttribute), true))
|| propertyName == SqlConstants.Columns.RowVersion)
{
continue;
}
sb.Append(string.Format(SqlConstants.StatementFormatting.COLUMN_EQUALS_PARAMETER_COMMA, propertyName, propertyName));
}
sb.Remove(sb.Length - 2, 2);
sb.Append(' ');
return sb.ToString();
}
private string GetSqlStatementIdentityWhere()
{
if (this.Entity == null)
{
return string.Empty;
}
StringBuilder sb = new StringBuilder();
sb.Append(' ');
if (this.IsRowVersionExist)
{
sb.Append(string
.Format(SqlConstants.StatementFormatting.WHERE_TWO_COLUMNS_EQUALS_PARAMETER
, SqlConstants.Columns.IDENTITY
, SqlConstants.Columns.IDENTITY
, SqlConstants.Columns.RowVersion
, SqlConstants.Columns.RowVersion));
}
else
{
sb.Append(string.Format(SqlConstants.StatementFormatting.WHERE_COLUMN_EQUALS_PARAMETER, SqlConstants.Columns.IDENTITY, SqlConstants.Columns.IDENTITY));
}
return sb.ToString();
}
private string GetSqlStatementInsertColumnsAndParameters()
{
if (this.Entity == null)
{
return string.Empty;
}
StringBuilder sbInsert = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
sbInsert.Append("( ");
sbValues.Append("VALUES (");
T entity = this.Entity as T;
PropertyInfo[] properties = entity.GetType().GetProperties();
foreach (PropertyInfo property in properties)
{
string propertyName = property.IsDefined(typeof(DbColumnNameAttribute), true)
? ((DbColumnNameAttribute)property.GetCustomAttributes(typeof(DbColumnNameAttribute), true).First()).Value
: property.Name;
if (property.IsDefined(typeof(DbIgnoreAttribute), true) || property.IsDefined(typeof(DbReadOnlyAttribute), true) ||
(propertyName == SqlConstants.Columns.IDENTITY && !property.IsDefined(typeof(DbCompositeKeyAttribute), true))
|| propertyName == SqlConstants.Columns.RowVersion)
{
continue;
}
sbInsert.Append(string.Format(SqlConstants.StatementFormatting.INSERT_COLUMN, propertyName));
sbValues.Append(string.Format(SqlConstants.StatementFormatting.PARAMETER_COMMA, propertyName));
}
sbInsert.Remove(sbInsert.Length - 2, 2);
sbValues.Remove(sbValues.Length - 2, 2);
sbInsert.Append(')');
sbValues.Append(')');
return string.Format("{0} {1}", sbInsert.ToString(), sbValues.ToString());
}
private string GetSqlStatementAction(SqlCommandTextEnum sqlCommandTextEnum)
{
StringBuilder sb = new StringBuilder();
switch (sqlCommandTextEnum)
{
case SqlCommandTextEnum.INSERT:
{
sb.Append(string.Format(SqlConstants.Actions.INSERT_INTO, this.GetDbTableName()));
return sb.ToString();
}
case SqlCommandTextEnum.UPDATE:
{
sb.Append(string.Format(SqlConstants.Actions.UPDATE, this.GetDbTableName()));
return sb.ToString();
}
case SqlCommandTextEnum.DELETE:
{
sb.Append(string.Format(SqlConstants.Actions.DELETE_FROM, this.GetDbTableName()));
return sb.ToString();
}
default:
{
return string.Empty;
}
}
}
public void GetSqlCommandParams(SqlParameterCollection parameters, SqlCommandTextEnum sqlCommandTextEnum)
{
switch (sqlCommandTextEnum)
{
case SqlCommandTextEnum.INSERT:
{
AddInsertSqlParameters(parameters);
break;
}
case SqlCommandTextEnum.UPDATE:
{
AddUpdateSqlParameters(parameters);
break;
}
case SqlCommandTextEnum.DELETE:
{
AddDeleteSqlParameters(parameters);
break;
}
}
}
#endregion
#region SQL DataSet Mapping
public virtual T MapRow(SqlDataReader reader)
{
T entity = new T();
entity.Map(reader);
return entity;
}
IAdoEntity IAdoTable.MapRow(SqlDataReader reader)
{
return MapRow(reader);
}
#endregion
#region Ctor
public TableBase()
{
}
public TableBase(T entity)
{
this.Entity = entity;
}
#endregion
#region Common Parameter
public static void GetReadSqlParameterDateRange(SqlParameterCollection parameters, object entity)
{
parameters.AddWithValue("@UserId", ((dynamic)entity).UserId);
parameters.AddWithValue("@StartDate", ((dynamic)entity).StartDate);
parameters.AddWithValue("@EndDate", ((dynamic)entity).EndDate);
parameters.AddWithValue("@CutOffDate", ((dynamic)entity).CutOffDate);
}
public static void GetReadReconCommonSqlParameters(SqlParameterCollection parameters, object entity)
{
parameters.AddWithValue("@UserId", ((dynamic)entity).UserId);
parameters.AddWithValue("@StartDate", ((dynamic)entity).StartDate);
parameters.AddWithValue("@EndDate", ((dynamic)entity).EndDate);
parameters.AddWithValue("@CutOffDate", ((dynamic)entity).CutOffDate);
parameters.AddWithValue("@IncludeFinalizedRecord", ((dynamic)entity).IncludeFinalizedRecord);
}
public static void GetReadCommonByUserIdSqlParameters(SqlParameterCollection parameters, object entity)
{
int userId = ((ApplicationUser)entity).Id;
parameters.AddWithValue("@UserId", userId);
}
public static void GetByUserIdDateRangeSqlParameters(SqlParameterCollection parameters, object entity)
{
var entityBase = entity as EntityBase;
parameters.AddWithValue("@UserId", ((dynamic)entityBase).UserId);
parameters.AddWithValue("@StartDate", ((dynamic)entityBase).StartDate);
parameters.AddWithValue("@EndDate", ((dynamic)entityBase).EndDate);
}
public static void GetByDateRangeSqlParameters(SqlParameterCollection parameters, object entity)
{
var entityBase = entity as EntityBase;
parameters.AddWithValue("@StartDate", ((dynamic)entityBase).StartDate);
parameters.AddWithValue("@EndDate", ((dynamic)entityBase).EndDate);
}
#endregion
}IAdoTable.cs
public interface IAdoTable<T> : IAdoTable where T : class, IAdoEntity, new()
{
new T MapRow(SqlDataReader reader);
new T Entity { get; set; }
}IAdoEntity.cs
public interface IAdoEntity
{
int Id { get; set; }
int GetDbPriority();
int GetDeletePriority();
bool IsUpdateAdoHash { get; }
string AdoHash { get; set; }
string AdoCacheKey { get; set; }
void CascadeDelete();
void Map(SqlDataReader row);
void GenerateDataTableColumns(DataTable dataTable);
DataRow MapToDataRow(DataTable dataTable);
void RaisePropertyChangedEvent(string propertyName);
void SetForeignKeys();
void SetCompositeKeys();
AdoEntityStateEnum State { get; set; }
object Clone();
bool IsBulkEntity { get; set; }
}Attributes
public sealed class DbTableConnectionStringAttribute : Attribute
{
private string _value;
public string Value
{
get { return _value; }
set { _value = value; }
}
public DbTableConnectionStringAttribute(string connectionStringName)
{
this.Value = connectionStringName;
}
}
public sealed class DbTableNameAttribute : System.Attribute
{
private string _value;
public string Value
{
get { return _value; }
set
{
_value = value;
}
}
public DbTableNameAttribute(string table)
{
this.Value = table;
}
}