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;
}
}
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="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;
}
}
private bool EnlistSessionIfHasTransactionActive(String key, SqlMapper sqlMap)
{
if (!_kernel.HasComponent(typeof(ITransactionManager)))
{
return(false);
}
bool enlisted = false;
if (key == null)
{
key = "iBATIS.DataMapper";
}
ITransactionManager manager = (ITransactionManager)_kernel[typeof(ITransactionManager)];
Transaction transaction = manager.CurrentTransaction;
if (transaction != null)
{
if (!transaction.Context.Contains(key))
{
transaction.Context[key] = true;
transaction.Enlist(new ResourceSqlMapAdapter(sqlMap.BeginTransaction(false)));
transaction.RegisterSynchronization(new SqlMapKeeper(sqlMap));
enlisted = true;
}
}
_kernel.ReleaseComponent(manager);
return(enlisted);
}
/// <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>
/// 禁用、启用学科
/// </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;
}
}
/// <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);
}
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>
/// 加入购物车 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>
/// <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>
/// 订单数据回滚
/// </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>
/// 删除章节题目
/// </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>
/// 批量删除订单课程 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>
/// Begin transaction.
/// </summary>
public bool BeginTransaction()
{
logger.Start("BeginConnection");
logger.Info("Begin transaction");
try
{
sqlMapEmployee.BeginTransaction();
logger.Info("BeginTransaction successful.");
logger.End("BeginTransaction.");
return(true);
}
catch (Exception ex)
{
error = ex;
logger.Error(ex.ToString());
logger.Info("BeginTransaction fail.");
logger.End("BeginTransaction.");
throw (new Exception("DisConnectDB:ERR-134", new Exception("DisConnectDB:ERR-134")));
}
}
public void StartJob(string jobId)
{
var job = SqlMapper.Entity <Models.ScheduleJobInfo>().Where(x => x.JobId == jobId).FirstOrDefault();
using (var trans = SqlMapper.BeginTransaction())
{
try
{
job.JobState = JobState.Normal;
trans.Entity <Models.ScheduleJobInfo>().Update(job);
ScheduleManager.AddJob(job);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
}
public ScheduleJobInfo UpdateJob(ScheduleJobInfo dto)
{
var model = dto;
using (var trans = SqlMapper.BeginTransaction())
{
try
{
trans.Entity <Models.ScheduleJobInfo>().Set(x => new { x.JobState }, false).Update(model);
ScheduleManager.UpdateJob(model);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return(GetJob(model.JobId));
}
/// <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;
}
}
public ScheduleJobInfo AddJob(ScheduleJobInfo dto)
{
var model = dto;
model.JobId = Guid.NewGuid().ToString("N").ToLower();
using (var trans = SqlMapper.BeginTransaction())
{
try
{
trans.Entity <Models.ScheduleJobInfo>().Insert(model);
ScheduleManager.AddJob(model);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return(GetJob(model.JobId));
}
/// <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;
}
}
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>
/// <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);
}