public async Task BatchExcuteAsync(BulkCopyModel bulkCopyModel, params SqlCommand[] commands) //IContextInfo contextInfo,
{
if (commands?.Length < 1)
{
return;
}
using (var connection = new sql.SqlConnection(_connectionString))
{
//connection.StateChange += new System.Data.StateChangeEventHandler((sender, e) =>
//{
// if (e.CurrentState == System.Data.ConnectionState.Open)
// setContextInfo(connection, contextInfo);
//});
connection.TryOpen();
using (var tran = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
foreach (var command in commands)
{
command.Connection = connection;
command.Transaction = tran;
await command.ExecuteNonQueryAsync();
}
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran))
{
bulkCopy.BatchSize = bulkCopyModel.BatchSize; // ~ 100
bulkCopy.DestinationTableName = bulkCopyModel.TableName;
if (bulkCopyModel.ColumnMappings != null)
{
bulkCopyModel.ColumnMappings.ForEach(c => bulkCopy.ColumnMappings.Add(c));
}
var dataTable = bulkCopyModel.DataTable;
bulkCopy.BulkCopyTimeout = bulkCopyModel.Timeout;
await bulkCopy.WriteToServerAsync(dataTable);
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw e;
}
finally
{
//clearContextInfo(connection, contextInfo);
connection.TryClose();
}
}
}
}
public async Task BatchExcuteAsync(params sql.SqlCommand[] commands) //IContextInfo contextInfo,
{
if (commands?.Length < 1)
{
return;
}
using (var connection = new sql.SqlConnection(_connectionString))
{
//connection.StateChange += new System.Data.StateChangeEventHandler((sender, e) =>
//{
// if (e.CurrentState == System.Data.ConnectionState.Open)
// setContextInfo(connection, contextInfo);
//});
await connection.TryOpenAsync();
using (var tran = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
foreach (var command in commands)
{
command.Connection = connection;
command.Transaction = tran;
await command.ExecuteNonQueryAsync();
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw e;
}
finally
{
//clearContextInfo(connection, contextInfo);
connection.TryClose();
}
}
}
}
protected System.Data.DataSet Execute(sql.SqlCommand command) //, IContextInfo contextInfo
{
var dataSet = new System.Data.DataSet();
using (var connection = new sql.SqlConnection(_connectionString))
{
//connection.StateChange += new System.Data.StateChangeEventHandler((sender, e) =>
//{
// if (e.CurrentState == System.Data.ConnectionState.Open)
// setContextInfo(connection, contextInfo);
//});
command.Connection = connection;
connection.TryOpen();
using (var reader = command.ExecuteReader())
{
do
{
if (reader.HasRows && reader.FieldCount > 0)
{
using (var table = dataSet.Tables.Add())
{
table.Load(reader);
dataSet.Tables.Add(table);
}
}
else
{
reader.Close();
}
}while (!reader.IsClosed);
}
//clearContextInfo(connection, contextInfo);
connection.TryClose();
}
return(dataSet);
}
private DataTable loadData(string query)
{
var table = new DataTable();
using (var connection = new q.SqlConnection(_connectionString))
{
connection.TryOpen();
using (var command = connection.CreateCommand())
{
command.CommandText = query;
using (var reader = command.ExecuteReader())
{
table.Load(reader);
reader.Close();
}
}
connection.TryClose();
}
return(table);
}
