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 Init() { Console.WriteLine("启动EnumDemo.Init"); using (SqlSugarClient db = SugarDao.GetInstance()) { var stuList = db.Queryable <Student>().ToList(); db.Insert <Student>(new Student() { sch_id = SchoolEnum.北大青鸟 }); db.Update <Student>(new Student() { sch_id = SchoolEnum.it清华, id = 11 }); var stuList2 = db.Queryable <Student>().Where(it => it.sch_id == SchoolEnum.全智).ToList(); } }
/// <summary> /// 更新是否查看推送 /// </summary> /// <param name="pushVguid"></param> /// <param name="userID"></param> /// <returns></returns> public bool UpdateIsRead(string pushVguid, string userID) { using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance()) { bool isRead = false; try { Guid pushVGUID = Guid.Parse(pushVguid); isRead = dbMsSql.Update <Business_WeChatPushDetail_Information>(new { ISRead = "1" }, i => i.Business_WeChatPushVguid == pushVGUID && i.PushObject == userID); } catch (Exception exp) { LogHelper.WriteLog("更新是否查看推送状态:" + pushVguid + "/" + exp.ToString()); } return(isRead); } }
/// <summary> /// 修改密码 /// </summary> /// <param name="vguid"></param> /// <param name="newPwd"></param> /// <param name="oldPwd"></param> /// <param name="name"></param> /// <param name="msg"></param> /// <returns></returns> public bool ChangePassWord(Guid vguid, string newPwd, string oldPwd, string name, out string msg) { msg = ""; bool result = false; using (SqlSugarClient sqlSugar = SugarDao_MsSql.GetInstance()) { var list = sqlSugar.Queryable <Sys_User>().Where(i => i.Vguid == vguid).ToList(); if (list[0].Password != oldPwd) { msg = "旧密码不正确!"; return(false); } result = sqlSugar.Update <Sys_User>(new { Password = newPwd, ChangeDate = DateTime.Now, ChangeUser = name }, i => i.Vguid == vguid); _logLogic.SaveLog(4, 0, Common.CurrentUser.GetCurrentUser().LoginName, "更新密码", oldPwd + "->" + newPwd); return(result); } }
public bool Update(BoxCreateDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = true; R_Box model = new R_Box() { Name = req.Name, Description = req.Description, Id = req.Id, R_Restaurant_Id = req.Restaurant, R_Area_Id = req.RestaurantArea }; result = db.Update(model); return(result); } }
public bool Update(MarketCreateDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = true; R_Market model = new R_Market() { Name = req.Name, Description = req.Description, Id = req.Id, R_Restaurant_Id = req.Restaurant, StartTime = req.StartTime, EndTime = req.EndTime }; result = db.Update(model); return(result); } }
public bool Update(ExtendCreateDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = true; R_ProjectExtend model = new R_ProjectExtend() { Name = req.Name, Description = req.Description, Id = req.Id, CyxmKzType = req.CyxmKzType, Price = req.Price, Unit = req.Unit, R_ProjectExtendType_Id = req.ExtendType }; result = db.Update(model); return(result); } }
public bool Update(PrinterDTO dto) { using (var db = new SqlSugarClient(Connection)) { bool result = false; result = db.Update <Printer>( new { Name = dto.Name, Code = dto.Code, Remark = dto.Remark, PcName = dto.PcName, PrintPort = dto.PrintPort, IpAddress = dto.IpAddress, IsDelete = dto.IsDelete }, x => x.Id == dto.Id); return(result); } }
/// <summary> /// 上传用户头像 /// </summary> /// <param name="url"></param> /// <param name="exerciseVguid"></param> /// <param name="personVguid"></param> /// <returns></returns> public bool UpLoadImg(string url, string exerciseVguid, string personVguid) { using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance()) { bool result = false; try { Guid mainExerciseVguid = Guid.Parse(exerciseVguid); Guid personalVguid = Guid.Parse(personVguid); Business_ExercisesAnswer_Information exerciseAnswerModel = new Business_ExercisesAnswer_Information(); exerciseAnswerModel = dbMsSql.Queryable <Business_ExercisesAnswer_Information>().Where(i => i.BusinessExercisesVguid == mainExerciseVguid && i.BusinessPersonnelVguid == personalVguid).SingleOrDefault(); if (exerciseAnswerModel != null) { if (string.IsNullOrEmpty(exerciseAnswerModel.PicturePath)) { result = dbMsSql.Update <Business_ExercisesAnswer_Information>(new { PicturePath = url }, i => i.BusinessExercisesVguid == mainExerciseVguid && i.BusinessPersonnelVguid == personalVguid); } } else //如果Business_ExercisesAnswer_Information没有数据要新增一条 { Business_ExercisesAnswer_Information mainExercise = new Business_ExercisesAnswer_Information(); mainExercise.Vguid = Guid.NewGuid(); mainExercise.BusinessExercisesVguid = mainExerciseVguid; mainExercise.BusinessPersonnelVguid = personalVguid; mainExercise.ChangeDate = DateTime.Now; mainExercise.CreatedDate = DateTime.Now; mainExercise.PicturePath = url; mainExercise.SolveNumber = 0; result = dbMsSql.Insert <Business_ExercisesAnswer_Information>(mainExercise, false) != DBNull.Value; } //存入操作日志表 string logData = JsonHelper.ModelToJson <Business_ExercisesAnswer_Information>(exerciseAnswerModel); _ll.SaveLog(13, 34, personVguid, "用户图片", logData); } catch (Exception ex) { LogHelper.WriteLog(ex.Message); _ll.SaveLog(5, 34, personVguid, "用户图片", ex.Message); } return(result); } }
/// <summary> /// 更新用户是否阅读推送状态 /// </summary> /// <param name="userID">用户微信UserID</param> /// <param name="pushVguid">推送内容Vguid</param> /// <returns></returns> public bool UpdateIsRead(string userID, Guid pushVguid) { using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance()) { bool result = false; try { _dbMsSql.BeginTran(); result = _dbMsSql.Update <Business_WeChatPushDetail_Information>(new { ISRead = "1" }, i => i.PushObject == userID && i.Business_WeChatPushVguid == pushVguid); _dbMsSql.CommitTran(); } catch (Exception exp) { _dbMsSql.RollbackTran(); LogHelper.WriteLog(exp.Message + "/n" + exp.ToString() + "/n" + exp.StackTrace); } return(result); } }
public bool Unlock(CheckOutBillDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = true; var tableCount = db.Queryable <R_OrderTable>() .Where(p => req.OrderTableIds.Contains(p.R_Table_Id) && p.R_Order_Id == req.OrderId && p.IsCheckOut == false).Count(); if (tableCount == req.OrderTableIds.Count()) { result = db.Update <R_OrderTable>(new { IsLock = false }, p => p.R_Order_Id == req.OrderId && req.OrderTableIds.Contains(p.R_Table_Id)); } else { result = false; } return(result); } }
//进行标记啊啥的,不需要拆66字了 private void GetFinalList(List <Sev_SendDateDetail> HttpSendList) { try { for (int x = 0; x < HttpSendList.Count(); x++) { Sev_FinalSendDetail senditem = new Sev_FinalSendDetail(); senditem.F_Id = Guid.NewGuid().ToString(); senditem.F_SendId = HttpSendList[x].F_Id; senditem.F_Level = HttpSendList[x].F_Level; senditem.F_DealState = 0; //默认未发送 senditem.F_Report = "0"; //默认未收到回复报告 senditem.F_CreateTime = DateTime.Now; senditem.F_SendTime = HttpSendList[x].F_SendTime; senditem.F_Reissue = 0; //初始化补发次数为0; senditem.F_Response = 0; //默认未收到应答 senditem.F_SmsContent = HttpSendList[x].F_SmsContent; //内容直接复制拆大段表的内容,不需要拆分 senditem.F_CreatorUserId = HttpSendList[x].F_CreatorUserId; senditem.F_IsCashBack = false; //decimal SmsCount = Math.Ceiling(Convert.ToDecimal(item.F_SmsContent.Length) / 66);//向上取整获取发送条数 //for (int i = 0; i < SmsCount; i++) //{ // senditem.F_Id = Guid.NewGuid().ToString(); // senditem.F_SmsOrderNo = i + 1; // if ((i + 1) * 66 < item.F_SmsContent.Length) // senditem.F_SmsContent = item.F_SmsContent.Substring(i * 66, 66);//进行66个字的拆分 // else // senditem.F_SmsContent = item.F_SmsContent.Substring(i * 66, item.F_SmsContent.Length-66);//避免超过长度 // senditem.F_SmsCount = senditem.F_SmsContent.Length; // FinalList.Add(senditem);//加入队列 //} using (SqlSugarClient db = new SqlSugarClient(connStr)) //开启数据库连接 { db.Insert(senditem); //单个插入,客户要求 db.Update <Sev_SendDateDetail>(new { F_DealState = 1 }, it => it.F_Id == HttpSendList[x].F_Id); //更新状态为已拆分状态 } } } catch (Exception ex) { LogHelper.WriteLog("发送任务出现异常:" + ex); } }
/// <summary> /// 删除用户信息 /// </summary> /// <param name="vguid"></param> /// <returns></returns> public bool DeleteUserInfo(string vguid) { using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance()) { bool result = false; Guid Vguid = Guid.Parse(vguid); Business_Personnel_Information userInfo = dbMsSql.Queryable <Business_Personnel_Information>().Where(i => i.Vguid == Vguid).SingleOrDefault(); try { dbMsSql.BeginTran(); string weChatJson = JsonHelper.ModelToJson(userInfo); //从微信中删除 string accessToken = Common.WeChatPush.WeChatTools.GetAccessoken(); string focusResult = Common.WeChatPush.WeChatTools.DisableWeChatData(accessToken, userInfo.UserID);//禁用微信通讯录人员 U_FocusResult resultMsg = new U_FocusResult(); resultMsg = JsonHelper.JsonToModel <U_FocusResult>(focusResult); result = dbMsSql.Update <Business_Personnel_Information>(new { ApprovalStatus = 4 }, i => i.Vguid == Vguid); if (resultMsg.errcode == 0)//删除成功 { result = true; } else { LogHelper.WriteLog("人员:" + userInfo.UserID + "离职失败:错误码为-" + resultMsg.errcode + ",错误消息为_" + resultMsg.errmsg); result = false; } //存入操作日志表 _ll.SaveLog(2, 5, CurrentUser.GetCurrentUser().LoginName, userInfo.Name, weChatJson); dbMsSql.CommitTran(); return(result); } catch (Exception ex) { dbMsSql.RollbackTran(); LogHelper.WriteLog("人员:" + userInfo.UserID + "离职异常:异常信息为_" + ex.Message + "/n" + ex.ToString() + "/n" + ex.StackTrace); } return(result); } }
/// <summary> /// 保存角色信息 /// </summary> /// <param name="roleTypeModel"></param> /// <param name="permissionList"></param> /// <param name="isEdit"></param> /// <returns></returns> public bool SaveRole(Sys_Role roleTypeModel, List <Sys_Role_Module> permissionList, bool isEdit) { using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance()) { bool result = false; try { _dbMsSql.BeginTran(); if (isEdit)//编辑 { var model = new { ChangeDate = roleTypeModel.ChangeDate, ChangeUser = roleTypeModel.ChangeUser, Role = roleTypeModel.Role, Description = roleTypeModel.Description }; string logData = JsonHelper.ModelToJson <Sys_Role>(roleTypeModel); _logLogic.SaveLog(4, 32, Common.CurrentUser.GetCurrentUser().LoginName, roleTypeModel.Role, logData); result = _dbMsSql.Update <Sys_Role>(model, i => i.Vguid == roleTypeModel.Vguid); } else//新增 { string logData = JsonHelper.ModelToJson <Sys_Role>(roleTypeModel); _logLogic.SaveLog(1, 31, Common.CurrentUser.GetCurrentUser().LoginName, roleTypeModel.Role, logData); result = _dbMsSql.Insert <Sys_Role>(roleTypeModel, false) != DBNull.Value; } _dbMsSql.Delete <Sys_Role_Module>(i => i.RoleVGUID == roleTypeModel.Vguid); _dbMsSql.InsertRange(permissionList, false); _dbMsSql.CommitTran(); } catch (Exception ex) { _dbMsSql.RollbackTran(); Common.LogHelper.LogHelper.WriteLog(ex.Message + "/n" + ex.ToString() + "/n" + ex.StackTrace); } finally { } return(result); } }
internal void SaveAvatar(SqlSugarClient db, UserInfo user) { //将远程图片下载到本地 if (user.Avatar.IsValuable() && user.Avatar.Contains("http://")) { string path = "/_theme/img/avatar{0}.jpg".ToFormat(user.Id); string savePath = FileSugar.GetMapPath("~" + path); WebClient my = new WebClient(); byte[] mybyte; mybyte = my.DownloadData(user.Avatar.Replace(@"/30", @"/100")); using (MemoryStream ms = new MemoryStream(mybyte)) { System.Drawing.Image img; img = System.Drawing.Image.FromStream(ms); img.Save(savePath, ImageFormat.Jpeg);//保存 } user.Avatar = path; db.Update <UserInfo>(new { Avatar = user.Avatar }, it => it.Id == user.Id); } }
public JsonResult Edit(GridTable gt) { using (SqlSugarClient db = SugarDao.GetInstance()) { ActionResultModel <string> model = new ActionResultModel <string>(); string message = string.Empty; var isValid = ValidationSugar.PostValidation("validate_key_grid_index", out message); if (isValid)//后台验证数据完整性 { model.isSuccess = db.Update <GridTable>(gt, it => it.id == gt.id); model.responseInfo = model.isSuccess ? "编辑成功" : "编辑失败"; } else { model.isSuccess = false; model.responseInfo = message; } return(Json(model)); } }
public bool Update(CategoryCreateDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = true; R_Category model = new R_Category() { Id = req.Id, Name = req.Name, Description = req.Description, DiscountRate = req.DiscountRate, PId = req.Pid, IsDiscount = req.IsDiscount, Sorted = req.Sorted }; result = db.Update(model); return(result); } }
private void UpdateMyRights(SqlSugarClient _db, Business_EquityAllocation equity) { if (equity.ValidType == "周期") { getPeriodDate(equity, equity.Period); } var myRights = _db.Queryable <Business_MyRights>().Where(x => x.VGUID == equity.VGUID).FirstOrDefault(); var data = new { RightsName = equity.RightsName, Description = equity.Description, Type = equity.Type, StartValidity = equity.StartValidity, EndValidity = equity.EndValidity, ChangeUser = equity.ChangeUser, ChangeDate = equity.ChangeDate }; _db.Update <Business_MyRights>(data, i => i.VGUID == equity.VGUID); }
public void UpdateBusiness_VehicleRepairComplaints(Business_VehicleRepairComplaints vehicleRepairComplaints) { using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance()) { if (vehicleRepairComplaints.VGUID == Guid.Empty) { vehicleRepairComplaints.VGUID = Guid.NewGuid(); vehicleRepairComplaints.CreateDate = DateTime.Now; vehicleRepairComplaints.ChangeDate = DateTime.Now; vehicleRepairComplaints.ChangeUser = vehicleRepairComplaints.CreateUser; _dbMsSql.Insert(vehicleRepairComplaints); } else { vehicleRepairComplaints.ReflectDate = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); vehicleRepairComplaints.ChangeDate = DateTime.Now; vehicleRepairComplaints.ChangeUser = vehicleRepairComplaints.CreateUser; _dbMsSql.Update(vehicleRepairComplaints); } } }
public bool Delete(int id) { using (var db = new SqlSugarClient(Connection)) { bool res = false; try { var model = db.Queryable <R_PayMethod>().InSingle(id); if (model.IsSystem) { throw new Exception("该支付项为系统配置,不能删除"); } res = db.Update <R_PayMethod>(new { IsDelete = true }, p => p.Id == id || p.Pid == id); } catch (Exception ex) { throw new Exception(ex.Message); } return(res); } }
public WarmUp() { Console.WriteLine("开启预热"); //预热处理 for (int i = 0; i < 2; i++) { using (SqlConnection conn = new SqlConnection(PubConst.connectionString)) { var list = conn.QueryFirst <Test>("select top 1 * from Test"); conn.Update(new Test()); } using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString)) { var list = conn.Queryable <Test>().Where(it => 1 == 2).ToList(); conn.Update(new Test()); } } Console.WriteLine("预热完毕"); Console.WriteLine("----------------比赛开始-------------------"); }
public WarmUp() { Console.WriteLine("开启预热"); //预热处理 for (int i = 0; i < 2; i++) { using (SqlConnection conn = new SqlConnection(PubConst.connectionString)) { var list = conn.QueryFirst<Test>("select top 1 * from Test"); conn.Update(new Test()); } using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString)) { var list = conn.Queryable<Test>().Where(it => 1 == 2).ToList(); conn.Update(new Test()); } } Console.WriteLine("预热完毕"); Console.WriteLine("----------------比赛开始-------------------"); }
public int Update(PackageCreateDTO req) { using (var db = new SqlSugarClient(Connection)) { int result = 0; var baseModel = db.Queryable <R_Package>() .First(p => p.Id == req.Id); R_Package model = new R_Package() { Id = req.Id, Name = req.Name, IsOnSale = req.IsOnSale, Property = req.IsDiscount + req.IsChangePrice + req.IsGive, Describe = req.Describe, CostPrice = baseModel.CostPrice, Price = baseModel.Price, R_Category_Id = req.R_Category_Id }; result = db.Update(model) ? req.Id : 0; return(result); } }
/// <summary> /// 逻辑删除 /// </summary> /// <param name="id">包厢id</param> /// <returns></returns> public bool IsDelete(int id) { using (var db = new SqlSugarClient(Connection)) { bool result = true; try { db.BeginTran(); //包厢 db.Update <R_Box>(new { IsDelete = true }, x => x.Id == id); //删除包厢台号关系表 db.Delete <R_BoxTable>(p => p.R_Box_Id == id); db.CommitTran(); } catch (Exception) { db.RollbackTran(); result = false; } return(result); } }
public bool SaveBusiness_RideCheckFeedbackItem(string user, Guid rideCheckFeedbackVguid, int feedbackNumber, string answer1, string answer2, string answer3, string answer4, string answer5, string answer6, string answer7) { Business_RideCheckFeedback_Item item = new Business_RideCheckFeedback_Item(); using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance()) { item = _dbMsSql.Queryable <Business_RideCheckFeedback_Item>().Where(c => c.RideCheckFeedbackVGUID == rideCheckFeedbackVguid && c.FeedbackNumber == feedbackNumber).FirstOrDefault(); if (item == null) { item = new Business_RideCheckFeedback_Item(); item.VGUID = Guid.NewGuid(); item.RideCheckFeedbackVGUID = rideCheckFeedbackVguid; item.ChangeUser = item.CreateUser = user; item.ChangeDate = item.CreateDate = DateTime.Now; item.FeedbackNumber = feedbackNumber; item.FeedbackAnswer1 = answer1; item.FeedbackAnswer2 = answer2; item.FeedbackAnswer3 = answer3; item.FeedbackAnswer4 = answer4; item.FeedbackAnswer5 = answer5; item.FeedbackAnswer6 = answer6; item.FeedbackAnswer7 = answer7; _dbMsSql.Insert(item); } else { item.FeedbackAnswer1 = answer1; item.FeedbackAnswer2 = answer2; item.FeedbackAnswer3 = answer3; item.FeedbackAnswer4 = answer4; item.FeedbackAnswer5 = answer5; item.FeedbackAnswer6 = answer6; item.FeedbackAnswer7 = answer7; _dbMsSql.Update(item); } } return(true); }
/// <summary> /// 保存配置文件内容 /// </summary> /// <param name="configModel">配置表实体</param> /// <returns></returns> public bool SaveConfig(Master_Configuration configModel) { using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance()) { bool result = false; try { result = _dbMsSql.Update <Master_Configuration>(new { ConfigValue = configModel.ConfigValue }, i => i.ID == configModel.ID); //写入日志 Master_Configuration configuration = new Master_Configuration(); configuration = _dbMsSql.Queryable <Master_Configuration>().Where(i => i.ID == configModel.ID).SingleOrDefault(); string logData = Common.JsonHelper.ModelToJson(configuration); _logLogic.SaveLog(4, 35, Common.CurrentUser.GetCurrentUser().LoginName, configuration.ConfigDescription, logData); } catch (Exception exp) { Common.LogHelper.LogHelper.WriteLog(exp.Message + "/n" + exp.ToString() + "/n" + exp.StackTrace); } return(result); } }
public bool Update(StallsCreateDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = true; R_Stall model = new R_Stall() { Id = req.Id, Name = req.Name, Description = req.Description }; //result = db.Update(model); result = db.Update <R_Stall>( new { Name = req.Name, Description = req.Description, Print_Id = req.Print_Id, R_Company_Id = req.R_Company_Id, }, x => x.Id == req.Id); return(result); } }
/// <summary> /// 逻辑删除 /// </summary> /// <param name="id">台号id</param> /// <returns></returns> public bool IsDelete(int id, out string msg) { using (var db = new SqlSugarClient(Connection)) { bool result = true; msg = ""; //先查询是否此台号还存在未结账的订单 var orderTableIds = db.Queryable <R_OrderTable>() .JoinTable <R_Order>((s1, s2) => s1.R_Order_Id == s2.Id) .Where(" s1.R_Table_Id = " + id + " and s2.CyddStatus not in (6,7) and s2.IsDelete=0").ToList(); if (orderTableIds != null && orderTableIds.Count > 0) { result = false; msg = "此台号还存在未结账的订单,删除失败!"; } else { //删除台号 result = db.Update <R_Table>(new { IsDelete = true }, x => x.Id == id); } return(result); } }
public bool Edit(List <CustomConfigDTO> req) { using (var db = new SqlSugarClient(Connection)) { var res = true; try { if (req.Any()) { foreach (var item in req) { db.Update <R_CustomConfig>(new { Sorted = item.Sorted, ModuleName = item.ModuleName, FunctionName = item.FunctionName, Colour = item.Colour }, p => p.Id == item.Id); } } } catch (Exception e) { res = false; throw e; } return(res); } }
public bool Edit(OrderDetailCauseDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = false; try { var model = Mapper.Map <OrderDetailCauseDTO, R_OrderDetailCause>(req); if (req.Id > 0) { result = db.Update(model); } else { result = db.Insert(model) != null ? true : false; } } catch (Exception e) { throw e; } return(result); } }
protected void Page_Load(object sender, EventArgs e) { using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接 { string aaa = null; var xx = db.Queryable <School>().Where(it => true).ToList(); var xx2 = db.Queryable <Student>().Where(it => it.isOk == false).ToList(); var sl2 = db.Sqlable().Form <Student>("s").SelectToList <Student>("id"); var sl = db.Sqlable().Form <Student>("s").SelectToList <Student>("*"); db.Queryable <Student>().In("id", "1", "2", "3").ToList(); db.Queryable <Student>().In("id", new string[] { "1", "2", "3" }).ToList(); db.Queryable <Student>().In("id", new List <string> { "1", "2", "3" }).ToList(); var array = new string[] { "1", "2", "3" }; db.Queryable <Student>().Where(it => array.Contains(it.name)); db.Delete <Student, int>(1, 2); //开启事务,可以不使用事务,也可以使用多个事务 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 = db.GetInt("select top 1 id from Student") }; //查询单条 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); int maxId = db.Queryable <Student>().Max <Student, int>("id"); int minId = db.Queryable <Student>().Where(c => c.id > 0).Min <Student, int>("id"); //---------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, int>(new { name = "蓝翔2" }, 1, 3, 12); db.Update <School, string>(new { name = "蓝翔2" }, new string[] { "1", "2" }); //整个实体更新,注意主键必需为实体类的第一个属性 db.Update(new School { id = id, name = "蓝翔2" }); db.Update <School>(new School { id = id, name = "蓝翔2" }, it => it.id == id); /************************************************************************************************************/ /*************************************************5、删除****************************************************/ /************************************************************************************************************/ db.Delete <School, int>(10);//注意主键必需为实体类的第一个属性 db.Delete <School>(it => it.id > 100); db.Delete <School, string>(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=" + stud.id); using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader db.GetScalar(sql); db.GetString(sql); db.GetInt(sql); } catch (Exception ex) { //回滚事务 db.RollbackTran(); throw ex; } }//关闭数据库连接 }
public bool Update(TableCreateDTO req) { using (var db = new SqlSugarClient(Connection)) { bool result = true; try { db.BeginTran(); R_Table model = db.Queryable <R_Table>().First(p => p.Id == req.Id); model.Name = req.Name; model.Describe = req.Description; model.R_Restaurant_Id = req.RestaurantId; model.ServerRate = req.ServerRate; model.R_Area_Id = req.RestaurantArea; model.SeatNum = req.SeatNum; model.IsVirtual = req.IsVirtual; model.Sorted = req.Sorted; result = db.Update(model); var cybxth = db.Queryable <R_BoxTable>() .Where(p => p.R_Table_Id == model.Id) .FirstOrDefault(); if (req.Box > 0) { if (cybxth != null) { if (cybxth.R_Box_Id != req.Box) { cybxth.R_Box_Id = req.Box; db.Update <R_BoxTable>(cybxth); } } else { db.Insert <R_BoxTable>(new R_BoxTable() { R_Table_Id = model.Id, R_Box_Id = req.Box }); } } else { if (cybxth != null) { db.Delete <R_BoxTable>(p => p.Id == cybxth.Id); } } db.CommitTran(); } catch (Exception) { db.RollbackTran(); result = false; throw; } return(result); } }
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.Update(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())) { //ef using (WebTest.TestLib.SqlSugarTestEntities ef = new TestLib.SqlSugarTestEntities()) { pt.Execute(i => { var datas = ef.InsertTests.Where(c => c.id == 1 || c.id > 1000); foreach (var r in datas) { r.v1 = "new11"; r.v2 = "newv22"; r.v3 = "new33"; } ef.SaveChanges(); }, m => { }, "EF4.0+sql05 Linq语法"); } //ado.net pt.Execute(i => { db.ExecuteCommand(@" UPDATE InsertTest SET v1 =@v1 , v2 =@v2 , v3 =@v3 WHERE 1=1 and ((id =@id1) Or (id >@id1000))",new SqlParameter[]{ new SqlParameter("@v1","new11"), new SqlParameter("@v2","new22"), new SqlParameter("@v3","new33"), new SqlParameter("@id1","1"), new SqlParameter("@id1000","1000") }); }, m => { },"ado.net"); //dapper var sqlConn=db.GetConnection(); pt.Execute(i => { sqlConn.Execute(@" UPDATE InsertTest SET v1 =@v1 , v2 =@v2 , v3 =@v3 WHERE 1=1 and ((id ='1') Or (id >'1000'))", new { v1 = "newv11", v2 = "newv22", v3 = "newv33",id1=1,id1000=1000 } ); }, m => { }, "dapper"); //sqlSugar pt.Execute(i => { //更新10000次 db.Update<Models.InsertTest>(new { v1 = "newv11", v2 = "newv22", v3 = "newv33" }, it => it.id == 1 || it.id > 1000); }, m => { }, "sqlSugar"); } //输出测试页面 GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }