/// <summary>
/// 2020.06.15增加Dapper事务处理
/// <param name="action"></param>
/// <param name="error"></param>
public void BeginTransaction(Func <ISqlDapper, bool> action, Action <Exception> error)
{
_transaction = true;
using (var connection = DBServerProvider.GetDbConnection(_connectionString))
{
try
{
_transactionConnection = connection;
_transactionConnection.Open();
dbTransaction = _transactionConnection.BeginTransaction();
bool result = action(this);
if (result)
{
dbTransaction?.Commit();
}
else
{
dbTransaction?.Rollback();
}
}
catch (Exception ex)
{
dbTransaction?.Rollback();
error(ex);
}
finally
{
_transaction = false;
dbTransaction?.Dispose();
}
}
}
private T Execute <T>(Func <IDbConnection, IDbTransaction, T> func, bool beginTransaction = false)
{
if (_transaction)
{
return(func(_transactionConnection, dbTransaction));
}
if (beginTransaction)
{
return(ExecuteTransaction(func));
}
using (var connection = DBServerProvider.GetDbConnection(_connectionString))
{
return(func(connection, dbTransaction));
}
}
/// <summary>
///大批量数据插入,返回成功插入行数
////
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="table">数据表</param>
/// <returns>返回成功插入行数</returns>
private int MySqlBulkInsert(DataTable table, string tableName, string fileName = null, string tmpPath = null)
{
if (table.Rows.Count == 0)
{
return(0);
}
tmpPath = tmpPath ?? FileHelper.GetCurrentDownLoadPath();
int insertCount = 0;
string csv = DataTableToCsv(table);
string text = $"当前行:{table.Rows.Count}";
MemoryStream stream = null;
try
{
using (var Connection = DBServerProvider.GetDbConnection(_connectionString))
{
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
using (IDbTransaction tran = Connection.BeginTransaction())
{
MySqlBulkLoader bulk = new MySqlBulkLoader(Connection as MySqlConnection)
{
LineTerminator = "\n",
TableName = tableName,
CharacterSet = "UTF8"
};
var array = Encoding.UTF8.GetBytes(csv);
using (stream = new MemoryStream(array))
{
stream = new MemoryStream(array);
bulk.SourceStream = stream; //File.OpenRead(fileName);
bulk.Columns.AddRange(table.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList());
insertCount = bulk.Load();
tran.Commit();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(insertCount);
// File.Delete(path);
}
private T Execute <T>(Func <IDbConnection, IDbTransaction, T> func, bool beginTransaction = false)
{
if (_transaction)
{
return(func(_transactionConnection, dbTransaction));
}
if (beginTransaction)
{
return(ExecuteTransaction(func));
}
using (var connection = DBServerProvider.GetDbConnection(_connectionString))
{
T reslutT = func(connection, dbTransaction);
if (!_transaction && dbTransaction != null)
{
dbTransaction.Commit();
}
return(reslutT);
}
}
/// <summary>
/// 通过Bulk批量插入
/// </summary>
/// <param name="table"></param>
/// <param name="tableName"></param>
/// <param name="sqlBulkCopyOptions"></param>
/// <param name="dbKeyName"></param>
/// <returns></returns>
private int MSSqlBulkInsert(DataTable table, string tableName, SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.UseInternalTransaction, string dbKeyName = null)
{
using (var Connection = DBServerProvider.GetDbConnection(_connectionString))
{
if (!string.IsNullOrEmpty(dbKeyName))
{
Connection.ConnectionString = DBServerProvider.GetConnectionString(dbKeyName);
}
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(Connection.ConnectionString, sqlBulkCopyOptions))
{
sqlBulkCopy.DestinationTableName = tableName;
sqlBulkCopy.BatchSize = table.Rows.Count;
for (int i = 0; i < table.Columns.Count; i++)
{
sqlBulkCopy.ColumnMappings.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
}
sqlBulkCopy.WriteToServer(table);
return(table.Rows.Count);
}
}
}
private T ExecuteTransaction <T>(Func <IDbConnection, IDbTransaction, T> func)
{
using (var connection = DBServerProvider.GetDbConnection(_connectionString))
{
try
{
connection.Open();
dbTransaction = connection.BeginTransaction();
T reslutT = func(connection, dbTransaction);
if (!_transaction && dbTransaction != null)
{
dbTransaction.Commit();
}
return(reslutT);
}
catch (Exception ex)
{
dbTransaction?.Rollback();
throw ex;
}
}
}
private T ExecuteTransaction <T>(Func <IDbConnection, IDbTransaction, T> func)
{
using (_transactionConnection = DBServerProvider.GetDbConnection(_connectionString))
{
try
{
_transactionConnection.Open();
dbTransaction = _transactionConnection.BeginTransaction();
T reslutT = func(_transactionConnection, dbTransaction);
dbTransaction.Commit();
return(reslutT);
}
catch (Exception ex)
{
dbTransaction?.Rollback();
throw ex;
}
finally
{
dbTransaction?.Dispose();
}
}
}