private async Task BatchInsertExecuter(List <AsyncInputEvent <AppendInput, bool> > wrapperList)
{
using var conn = CreateConnection() as SqlConnection;
await conn.OpenAsync();
using var trans = conn.BeginTransaction();
try
{
using var bulkCopy = new SqlBulkCopy(conn, Microsoft.Data.SqlClient.SqlBulkCopyOptions.UseInternalTransaction, trans)
{
DestinationTableName = options.Value.TableName,
BatchSize = wrapperList.Count
};
using var dt = new DataTable();
dt.Columns.Add("UnitName", typeof(string));
dt.Columns.Add("TransactionId", typeof(long));
dt.Columns.Add("Data", typeof(string));
dt.Columns.Add("Status", typeof(int));
foreach (var item in wrapperList)
{
var row = dt.NewRow();
row["UnitName"] = item.Value.UnitName;
row["TransactionId"] = item.Value.TransactionId;
row["Data"] = item.Value.Data;
row["Status"] = (int)item.Value.Status;
dt.Rows.Add(row);
}
await conn.OpenAsync();
await bulkCopy.WriteToServerAsync(dt);
trans.Commit();
wrapperList.ForEach(wrap => wrap.TaskSource.TrySetResult(true));
}
catch
{
trans.Rollback();
using var insert_trans = conn.BeginTransaction();
try
{
await conn.ExecuteAsync(insert_sql, wrapperList.Select(wrapper => new
{
wrapper.Value.UnitName,
wrapper.Value.TransactionId,
wrapper.Value.Data,
Status = (short)wrapper.Value.Status
}).ToList(), insert_trans);
insert_trans.Commit();
wrapperList.ForEach(wrap => wrap.TaskSource.TrySetResult(true));
}
catch (Exception e)
{
insert_trans.Rollback();
wrapperList.ForEach(wrap => wrap.TaskSource.TrySetException(e));
}
}
}
private async Task BatchProcessing(List <AsyncInputEvent <AppendInput, bool> > wrapperList)
{
try
{
using var conn = CreateConnection() as SqlConnection;
using var bulkCopy = new SqlBulkCopy(conn)
{
DestinationTableName = options.Value.TableName,
BatchSize = wrapperList.Count
};
using var dt = new DataTable();
dt.Columns.Add("UnitName", typeof(string));
dt.Columns.Add("TransactionId", typeof(long));
dt.Columns.Add("Data", typeof(string));
dt.Columns.Add("Status", typeof(int));
foreach (var item in wrapperList)
{
var row = dt.NewRow();
row["UnitName"] = item.Value.UnitName;
row["TransactionId"] = item.Value.TransactionId;
row["Data"] = item.Value.Data;
row["Status"] = (int)item.Value.Status;
dt.Rows.Add(row);
}
await conn.OpenAsync();
await bulkCopy.WriteToServerAsync(dt);
wrapperList.ForEach(wrap => wrap.TaskSource.TrySetResult(true));
}
catch
{
var saveSql = $"if NOT EXISTS(SELECT * FROM {options.Value.TableName} where UnitName=@UnitName and TransactionId=@TransactionId)INSERT INTO {options.Value.TableName}(UnitName,TransactionId,Data,Status) VALUES(@UnitName,@TransactionId,@Data,@Status)";
using var conn = CreateConnection();
await conn.OpenAsync();
using var trans = conn.BeginTransaction();
try
{
await conn.ExecuteAsync(saveSql, wrapperList.Select(wrapper => new
{
wrapper.Value.UnitName,
wrapper.Value.TransactionId,
wrapper.Value.Data,
Status = (short)wrapper.Value.Status
}).ToList(), trans);
trans.Commit();
wrapperList.ForEach(wrap => wrap.TaskSource.TrySetResult(true));
}
catch (Exception e)
{
trans.Rollback();
wrapperList.ForEach(wrap => wrap.TaskSource.TrySetException(e));
}
}
}