public void Update_Transaction() { try { SqlMapper.BeginTransaction(); var list = SqlMapper.Query <T_Test>(new RequestContext { Scope = "T_Test", SqlId = "GetList", Request = null }); foreach (var test in list) { test.Name = test.Name + "-Update"; int exeNum = SqlMapper.Execute(new RequestContext { Scope = "T_Test", SqlId = "Update", Request = test }); } SqlMapper.CommitTransaction(); } catch (Exception ex) { SqlMapper.RollbackTransaction(); throw ex; } }
/// <summary> /// 上架下架网课 /// </summary> /// <param name="Model"></param> /// <param name="System_Station_ID"></param> /// <returns></returns> public bool UpdateCourseIsPutaway(CampusModel Model, int System_Station_ID) { SqlMapper.BeginTransaction(); try { W_Course model = Orm.Single <W_Course>(x => x.System_Station_ID == System_Station_ID && x.ID == Model.ID); if (model == null) { throw new ApiException("您要操作的数据不存在"); } model.IsPutaway = Model.IsPutaway; if (Orm.Update(model) <= 0) { throw new ApiException("修改失败,请重新操作"); } SqlMapper.CommitTransaction(); return(true); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw ex; } }
/// <summary> /// 拖拽排序(拖拽完就保存) /// </summary> /// <param name="new_prev_order">拖拽后上节点排序值</param> /// <param name="currDisciplineId">当前被拖拽ID</param> /// <param name="currParentId">当前被拖拽ID的父ID</param> /// <returns></returns> public bool MoveDiscipline(SortDisciplineModel model, int System_Station_ID) { SqlMapper.BeginTransaction(); try { W_Discipline disModel = Orm.Single <W_Discipline>(x => x.ID == model.currDisciplineId && x.System_Station_ID == System_Station_ID); if (disModel == null) { throw new ApiException("请刷新重试,您要操作的数据不存在"); } if (model.currParentId == 0) { SqlMapper.Update("MoveIDDiscipline", new { new_prev_order = model.new_prev_order, currParentId = 0, currDisciplineId = model.currDisciplineId, System_Station_ID = System_Station_ID }); } else if (model.currParentId > 0) { SqlMapper.Update("MoveIDDiscipline", new { currParentId = model.currParentId, new_prev_order = model.new_prev_order, currDisciplineId = model.currDisciplineId, System_Station_ID = System_Station_ID }); } SqlMapper.CommitTransaction(); return(true); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw ex; } }
public void Insert_Transaction() { try { SqlMapper.BeginTransaction(); int i = 0; int insertNum = 10; long preId = 0; for (i = 0; i < insertNum; i++) { preId = SqlMapper.ExecuteScalar <long>(new RequestContext { Scope = "T_Test", SqlId = "Insert", Request = new T_Test { Name = $"Name-{preId}" } }); } SqlMapper.CommitTransaction(); Assert.Equal <int>(i, insertNum); } catch (Exception ex) { SqlMapper.RollbackTransaction(); throw ex; } }
public bool RemoveUsersFromRoles(string applicationName, string[] usernames, string[] rolenames) { bool b = false; try { SqlMapper.BeginTransaction(); foreach (string username in usernames) { foreach (string rolename in rolenames) { Sys_UserInRole ur = new Sys_UserInRole(); ur.ApplicationName = applicationName; ur.UserName = username; ur.RoleName = rolename; Delete(ur); } } SqlMapper.CommitTransaction(); b = true; } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw ex; } return(b); }
/// <summary> /// 删除网课及对应的关系表 /// </summary> /// <param name="Model"></param> /// <param name="System_Station_ID"></param> /// <returns></returns> public bool DeleteCourse(CampusModel Model, int System_Station_ID) { SqlMapper.BeginTransaction(); try { W_Course model = Orm.Single <W_Course>(x => x.System_Station_ID == System_Station_ID && x.ID == Model.ID); if (model == null) { throw new ApiException("您要操作的数据不存在"); } if (model.Valid == 1) { throw new ApiException("请先禁用,再执行删除操作"); } int count = SqlMapper.QueryForObject <int>("GetCourseOrderCount", new { ID = Model.ID }); if (count > 0) { throw new ApiException("该课程已被网校学生购买无法删除!"); } Orm.Delete <W_Course>(x => x.ID == Model.ID && x.System_Station_ID == System_Station_ID); Orm.Delete <W_Course_Chapters>(x => x.Course_ID == Model.ID && x.System_Station_ID == System_Station_ID); int isDelete = SqlMapper.QueryForObject <int>("DeleteCourse", new { ID = Model.ID, System_Station_ID = System_Station_ID }); Orm.Delete <W_Course_Unit>(x => x.Course_ID == Model.ID && x.System_Station_ID == System_Station_ID); Orm.Delete <W_DataInfo>(x => x.BusID == Model.ID && x.System_Station_ID == System_Station_ID); SqlMapper.CommitTransaction(); return(true); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw ex; } }
/// <summary> /// 修改组合题材料 /// </summary> /// <param name="id"></param> /// <param name="content"></param> /// <returns></returns> public bool UpdateQuestionData(int id, string content, int storeId) { W_QuestionData model = Orm.Single <W_QuestionData>(x => x.ID == id); if (model == null) { throw new ApiException("题目不存在或者已删除"); } model.Content = content; List <W_Question> list = Orm.Select <W_Question>(x => x.QuestionData_ID == id).ToList(); SqlMapper.BeginTransaction(); try { if (Orm.Update(model) > 0) { foreach (W_Question question in list) { if (question.QuestionStore_ID != storeId) { question.QuestionStore_ID = storeId; Orm.Update <W_Question>(question); } } } SqlMapper.CommitTransaction(); } catch { SqlMapper.RollBackTransaction(); return(false); } return(true); }
/// <summary> /// 给章节添加题目 /// </summary> /// <returns></returns> public bool AddChapterQuestions(ChapterPostModel model) { W_Chapter paper = Orm.Single <W_Chapter>(x => x.ID == model.ChapterID); if (paper == null) { throw new ApiException("章节不存在或者已删除"); } SqlMapper.BeginTransaction(); try { foreach (int questionId in model.questionIds) { Orm.Insert <W_Chapter_Question>(new W_Chapter_Question() { Chapter_ID = model.ChapterID, Question_ID = questionId }); } SqlMapper.CommitTransaction(); } catch { SqlMapper.RollBackTransaction(); return(false); } return(true); }
/// <summary> /// 自动组卷 /// </summary> /// <param name="examPaperId"></param> /// <returns></returns> public bool CombinationExamPaper(int examPaperId) { W_ExamPaper paper = Orm.Single <W_ExamPaper>(x => x.ID == examPaperId); if (paper == null) { throw new ApiException("试卷不存在或者已删除"); } //查出试卷所有题型 List <W_ExamPaperDetail> paperDetails = Orm.Select <W_ExamPaperDetail>(x => x.ExamPaper_ID == examPaperId).ToList(); if (paperDetails.Sum(x => x.Sorce * x.Num) < paper.PassScore) { throw new ApiException("试卷及格分数大于所有试卷题型总分,不符合大自然规律,请确保试卷题型总分大于及格分数再组卷"); } //根据试卷题库ID查出题目 List <W_Question> questions = Orm.Select <W_Question>(x => x.QuestionStore_ID == paper.QuestionStore_ID).ToList(); SqlMapper.BeginTransaction(); try { //删除原先已组好的试题 Orm.Delete <W_ExamPaperDetail_Detail>(x => x.ExamPaper_ID == paper.ID); //依据试卷题型的题目类型与题目数量组成试卷 foreach (W_ExamPaperDetail pd in paperDetails) { List <W_Question> tempList; if (pd.QuestionType_ID != 7) { tempList = questions.Where(x => x.QuestionType_ID == pd.QuestionType_ID && x.QuestionData_ID == 0).OrderBy(x => Guid.NewGuid()).Take(pd.Num).ToList(); } else//组合题 { tempList = questions.Where(x => x.QuestionData_ID > 0).OrderBy(x => x.QuestionData_ID).Take(pd.Num).ToList(); } if (tempList.Count < pd.Num) { throw new ApiException("组卷失败:" + pd.QuestionType_Name + "题目不够"); } foreach (W_Question question in tempList) { Orm.Insert <W_ExamPaperDetail_Detail>(new W_ExamPaperDetail_Detail() { ExamPaper_ID = paper.ID, ExamPaper_Detail_ID = pd.ID, Question_ID = question.ID }); } } SqlMapper.CommitTransaction(); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw new ApiException(ex.Message); } return(true); }
/// <summary> /// 创建目录并保存 hx add 20180105 /// </summary> /// <param name="Message">创建消息</param> /// <param name="PolyvInfo_ID">主表ID</param> /// <param name="cataid">分类cataid</param> /// <param name="Valid">禁用/启用</param> /// <param name="PolyvSource"></param> /// <param name="IsAdd">是添加还是修改</param> /// <returns></returns> public dynamic SavePolyvDetail(bool isAdd, int Station_ID, int PolyvInfo_ID, string cataid, int Valid, int PolyvSource) { bool save = false; W_PolyvInfo_Detail PolyvInfoModel_Detail = new W_PolyvInfo_Detail(); //明细表添加数据 PolyvInfoModel_Detail.System_Station_ID = Station_ID; PolyvInfoModel_Detail.PolyvInfo_ID = PolyvInfo_ID; PolyvInfoModel_Detail.cataid = cataid; PolyvInfoModel_Detail.Valid = Valid; try { SqlMapper.BeginTransaction();//开启事务 if (isAdd) { //添加明细数据 Orm.Insert(PolyvInfoModel_Detail); //修改配置表账号类型 if (SqlMapper.Update("UpdatePolyvinfoType", new { PolyvinfoType = PolyvSource, System_Station_ID = Station_ID }) > 0) { save = true; } } else { //修改明细数据 if (SqlMapper.Update("UpdatePolyvInfoDetail", new { PolyvInfo_ID = PolyvInfo_ID, cataid = cataid, Valid = Valid, System_Station_ID = Station_ID }) > 0) { if (SqlMapper.Update("UpdatePolyvinfoType", new { PolyvinfoType = PolyvSource, System_Station_ID = Station_ID }) > 0) { save = true; } } else { throw new ApiException("设置不成功,明细修改异常!"); } } SqlMapper.CommitTransaction(); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw new ApiException(ex.Message); } finally { //SqlMapper.CloseConnection(); } return(save); }
/// <summary> /// 禁用、启用学科 /// </summary> public bool SetDisciplineValid(W_Discipline model, int System_Station_ID) { SqlMapper.BeginTransaction(); try { if (model.ID < 0) { throw new ApiException("无效参数"); } W_Discipline modelDiscipline = Orm.Single <W_Discipline>(x => x.ID == model.ID && x.System_Station_ID == System_Station_ID); if (modelDiscipline == null) { throw new ApiException("操作失败,您无权操作!"); } if (model.Valid == 1) { if (modelDiscipline.CID != 0) { W_Discipline CidModel = Orm.Single <W_Discipline>(x => x.ID == modelDiscipline.CID && x.System_Station_ID == System_Station_ID); if (CidModel.Valid == 0) { throw new ApiException("请先启用上级学科!"); } } modelDiscipline.Valid = model.Valid; if (Orm.Update(modelDiscipline) <= 0) { throw new ApiException("修改失败"); } } else { List <W_Discipline> DisModel = SqlMapper.QueryForList <W_Discipline>("GetDisciplineValid", new { ID = modelDiscipline.ID, System_Station_ID = System_Station_ID }).ToList(); if (DisModel != null && DisModel.Count > 0) { DisModel.ForEach(X => { SqlMapper.Update("UpdateDisciplineValid", new { ID = X.ID, Valid = model.Valid, System_Station_ID = System_Station_ID }); GetDiscipline_ChildSetValid(X.ID, model.Valid, System_Station_ID); }); } modelDiscipline.Valid = model.Valid; if (Orm.Update(modelDiscipline) <= 0) { throw new ApiException("操作失败"); } } SqlMapper.CommitTransaction(); return(true); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw ex; } }
public async void QueryAsync() { int i = 0; SqlMapper.BeginTransaction(); for (i = 0; i < 10; i++) { var list = await SqlMapper.QueryAsync <T_Test>(new RequestContext { Scope = "T_Test", SqlId = "GetList", Request = new { Ids = new long[] { 1, 2, 3, 4 } } }); } SqlMapper.CommitTransaction(); Assert.True(i == 10); }
/// <summary> /// 给试卷题型添加题目 /// </summary> /// <returns></returns> public bool AddPaperDetailQuestions(ExamPaperPostModel model) { W_ExamPaper paper = Orm.Single <W_ExamPaper>(x => x.ID == model.examPaperId); if (paper == null) { throw new ApiException("试卷不存在或者已删除"); } W_ExamPaperDetail paperDetail = Orm.Single <W_ExamPaperDetail>(x => x.ID == model.examPaperDetailId); if (paperDetail == null) { throw new ApiException("试卷题型不存在或者已删除"); } if (paperDetail.ExamPaper_ID != paper.ID) { throw new ApiException("试卷题型与试卷不匹配"); } List <W_ExamPaperDetail_Detail> list = Orm.Select <W_ExamPaperDetail_Detail>(x => x.ExamPaper_ID == model.examPaperId && x.ExamPaper_Detail_ID == model.examPaperDetailId).ToList(); if (list.Count + model.questionIds.Length > paperDetail.Num) { throw new ApiException("选择的题目不能超过配置的数目"); } SqlMapper.BeginTransaction(); try { foreach (int questionId in model.questionIds) { Orm.Insert <W_ExamPaperDetail_Detail>(new W_ExamPaperDetail_Detail() { ExamPaper_ID = model.examPaperId, ExamPaper_Detail_ID = model.examPaperDetailId, Question_ID = questionId }); } SqlMapper.CommitTransaction(); } catch { SqlMapper.RollBackTransaction(); return(false); } return(true); }
/// <summary> /// 加入购物车 hx add 2018-01-13 /// </summary> /// <returns></returns> public dynamic PutInShoppingCar(string Course_IDs, string StuId) { bool put = false; if (string.IsNullOrEmpty(Course_IDs)) { throw new ApiException("参数Course_IDs为空!"); } if (string.IsNullOrEmpty(StuId)) { throw new ApiException("参数StuId为空!"); } string[] arr = Course_IDs.Split(','); if (arr.Length > 0) { try { SqlMapper.BeginTransaction();//开启事务 foreach (string id in arr) { if (id != null) { W_Shoppingcar model = new W_Shoppingcar(); model.Student_ID = StuId; model.Course_ID = int.Parse(id); Orm.Insert <W_Shoppingcar>(model); } } put = true; SqlMapper.CommitTransaction(); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw new ApiException(ex.Message); } finally { //SqlMapper.CloseConnection(); } } return(put); }
/// <summary> /// 删除章节题目 /// </summary> /// <param name="request"></param> /// <returns></returns> public bool DeleteChapterQuestion(ChapterPostModel request) { SqlMapper.BeginTransaction(); try { foreach (var item in request.questionIds) { Orm.Delete <W_Chapter_Question>(x => x.Question_ID == item && x.Chapter_ID == request.ChapterID); } SqlMapper.CommitTransaction(); } catch { SqlMapper.RollBackTransaction(); return(false); } return(true); }
/// <summary> /// 订单数据回滚 /// </summary> /// <param name="id"></param> /// <returns></returns> public bool DeleteOrder(int id, int System_Station_ID) { SqlMapper.BeginTransaction(); try { if (Orm.Delete <W_Order>(x => x.ID == id && x.System_Station_ID == System_Station_ID) > 0) { Orm.Delete <W_Order_Detail>(x => x.CID == id); } SqlMapper.CommitTransaction(); return(true); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw ex; } }
/// <summary> /// 批量删除订单课程 hx add 2018-01-12 /// </summary> public bool DeleteOrderCourseByCID(CampusModel model) { bool flag = false; if (string.IsNullOrEmpty(model.Ids)) { throw new ApiException("删除参数Ids,不能为空!"); } string[] ids = model.Ids.Split(','); if (ids.Length > 0) { try { SqlMapper.BeginTransaction();//开启事务 for (int i = 0; i < ids.Length; i++) { if (!string.IsNullOrEmpty(ids[i])) { SqlMapper.Update("DeleteOrderCourseByCID", new { CID = ids[i] }); SqlMapper.Update("UpdateW_OrderState", new { CID = ids[i] }); } } SqlMapper.CommitTransaction(); //提交 flag = true; } catch (Exception ex) { SqlMapper.RollBackTransaction(); //回滚 throw new ApiException(ex.Message); } finally { //SqlMapper.CloseConnection(); } } return(flag); }
/// <summary> /// Commit transaction. /// </summary> public bool CommitTransaction() { logger.Start("CommitTransaction"); logger.Info("CommitTransaction"); try { sqlMapEmployee.CommitTransaction(); logger.Info("CommitTransaction successful."); logger.End("CommitTransaction."); return(true); } catch (Exception ex) { logger.Error(ex.ToString()); error = ex; logger.Info("CommitTransaction fail."); logger.End("CommitTransaction."); return(false); } }
/// <summary> /// 删除订单课程 hx add 2018-01-12 /// </summary> /// <param name="id"></param> /// <returns></returns> public bool DeleteOrderCourse(CampusModel model) { bool flag = false; W_Order_Detail Ordermodel = Orm.Single <W_Order_Detail>(x => x.ID == model.orderDetailId); if (Ordermodel == null) { throw new ApiException("未找到该数据,请刷新后再试"); } try { SqlMapper.BeginTransaction();//开启事务 if (SqlMapper.Update("DeleteOrderCourse", new { orderDetailId = model.orderDetailId }) > 0) { int count = SqlMapper.QueryForObject <int>("OrderByDetailId_Count", new { CID = Ordermodel.CID }); if (count == 0) { SqlMapper.Update("UpdateW_OrderState", new { CID = Ordermodel.CID }); } else { SqlMapper.Update("UpdateW_OrderPrice", new { CID = Ordermodel.CID }); } flag = true; } SqlMapper.CommitTransaction(); //提交 } catch (Exception ex) { SqlMapper.RollBackTransaction(); //回滚 throw new ApiException(ex.Message); } finally { //SqlMapper.CloseConnection(); } return(flag); }
/// <summary> /// 章节选择题目 /// </summary> /// <param name="chapterId"></param> /// <param name="list"></param> /// <returns></returns> public bool CheckChapterQuestion(int chapterId, List <Question> list) { SqlMapper.BeginTransaction(); try { foreach (Question question in list) { Orm.Insert <W_Chapter_Question>(new W_Chapter_Question() { Chapter_ID = chapterId, Question_ID = question.ID }); } SqlMapper.CommitTransaction(); } catch { SqlMapper.RollBackTransaction(); return(false); } return(true); }
/// <summary> /// 删除网课章节及对应的关系表 /// </summary> /// <param name="Model"></param> /// <returns></returns> public dynamic DeleteCourseChapters(W_Course_Chapters Model, int System_Station_ID) { SqlMapper.BeginTransaction(); try { W_Course_Chapters model = Orm.Single <W_Course_Chapters>(x => x.ID == Model.ID && x.System_Station_ID == System_Station_ID); if (model == null) { throw new ApiException("未找到对应数据,请查看再试"); } Orm.Delete <W_Course_Chapters>(x => x.ID == Model.ID && x.System_Station_ID == System_Station_ID); Orm.Delete <W_DataInfo>(x => x.BusID == Model.ID && x.BusType == 1); SqlMapper.CommitTransaction(); return(true); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw ex; } }
/// <summary> /// 删除单元及对应的关系表 /// </summary> /// <param name="Model"></param> /// <returns></returns> public dynamic DeleteCourseUnit(W_Course_Unit Model, int System_Station_ID) { SqlMapper.BeginTransaction(); try { W_Course_Unit model = Orm.Single <W_Course_Unit>(x => x.ID == Model.ID && x.System_Station_ID == System_Station_ID); if (model == null) { throw new ApiException("找不到对应数据,请重试"); } Orm.Delete <W_Course_Unit_ClassTime>(x => x.Unit_ID == Model.ID); Orm.Delete <W_Course_Unit>(x => x.ID == Model.ID); Orm.Delete <W_DataInfo>(x => x.BusID == Model.ID && x.BusType == 2); SqlMapper.CommitTransaction(); return(true); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw ex; } }
/// <summary> /// 拖拽排序(拖拽完就保存) /// </summary> /// <param name="old_prev_order">拖拽前上节点排序值</param> /// <param name="old_next_order">拖拽前下节点排序值</param> /// <param name="new_prev_order">拖拽后上节点排序值</param> /// <param name="new_next_order">拖拽后下节点排序值</param> /// <param name="currDisciplineId">当前被拖拽ID</param> /// <param name="currParentId">当前被拖拽ID的父ID</param> /// <param name="System_Station_ID">系统ID</param> /// <returns></returns> public bool SortDiscipline(SortDisciplineModel model, int System_Station_ID) { SqlMapper.BeginTransaction(); try { W_Discipline Disciplinemodel = Orm.Single <W_Discipline>(x => x.ID == model.currDisciplineId && x.System_Station_ID == System_Station_ID); if (model.new_prev_order == model.old_prev_order && model.new_next_order == model.old_next_order) //没移动 { return(false); } if (model.new_prev_order == 0) //第一种情况:拖到最上面 (拖拽后上一层order为0) { Disciplinemodel.Orders = 1; if (Orm.Update(Disciplinemodel) > 0) { if (model.old_next_order == 0) //从最下面拖到最上面 { SqlMapper.Update("SortDiscipline", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, currDisciplineId = model.currDisciplineId }); } else { SqlMapper.Update("SortDiscipline1", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, old_next_order = model.old_next_order, currDisciplineId = model.currDisciplineId }); } } } else if (model.new_next_order == 0)//第二种情况:拖到最下面(拖拽后下一层order为0) { if (SqlMapper.Update("SortDiscipline2", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, currDisciplineId = model.currDisciplineId }) > 0) { if (model.old_prev_order == 0) //从最上面拖到最下面 { SqlMapper.Update("SortDiscipline3", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, currDisciplineId = model.currDisciplineId }); } else { SqlMapper.Update("SortDiscipline4", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, old_prev_order = model.old_prev_order, currDisciplineId = model.currDisciplineId }); } } } else if (model.new_prev_order > 0 && model.new_next_order > 0) //第三种情况:拖到中间 { if (model.new_prev_order < model.old_prev_order && model.new_next_order < model.old_next_order) //往上拖 { if (SqlMapper.Update("SortDiscipline5", new { new_next_order = model.new_next_order, currDisciplineId = model.currDisciplineId, System_Station_ID = System_Station_ID }) > 0) { if (model.old_next_order == 0) //从最下面往上拖 { SqlMapper.Update("SortDiscipline6", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, new_prev_order = model.new_prev_order, currDisciplineId = model.currDisciplineId }); } else { SqlMapper.Update("SortDiscipline7", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, old_next_order = model.old_next_order, new_prev_order = model.new_prev_order, currDisciplineId = model.currDisciplineId }); } } } else if (model.new_prev_order > model.old_prev_order && model.new_next_order > model.old_next_order)//往下拖 { if (SqlMapper.Update("SortDiscipline8", new { new_prev_order = model.new_prev_order, currDisciplineId = model.currDisciplineId, System_Station_ID = System_Station_ID }) > 0) { if (model.old_prev_order == 0) //从最上面往下拖 { SqlMapper.Update("SortDiscipline9", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, new_next_order = model.new_next_order, currDisciplineId = model.currDisciplineId }); } else { SqlMapper.Update("SortDiscipline10", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, new_next_order = model.new_next_order, old_prev_order = model.old_prev_order, currDisciplineId = model.currDisciplineId }); } } } } SqlMapper.CommitTransaction(); return(true); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw ex; } }
public void TestTransaction() { var sqlCmd = $"{ConvertSeparate(Insert, DatabaseType.PostgreSQL)}; update account set \"name\" = 'Eddie in transaction' where id in (SELECT currval(pg_get_serial_sequence('account','id')));"; var result = Brook.Load("posql").Transaction(sqlCmd, new List <DbParameter[]> { new[] { Brook.Load("posql").Parameter("@name", "QQ1"), Brook.Load("posql").Parameter("@email", $"*****@*****.**") }, new[] { Brook.Load("posql").Parameter("@name", "QQ2"), Brook.Load("posql").Parameter("@email", $"*****@*****.**") } }); if (!result.Ok) { TestContext.WriteLine($"Why:{result.Err}"); } result = Brook.Load("posql").Transaction("insert into account (name,email)values('Eddie', '@email');"); if (!result.Ok) { TestContext.WriteLine($"Why:{result.Err}"); } var qq = Brook.Load("posql").First <Account>("SELECT id AS \"Id\", name AS \"Name\", email AS \"Email\",time AS \"Time\" FROM account order by id desc limit 1;"); SqlMapper db = null; try { db = Brook.Load("posql"); var name = $"我是交易1-{DateTime.Now:HHmmss}"; db.BeginTransaction(); db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[] { new[] { db.Parameter("@name", name), db.Parameter("@email", $"{name}@sqlserver.com") } }); var t = db.Table(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) }); TestContext.WriteLine($"[Table] Id = {t.Rows[0]["Id"]} Name = {t.Rows[0]["Name"]} Email = {t.Rows[0]["Email"]} "); db.CommitTransaction(); name = $"我是交易2-{DateTime.Now:HHmmss}"; db.BeginTransaction(); db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[] { new[] { db.Parameter("@name", name), db.Parameter("@email", $"{name}@sqlserver.com") } }); var account = db.First <Account>(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) }); TestContext.WriteLine($"[First] Id = {account.Id} Name = {account.Name} Email = {account.Email}"); var accounts = db.Query <Account>(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) }); TestContext.WriteLine($"[Query] Id = {accounts[0].Id} Name = {accounts[0].Name} Email = {accounts[0].Email}"); db.CommitTransaction(); name = $"我是交易3-{DateTime.Now:HHmmss}"; db.BeginTransaction(); db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[] { new[] { db.Parameter("@name", name), db.Parameter("@email", $"{name}@sqlserver.com") } }); db.RollbackTransaction(); name = $"我不是交易1-{DateTime.Now:HHmmss}"; db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[] { new[] { db.Parameter("@name", name), db.Parameter("@email", $"{name}@sqlserver.com") } }); var ds = db.DataSet(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) }); TestContext.WriteLine($"[DataSet] Id = {ds.Tables[0].Rows[0]["Id"]} Name = {ds.Tables[0].Rows[0]["Name"]} Email = {ds.Tables[0].Rows[0]["Email"]}"); name = $"我是交易4-{DateTime.Now:HHmmss}"; db.BeginTransaction(); db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[] { new[] { db.Parameter("@name", name), db.Parameter("@email", $"{name}@sqlserver.com") } }); db.ChangeDatabase("postgres"); //throw new Exception("QQ"); } catch (Exception e) { TestContext.WriteLine(e); if (db != null) { db.RollbackTransaction(); db.Dispose(); } } }
/// <summary> /// 添加老师 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool TeacherInsert(TeacherModel model) { bool add = false; if (model.System_Station_ID == 0) { throw new ApiException("机构ID不存在!"); } if (string.IsNullOrEmpty(model.Name)) { throw new ApiException("姓名不能为空!"); } if (string.IsNullOrEmpty(model.CardNo)) { //throw new ApiException("身份证不能为空!"); } else { string CardNumRegexStr = "(^\\d{15}$)|(^\\d{18}$)|(^\\d{17}(\\d|X|x)$)"; if (!Regex.IsMatch(model.CardNo, CardNumRegexStr)) { throw new ApiException("身份证格式不正确"); } //查询老师身份证是否存在 TeacherModel teacherModel = SqlMapper.QueryForObject <TeacherModel>("GetTeacherByWhere", new { CardNo = model.CardNo, Phone = "", System_Station_ID = model.System_Station_ID, TeacherDetail_ID = 0 }); if (teacherModel != null) { throw new ApiException("身份证为" + model.CardNo + "的老师已存在,姓名为" + teacherModel.Name); } } //查询老师手机号码是否存在 TeacherModel PhoneModel = SqlMapper.QueryForObject <TeacherModel>("GetTeacherByWhere", new { CardNo = "", Phone = model.Phone, System_Station_ID = model.System_Station_ID, TeacherDetail_ID = 0 }); if (PhoneModel != null) { throw new ApiException("电话号码为" + model.Phone + "的老师已存在,姓名为" + PhoneModel.Name); } try { SqlMapper.BeginTransaction();//开启事务 //根据身份证查询老师主表 T_TeacherInfo teacher = null; if (!string.IsNullOrEmpty(model.CardNo)) { teacher = SqlMapper.QueryForObject <T_TeacherInfo>("GetTeacherByCardOrPhone", new { CardNo = model.CardNo, Phone = "" }); } //根据手机号码查询老师主表 T_TeacherInfo teacher2 = SqlMapper.QueryForObject <T_TeacherInfo>("GetTeacherByCardOrPhone", new { CardNo = "", Phone = model.Phone }); if (teacher == null && teacher2 == null) //主表没有,插入主表 { T_TeacherInfo teacher3 = new T_TeacherInfo(); teacher3.Name = model.Name; teacher3.Sex = model.Sex; teacher3.CardNo = model.CardNo; teacher3.JobTitle = model.JobTitle; teacher3.HeadImage = model.HeadImage; teacher3.Phone = model.Phone; teacher3.Email = model.Email; teacher3.Birthday = model.Birthday; teacher3.Address = model.Address; teacher3.Education_ID = model.Education_ID; teacher3.GraduateSchool = model.GraduateSchool; teacher3.Introduction = model.Introduction; teacher3.AddPerson = model.AddPerson; teacher3.AddTime = model.AddTime; int id = (int)Orm.Insert(teacher3, true); if (id > 0) { //添加主表成功,添加子表 model.ID = id; add = InsertTeacherDetail(model); } else { throw new ApiException("添加老师主表失败!"); } } else //主表有,插入子表 { if (teacher != null && teacher2 != null) { //如果是同一主表 if (teacher.ID == teacher2.ID) { model.ID = teacher.ID; } else { //不是同一主表,以身份证为主表 model.ID = teacher.ID; model.CardNo = ""; //不更新身份证 model.Phone = ""; //不更新号码 } } else if (teacher != null && teacher2 == null) { model.ID = teacher.ID; } else if (teacher == null && teacher2 != null) { model.ID = teacher2.ID; } int updateTeacher = SqlMapper.Update("TeacherUpdate", new { Name = model.Name, Sex = model.Sex, CardNo = model.CardNo, JobTitle = model.JobTitle, HeadImage = model.HeadImage, Phone = model.Phone, Email = model.Email, Birthday = model.Birthday, Address = model.Address, Education_ID = model.Education_ID, GraduateSchool = model.GraduateSchool, Introduction = model.Introduction, ID = model.ID, //System_Station_ID = model.System_Station_ID }); if (updateTeacher > 0) //修改主表 { //添加子表 add = InsertTeacherDetail(model); } } SqlMapper.CommitTransaction(); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw new ApiException(ex.Message); } finally { //SqlMapper.CloseConnection(); } return(add); }
/// <summary> /// 修改老师 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool TeacherUpdate(TeacherModel model) { if (model.System_Station_ID == 0) { throw new ApiException("机构ID不存在!"); } if (model.TeacherDetail_ID == 0) { throw new ApiException("TeacherDetail_ID不能为空!"); } if (string.IsNullOrEmpty(model.Name)) { throw new ApiException("姓名不能为空!"); } if (string.IsNullOrEmpty(model.CardNo)) { //throw new ApiException("身份证不能为空!"); } else { string CardNumRegexStr = "(^\\d{15}$)|(^\\d{18}$)|(^\\d{17}(\\d|X|x)$)"; if (!Regex.IsMatch(model.CardNo, CardNumRegexStr)) { throw new ApiException("身份证格式不正确!"); } } //根据老师详细ID查询老师 TeacherModel teacherModel = SqlMapper.QueryForObject <TeacherModel>("GetTeacherByWhere", new { CardNo = "", Phone = "", System_Station_ID = model.System_Station_ID, TeacherDetail_ID = model.TeacherDetail_ID }); if (teacherModel == null) { throw new ApiException("老师不存在,TeacherDetail_ID是否正确!"); } //如果身份证有变更 if ((!string.IsNullOrEmpty(model.CardNo)) && model.CardNo != teacherModel.CardNo) { //判断身份证是否已使用 T_TeacherInfo teacher = SqlMapper.QueryForObject <T_TeacherInfo>("GetTeacherByCardOrPhone", new { CardNo = model.CardNo, Phone = "" }); if (teacher != null) { throw new ApiException("身份证已存在,姓名为:" + teacher.Name); } } //如果手机号有变更 if (model.Phone != teacherModel.Phone) { //判断身份证是否已使用 T_TeacherInfo teacher = SqlMapper.QueryForObject <T_TeacherInfo>("GetTeacherByCardOrPhone", new { CardNo = "", Phone = model.Phone }); if (teacher != null) { throw new ApiException("手机号已存在,姓名为:" + teacher.Name); } } bool update = false; try { SqlMapper.BeginTransaction();//开启事务 int updateTeacher = SqlMapper.Update("TeacherUpdate", new { Name = model.Name, Sex = model.Sex, CardNo = model.CardNo, JobTitle = model.JobTitle, HeadImage = model.HeadImage, Phone = model.Phone, Email = model.Email, Birthday = model.Birthday, Address = model.Address, Education_ID = model.Education_ID, GraduateSchool = model.GraduateSchool, Introduction = model.Introduction, ID = model.ID, System_Station_ID = model.System_Station_ID }); if (updateTeacher > 0) { //删除老师学科关系 SqlMapper.Delete("DeleteTeacherDiscipline", new { TeacherDetail_ID = model.TeacherDetail_ID, System_Station_ID = model.System_Station_ID }); //添加老师学科关系 if (model.Teacher_DisciplineIds != null && model.Teacher_DisciplineIds.Length > 0) { string[] dics = model.Teacher_DisciplineIds.Split(','); if (dics.Length > 0) { T_TeacherInfo_Detail_Discipline teacherD_Dis = null; foreach (string disciplineId in dics) { if (string.IsNullOrEmpty(disciplineId)) { continue; } teacherD_Dis = new T_TeacherInfo_Detail_Discipline(); teacherD_Dis.TeacherDetail_ID = model.TeacherDetail_ID; teacherD_Dis.System_Station_ID = model.System_Station_ID; teacherD_Dis.Discipline_ID = int.Parse(disciplineId); teacherD_Dis.AddTime = model.AddTime; teacherD_Dis.AddPerson = model.AddPerson; //添加老师所教学科 Orm.Insert(teacherD_Dis); } } } update = true; } else { throw new ApiException("修改老师信息失败!"); } SqlMapper.CommitTransaction(); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw new ApiException(ex.Message); } finally { //SqlMapper.CloseConnection(); } return(update); }
/// <summary> /// 提交事务 /// </summary> public virtual void CommitTransaction() { SqlMapper.CommitTransaction(); }
/// <summary> /// 学生开课数据录入 /// </summary> /// <param name="request"></param> /// <returns></returns> public bool AddOrderStudent(OrderModel model, int System_Station_ID, string StuId) { bool flag = false; string stuID = string.IsNullOrEmpty(StuId) ? model.StuId : StuId; Random ran = new Random(); model.AddTime = DateTime.Now; model.PayTime = DateTime.Now; model.OrderNo = DateTime.Now.ToString("yyyyMMddHHmmss") + ran.Next(1000, 9999).ToString(); model.StuId = stuID; model.System_Station_ID = System_Station_ID; model.OrderId = Guid.NewGuid().ToString("N").ToUpper(); if (!string.IsNullOrWhiteSpace(model.Course_IDs)) { W_Order order = new W_Order(); order.StuId = model.StuId; order.OrderNo = model.OrderNo; order.System_Station_ID = model.System_Station_ID; order.AddTime = model.AddTime; order.Price = model.Price; order.PayStatus = model.PayStatus; order.PayType = model.PayType; order.PayTime = model.PayTime; order.PayPrice = model.PayPrice; order.TicketNumber = model.TicketNumber; order.OrderState = model.OrderState; order.OrderId = model.OrderId; try { SqlMapper.BeginTransaction();//开启事务 //添加订单 int id = (int)Orm.Insert(order, true); if (id > 0) { string[] c_id = model.Course_IDs.Split(','); string[] c_price = model.Prices.Split(','); for (int i = 0; i < c_id.Length; i++) { W_Order_Detail orderdetail = new W_Order_Detail(); orderdetail.CID = id; orderdetail.Course_ID = int.Parse(c_id[i]); orderdetail.Price = decimal.Parse(c_price[i]); //添加订单详细 Orm.Insert(orderdetail); } flag = true; } SqlMapper.CommitTransaction(); //提交 } catch (Exception ex) { SqlMapper.RollBackTransaction(); //回滚 throw new ApiException(ex.Message); } finally { //SqlMapper.CloseConnection(); } } return(flag); }
/// <summary> /// 添加录播的观看视频 /// </summary> /// <param name="Model"></param> /// <returns></returns> public dynamic SaveCourse_Look(W_Course_Look_DetailModel Model, string StuId, int System_Station_ID) { SqlMapper.BeginTransaction(); try { if (Model.CourseChapters_ID <= 0) { throw new ApiException("章节ID不能小于0"); } if (Model.LookType < 0) { throw new ApiException("观看类型LookType不能为空!"); } Model.EndTime = DateTime.Now; Model.AddTime = DateTime.Now; W_Course_Look_Detail OldModel = SqlMapper.QueryForObject <W_Course_Look_Detail>("GetCourse_Look_Detail", new { StuId = StuId, CourseChapters_ID = Model.CourseChapters_ID }); if (OldModel != null) { OldModel.EndTime = OldModel.EndTime.AddSeconds(double.Parse(Model.LookTime.ToString())); if (SqlMapper.Update("UpdateCourse_Look_Detail", new { ID = OldModel.ID, EndTime = OldModel.EndTime }) <= 0) { throw new ApiException("更新最后播放时间出错"); } CourseLookTimeModel LookTimeModel = SqlMapper.QueryForObject <CourseLookTimeModel>("GetCourseLookTimeModel", new { stuid = StuId, CourseChapters_ID = Model.CourseChapters_ID }); int TotalSeconds = 0; //已播放的秒数 int Duration = 0; //视频的时长 if (LookTimeModel != null) { TotalSeconds = LookTimeModel.TotalSeconds; Duration = LookTimeModel.Duration; } else { throw new ApiException("未找到用户的播放记录"); } //未获取到视频播放时长 if (Duration <= 0) { throw new ApiException("视频的时长为0"); } //默认观看6分钟添加记录 int LookDuration = 6; //查询配置信息 视频播放时长 W_Configuration ConfigurationModel = new HConfigurationMapper().GetWConfiguration(System_Station_ID); if (ConfigurationModel != null) { LookDuration = ConfigurationModel.LookDuration; } //判断学生观看视频记录时间是否大于设置的时长 或者 视频时长小于设置的时长的时候判断观看时长和视频时长相差不到10秒的时候执行 if (TotalSeconds >= LookDuration * 60 || (Duration < LookDuration * 60 && (Duration - TotalSeconds) <= 10)) { int counts = SqlMapper.QueryForObject <int>("GetW_Course_LookCounts", new { stuid = StuId, CourseChapters_ID = Model.CourseChapters_ID }); if (counts == 0) { W_Course_Look CourseLook = new W_Course_Look(); CourseLook.CourseChapters_ID = Model.CourseChapters_ID; CourseLook.IP = Model.IP; CourseLook.LookTime = DateTime.Now; CourseLook.LookType = Model.LookType; CourseLook.StuId = StuId; Orm.Insert <W_Course_Look>(CourseLook); } } } else { W_Course_Look_Detail model = new W_Course_Look_Detail(); model.AddTime = Model.AddTime; model.CourseChapters_ID = Model.CourseChapters_ID; model.EndTime = Model.EndTime; model.IP = Model.IP; model.LookType = Model.LookType; model.StuId = StuId; Orm.Insert(model); } SqlMapper.CommitTransaction(); return(true); } catch (Exception ex) { SqlMapper.RollBackTransaction(); throw ex; } }
/// <summary> /// 交卷 /// </summary> /// <param name="model"></param> /// <param name="stuId"></param> /// <returns></returns> public bool SubmitPractice(PracticeModel model, string stuId) { W_DoExamResult result = Orm.Select <W_DoExamResult>(x => x.ID == model.resultId).FirstOrDefault(); if (result == null) { throw new ApiException("对象错误"); } else if (string.IsNullOrEmpty(stuId) || result.StuId != stuId) { throw new ApiException("登陆超时,请重新登陆"); } result.EndTime = DateTime.Now; result.Valid = 1;//交卷 SqlMapper.BeginTransaction(); try { //获取试卷题型对应的分数 List <W_ExamPaperDetail> detailList = null; if (result.BusType == 0)//试卷模式 { //更新试卷测试次数 W_ExamPaper paper = Orm.Single <W_ExamPaper>(x => x.ID == result.BusID); paper.DoCount += 1; Orm.Update <W_ExamPaper>(paper); //试卷题型对应得分 detailList = Orm.Select <W_ExamPaperDetail>(x => x.ExamPaper_ID == result.BusID).ToList(); } else//更改章节练习状态 { W_ChapterPractice cp = Orm.Single <W_ChapterPractice>(x => x.ID == result.BusID); cp.Status = 1; Orm.Update <W_ChapterPractice>(cp); } //保存做题记录明细 foreach (Question question in model.list) { decimal sorce = 0; if (result.BusType == 0)//试卷模式 { W_ExamPaperDetail temp = detailList.FirstOrDefault(x => x.QuestionType_ID == question.QuestionType_ID); sorce = temp != null ? temp.Sorce : 0; } Orm.Insert <W_DoExamResult_Detail>(new W_DoExamResult_Detail() { DoExamResult_ID = result.ID, Question_ID = question.ID, StuID = result.StuId, MyAnswer = question.MyAnswer, Judge = question.Judge, Sorce = sorce, UpdateTime = result.EndTime }); //更新题目信息 SqlMapper.Update("UpdateQuestion", new { ID = question.ID, Judge = question.Judge, EasyWrongAnswer = question.MyAnswer }); } //修改做题记录主表 Orm.Update <W_DoExamResult>(result); SqlMapper.CommitTransaction(); } catch { SqlMapper.RollBackTransaction(); return(false); } return(true); }