private void InitData(SqlSugarClient db) { db.ExecuteCommand("truncate table Subject"); db.ExecuteCommand("truncate table student"); db.ExecuteCommand("truncate table school"); db.ExecuteCommand("truncate table area"); db.SqlBulkCopy(SchoolList); db.SqlBulkCopy(StudentList); db.SqlBulkCopy(SubjectList); db.InsertRange(AreaList); }
private void InitData(SqlSugarClient db) { db.ExecuteCommand("delete from Subject"); db.ExecuteCommand("delete from student"); db.ExecuteCommand("delete from school"); db.ExecuteCommand("delete from area"); db.SqlBulkCopy(SchoolList); db.SqlBulkCopy(StudentList); db.SqlBulkCopy(SubjectList); db.InsertRange(AreaList); }
/// <summary> /// 新增菜单 /// </summary> /// <param name="parentTreeId"></param> /// <param name="treeId"></param> /// <param name="menuName"></param> /// <param name="sort"></param> /// <param name="founder"></param> /// <returns></returns> public TreeViewModel InsertMenu(Guid parentTreeId, Guid treeId, string menuName, string icon, bool isInnerPage, int sort, string founder) { _db.ExecuteCommand("exec SP_SysBest_AddMenuTree @parentTreeId, @treeId,@menuName,@icon,@isDeleted,@isInnerPage,@sort,@founder,@createTime,@MenuUrl", new { parentTreeId = parentTreeId, treeId = treeId, menuName = menuName, icon = icon, isDeleted = false, isInnerPage = isInnerPage, sort = sort, founder = founder, createTime = DateTime.Now, MenuUrl = RequestInfo.QueryString("MenuUrl") }); var reval = _db.Queryable <SysBest_Menu>().Where(it => it.TreeId == treeId).ToList().Select(it => new TreeViewModel() { id = it.Id.TryToString().Replace("/", "_"), parentid = it.ParentId.TryToString().Replace("/", "_"), value = it.TreeId, text = it.MenuName, expanded = true, label = $"{it.Id.TryToString()} {it.MenuName}" }).Single(); return(reval); }
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); }
protected void Page_Load(object sender, EventArgs e) { using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接 { var r1 = db.GetDataTable("select * from student"); var r2 = db.GetSingle <Student>("select * from student limit 0,1"); var r3 = db.GetScalar("select count(1) from student"); var r4 = db.GetReader("select count(1) from student"); r4.Dispose(); var r5 = db.GetString("select name from student limit 0,1"); var r6 = db.ExecuteCommand("select 1"); } }
public void Init() { using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接 { var r1 = db.GetDataTable("select * from student"); var r2 = db.GetSingle <Student>("select top 1 * from student"); var r3 = db.GetScalar("select count(1) from student"); var r4 = db.GetReader("select count(1) from student"); r4.Dispose(); var r5 = db.GetString("select top 1 name from student"); var r6 = db.ExecuteCommand("select 1"); } }
protected void Page_Load(object sender, EventArgs e) { using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接 { var r1 = db.GetDataTable("select * from Student "); var r2 = db.GetSingle <STUDENT>("SELECT * FROM (SELECT * FROM Student ORDER BY id DESC ) A where ROWNUM=1"); var r3 = db.GetScalar("select count(1) from Student"); var r4 = db.GetReader("select count(1) from Student"); r4.Dispose(); var r5 = db.GetString("SELECT name FROM (SELECT * FROM Student ORDER BY id DESC ) A where ROWNUM=1"); var r6 = db.ExecuteCommand("delete from Student where id=20"); } }
public int Delete(int id, bool isSoftDeleted = true) { string sql = ""; if (isSoftDeleted) { sql = "update Job_Config set Status=3 where id=" + id; } else { sql = "delete from Job_Config where id=" + id; } return(_sqlClient.ExecuteCommand(sql)); }
protected void Page_Load(object sender, EventArgs e) { PerformanceTest pt = new PerformanceTest(); pt.SetCount(1100);//设置循环次数 using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { TuncateTable(db); AddTest(pt, db); //sqlSugar pt.Execute(i => { db.Delete<Models.InsertTest>(it=>it.id==i); }, m => { }, "sqlSugar"); TuncateTable(db); AddTest(pt, db); //ado.net pt.Execute(i => { db.ExecuteCommand("delete InsertTest where id=@id", new SqlParameter("@id", i)); }, m => { }, "ado.net"); TuncateTable(db); AddTest(pt, db); //dapper pt.Execute(i => { db.GetConnection().Execute("delete InsertTest where id=@id", new { id=i}); }, m => { }, "dapper"); } //输出测试页面 GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }
protected void Page_Load(object sender, EventArgs e) { PerformanceTest pt = new PerformanceTest(); pt.SetCount(1100);//设置循环次数 using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { TuncateTable(db); AddTest(pt, db); //sqlSugar pt.Execute(i => { db.Delete <Models.InsertTest>(it => it.id == i); }, m => { }, "sqlSugar"); TuncateTable(db); AddTest(pt, db); //ado.net pt.Execute(i => { db.ExecuteCommand("delete InsertTest where id=@id", new SqlParameter("@id", i)); }, m => { }, "ado.net"); TuncateTable(db); AddTest(pt, db); //dapper pt.Execute(i => { db.GetConnection().Execute("delete InsertTest where id=@id", new { id = i }); }, m => { }, "dapper"); } //输出测试页面 GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }
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; } }//关闭数据库连接 }
/// <summary> /// 创建多语言视图,带有LanguageId=1的所有有视图1替换成languageValue 并且新创视图 名称为 原有视图名+_$_+suffix /// </summary> /// <returns></returns> public static void UpdateView(PubModel.Language lan, SqlSugarClient db) { if (lan == null) { return; } if (lan.Suffix.IsNullOrEmpty()) { Check.Exception(true, "LanguageHelper.lan.Suffix is Null Or Empty"); } if (PreSuffix.IsNullOrEmpty()) { Check.Exception(true, "LanguageHelper.PreSuffix is Null Or Empty"); } if (!lan.Suffix.StartsWith(PreSuffix)) { lan.Suffix = PreSuffix + lan.Suffix; } string sql = @" --验证参数传递规则 if LEFT(ltrim(@Suffix),3)<>'" + PreSuffix + @"' begin raiserror('参数传递格式不规范',16,1) return; end else if(ISNULL(" + lan.LanguageValue + @",'')='') begin raiserror('参数传递格式不规范',16,1) return; end declare @name varchar(100), --视图名称 @definition varchar(max) --视图脚本 --删除数据库里面所有带传递参数几号的视图 declare my_cursor cursor for select a.name,b.[definition] from sys.objects a JOIN sys.sql_modules b on a.[object_id]=b.[object_id] where [type]='v' and b.[definition] like '%" + lan.ReplaceViewStringKey + @"%' and a.name not like '%" + PreSuffix + @"%' --打开处理器 open my_cursor fetch next from my_cursor into @name,@definition while @@FETCH_STATUS=0 begin --脚本查询语言ID更改,并且更改新脚本语言的对象名称 set @definition=REPLACE( REPLACE( @definition, '" + lan.ReplaceViewStringKey + @"', '" + string.Format(lan.ReplaceViewStringValue, lan.LanguageValue) + @"' ), @name, @name+@Suffix ) --判断新脚本语言的对象名称是否存在,存在删除 exec( ' if object_id('''+@name+@Suffix+''',''v'') is not null begin drop view '+@name+@Suffix+' end ' ) exec(@definition) fetch next from my_cursor into @name,@definition end close my_cursor deallocate my_cursor "; db.ExecuteCommand(sql, new { Suffix = lan.Suffix }); }
//清空数据表 private static void TuncateTable(SqlSugarClient db) { db.ExecuteCommand("truncate table InsertTest"); System.Threading.Thread.Sleep(1000); }
private void InitData(SqlSugarClient db) { try { db.ExecuteCommand(" DROP sequence SEQ"); } catch (Exception) { } try { db.ExecuteCommand(" DROP sequence SEQ2"); } catch (Exception) { } try { db.ExecuteCommand(" DROP sequence SEQ3"); } catch (Exception) { } try { db.ExecuteCommand(" DROP sequence SEQ4"); } catch (Exception) { } db.ExecuteCommand(" create sequence SEQ"); db.ExecuteCommand(" create sequence SEQ2"); db.ExecuteCommand(" create sequence SEQ3"); db.ExecuteCommand(" create sequence SEQ4"); db.ExecuteCommand("truncate table Subject"); db.ExecuteCommand("truncate table student"); db.ExecuteCommand("truncate table school"); db.ExecuteCommand("truncate table area"); db.SqlBulkCopy(StudentList); db.SqlBulkCopy(SchoolList); db.SqlBulkCopy(SubjectList); db.InsertRange(AreaList); }
public CheckOutResultDTO ReverseOrder(ReverseOrderDTO req) { using (var db = new SqlSugarClient(Connection)) { CheckOutResultDTO res = null; try { var dateItem = _extendItemRepository.GetModelList(req.CompanyId, 10003).FirstOrDefault(); if (dateItem == null) { throw new Exception("餐饮账务日期尚未初始化,请联系管理员"); } DateTime accDate = DateTime.Today; if (!DateTime.TryParse(dateItem.ItemValue, out accDate)) { throw new Exception("餐饮账务日期设置错误,请联系管理员"); } var tables = db.Queryable <R_Table>().JoinTable <R_OrderTable> ((s1, s2) => s1.Id == s2.R_Table_Id).Where <R_OrderTable>((s1, s2) => s2.R_OrderMainPay_Id == req.MainPayId).Select("s1.*").ToList(); var tableIds = tables.Select(p => p.Id).ToArray(); var tableOrderIngs = tables.Where(p => p.CythStatus == CythStatus.在用).ToList(); if (tableOrderIngs.Any()) { var tableNames = string.Join(",", tableOrderIngs.Select(p => p.Name)); throw new Exception(string.Format("({0}) 正在开台状态,请结账后再执行该账单的反结操作", tableNames)); } var reverTableNames = string.Join(",", tables.Select(p => p.Name)); var mainPayModel = db.Queryable <R_OrderMainPay>().First(p => p.Id == req.MainPayId); if (mainPayModel.BillDate.Date != accDate.Date) { throw new Exception("该账单账务日期和系统当前账务日期不一致,不允许反结"); } var orderModel = db.Queryable <R_Order>().First(p => p.Id == mainPayModel.R_Order_Id); var orderTables = db.Queryable <R_OrderTable>() .Where(p => p.R_OrderMainPay_Id == mainPayModel.Id).ToList(); var orderTableIds = orderTables.Select(p => p.Id).ToArray(); var orderDetails = db.Queryable <R_OrderDetail>() .Where(p => orderTableIds.Contains(p.R_OrderTable_Id)).ToList(); var orderPayRecords = db.Queryable <R_OrderPayRecord>() .Where(p => p.R_OrderMainPay_Id == mainPayModel.Id).ToList(); if (orderPayRecords.Any(p => p.CyddJzStatus == CyddJzStatus.已结 && p.CyddJzType != CyddJzType.定金)) { throw new Exception(string.Format("该主结单已经做过反结操作,不能重复反结")); } db.BeginTran(); //db.Delete<R_OrderPayRecord>(p => p.R_OrderMainPay_Id == mainPayModel.Id); //db.Delete<R_OrderMainPay>(p => p.Id == mainPayModel.Id); //db.Update<R_OrderPayRecord>(new { CyddJzStatus = (int)CyddJzStatus.已付 }, p => p.R_Order_Id == orderModel.Id); //db.Delete<R_OrderPayRecord>(p=>p.R_Order_Id == orderModel.Id && p.PayAmount<=0 && p.CyddJzType==CyddJzType.定金); var depositReverAll = db.Queryable <R_OrderPayRecord>().Where(p => p.R_Order_Id == orderModel.Id && p.R_OrderMainPay_Id == 0 && p.CyddJzType == CyddJzType.定金).ToList(); var depositRePids = depositReverAll.Where(p => p.CyddJzStatus == CyddJzStatus.已退).Select(p => p.PId).ToList(); var depositReverIds = depositReverAll.Where(p => !depositRePids.Contains(p.Id) && p.CyddJzStatus == CyddJzStatus.已结) .Select(p => p.Id).ToList(); db.Update <R_OrderPayRecord>(new { CyddJzStatus = (int)CyddJzStatus.已付 }, p => depositReverIds.Contains(p.Id)); db.Update <R_OrderPayRecord>(new { CyddJzStatus = (int)CyddJzStatus.已结 }, p => p.R_OrderMainPay_Id == mainPayModel.Id); db.Delete <R_OrderPayRecord>(p => p.R_Order_Id == orderModel.Id && p.CyddJzType == CyddJzType.定金 && p.CyddJzStatus == CyddJzStatus.已结 && p.R_OrderMainPay_Id > 0); db.Update <R_OrderDetail>(new { PayableTotalPrice = 0, DiscountRate = 1 }, p => orderTableIds.Contains(p.R_OrderTable_Id)); db.Update <R_OrderTable>(new { IsCheckOut = 0, IsOpen = 1, R_OrderMainPay_Id = 0 }, p => orderTableIds.Contains(p.Id)); db.Update <R_Order>(new { RealAmount = 0, CyddStatus = (int)CyddStatus.反结, OriginalAmount = 0, ConAmount = 0, DiscountRate = 0, DiscountAmount = 0, GiveAmount = 0, ClearAmount = 0 }, p => p.Id == orderModel.Id); db.Update <R_Table>(new { CythStatus = (int)CythStatus.在用 }, p => tableIds.Contains(p.Id)); db.Insert <R_OrderRecord>(new R_OrderRecord() { CreateDate = DateTime.Now, CreateUser = req.UserId, CyddCzjlStatus = CyddStatus.反结, CyddCzjlUserType = CyddCzjlUserType.员工, Remark = string.Format("执行了反结操作,台号:({0})", reverTableNames), R_OrderTable_Id = 0, R_Order_Id = orderModel.Id }); List <R_OrderPayRecord> reverseRecords = new List <R_OrderPayRecord>(); #region 反写应收账 foreach (var item in orderPayRecords) { if (item.CyddPayType == (int)CyddPayType.挂账 || item.CyddPayType == (int)CyddPayType.转客房 || item.CyddPayType == (int)CyddPayType.会员卡) { if (item.CyddPayType == (int)CyddPayType.会员卡) { if (EnabelGroupFlag) { try { var dto = AutoMapper.Mapper.Map <OrderPayRecordDTO>(item); dto.Remark = string.Format("{0} {1}", "反结会员卡", item.Remark); List <OrderPayRecordDTO> dtoList = new List <OrderPayRecordDTO> { dto }; SaveMemberConsumeInfo(dtoList, req.UserCode, false, orderModel.R_Restaurant_Id); //ApplyChangeMemberToDb(item.SourceId, item.SourceName, req.UserCode, // -item.PayAmount, string.Format("{0} {1}", "反结会员卡", item.Remark), false, new SqlSugarClient(ConnentionGroup)); } catch (Exception ex) { throw new Exception("反结集团库记录会员卡消费信息操作失败:" + ex.Message); } } else { try { ApplyChangeMemberToDb(item.SourceId, item.SourceName, req.UserCode, -item.PayAmount, string.Format("{0} {1}", "反结会员卡", item.Remark), false, db, orderModel.R_Restaurant_Id); } catch (Exception ex) { throw new Exception("反结本地库记录会员卡消费信息操作失败:" + ex.Message); } } //if (!EnabelGroupFlag) //{ // //若已启用集团会员库,里面则不再执行,本地库会员消费记录已在插入集团库时一并插入到本地库 //} } else if (item.CyddPayType == (int)CyddPayType.挂账) { var verifyInfo = new VerifySourceInfoDTO(); verifyInfo.SourceId = item.SourceId; verifyInfo.SourceName = item.SourceName; verifyInfo.RestaruantId = orderModel.R_Restaurant_Id; verifyInfo.PayMethod = (int)CyddPayType.挂账; verifyInfo.OperateValue = item.PayAmount; string remark = string.Format("反结挂账客户【{0}】- 代码:({1})", item.SourceName, item.SourceId); List <string> resultList = SearchVerifyOutsideInfo(verifyInfo, db); try { var paras = SqlSugarTool.GetParameters(new { xh = orderModel.Id, //餐饮单序号 dh = orderModel.R_Restaurant_Id + "." + orderModel.Id, //餐厅代码+'.'+餐饮单单号 lx = resultList[0].Trim(), //协议单位代码(lxdmdm00) je = -item.PayAmount, //金额 cz = req.UserCode, //操作员代码 ctmc = orderModel.R_Restaurant_Id, //餐厅名称 fsmc = "", //分市名称 th = orderModel.Id, rs = orderModel.PersonNum, bz = remark, mz = "", atr = 0 }); db.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数 db.ExecuteCommand("p_po_toys_newCY", paras); db.CommandType = System.Data.CommandType.Text; //还原回默认 } catch (Exception ex) { throw new Exception("反结挂账操作失败:" + ex.Message); } } else if (item.CyddPayType == (int)CyddPayType.转客房) { #region 转客房处理 var verifyInfo = new VerifySourceInfoDTO(); verifyInfo.SourceId = item.SourceId; verifyInfo.SourceName = item.SourceName; verifyInfo.RestaruantId = orderModel.R_Restaurant_Id; verifyInfo.PayMethod = (int)CyddPayType.转客房; verifyInfo.OperateValue = item.PayAmount; List <string> resultList = SearchVerifyOutsideInfo(verifyInfo, db); try { var paras = SqlSugarTool.GetParameters(new { zh00 = Convert.ToInt32(resultList[1]), //客人账号(krzlzh00) zwdm = resultList[0], //账项代码 hsje = -item.PayAmount, //金额 ckhm = item.SourceName, //房号(krzlfh00) czdm = req.UserCode, //操作员代码 xfje = 1, bz00 = "反结餐厅转客房", bc00 = "", }); db.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数 db.ExecuteCommand("p_zw_addx", paras); db.CommandType = System.Data.CommandType.Text; //还原回默认 } catch (Exception ex) { throw new Exception("反结转客房操作失败:" + ex.Message); } #endregion } } if (item.PayAmount != 0 && (item.CyddJzStatus != CyddJzStatus.已退 || item.CyddJzStatus != CyddJzStatus.已结) && item.CyddJzType != CyddJzType.定金) { item.PayAmount = -item.PayAmount; item.CreateDate = DateTime.Now; item.BillDate = accDate; item.R_Market_Id = req.CurrentMarketId; item.CreateUser = req.UserId; item.CyddJzStatus = CyddJzStatus.已结; if (item.CyddJzType == CyddJzType.找零) { item.Remark = string.Format("反结找零纪录 {0}", item.SourceName); } else { item.Remark = string.Format("反结付款纪录 {0}", item.SourceName); } reverseRecords.Add(item); } //if (item.CyddJzType==CyddJzType.转结 && item.CyddJzStatus==CyddJzStatus.已结) //{ // reverseRecords.Add(new R_OrderPayRecord() // { // PayAmount = -item.PayAmount, // CreateDate = DateTime.Now, // BillDate = accDate, // R_Market_Id = req.CurrentMarketId, // CreateUser = req.UserId, // CyddJzStatus = CyddJzStatus.已付, // CyddJzType = CyddJzType.定金, // CyddPayType=item.CyddPayType, // SourceId=0, // R_OrderMainPay_Id= mainPayModel.Id, // Remark = string.Format("反结重置定金纪录") // }); //} } db.InsertRange <R_OrderPayRecord>(reverseRecords); #endregion db.CommitTran(); res = new CheckOutResultDTO() { OrderId = orderModel.Id, OrderMainPayId = mainPayModel.Id, OrderTables = orderTableIds.ToList(), ReverTableNames = reverTableNames }; } catch (Exception ex) { db.RollbackTran(); throw ex; } return(res); } }
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); }
private MemberInfoDTO ApplyChangeMemberToDb(int memberId, string pwd, string userCode, decimal amount, string remark, bool isGroup, SqlSugarClient db, int restaurantId) { SqlSugarClient localDb = null; if (isGroup)//是否集团库连接 { localDb = new SqlSugarClient(Connection); } string xtdmSql = "SELECT xtdmbz00 FROM xtdm WHERE xtdmlx00='YKBZ' AND xtdmdm00='YKBZ' AND xtdmbzs0='Y'"; List <string> list = null; //始终查本地库的会员卡配置的系统代码标识 if (isGroup) { list = localDb.SqlQuery <string>(xtdmSql); } else { list = db.SqlQuery <string>(xtdmSql); } if (list == null || list.Count == 0) { if (isGroup && localDb != null) { localDb.Dispose(); } throw new Exception("会员卡消费挂账关联的系统代码配置不正确,请联系管理员!"); } SqlParameter[] paras; var member = VerifyMemberInfo(memberId, amount, pwd, db); Guid guid = Guid.NewGuid(); paras = SqlSugarTool.GetParameters(new { Zs = member.Id, //客人历史序号Id KH = member.Id, //客人历史序号Id Lx = "B", dd = restaurantId.ToString(), bz = remark, //备注 je = amount, //金额 cz = userCode, //操作员代码 fs = "01", // GPID = list[0], //协议单位代码(lxdmdm00) GUID = guid }); db.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数 db.ExecuteCommand("p_zw_gbxf", paras); db.CommandType = System.Data.CommandType.Text; //还原回默认 if (isGroup) { string krlsSql = string.Format("SELECT krlsxh00 FROM krls WHERE krlsGUID = '{0}'", member.MemberGUID); var memberList = localDb.SqlQuery <int>(krlsSql);//查本地库的会员 if (memberList == null || memberList.Count == 0) { string insertSql = string.Format( "INSERT krls(krlszwxm, krlszt00, krlsvpkh, krlsxb00, krlsGPID, krlsGUID, krlsdh00, krlszjhm) " + "VALUES('{0}', 'Y', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}') ", member.MemberName, member.MemberCardNo, member.MemberGender, member.MemberGPID, member.MemberGUID, member.MemberPhoneNo, member.MemberIdentityNo); localDb.CommandType = System.Data.CommandType.Text; localDb.ExecuteCommand(insertSql, paras); memberList = localDb.SqlQuery <int>(krlsSql);//查本地库的会员 } paras = SqlSugarTool.GetParameters(new { Zs = memberList[0], //本地库客人历史序号Id KH = memberList[0], //本地库客人历史序号Id Lx = "B", dd = restaurantId.ToString(), bz = remark, //备注 je = amount, //金额 cz = userCode, //操作员代码 fs = "01", // GPID = list[0], //协议单位代码(lxdmdm00) GUID = guid }); localDb.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数 localDb.ExecuteCommand("p_zw_gbxf", paras); localDb.CommandType = System.Data.CommandType.Text; //还原回默认 localDb.Dispose(); //销毁本地连接 } return(member); }
/// <summary> /// 创建多语言视图,带有LanguageId=1的所有有视图1替换成languageValue 并且新创视图 名称为 原有视图名+_$_+suffix /// </summary> /// <returns></returns> public static void UpdateView(PubModel.Language lan, SqlSugarClient db) { if (lan == null) return; if (lan.Suffix.IsNullOrEmpty()) { Check.Exception(true, "LanguageHelper.lan.Suffix is Null Or Empty"); } if (PreSuffix.IsNullOrEmpty()) { Check.Exception(true, "LanguageHelper.PreSuffix is Null Or Empty"); } if (!lan.Suffix.StartsWith(PreSuffix)) { lan.Suffix = PreSuffix + lan.Suffix; } string sql = @" --验证参数传递规则 if LEFT(ltrim(@Suffix),3)<>'" + PreSuffix + @"' begin raiserror('参数传递格式不规范',16,1) return; end else if(ISNULL("+lan.LanguageValue+@",'')='') begin raiserror('参数传递格式不规范',16,1) return; end declare @name varchar(100), --视图名称 @definition varchar(max) --视图脚本 --删除数据库里面所有带传递参数几号的视图 declare my_cursor cursor for select a.name,b.[definition] from sys.objects a JOIN sys.sql_modules b on a.[object_id]=b.[object_id] where [type]='v' and b.[definition] like '%" + lan.ReplaceViewStringKey + @"%' and a.name not like '%"+PreSuffix+@"%' --打开处理器 open my_cursor fetch next from my_cursor into @name,@definition while @@FETCH_STATUS=0 begin --脚本查询语言ID更改,并且更改新脚本语言的对象名称 set @definition=REPLACE( REPLACE( @definition, '" + lan.ReplaceViewStringKey + @"', '" + string.Format(lan.ReplaceViewStringValue, lan.LanguageValue) + @"' ), @name, @name+@Suffix ) --判断新脚本语言的对象名称是否存在,存在删除 exec( ' if object_id('''+@name+@Suffix+''',''v'') is not null begin drop view '+@name+@Suffix+' end ' ) exec(@definition) fetch next from my_cursor into @name,@definition end close my_cursor deallocate my_cursor "; db.ExecuteCommand(sql, new { Suffix = lan.Suffix }); }
public int ExecuteCommand(string sql, object pars) { return(_db.ExecuteCommand(sql, pars)); }
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; } }//关闭数据库连接 }