Hello Team,
Performance Issue in SqlBulkCopy c#
await BulkHelper.PerAsync(Datas, connection, transaction);
await BulkHelper.PerAsync(Limits, connection, transaction);
await BulkHelper.PerAsync(variables, connection, transaction);
await BulkHelper.PerAsync(rValues, connection, transaction);
await BulkHelper.PerAsync(traResults, connection, transaction);
await BulkHelper.PerAsync(resLimits, connection, transaction);
"Datas" table is chained to "Limits" table and so on : Based on "Datas" table "Limits" Table will be updated.
public static async Task PerAsync<T>(IEnumerable<T> items, SqlConnection destinationConnection, IDbTransaction transaction)
{
ReflectionHelper.PrepareForType<T>();
string tableName = ReflectionHelper.GetTableNameForType<T>();
string tempTableName = $"dbo.#{tableName}";
DataTable dt = DataTableHelper.CreateDataTable<T>();
ConvertToDatatable(items, dt);
if (destinationConnection.State == ConnectionState.Closed)
{
destinationConnection.Open();
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.Default, transaction as SqlTransaction))
{
var command = new SqlCommand
{
Connection = destinationConnection,
CommandType = CommandType.Text,
CommandText = $"SELECT TOP(0) * INTO {tempTableName} FROM {tableName};",
Transaction = transaction as SqlTransaction
};
await command.ExecuteNonQueryAsync();
bulkCopy.DestinationTableName = tempTableName;
foreach (DataColumn col in dt.Columns)
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(col.ColumnName, col.ColumnName));
}
await bulkCopy.WriteToServerAsync(dt);
var updateQuery = DataTableHelper.GetUpdateQuery(typeof(T), tempTableName, tableName);
var insertQuery = DataTableHelper.GetInsertQuery(typeof(T), tempTableName, tableName);
var dropQuery = $"DROP TABLE {tempTableName}";
var updateCommand = transaction != null ? new SqlCommand(updateQuery, destinationConnection, transaction as SqlTransaction) : new SqlCommand(updateQuery, destinationConnection);
updateCommand.CommandType = CommandType.Text;
var insertCommand = transaction != null ? new SqlCommand(insertQuery, destinationConnection, transaction as SqlTransaction) : new SqlCommand(insertQuery, destinationConnection);
insertCommand.CommandType = CommandType.Text;
var dropCommand = transaction != null ? new SqlCommand(dropQuery, destinationConnection, transaction as SqlTransaction) : new SqlCommand(dropQuery, destinationConnection);
dropCommand.CommandType = CommandType.Text;
await insertCommand.ExecuteNonQueryAsync();
await updateCommand.ExecuteNonQueryAsync();
await dropCommand.ExecuteNonQueryAsync();
}
}
private static void ConvertToDatatable<T>(IEnumerable<T> data, DataTable dt)
{
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
foreach (var item in data)
{
var dr = dt.NewRow();
foreach (DataColumn dataColumn in dt.Columns)
{
var colData = props[dataColumn.Caption].GetValue(item);
dr[dataColumn] = colData ?? DBNull.Value;
}
dt.Rows.Add(dr);
}
}
}
Requirement : Need to better solution to update, delete, Create without Concurrency.
and Also need in Parallel Excecution.