/// <summary> /// 给阅读消息历史并且没有答过题的人重新推送习题 /// </summary> /// <param name="businessPersonnelVguid"></param> /// <param name="wechatMainVguid"></param> /// <returns></returns> public bool ReWechatPushExercise(string businessPersonnelVguid, string wechatMainVguid) { Guid mainVguid = Guid.Parse(wechatMainVguid); Guid personnelVguid = Guid.Parse(businessPersonnelVguid); bool result = false; using (SqlSugarClient db = SugarDao_MsSql.GetInstance()) { try { db.BeginTran(); var wechatMainModel = db.Queryable <Business_WeChatPush_Information>().Where(i => i.VGUID == mainVguid).SingleOrDefault(); var currentUser = db.Queryable <Business_Personnel_Information>().Where(i => i.Vguid == personnelVguid).Select(i => i.UserID).SingleOrDefault(); DateTime?dateTime = null; var wechatMain = new Business_WeChatPush_Information() { PushType = 1, Title = wechatMainModel.Title, MessageType = 4, Timed = false, TimedSendTime = null, Important = wechatMainModel.PeriodOfValidity == null, PeriodOfValidity = wechatMainModel.PeriodOfValidity == null ? dateTime : DateTime.Now.AddMonths(1), Message = "从消息历史获取", PushPeople = wechatMainModel.PushPeople, Status = 3, CreatedDate = DateTime.Now, CreatedUser = wechatMainModel.CreatedUser, VGUID = Guid.NewGuid(), CoverImg = wechatMainModel.CoverImg, CoverDescption = wechatMainModel.CoverDescption, ExercisesVGUID = wechatMainModel.ExercisesVGUID, History = "0", Department_VGUID = Guid.Empty }; var wechatDetail = new Business_WeChatPushDetail_Information() { Type = "1", PushObject = currentUser, CreatedUser = wechatMainModel.CreatedUser, CreatedDate = DateTime.Now, Vguid = Guid.NewGuid(), Business_WeChatPushVguid = wechatMain.VGUID, ISRead = "0", }; string logdata = Extend.ModelToJson(wechatMain); _ll.SaveLog(10, 34, currentUser, "从消息历史获取习题", logdata); db.Insert(wechatDetail, false); db.Insert(wechatMain, false); db.CommitTran(); result = true; } catch (Exception ex) { db.RollbackTran(); LogHelper.WriteLog(ex.ToString()); } } return(result); }
/// <summary> /// 添加一条记录 /// </summary> /// <param name="bookid"></param> /// <param name="booktitle"></param> public static void AddBook(string bookid, string booktitle) { var ret = db.Insert(new t_book() { bookid = bookid, booktitle = booktitle }); }
protected void Page_Load(object sender, EventArgs e) { //设置序列 OracleConfig.SequenceMapping = new List <PubModel.SequenceModel>() { new PubModel.SequenceModel() { ColumnName = "ID", TableName = "STUDENT", Value = "SEQ_D" }, new PubModel.SequenceModel() { ColumnName = "ID", TableName = "SCHOOL", Value = "SEQ_D" } }; using (SqlSugarClient db = SugarDaoSerNum.GetInstance())//开启数据库连接 { var obj = Convert.ToInt32(db.Insert <STUDENT>(new STUDENT() { })); var name = db.Queryable <STUDENT>().Single(it => it.ID == obj).NAME; var obj2 = Convert.ToInt32(db.Insert <SCHOOL>(new SCHOOL() { })); var name2 = db.Queryable <SCHOOL>().Single(it => it.ID == obj2).NAME; } }
public Business_ScanData_Information Save(Guid vguid) { Business_ScanData_Information ScanData = new Business_ScanData_Information(); using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance()) { var list = _dbMsSql.Queryable <Business_Personnel_Information>().Where(i => i.Vguid == vguid).SingleOrDefault(); var Organization = _dbMsSql.Queryable <Master_Organization>().Where(i => i.Vguid == list.OwnedFleet).SingleOrDefault(); if (list != null) { ScanData.MachineCode = ""; ScanData.SystemID = ""; ScanData.ID = list.IDNumber; ScanData.Age = list.Age; ScanData.Sex = list.Sex; ScanData.JobNumber = list.JobNumber; ScanData.ServiceNumber = list.ServiceNumber; ScanData.OwnedFleet = Organization.OrganizationName.ToString(); ScanData.UserVguid = vguid; ScanData.Vguid = Guid.NewGuid(); ScanData.LicensePlate = list.LicensePlate; ScanData.Name = list.Name; ScanData.ScanUser = list.UserID; ScanData.PhoneNumber = list.PhoneNumber; ScanData.ScanDate = DateTime.Now; ScanData.CreatedDate = DateTime.Now; ScanData.CreatedUser = list.Name; bool result = false; result = _dbMsSql.Insert(ScanData, false) != DBNull.Value; } if (list != null) { var history = new Business_ScanHistory_Information() { MachineCode = "", SystemID = "", ScanUser = list.UserID, Data = "", User = "", ScanTime = DateTime.Now, CreatedUser = list.Name, CreatedDate = DateTime.Now, Vguid = Guid.NewGuid() }; _dbMsSql.Insert(history, false); } //存入操作日志表 string logData = JsonHelper.ModelToJson(ScanData); _ll.SaveLog(1, 2, list.Name, ScanData.Name, logData); return(ScanData); } }
public bool Create(DiscountCreateDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = true; try { db.BeginTran(); R_Discount model = new R_Discount() { R_Restaurant_Id = req.Restaurant, R_Area_Id = req.Area, R_Market_Id = req.Market, IsEnable = req.IsEnable, Name = req.Name, StartDate = req.StartDate, EndDate = req.EndDate, R_Company_Id = req.CompanyId }; var newId = db.Insert(model); if (req.CyxmZkCp != null) { var list = req.CyxmZkCp.Where(p => p.Id == 0).ToList(); list.ForEach(p => p.R_Discount_Id = Convert.ToInt32(newId)); //插入之前先判断是否存在,只插入不存在的 foreach (var item in list) { //查询是否存在 List <R_DiscountCategory> data = db.Queryable <R_DiscountCategory>() .Where(p => p.R_Discount_Id == Convert.ToInt32(newId)) .Where(p => p.R_Category_Id == item.R_Category_Id) .ToList(); //插入不存在的 if (data.Count <= 0) { db.Insert(item); } } //db.InsertRange<R_DiscountCategory>(list); } db.CommitTran(); } catch (Exception) { db.RollbackTran(); } return(result); } }
/// <summary> /// 保存 page Content /// </summary> /// <param name="sp"></param> /// <param name="userName"></param> public void SavePageContent(SysBest_PageContent sp, string userName) { var id = sp.Id; try { _db.BeginTran(); Expression <Func <SysBest_PageContent, bool> > whereExpress = it => it.AddressId == sp.AddressId && it.LanguageId == sp.LanguageId; var isAny = _db.Queryable <SysBest_PageContent>().Any(whereExpress); if (isAny) { var data = _db.Queryable <SysBest_PageContent>().Single(whereExpress); data.Html = sp.Html; data.layout = sp.layout; data.ModifiedBy = userName; data.ModifiedTime = DateTime.Now; _db.Update(data); id = data.Id; } else { sp.Sort = 0; sp.Founder = userName; sp.CreateTime = DateTime.Now; id = _db.Insert(sp).TryToInt(); } //表单中的所有控件html id List <string> formHtmlIdList = GetHtmlIdListByPageContent(sp); //数据库中存在的html var dbIdList = _db.Queryable <SysBest_Element>().Where(it => it.AddressId == sp.AddressId && it.LanguageId == sp.LanguageId).ToList().Select(it => it.Html_Id).ToList(); //在数据中,但是不在表单里面的需要删除 var delHtmlIdList = dbIdList.Where(it => formHtmlIdList.Any(htmlId => htmlId == it).IsFalse()).ToList();//不存在content里面的元素需要删除 foreach (var it in delHtmlIdList) { _db.Delete <SysBest_Element>(m => m.Html_Id == it); } //插入备份 InsertElementItemBak(sp.AddressId, sp.LanguageId, _db); //处理其它页面控件的HTMLID distinctHtmlIdList(sp, formHtmlIdList); _db.CommitTran(); } catch (Exception ex) { _db.RollbackTran(); throw ex; } }
/// <summary> /// 保存问卷信息(主信息、详细信息) /// </summary> /// <param name="questionMainModel"></param> /// <param name="isEdit"></param> /// <returns></returns> public bool SaveQuestionMain(Business_Questionnaire questionMainModel, bool isEdit, List <Business_QuestionnaireDetail> questionDetailList) { using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance()) { bool result = false; try { _dbMsSql.BeginTran(); if (isEdit) { //定义匿名类型model var model = new { QuestionnaireName = questionMainModel.QuestionnaireName, Description = questionMainModel.Description, Remarks = questionMainModel.Remarks, EffectiveDate = questionMainModel.EffectiveDate, Status = questionMainModel.Status, ChangeUser = questionMainModel.ChangeUser, ChangeDate = questionMainModel.ChangeDate }; result = _dbMsSql.Update <Business_Questionnaire>(model, i => i.Vguid == questionMainModel.Vguid); //存入操作日志表 string logData = JsonHelper.ModelToJson(questionMainModel); _ll.SaveLog(4, 54, CurrentUser.GetCurrentUser().LoginName, questionMainModel.QuestionnaireName + " " + "主信息", logData); } else { result = _dbMsSql.Insert(questionMainModel, false) != DBNull.Value; //存入操作日志表 string logData = JsonHelper.ModelToJson(questionMainModel); _ll.SaveLog(1, 53, CurrentUser.GetCurrentUser().LoginName, questionMainModel.QuestionnaireName + " " + "主信息", logData); } _dbMsSql.Delete <Business_QuestionnaireDetail>(i => i.QuestionnaireVguid == questionMainModel.Vguid); foreach (var item in questionDetailList) { _dbMsSql.Insert(item, false); //存入操作日志表 string logData = JsonHelper.ModelToJson(item); _ll.SaveLog(4, 55, CurrentUser.GetCurrentUser().LoginName, questionMainModel.QuestionnaireName + " " + "问卷详细信息", logData); } _dbMsSql.CommitTran(); } catch (Exception ex) { _dbMsSql.RollbackTran(); Common.LogHelper.LogHelper.WriteLog(ex.Message + "/n" + ex + "/n" + ex.StackTrace); } return(result); } }
//更新余额(返款),更新返款状态 private void UpdateBalance(decimal cost, int UserId, string F_Id) { OC_RechargeRecord RechargeModel = new OC_RechargeRecord(); using (SqlSugarClient db = new SqlSugarClient(connStr)) { RechargeModel.F_Id = Guid.NewGuid().ToString(); Models.OC_UserInfo usermodel = db.Queryable <Models.OC_UserInfo>().Single(t => t.F_UserId == UserId); usermodel.F_Balance = usermodel.F_Balance + cost; //返款 db.Update(usermodel); //更新 db.Update <Sev_FinalSendDetail>(new { F_IsCashBack = true }, it => it.F_Id == F_Id); //详单状态变成已返款 RechargeModel.F_Account = usermodel.F_Account; RechargeModel.F_PayTime = RechargeModel.F_CreatorTime = DateTime.Now; RechargeModel.F_CreatorUserId = "9f2ec079-7d0f-4fe2-90ab-8b09a8302aba";//admin RechargeModel.F_DeleteMark = false; RechargeModel.F_EnabledMark = true; RechargeModel.F_ManagerId = usermodel.F_ManagerId.ObjToInt(); RechargeModel.F_OperatorId = 0; //代表系统 RechargeModel.F_PayMode = 4; //支付方式,4,返款 RechargeModel.F_Price = RechargeModel.F_TrueCash = RechargeModel.F_ShowCash = cost; //价格,实际金额,显示金额 RechargeModel.F_RechargeOver = 1; RechargeModel.F_RechargeStar = 1; RechargeModel.F_ShowDescription = "自动返款"; RechargeModel.F_State = "已付款"; RechargeModel.F_TrueDescription = "自动返款"; RechargeModel.F_UserId = usermodel.F_UserId.ToString();; db.Insert(RechargeModel); } }
public bool Create(ExtendCreateDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = true; R_ProjectExtend model = new R_ProjectExtend() { Name = req.Name, Description = req.Description, CyxmKzType = req.CyxmKzType, Price = req.Price, Unit = req.Unit, R_ProjectExtendType_Id = req.ExtendType, R_Company_Id = req.R_Company_Id }; if (db.Insert(model) == null) { result = false; } return(result); } }
public Business_RideCheckFeedback AddBusiness_RideCheckFeedback(string user) { Business_RideCheckFeedback rideCheckFeedback = new Business_RideCheckFeedback(); rideCheckFeedback.FeedbackCode = DateTime.Now.ToString("yyyyMMddHHmmss"); rideCheckFeedback.ChangeUser = rideCheckFeedback.CreateUser = user; rideCheckFeedback.ChangeDate = rideCheckFeedback.CreateDate = DateTime.Now; rideCheckFeedback.VGUID = Guid.NewGuid(); rideCheckFeedback.Status = "1"; using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance()) { _dbMsSql.DisableInsertColumns = new string[] { "RideCheckFeedback_Items", "RideCheckFeedback_Attachments" }; _dbMsSql.Insert(rideCheckFeedback); SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 1, DateTime.Now.ToString("yyyy-MM-dd"), DateTime.Now.ToString("HH:mm"), "", "", "", "", ""); SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 2, "", "", "", "", "", "", ""); SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 3, "", "", "", "", "", "", ""); SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 4, "", "", "", "", "", "", ""); SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 5, "", "", "", "", "", "", ""); SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 6, "", "", "", "", "", "", ""); SaveBusiness_RideCheckFeedbackItem(user, rideCheckFeedback.VGUID, 7, "", "", "", "", "", "", ""); rideCheckFeedback.RideCheckFeedback_Items = _dbMsSql.Queryable <Business_RideCheckFeedback_Item>().Where(c => c.RideCheckFeedbackVGUID == rideCheckFeedback.VGUID).OrderBy(c => c.FeedbackNumber).ToList(); } return(rideCheckFeedback); }
protected void Page_Load(object sender, EventArgs e) { //设置序列,程序起动时设置一次便可 OracleConfig.SequenceMapping = new List <PubModel.SequenceModel>() { new PubModel.SequenceModel() { ColumnName = "ID", TableName = "STUDENT", Value = "SEQ_D" } }; using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接 { STUDENT s = new STUDENT() { NAME = "张" + new Random().Next(1, int.MaxValue) }; db.Insert(s); //插入一条记录 (有主键也好,没主键也好,有自增列也好都可以插进去) List <STUDENT> list = new List <STUDENT>() { new STUDENT() { NAME = "张" + new Random().Next(1, int.MaxValue) }, new STUDENT() { NAME = "张" + new Random().Next(1, int.MaxValue) } }; db.InsertRange(list); //批量插入 } }
/// <summary> /// 插入数据 /// </summary> /// <param name="entity">实体对象</param> /// <param name="isIdentity">是否包含主键</param> /// <returns></returns> public object Insert(T entity, bool isIdentity = true) { using (SqlSugarClient dbClient = SqlSugarInstance.GetInstance()) { return(dbClient.Insert <T>(entity, isIdentity)); } }
protected void Page_Load(object sender, EventArgs e) { string connStr = "server=.;uid=sa;password=sasa;database=nfd"; using (SqlSugarClient db = new SqlSugarClient(connStr)) { db.BeginTran(); db.Sqlable.IsNoLock = true; try { var sql = db.Sqlable.MappingTable<CutBill, CutBillShipment>("t1.c_id=t2.c_id").SelectToSql("t1.*"); var dt = db.GetDataTable(sql); var id = db.Insert(new test() { name = "哈哈" + DateTime.Now }); var del = db.Delete<test>(21); var update = db.Update<test>(new { name = "5555" + DateTime.Now }, new { id=1 }); } catch (Exception) { db.RollbackTran(); } } ; //var xx = SqlTool.CreateMappingTable(20); Console.Read(); }
public bool Add(Sys_Role obj, List <Sys_RoleAuthorize> list, bool isadd) { using (SqlSugarClient db = SqlSugarDao.GetInstance())//开启数据库连接 { bool i = false; db.CommandTimeOut = 30000;//设置超时时间 try { db.BeginTran();//开启事务 if (!isadd) { db.Delete <Sys_RoleAuthorize>("F_ObjectId=@F_id", new { F_id = obj.F_Id }); string[] notstr = { "F_CreatorUserId", "F_CreatorTime", "F_Category" }; db.AddDisableUpdateColumns(notstr);//添加禁止更新列 db.Update(obj); } else { db.Insert(obj); } db.InsertRange(list); i = true; db.CommitTran();//提交事务 } catch (Exception ex) { db.RollbackTran();//回滚事务 i = false; } return(i); } }
protected void Page_Load(object sender, EventArgs e) { using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接 { Student s = new Student() { name = "张" + new Random().Next(1, int.MaxValue) }; db.Insert(s); //插入一条记录 (有主键也好,没主键也好,有自增列也好都可以插进去) List <Student> list = new List <Student>() { new Student() { name = "张" + new Random().Next(1, int.MaxValue) }, new Student() { name = "张" + new Random().Next(1, int.MaxValue) } }; db.InsertRange(list); //批量插入 } }
/// <summary> /// 保存部门信息 /// </summary> /// <param name="organizationModel"></param> /// <param name="isEdit"></param> /// <returns></returns> public bool Save(Master_Organization organizationModel, bool isEdit) { using (SqlSugarClient dbMsSql = SugarDao.SugarDao_MsSql.GetInstance()) { bool result = false; if (isEdit)//编辑 { var model = new { OrganizationName = organizationModel.OrganizationName, //OrganizationCode = organizationModel.OrganizationCode, Description = organizationModel.Description, ChangeDate = organizationModel.ChangeDate, ChangeUser = organizationModel.ChangeUser }; result = dbMsSql.Update <Master_Organization>(model, i => i.Vguid == organizationModel.Vguid); //存入操作日志表 string logData = JsonHelper.ModelToJson(organizationModel); _ll.SaveLog(4, 3, CurrentUser.GetCurrentUser().LoginName, organizationModel.OrganizationName, logData); } else//新增 { result = dbMsSql.Insert(organizationModel, false) != DBNull.Value; //存入操作日志表 string logData = JsonHelper.ModelToJson(organizationModel); _ll.SaveLog(1, 2, CurrentUser.GetCurrentUser().LoginName, organizationModel.OrganizationName, logData); } return(result); } }
protected void Page_Load(object sender, EventArgs e) { string connStr = "server=.;uid=sa;password=sasa;database=nfd"; using (SqlSugarClient db = new SqlSugarClient(connStr)) { db.BeginTran(); db.Sqlable.IsNoLock = true; try { var sql = db.Sqlable.MappingTable <CutBill, CutBillShipment>("t1.c_id=t2.c_id").SelectToSql("t1.*"); var dt = db.GetDataTable(sql); var id = db.Insert(new test() { name = "哈哈" + DateTime.Now }); var del = db.Delete <test>(21); var update = db.Update <test>(new { name = "5555" + DateTime.Now }, new { id = 1 }); } catch (Exception) { db.RollbackTran(); } } ; //var xx = SqlTool.CreateMappingTable(20); Console.Read(); }
public void AddHomecomingSurvey(Business_HomecomingSurvey hs) { using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance()) { _dbMsSql.Insert(hs); } }
public User Add(User user) { int id = Convert.ToInt32(_sqlClient.Insert(user)); user.ID = id; return(user); }
public void Init() { var mstStr = "server=.;uid=sa;pwd=sasa;database=SqlSugarTest"; var conList = new List <string>() { "server=localhost;uid=sa;pwd=sasa;database=SqlSugarTest" }; SqlSugarClient db = new SqlSugarClient(mstStr, conList); var l1 = db.Queryable <Student>().ToList(); //走从 var d1 = db.Queryable <Student>().ToDataTable(); //走从 var t3 = db.Queryable <Student>().ToList(); //走从 db.Insert <Student>(new Student() { name = "主" }); db.BeginTran(); var l2 = db.Queryable <Student>().ToList();//走主 db.CommitTran(); var l3 = db.Queryable <Student>().ToList();//走从 db.Dispose(); }
public void Init() { using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接 { Student s = new Student() { name = "张" + new Random().Next(1, int.MaxValue) }; db.Insert(s); //插入一条记录 (有主键也好,没主键也好,有自增列也好都可以插进去) List <Student> list = new List <Student>() { new Student() { name = "张" + new Random().Next(1, int.MaxValue) }, new Student() { name = "张" + new Random().Next(1, int.MaxValue) } }; db.InsertRange(list); //批量插入 } }
public bool Create(TableCreateDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = true; try { db.BeginTran(); R_Table model = new R_Table() { Name = req.Name, Describe = req.Description, R_Restaurant_Id = req.RestaurantId, ServerRate = req.ServerRate, R_Area_Id = req.RestaurantArea, CythStatus = CythStatus.空置, SeatNum = req.SeatNum, IsVirtual = req.IsVirtual, Sorted = req.Sorted }; var insert = db.Insert(model); if (req.Box > 0) { db.Insert(new R_BoxTable() { R_Table_Id = Convert.ToInt32(insert), R_Box_Id = req.Box }); } db.CommitTran(); } catch (Exception) { result = false; db.RollbackTran(); throw; } return(result); } }
public void AddTest(PerformanceTest pt, SqlSugarClient db) { //sqlSuagr pt.Execute(i => { db.Insert <Models.InsertTest>(GetData()); }, m => { }, "添加记录"); }
public void Init() { Console.WriteLine("启动SerialNumber.Init"); using (SqlSugarClient db = SugarFactory.GetInstance())//开启数据库连接 { var dientityValue = db.Insert <StudentEntity>(new StudentEntity() { }); var name = db.Queryable <StudentEntity>().Single(it => it.id == dientityValue.ObjToInt()).name; Console.WriteLine(name); var dientityValue2 = db.Insert <School>(new School() { }); var name2 = db.Queryable <School>().Single(it => it.id == dientityValue2.ObjToInt()).name; Console.WriteLine(name2);; } }
protected void Page_Load(object sender, EventArgs e) { using (SqlSugarClient db = SugarDaoSerNum.GetInstance()) //开启数据库连接 { var obj = Convert.ToInt32(db.Insert <Student>(new Student() { })); var name = db.Queryable <Student>().Single(it => it.id == obj).name; var obj2 = Convert.ToInt32(db.Insert <School>(new School() { })); var name2 = db.Queryable <School>().Single(it => it.id == obj2).name; } }
public void AddTest(PerformanceTest pt, SqlSugarClient db) { //sqlSuagr pt.Execute(i => { db.Insert<Models.InsertTest>(GetData()); }, m => { }, "添加记录"); }
public int Add(Job_Config record) { object newRec = _sqlClient.Insert(record); if (newRec != null) { return(Convert.ToInt32(((Job_Config)newRec).ID)); } return(-1); }
public bool UpdateWeixinPrint(WeixinPrintDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = true; try { db.BeginTran(); R_WeixinPrint model = Mapper.Map <WeixinPrintDTO, R_WeixinPrint>(req); if (model.Id <= 0) { var insertId = Convert.ToInt32(db.Insert <R_WeixinPrint>(model)); if (req.PrintAreas != null && req.PrintAreas.Any()) { List <R_WeixinPrintArea> list = new List <R_WeixinPrintArea>(); foreach (var item in req.PrintAreas) { list.Add(new R_WeixinPrintArea() { R_Area_Id = item.R_Area_Id, R_WeixinPrint_Id = insertId }); } db.InsertRange <R_WeixinPrintArea>(list); } } else { db.Update <R_WeixinPrint>(model); db.Delete <R_WeixinPrintArea>(p => p.R_WeixinPrint_Id == model.Id); if (req.PrintAreas != null && req.PrintAreas.Any()) { List <R_WeixinPrintArea> list = new List <R_WeixinPrintArea>(); foreach (var item in req.PrintAreas) { list.Add(new R_WeixinPrintArea() { R_Area_Id = item.R_Area_Id, R_WeixinPrint_Id = model.Id }); } db.InsertRange <R_WeixinPrintArea>(list); } } db.CommitTran(); } catch (Exception ex) { result = false; db.RollbackTran(); throw ex; } return(result); } }
public JsonResult AddTreeMenuDetail(SysBest_MenuAddress entity) { entity.CreateTime = DateTime.Now; entity.Founder = "聪哥"; var model = new ActionResultModel <string>(); _db.Insert(entity); model.isSuccess = true; model.responseInfo = model.isSuccess.IIF("新增菜单详情成功!", "新增菜单详情失败!"); return(Json(model)); }
[ValidateInput(false)]//敏感值验证 public JsonResult SaveCleaningCompany(Business_CleaningCompany cleaning, bool isEdit) { var model = new ActionResultModel <string>(); model.isSuccess = false; using (SqlSugarClient _db = SugarDao_MsSql.GetInstance()) { bool isExistCompanyName = _db.Queryable <Business_CleaningCompany>().Any(x => x.CompanyName == cleaning.CompanyName); if (!isEdit && isExistCompanyName) { model.isSuccess = false; model.respnseInfo = "2"; } else { //保存时生成最新的二维码 if (cleaning.Vguid == Guid.Empty || cleaning.Vguid == null) { cleaning.Vguid = Guid.NewGuid(); } cleaning.QRCode = getWeChatQRCode(cleaning); if (isEdit) { cleaning.ChangeDate = DateTime.Now; cleaning.ChangeUser = CurrentUser.GetCurrentUser().LoginName; var data = new { CompanyName = cleaning.CompanyName, Address = cleaning.Address, Location = cleaning.Location, TXLocation = cleaning.TXLocation, ContactNumber = cleaning.ContactNumber, ContactPerson = cleaning.ContactPerson, QRCode = cleaning.QRCode, Radius = cleaning.Radius, ChangeUser = cleaning.ChangeUser, ChangeDate = cleaning.ChangeDate }; model.isSuccess = _db.Update <Business_CleaningCompany>(data, i => i.Vguid == cleaning.Vguid); } else { cleaning.CreatedUser = CurrentUser.GetCurrentUser().LoginName; cleaning.CreatedDate = DateTime.Now; cleaning.ChangeDate = DateTime.Now; cleaning.ChangeUser = CurrentUser.GetCurrentUser().LoginName; model.isSuccess = _db.Insert(cleaning, false) != DBNull.Value; } model.isSuccess = true; model.respnseInfo = model.isSuccess == true ? cleaning.QRCode + "," + cleaning.Vguid : "0"; } } return(Json(model, JsonRequestBehavior.AllowGet)); }
private static void SqlSugar(int eachCount) { GC.Collect(); //回收资源 System.Threading.Thread.Sleep(2000); //休息2秒 PerHelper.Execute(eachCount, "SqlSugar", () => { using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString)) { var list = conn.Insert(GetItem); } }); }
public bool Save(list list) { var isAdd = list.id == 0; if (isAdd) { return(db.Insert(list) != DBNull.Value); } else { return(db.Update <list>(list, it => it.id == list.id)); } }
public void Init() { SqlSugarClient db = new SqlSugarClient("server=.;uid=sa;pwd=sasa;database=SqlSugarTest", "server=localhost;uid=sa;pwd=sasa;database=SqlSugarTest"); var l1= db.Queryable<Student>().ToList();//走从 var d1= db.Queryable<Student>().ToDataTable();//走从 var t3 = db.Queryable<Student>().ToList();//走从 db.Insert<Student>(new Student() { name="主" }); db.BeginTran(); var l2 = db.Queryable<Student>().ToList();//走主 db.CommitTran(); var l3 = db.Queryable<Student>().ToList();//走从 db.Dispose(); }
private static void SqlSugar(int eachCount) { GC.Collect();//回收资源 System.Threading.Thread.Sleep(2000);//休息2秒 PerHelper.Execute(eachCount, "SqlSugar", () => { using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString)) { var list = conn.Insert(GetItem); } }); }
protected void Page_Load(object sender, EventArgs e) { //连接字符串 string connStr = @"Server=.;uid=sa;pwd=sasa;database=SqlSugarTest"; using (SqlSugarClient db = new SqlSugarClient(connStr))//开启数据库连接 { //开启事务,可以不使用事务,也可以使用多个事务 db.BeginTran(); //db.CommitTran 提交事务会,在using结束前自动执行,可以不声名 //db.RollbackTran(); 事务回滚,catch中声名 //查询是允许脏读的,可以声名多个(默认值:不允许) db.IsNoLock = true; try { /************************************************************************************************************/ /*********************************************1、实体生成****************************************************/ /************************************************************************************************************/ //根据当前数据库生成所有表的实体类文件 (参数:SqlSugarClient ,文件目录,命名空间) //db.ClassGenerating.CreateClassFiles(db,Server.MapPath("~/Models"),"Models"); //根据表名生成实体类文件 //db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school"); //根据表名生成class字符串 var str = db.ClassGenerating.TableNameToClass(db, "Student"); //根据SQL语句生成class字符串 var str2 = db.ClassGenerating.SqlToClass(db, "select top 1 * from Student", "student"); /************************************************************************************************************/ /*********************************************2、查询********************************************************/ /************************************************************************************************************/ //---------Queryable<T>,扩展函数查询---------// //针对单表或者视图查询 //查询所有 var student = db.Queryable<Student>().ToList(); var stud = new Student() { id = 1 }; //查询单条 var single = db.Queryable<Student>().Single(c => c.id==stud.id); //取10-20条 var page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList(); //上一句的简化写法,同样取10-20条 var page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10); //查询条数 var count = db.Queryable<Student>().Where(c => c.id > 10).Count(); //从第2条开始以后取所有 var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList(); //取前2条 var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList(); // Not like string conval = "a"; var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList(); // 可以在拉姆达使用 ToString和 Convert,比EF出色的地方 var convert1 = db.Queryable<Student>().Where(c => c.name == "a".ToString()).ToList(); var convert2 = db.Queryable<Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();// var convert3 = db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList(); var convert4 = db.Queryable<Student>().Where(c => DateTime.Now > DateTime.Now).ToList(); //支持字符串Where 让你解决,更复杂的查询 var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100").ToList(); //存在记录反回true,则否返回false bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100); bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1); //---------Sqlable,创建多表查询---------// //多表查询 List<School> dataList = db.Sqlable() .Form("school", "s") .Join("student", "st", "st.id", "s.id", JoinType.INNER) .Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<@id").SelectToList<School>("st.*", new { id = 1 }); //多表分页 List<School> dataPageList = db.Sqlable() .Form("school", "s") .Join("student", "st", "st.id", "s.id", JoinType.INNER) .Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<100").SelectToPageList<School>("st.*", "s.id", 1, 10); //---------SqlQuery,根据SQL或者存储过程---------// //用于多用复杂语句查询 var School = db.SqlQuery<Student>("select * from Student"); //获取id var id = db.SqlQuery<int>("select top 1 id from Student").Single(); //存储过程 //var spResult = db.SqlQuery<school>("exec sp_school @p1,@p2", new { p1=1,p2=2 }); /************************************************************************************************************/ /*************************************************3、添加****************************************************/ /************************************************************************************************************/ School s = new School() { name = "蓝翔" }; //插入单条 var id2 = Convert.ToInt32(db.Insert(s)); //插入多条 List<School> sList = new List<School>(); sList.Add(s); var ids = db.InsertRange(sList); /************************************************************************************************************/ /*************************************************4、修改****************************************************/ /************************************************************************************************************/ //指定列更新 db.Update<School>(new { name = "蓝翔2" }, it => it.id == id); //整个实体更新,注意主键必需为实体类的第一个属性 db.Update<School>(new School { id = id, name = "蓝翔2" }, it => it.id == id); /************************************************************************************************************/ /*************************************************5、删除****************************************************/ /************************************************************************************************************/ db.Delete<School>(10);//注意主键必需为实体类的第一个属性 db.Delete<School>(it => it.id > 100); db.Delete<School>(new string[] { "100", "101", "102" }); //db.FalseDelete<school>("is_del", 100); //等同于 update school set is_del=0 where id in(100) //db.FalseDelete<school>("is_del", it=>it.id==100); /************************************************************************************************************/ /*************************************************6、基类****************************************************/ /************************************************************************************************************/ string sql = "select * from Student"; db.ExecuteCommand(sql); db.GetDataTable(sql); db.GetList<Student>(sql); db.GetSingle<Student>(sql + " where id=1"); using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader db.GetScalar(sql); db.GetString(sql); db.GetInt(sql); } catch (Exception ex) { //回滚事务 db.RollbackTran(); throw ex; } }//关闭数据库连接 }
protected void Page_Load(object sender, EventArgs e) { PerformanceTest pt = new PerformanceTest(); pt.SetCount(10000);//设置循环次数 using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { var sqlconn = db.GetConnection(); TuncateTable(db);//清空 //sqlSuagr pt.Execute(i => { db.Insert<Models.InsertTest>(GetData()); }, m => { }, "sqlSuagr"); TuncateTable(db); //ef4.0+Sql05 using (TestLib.SqlSugarTestEntities ef = new TestLib.SqlSugarTestEntities()) { pt.Execute(i => { ef.InsertTests.AddObject(GetEFData()); ef.SaveChanges(); }, m => { }, "ef4.0+Sql05"); } TuncateTable(db); //dapper pt.Execute(i => { sqlconn.Execute("insert into InsertTest (v1,v2,v3,int1,d1,txt ) values(@v1,@v2,@v3,@int1,@d1,@txt);select @@identity;", GetData()); }, m => { }, "dapper"); TuncateTable(db); //ado.net pt.Execute(i => { db.GetScalar("insert into InsertTest (v1,v2,v3,int1,d1,txt ) values(@v1,@v2,@v3,@int1,@d1,@txt);select @@identity;", new SqlParameter[]{ new SqlParameter("@d1", DateTime.Now), new SqlParameter("@int1",11), new SqlParameter("@txt","哈另一个txt"), new SqlParameter("@v1","v1sdfasdas"), new SqlParameter("@v2","v2sdfasfas"), new SqlParameter("@v3","v3adfasdf"), new SqlParameter("@id",1) }); }, m => { }, "ado.net"); } //输出测试页面 GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }