我們在用NET+EF+MVC開發接口的時候，很多時候需要用到事務送出，就是多個表要同時插入資料，筆者介紹一下批量插入和一般插入的方法，以供參考：
一、批量插入寫法，項目需要引用 Z.EntityFramework.Extensions.dll 檔案
接口以json格式送出 ：
{
"school_bus_ID": "465215309649218506084837",
"carName": "宇通001",
"TransferType": 2,
"startTime": "2022-10-20 10:30",
"ShouldNum": 30,
"driverID": "567642394750651749789141",
"driverName": "李司機",
"Token":"eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJVc2VyTmFtZSI6IjEzOTAxMDQxNDcwIiwiRXhwaXJ5RGF0ZVRpbWUiOiIyMDIyLTA5LTI2VDE2OjA2OjUwLjM4MjEyNzIrMDg6MDAiLCJUb2tlblR5cGUiOjR9.ZyiePFdekxmxr5BaY0atBAtmR8TSI8uStUtEZwbTpn4",
"teacherList": [
{
"TeachersID": "509078663660450549914899",
"Name": "張老師",
"isMain": 1
} ,
{
"TeachersID": "10101384099503212068864",
"Name": "李老師",
"isMain": 0
}
]
}
#region 釋出校車出車任務接口
/// <summary>
/// 釋出校車出車任務
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
[HttpPost]
public HttpResponseMessage School_bus_TaskRecordAdd(object data)
{
HttpResponseMessage result = null;
var ReturnStr = "{\"status\":\"N\",\"msg\":\"參數錯誤\"}";
//string Sdata = data.ToString();
//var admin = JsonConvert.DeserializeObject<dynamic>(Sdata);//動态對象
Api_school_bus_TaskRecordModel admin = JsonConvert.DeserializeObject<Api_school_bus_TaskRecordModel>(data.ToEnJsonString());
string school_bus_ID = null;
string carName = null;
string TransferType = null;
string startTime = null;
string ShouldNum = null;
string driverID = null;
string driverName = null;
if (admin.school_bus_ID != null)
{
school_bus_ID = admin.school_bus_ID;
}
if (admin.carName != null)
{
carName = admin.carName;
}
if (admin.TransferType != null)
{
TransferType = admin.TransferType;
}
if (admin.startTime != null)
{
startTime = admin.startTime;
}
if (admin.ShouldNum != null)
{
ShouldNum = admin.ShouldNum;
}
if (admin.driverID != null)
{
driverID = admin.driverID;
}
if (admin.driverName != null)
{
driverName = admin.driverName;
}
if (string.IsNullOrEmpty(school_bus_ID))
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"校車ID不能為空\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
else if (string.IsNullOrEmpty(carName))
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"校車名稱不能為空\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
else if (string.IsNullOrEmpty(TransferType))
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"接送類型不能為空\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
else if (string.IsNullOrEmpty(startTime))
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"發車時間不能為空\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
else if (string.IsNullOrEmpty(ShouldNum))
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"應上車人數不能為空\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
else if (string.IsNullOrEmpty(driverID))
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"司機ID不能為空\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
else if (string.IsNullOrEmpty(driverName))
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"司機姓名不能為空\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
else
{
#region 驗證token
string Token = admin.Token;
if (!string.IsNullOrEmpty(Token))
{
int t = new WisdomStars.Bll.Api.Common().PUB_VerificationToken(Token);
if (t <= 0)
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"登入失效\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
}
else
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"token不能為空\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
#endregion 驗證token
#region 釋出
TransactionOptions transactionOption = new TransactionOptions();
//設定事務隔離級别
transactionOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
// 設定事務逾時時間為60秒
transactionOption.Timeout = new TimeSpan(0, 1, 30);
using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required, transactionOption)) //需要事務復原的地方外面套一層using (TransactionScope sc = new TransactionScope()){}
{
try
{
var teacherList = admin.teacherList;
if (admin != null && teacherList != null&& teacherList.Count>0)
{
decimal yfschool_bus_ID = decimal.Parse(school_bus_ID.ToString());
hx_school_bus m_bus = ef.hx_school_bus.Where(a => a.school_bus_ID == yfschool_bus_ID).AsNoTracking().FirstOrDefault();
//yefeiyefeiyefei-2022-10-22
DateTime startdt = Convert.ToDateTime(startTime.Substring(0, 10) + " 00:00");
DateTime enddt =Convert.ToDateTime(startTime.Substring(0,10)+" 23:59");
hx_school_bus_TaskRecord yf_m1 = ef.hx_school_bus_TaskRecord.Where(a => a.school_bus_ID == yfschool_bus_ID && a.startTime >= startdt && a.startTime <= enddt && a.TransferType == 2).AsNoTracking().FirstOrDefault();
hx_school_bus_TaskRecord yf_m2 = ef.hx_school_bus_TaskRecord.Where(a => a.school_bus_ID == yfschool_bus_ID && a.startTime >= startdt && a.startTime <= enddt && a.TransferType == 3).AsNoTracking().FirstOrDefault();
if (yf_m1 != null)
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"該車任務當天的接的次數不能超過一次\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
if (yf_m2 != null)
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"該車任務當天的送的次數不能超過一次\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
if (m_bus != null)
{
int yfShouldNum =int.Parse(ShouldNum);
if (yfShouldNum > m_bus.loadnumber)
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"應上車人數不能超過該車核載人數！\",\"data\":\"\"}";
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
hx_school_bus_TaskRecord model = new hx_school_bus_TaskRecord();
decimal bus_TaskRecordID = WisdomStarts.Common.Utils.GenerateIntID();
model.bus_TaskRecordID = bus_TaskRecordID;
model.school_bus_ID = yfschool_bus_ID;
model.carName = carName;
model.platenumber = m_bus.platenumber;
model.TrialID = m_bus.TrialID;
model.KindergartenID = m_bus.KindergartenID;
model.driverID = decimal.Parse(driverID);
model.driverName = driverName;
model.TaskRoute = m_bus.LineRemarks;
model.TransferType = int.Parse(TransferType);
model.startTime = Convert.ToDateTime(startTime);
model.endTime = null;
model.ShouldNum = int.Parse(ShouldNum);
model.ActualNum = 0;
model.getOffNum = 0;
model.isAbnormal = 0;
model.remark = "";
model.isStart = 0; //發車狀态:0未發車 1行進中 2結束運作
model.createtime = DateTime.Now;
ef.hx_school_bus_TaskRecord.Add(model);
int t = ef.SaveChanges();
if (t>0)
{
#region 生成跟車老師清單
List<hx_school_bus_FollowTeacher> bus_FollowTeacherlist = new List<hx_school_bus_FollowTeacher>();
foreach (var item in teacherList)
{
decimal FollowTeacherID = WisdomStarts.Common.Utils.GenerateIntID();
hx_school_bus_FollowTeacher bus_FollowTeacher = new hx_school_bus_FollowTeacher();
bus_FollowTeacher.FollowTeacherID = FollowTeacherID;
bus_FollowTeacher.bus_TaskRecordID = bus_TaskRecordID;
bus_FollowTeacher.school_bus_ID = decimal.Parse(school_bus_ID);
bus_FollowTeacher.TeachersID =decimal.Parse(item.TeachersID);
bus_FollowTeacher.Name = item.Name;
bus_FollowTeacher.isMain = item.isMain;
bus_FollowTeacher.createtime = DateTime.Now;
ef.hx_school_bus_FollowTeacher.Add(bus_FollowTeacher);
}
ef.BulkInsert(bus_FollowTeacherlist);
ef.BulkSaveChanges();
ef.SaveChanges();
#endregion 生成跟車老師清單
trans.Complete();
ReturnStr = "{\"status\":\"Y\",\"msg\":\"操作成功\"}";
}
else
{
ReturnStr = "{\"status\":\"N\",\"msg\":\"操作失敗\"}";
}
}
else
{
ReturnStr = "{\"status\":\"N\",\"msg\":\"操作失敗\"}";
}
}
}
catch (Exception e)
{
ReturnStr = "{\"result\":\"" + (int)KeyEnum.AppReturn.失敗 + "\",\"msg\":\"釋出失敗\",\"data\":\"\"}";
trans.Dispose();
}
}
#endregion 釋出
}
result = new HttpResponseMessage { Content = new StringContent(ReturnStr, Encoding.GetEncoding("UTF-8"), "application/json") };
return result;
}
#endregion 釋出校車出車任務
二、第二種正常方式送出
#region 添加幼稚園送出
public int kindergartenAddPost(gpt_Kindergarten model)
{
#region 擷取登入使用者資訊
decimal adminUsrID = loginModel.adminUsrID;
var adminUsrname = loginModel.RealName;
decimal subsidiary_id = loginModel.subsidiary_id;
int company_type = loginModel.company_type;
#endregion 擷取登入使用者資訊
int res = 0;
if (model != null)
{
#region 校區名稱是否存在
gpt_Kindergarten dd = ef.gpt_Kindergarten.Where(a => a.isdel == 0 && a.kindergarten == model.kindergarten).AsNoTracking().FirstOrDefault();
if (dd != null)
{
return -1;
}
#endregion 校區名稱是否存在
#region 客戶已經存在
gpt_Kindergarten ddd = ef.gpt_Kindergarten.Where(a => a.isdel == 0 && a.sellt_customer_id == model.sellt_customer_id).AsNoTracking().FirstOrDefault();
if (ddd != null)
{
return -3;
}
#endregion 客戶已經存在
TransactionOptions transactionOption = new TransactionOptions();
//設定事務隔離級别
transactionOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
// 設定事務逾時時間為60秒
transactionOption.Timeout = new TimeSpan(0, 0, 30);
using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required, transactionOption)) //需要事務復原的地方外面套一層using (TransactionScope sc = new TransactionScope()){}
{
try
{
decimal NewOrderRelItemId = 0;
decimal OrderRelItemId = 0;
#region 生成申請試用表
//收費項目清單
List<gpt_chargeitem> chargeitemlist = new Distribution.Bll.SelectListByEF().GetchargeitemList();
decimal ServicePrice = 0;
foreach (var item in chargeitemlist)
{
ServicePrice += item.ChargePrice;
}
decimal TrialID = GetID.GetUrlID((int)KeyEnum.ServiceType.主鍵id); //申請試用ID
gpt_Customer_trial trialmodel = new gpt_Customer_trial();
trialmodel.TrialID = TrialID;
trialmodel.kindergarten = model.kindergarten;
trialmodel.KinderAddress = model.KinderAddress;
trialmodel.Cust_name = model.linkname;
ef.gpt_Customer_trial.Add(trialmodel);
#endregion 生成申請試用表
#region 生成幼稚園訂單
decimal ServiceOrderID = GetID.GetUrlID((int)KeyEnum.ServiceType.主鍵id); //服務訂單ID
gpt_Kindergarten_ServiceOrder ServiceOrderModel = new gpt_Kindergarten_ServiceOrder();
ServiceOrderModel.ServiceOrderID = ServiceOrderID;
ServiceOrderModel.TrialID = TrialID;
ServiceOrderModel.PayMethodID = 0;
ef.gpt_Kindergarten_ServiceOrder.Add(ServiceOrderModel);
#endregion 生成幼稚園訂單
#region 生成服務訂單與購買項目關系表
if (chargeitemlist != null && chargeitemlist.Count > 0)
{
foreach (var item in chargeitemlist)
{
OrderRelItemId = GetID.GetUrlID((int)KeyEnum.ServiceType.主鍵id); //服務訂單與項目關聯id
if (NewOrderRelItemId == 0)
{
NewOrderRelItemId = OrderRelItemId;
}
gpt_Service_Order_Rel_Item Rel_ItemModel = new gpt_Service_Order_Rel_Item();
Rel_ItemModel.OrderRelItemId = OrderRelItemId;
Rel_ItemModel.ServiceOrderID = ServiceOrderID;
Rel_ItemModel.ChargeitemId = item.ChargeitemId;
ef.gpt_Service_Order_Rel_Item.Add(Rel_ItemModel);
}
}
#endregion 生成服務訂單與購買項目關系表
#region 添加幼稚園
decimal KindergartenID = GetID.GetUrlID((int)KeyEnum.ServiceType.主鍵id);
//服務訂單ID
model.ServiceOrderID = ServiceOrderID;
model.KindergartenID = KindergartenID;
model.Createtime = DateTime.Now;
ef.gpt_Kindergarten.Add(model);
#endregion 添加幼稚園
res = ef.SaveChanges();
if (res > 0)
{
trans.Complete();
}
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
Distribution.Common.NetLog.WriteTextLog("Class:", validationErrors.Entry.Entity.GetType().FullName);
Distribution.Common.NetLog.WriteTextLog("Property:", validationError.PropertyName);
Distribution.Common.NetLog.WriteTextLog("Error:", validationError.ErrorMessage);
}
}
throw;
}
catch (Exception e)
{
Distribution.Common.NetLog.WriteTextLog("添加幼稚園", e.ToString());
res = 0;
trans.Dispose();
}
}
}
return res;
}
#endregion 添加幼稚園送出
