批量更新事物加回滚

public static String BulkInsert(SqlConnection conn, DataTable dt, SqlTransaction trans, Type t = null)
{
try
{
using (SqlBulkCopy bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans))
{
bulk.ColumnMappings.Clear();
bulk.BatchSize = 10000;
bulk.BulkCopyTimeout = 120;
if (dt.TableName.ToUpper().IndexOf("BPM_") >= 0 || dt.TableName.ToUpper().IndexOf("SYS_") >= 0)
{
bulk.DestinationTableName = dt.TableName;
}
else
{
bulk.DestinationTableName = "Biz_" + dt.TableName;
}

if (t != null)
{
Type tm = t.BaseType;
foreach (DataColumn col in dt.Columns)
{
PropertyInfo[] ptys = tm.GetProperties();
foreach (PropertyInfo pro in ptys)
{
//这里用于添加Filter中用于非Domain查询的字段
if (col.ColumnName == pro.Name)
{
bulk.ColumnMappings.Add(col.ColumnName, pro.Name);
}
}
}
}
else
{
foreach (DataColumn col in dt.Columns)
{
bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
}
bulk.WriteToServer(dt);
}
return "";
}
catch (Exception err)
{
return err.Message;
}
}

 

private static string SynchronousDb(ExportBayPlanFilter filter, List<ExportBayPlanDomain> listBayPlan, List<ExportBayPlanBlDomain> listBayPlanBl, List<ExportBayPlanImdgDomain> listImdg, LoginUser loginuser)
{
try
{
using (var conn = (SqlConnection)DataHelper.DbService)
{
SqlTransaction trans = conn.BeginTransaction();
try
{
string sql = @"DELETE imdg FROM dbo.Biz_ExportBayPlanImdg imdg
INNER JOIN dbo.Biz_ExportBayPlan bay ON imdg.BayplanId = bay.Id
WHERE bay.Vessel = @Vessel AND bay.Voyage = @Voyage AND bay.BranchCode = @BranchCode AND bay.IsLocked = '0' ;
DELETE bl FROM dbo.Biz_ExportBayPlanBl bl
INNER JOIN dbo.Biz_ExportBayPlan bay ON bl.BayplanId = bay.Id
WHERE bay.Vessel = @Vessel AND bay.Voyage = @Voyage AND bay.BranchCode = @BranchCode AND bay.IsLocked = '0' ;
DELETE FROM Biz_ExportBayPlan WHERE Vessel = @Vessel AND Voyage = @Voyage AND BranchCode = @BranchCode AND IsLocked = '0' ;";

DataHelper.Execute(sql, conn, trans, filter);

var dtbayPlan = ObjectTools.ConvertToDataTable(listBayPlan, typeof(ExportBayPlanDomain).ToString());
dtbayPlan.TableName = "ExportBayPlan";

var dtbayPlanBl = ObjectTools.ConvertToDataTable(listBayPlanBl, typeof(ExportBayPlanBlDomain).ToString());
dtbayPlanBl.TableName = "ExportBayPlanBl";

var dtImdg = ObjectTools.ConvertToDataTable(listImdg, typeof(ExportBayPlanImdgDomain).ToString());
dtImdg.TableName = "ExportBayPlanImdg";

 

var res = DataHelper.BulkInsert(conn, dtbayPlan, trans, typeof(ExportBayPlanDomain));
res += DataHelper.BulkInsert(conn, dtbayPlanBl, trans, typeof(ExportBayPlanBlDomain));
res += DataHelper.BulkInsert(conn, dtImdg, trans, typeof(ExportBayPlanImdgDomain));

if (String.IsNullOrEmpty(res))
{
trans.Commit();
}
else
{
trans.Rollback();
}

conn.Close();
return res;
}
catch (Exception er)
{
trans.Rollback();
conn.Close();
return er.Message;
}
}
}
catch (Exception err)
{
return err.Message;
}
}