protected void Page_Load(object sender, EventArgs e) { using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接 { var list = db.Sqlable().Form <Student>("s").SelectToList <V_Student>("s.id"); } }
public ISqlable Sqlable() { SqlServerSqlable reval = new SqlServerSqlable(); reval.SqlableCore = _db.Sqlable(); return(reval); }
public ISqlable Sqlable() { OracleSqlable reval = new OracleSqlable(); reval.SqlableCore = _db.Sqlable(); return(reval); }
public ISqlable Sqlable() { MySqlSqlable reval = new MySqlSqlable(); reval.SqlableCore = _db.Sqlable(); return(reval); }
public List <TableListDTO> GetOpenTableChoseList(TableSearchDTO req) { using (var db = new SqlSugarClient(Connection)) { List <TableListDTO> list = new List <TableListDTO>(); var data = db.Sqlable().From <R_Table>("s1"); if (req.RestaurantId > 0) { data = data.Where("s1.R_Restaurant_Id=" + req.RestaurantId); } if (req.AreaId > 0) { data = data.Where("s1.R_Area_Id=" + req.AreaId); } if ((int)req.CythStatus > 0) { data = data.Where("s1.CythStatus=" + (int)req.CythStatus); } list = data.SelectToList <TableListDTO>("s1.*,s1.R_Area_Id as AreaId"); return(list); } }
protected void Page_Load(object sender, EventArgs e) { PerformanceTest pt = new PerformanceTest(); pt.SetCount(1000); using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { //ado.GetDataTable pt.Execute(i => { db.GetDataTable(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id"); }, m => { }, "ado.DateTable 纯SQL写法"); //dapper var conn = db.GetConnection(); pt.Execute(i => { conn.Query<Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id").ToList(); }, m => { }, "dapper 纯SQL写法"); //sqlSugar pt.Execute(i => { db.Sqlable().Form("Student", "s") .Join("School","sc","sc.id","s.sch_id",JoinType.INNER) .Join("subject","sb","sb.sid","s.id",JoinType.LEFT).SelectToList<Models.Student>("*"); }, m => { }, "sqlSugar SQL语法糖"); //sqlSugar pt.Execute(i => { db.SqlQuery<Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id").ToList(); }, m => { }, "sqlSugar 纯SQL写法"); } using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities()) { //EF pt.Execute(i => { db.ExecuteStoreQuery<WebTest.TestLib.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id"); }, m => { }, "EF4.0+sql05 纯SQL写法"); } 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(10000); using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { //ado.GetDataTable pt.Execute(i => { db.GetDataTable(@"select * from (select s.*,row_number() over (order by s.id asc,s.name desc) r from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id where s.id>@id1 and s.id>@id2) t where t.r between @b and @e", new SqlParameter("@id1", "1"), new SqlParameter("@id2", "2"), new SqlParameter("@b", "11"), new SqlParameter("@e", "20")); }, m => { }, "ado.DateTable 纯SQL写法"); //dapper var conn = db.GetConnection(); pt.Execute(i => { conn.Query <Models.Student>(@"select * from (select s.*,row_number() over (order by s.id asc,s.name desc) r from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id where s.id>@id1 and s.id>@id2) t where t.r between @b and @e", new { id1 = 1, id2 = 2, b = 11, e = 20 }).ToList(); }, m => { }, "dapper 纯SQL写法"); //sqlSugar pt.Execute(i => { db.Sqlable().From("Student", "s") .Join("School", "sc", "sc.id", "s.sch_id", JoinType.INNER) .Join("subject", "sb", "sb.sid", "s.id", JoinType.LEFT).Where("s.id>@id1").Where("s.id>@id2") .SelectToPageList <Models.Student>("s.*", "s.id asc,s.name desc", 2, 10, new { id1 = 1, id2 = 2 }); }, m => { }, "sqlSugar SQL语法糖"); } using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities()) { //EF pt.Execute(i => { var reval = (from s in db.Student join sc in db.School on s.sch_id equals sc.id join sb in db.Subject on s.id equals sb.sid into ssb from sb2 in ssb.DefaultIfEmpty() select new { s.id, s.name, s.sch_id, s.sex }).Where(c => c.id > 1).Where(c => c.id > 2).OrderBy(c => c.id).ThenByDescending(c => c.name).Skip(10).Take(10).ToList(); }, m => { }, "EF4.0+sql05 LINQ TO SQL"); } GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }
public List <DiscountDTO> GetList(out int total, DiscountSearchDTO req) { var companyId = OperatorProvider.Provider.GetCurrent().CompanyId.ToInt(); using (var db = new SqlSugarClient(Connection)) { string order = "s1.Id desc"; var data = db.Sqlable() .From <R_Discount>("s1") .Join <R_Restaurant>("s2", "s2.IsDelete = 0 and s1.R_Restaurant_Id", "s2.Id", JoinType.Left) .Join <R_Market>("s3", "s3.IsDelete = 0 and s1.R_Market_Id", "s3.Id", JoinType.Left) .Join <R_Area>("s4", " s4.IsDelete = 0 and s1.R_Area_Id", "s4.Id", JoinType.Left) .Where(" s1.IsDelete = 0 "); data = data.Where("s1.R_Company_Id=" + companyId); if (!string.IsNullOrEmpty(req.Name)) { data = data.Where("s1.Name like '%" + req.Name + "%'"); } if (req.Restaurant > 0) { data = data.Where("s2.Id=" + req.Restaurant); } if (!string.IsNullOrEmpty(req.StartDate)) { data = data.Where("s1.StartDate>='" + req.StartDate + "'"); } if (!string.IsNullOrEmpty(req.EndDate)) { data = data.Where("s1.EndDate<='" + req.EndDate + "'"); } if (!string.IsNullOrEmpty(req.Sort)) { if (req.Sort.Equals("id", StringComparison.OrdinalIgnoreCase)) { order = "s1.Id desc"; } else { order = string.Format("{0} {1}", req.Sort, req.Order); } } total = data.Count(); List <DiscountDTO> list = data.SelectToPageList <DiscountDTO>( @"s1.Id,s1.Name,s3.Name as Market,s2.Name as Restaurant, s4.Name as Area,s1.IsEnable,s1.StartDate,s1.EndDate", order, (req.offset / req.limit) + 1, req.limit, null); return(list); } }
/// <summary> /// 查询人员状态清单 /// </summary> /// <param name="vguid"></param> /// <returns></returns> public List <CS_Master_2> ApprovalStatusSelect() { using (SqlSugarClient dbMsSql = SugarDao_MsSql.GetInstance()) { var list = dbMsSql.Queryable <CS_Master_1>().Where(i => i.MasterID == "ID10017").ToList(); var master2 = dbMsSql.Sqlable().From <CS_Master_2>("master2").Join <CS_Master_1>("master1", "master1.VGUID", "master2.VGUID", JoinType.Inner).Where("master1.VGUID=" + "'" + list[0].VGUID + "'").SelectToList <CS_Master_2>("master2.*"); return(master2); } }
public List <ReportListDTO> GetReportList() { using (var db = new SqlSugarClient(Connection)) { var data = db.Sqlable().From("pobb", "s1") .Where("s1.pobbsslb='05' and s1.pobbbzs0='Y'") .SelectToList <ReportListDTO>("s1.pobbid00 as Id,s1.pobbmc00 as Name,s1.pobbsslb as Category"); return(data); } }
protected void Page_Load(object sender, EventArgs e) { using (SqlSugarClient db = SugarDao.GetInstance()) { db.SerializerDateFormat = "yyyy-mm/dd"; var jsonStr = db.Queryable <INSERTTEST>().OrderBy("id").Take(1).ToJson(); var jsonStr2 = db.Sqlable().From <INSERTTEST>("t").SelectToJson(" top 1 *"); var jsonStr3 = db.SqlQueryJson("select top 1 * from InsertTest"); } }
protected void Page_Load(object sender, EventArgs e) { using (SqlSugarClient db = SugarDao.GetInstance()) { db.SerializerDateFormat = "yyyy-mm/dd"; var jsonStr = db.Queryable <InsertTest>().OrderBy("id").Take(1).ToJson(); var jsonStr2 = db.Sqlable().From <InsertTest>("t").SelectToPageList <InsertTest>("*", "id", 1, 1);//取第一条 var jsonStr3 = db.SqlQueryJson("select * from InsertTest limit 0,1 "); } }
public List <PayMethodListDTO> GetParents() { using (var db = new SqlSugarClient(Connection)) { var data = db.Sqlable().From <R_PayMethod>("s1") .Where("s1.Pid=0 and s1.IsDelete=0") .SelectToList <PayMethodListDTO>("s1.*"); return(data); } }
public void Init() { Console.WriteLine("启动SerializerDateFormat.Init"); using (SqlSugarClient db = SugarDao.GetInstance()) { db.SerializerDateFormat = "yyyy-mm/dd"; var jsonStr = db.Queryable <InsertTest>().OrderBy("id").Take(1).ToJson(); var jsonStr2 = db.Sqlable().From <InsertTest>("t").SelectToJson(" top 1 *"); var jsonStr3 = db.SqlQueryJson("select top 1 * from InsertTest"); } }
public List <PackageListDTO> GetList() { using (var db = new SqlSugarClient(Connection)) { List <PackageListDTO> list = new List <PackageListDTO>(); var data = db.Sqlable().From <R_Package>("s1").Where(" s1.IsDelete = 0 "); list = data.SelectToList <PackageListDTO>("s1.*"); //list = data.SelectToPageList<PackageListDTO>( // "s1.*", order, (req.offset / req.limit) + 1, req.limit, null); return(list); } }
protected void Page_Load(object sender, EventArgs e) { using (SqlSugarClient db = SugarDao.GetInstance()) { db.PageModel = PageModel.Offset; var list = db.Queryable <Student>().OrderBy("id").Skip(0).Take(2).ToList(); var list1 = db.Sqlable().From <Student>("t").SelectToPageList <Student>("*", "id", 1, 2); List <School> dataPageList = db.Sqlable() .From("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); db.PageModel = PageModel.RowNumber; var list2 = db.Queryable <Student>().OrderBy("id").Skip(0).Take(2).ToList(); var list22 = db.Sqlable().From <Student>("t").SelectToPageList <Student>("*", "id", 1, 2); } }
protected void Page_Load(object sender, EventArgs e) { PerformanceTest pt = new PerformanceTest(); pt.SetCount(1000); using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { //ado.GetDataTable pt.Execute(i => { db.GetDataTable(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id"); }, m => { }, "ado.DateTable 纯SQL写法"); //dapper var conn = db.GetConnection(); pt.Execute(i => { conn.Query <Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id").ToList(); }, m => { }, "dapper 纯SQL写法"); //sqlSugar pt.Execute(i => { db.Sqlable().Form("Student", "s") .Join("School", "sc", "sc.id", "s.sch_id", JoinType.INNER) .Join("subject", "sb", "sb.sid", "s.id", JoinType.LEFT).SelectToList <Models.Student>("*"); }, m => { }, "sqlSugar SQL语法糖"); //sqlSugar pt.Execute(i => { db.SqlQuery <Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id").ToList(); }, m => { }, "sqlSugar 纯SQL写法"); } using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities()) { //EF pt.Execute(i => { db.ExecuteStoreQuery <WebTest.TestLib.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id"); }, m => { }, "EF4.0+sql05 纯SQL写法"); } GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }
public List <DayMarketStatistics> GetDayStallStatistics(List <int> UserIds, DateTime?date) { using (var db = new SqlSugarClient(Connection)) { var operatorUser = OperatorProvider.Provider.GetCurrent(); date = date.Value == null ? DateTime.Now : date; List <DayMarketStatistics> res = new List <DayMarketStatistics>(); var queryable = db.Queryable <R_OrderPayRecord>() .JoinTable <R_Order>((s1, s2) => s1.R_Order_Id == s2.Id) .Where("DATEDIFF(day,BillDate,@Date)=0 and s2.R_Restaurant_Id=@Restaurant_Id", new { Date = date, Restaurant_Id = operatorUser.DepartmentId }); if (UserIds.Any()) { queryable = queryable.Where(p => UserIds.Contains(p.CreateUser)); } var payRecordList = queryable.Select <OrderPayRecordStatistics>("s1.*,s2.R_Market_Id as MarketId").ToList(); //符合条件的账务列表 if (payRecordList.Any()) { var orderGroupIds = payRecordList.GroupBy(p => p.R_Order_Id).Select(p => p.Key).ToArray(); var userGroupIds = string.Join(",", payRecordList.GroupBy(p => p.CreateUser).Select(p => p.Key).ToArray()); var userList = db.Sqlable().From("czdm", "s1").Where("s1.Id in (" + userGroupIds + ")") .SelectToList <UserDto>("s1.czdmmc00 as UserName,s1.Id as UserId", null); var marketGroupList = db.Queryable <R_Market>() .JoinTable <R_Order>((s1, s2) => s1.Id == s2.R_Market_Id) .Where <R_Order>((s1, s2) => orderGroupIds.Contains(s2.Id)) .GroupBy("s1.Id,s1.Name").Select("s1.Id,s1.Name").ToList(); if (marketGroupList.Any()) { foreach (var item in marketGroupList) { DayMarketStatistics model = new DayMarketStatistics(); model.MarketId = item.Id; model.MarketName = item.Name; model.Date = date.Value.ToString("yyyy-MM-dd"); model.UserList = (from pay in payRecordList join use in userList on pay.CreateUser equals use.UserId where pay.MarketId == item.Id group new { pay, use } by new { pay.MarketId, use.UserId, use.UserName } into g select new UserDayMarketStatistics { UserId = g.Key.UserId, UserName = g.Key.UserName, Total = g.Sum(t => t.pay.PayAmount) }).ToList(); model.Total = model.UserList.Sum(p => p.Total); res.Add(model); } } } return(res); } }
/// <summary> /// 根据订单id获得订单实体 /// </summary> /// <param name="orderId">订单id</param> /// <returns>订单实体类</returns> public CheckOutOrderDTO GetOrderById(int orderId) { using (var db = new SqlSugarClient(Connection)) { var data = db.Sqlable() .From <R_Order>("s1") .Where("s1.Id=" + orderId) .SelectToList <CheckOutOrderDTO>("s1.*, OrderTypeName = ISNULL((SELECT Name FROM dbo.ExtendItems WHERE Id = s1.OrderType), ''),RestaurantName=(select name from r_restaurant where id=s1.R_Restaurant_Id),MarketName=(select name from r_market where id=s1.R_Market_Id)") .FirstOrDefault(); return(data); } }
/// <summary> /// 根据订单id获得订单台号列表 /// </summary> /// <param name="orderId">订单id</param> /// <returns>订单台号实体类集合</returns> public List <CheckOutOrderTableDTO> GetOrderTableListBy(int orderId) { using (var db = new SqlSugarClient(Connection)) { var list = db.Sqlable() .From <R_OrderTable>("s1") .Join <R_Table>("s2", "s1.R_Table_Id", "s2.Id", JoinType.Left) .Where("s1.R_Order_Id=" + orderId + " AND s1.IsCheckout!=1 ") .SelectToList <CheckOutOrderTableDTO>(@"s1.*, s2.Name, s2.SeatNum, s2.Describe, s2.CythStatus, s2.ServerRate, s2.R_Area_Id"); return(list); } }
public JsonResult DataChild(GridSearchParams pars) { using (SqlSugarClient db = SugarDao.GetInstance()) { if (pars.sortdatafield == null) { //默认按id降序 pars.sortdatafield = "id"; pars.sortorder = "desc"; } Sqlable sable = db.Sqlable().Form <GridTable>("g"); //查询表的sqlable对象 var model = JQXGrid.GetWidgetsSource <Models.GridTable>(sable, pars, "*"); //根据grid的参数自动查询 return(Json(model, JsonRequestBehavior.AllowGet)); } }
public List <DiscountDTO> GetList() { using (var db = new SqlSugarClient(Connection)) { List <DiscountDTO> list = db.Sqlable() .From <R_Discount>("s1") .Join <R_Restaurant>("s2", "s1.R_Restaurant_Id", "s2.Id", JoinType.Left) .Join <R_Market>("s3", "s1.R_Market_Id", "s3.Id", JoinType.Left) .Join <R_Area>("s4", "s1.R_Area_Id", "s4.Id", JoinType.Left) .SelectToList <DiscountDTO>(@"s1.Id,s1.Name,s3.Name as Market, s2.Name as Restaurant,s4.Name as Area,s1.IsEnable"); return(list); } }
public List <PayMethodListDTO> GetList() { using (var db = new SqlSugarClient(Connection)) { string order = "s1.Id asc"; List <PayMethodListDTO> list = new List <PayMethodListDTO>(); var data = db.Sqlable().From <R_PayMethod>("s1") .Join <R_PayMethod>("s2", "s1.Pid", "s2.Id", JoinType.Left) .Where("s1.IsDelete = 0").OrderBy(order); list = data.SelectToList <PayMethodListDTO>( "s1.*,s2.Name as ParentName"); return(list); } }
public List <WeixinPrintListDTO> GetWeixinPrints(WeixinPrintSearchDTO req, out int total) { using (var db = new SqlSugarClient(Connection)) { int totalCount = 0; string order = "s1.Id desc"; List <WeixinPrintListDTO> list = new List <WeixinPrintListDTO>(); if (!string.IsNullOrEmpty(req.Sort)) { if (req.Sort.Equals("id", StringComparison.OrdinalIgnoreCase)) { order = "s1.Id desc"; } else { order = string.Format("{0} {1}", req.Sort, req.Order); } } //var data = db.Queryable<R_WeixinPrint>() // .JoinTable<R_Restaurant>((s1, s2) => s1.R_Restaurant_Id == s2.Id) // .JoinTable<Printer>((s1,s3)=>s1.Print_Id==s3.Id) // .Select<R_Restaurant, Printer, WeixinPrintListDTO >((s1, s2 ,s3) => new WeixinPrintListDTO() // { // Id=s1.Id, // R_Restaurant_Id=s2.Id, // Name=s1.Name, // RestaurantName=s1.Name, // Print_Id=s3.Id, // PrintName=s3.Name // }); var data = db.Sqlable() .From <R_WeixinPrint>("s1") .Join <R_Restaurant>("s2", "s1.R_Restaurant_Id", "s2.Id", JoinType.Left) .Join <Printer>("s3", "s1.Print_Id", "s3.Id", JoinType.Left); data = data.Where("s1.PrintType=" + (int)req.PrintType); if (req.Restaurant > 0) { data = data.Where("s1.R_Restaurant_Id=" + req.Restaurant); } totalCount = data.Count(); list = data.SelectToPageList <WeixinPrintListDTO>($@" s1.*,s2.Name as RestaurantName,s3.Name as PrintName", order, (req.offset / req.limit) + 1, req.limit, null); //list = data.Skip(req.offset).Take(req.limit).OrderBy(order).ToList(); total = totalCount; return(list); } }
public List <PackageListDTO> GetList(out int total, PackageSearchDTO req) { var companyId = OperatorProvider.Provider.GetCurrent().CompanyId.ToInt(); using (var db = new SqlSugarClient(Connection)) { List <PackageListDTO> list = new List <PackageListDTO>(); int totalCount = 0; string order = "s1.Id desc"; var data = db.Sqlable().From <R_Package>("s1").Where(" s1.IsDelete = 0 "); data = data.Where("s1.R_Company_Id=" + companyId); if (!string.IsNullOrEmpty(req.Name)) { data = data.Where("s1.Name like '%" + req.Name + "%'"); } if (req.MinPrice > 0) { data = data.Where("s1.Price>=" + req.MinPrice); } if (req.MaxPrice > 0) { data = data.Where("s1.Price <= " + req.MaxPrice); } if (!string.IsNullOrEmpty(req.Sort)) { if (req.Sort.Equals("id", StringComparison.OrdinalIgnoreCase)) { order = "s1.Id desc"; } else { order = string.Format("{0} {1}", req.Sort, req.Order); } } totalCount = data.Count(); list = data.SelectToPageList <PackageListDTO>( "s1.*", order, (req.offset / req.limit) + 1, req.limit, null); total = totalCount; return(list); } }
public List <TableListDTO> GetReseverChoseList(TableChoseSearchDTO req) { using (var db = new SqlSugarClient(Connection)) { string date = req.ReverDate.ToString("yyyyMMdd"); List <TableListDTO> list = new List <TableListDTO>(); var data = db.Sqlable().From <R_Table>("s1"); if (req.RestaurantId > 0) { data = data.Where("s1.R_Restaurant_Id=" + req.RestaurantId); } if (req.AreaId > 0) { data = data.Where("s1.R_Area_Id=" + req.AreaId); } data = data.Where("s1.IsDelete=0"); list = data.SelectToList <TableListDTO>("s1.*,s1.R_Area_Id as AreaId"); //取已存在的预订记录 var records = db.SqlQuery <BookingTableDTO>($@" select p2.R_Table_Id as TableId, p2.R_Order_Id as OrderId, p1.R_Market_Id as MarketId, convert(varchar(8), p1.ReserveDate, 112) as BookingDate from R_Order p1 inner join R_OrderTable p2 on p1.Id=p2.R_Order_Id where p1.CyddStatus={(int)CyddStatus.预定} and p1.R_Market_Id={req.Market} and convert(varchar(8), p1.ReserveDate, 112) = '{date}'"); var ids = records.Select(x => x.TableId).ToArray(); if (req.CurrentReservedOrderId > 0)//过滤非当前预订单关联的TableId { ids = records .Where(x => x.OrderId != req.CurrentReservedOrderId) .Select(x => x.TableId).ToArray(); } list = list.Where(x => !ids.Contains(x.Id)).ToList(); return(list); } }
protected void Page_Load(object sender, EventArgs e) { using (SqlSugarClient db = SugarDaoFilter.GetInstance())//开启数据库连接 { //设置走哪个过滤器 db.CurrentFilterKey = "role"; //queryable var list = db.Queryable <Student>().ToList(); //通过全局过滤器对需要权限验证的数据进行过滤 //相当于db.Queryable<Student>().Where("id=@id",new{id=1}) //sqlable var list2 = db.Sqlable().From <Student>("s").SelectToList <Student>("*"); //同上 //sqlQuery var list3 = db.SqlQuery <Student>("select * from Student WHERE 1=1"); //同上 } }
public void Init() { Console.WriteLine("启动Filter.Init"); using (SqlSugarClient db = SugarDaoFilter.GetInstance())//开启数据库连接 { //设置走哪个过滤器 db.CurrentFilterKey = "role"; //queryable var list = db.Queryable <Student>().ToList(); //通过全局过滤器对需要权限验证的数据进行过滤 //相当于db.Queryable<Student>().Where("id=@id",new{id=1}) //sqlable var list2 = db.Sqlable().From <Student>("s").SelectToList <Student>("*"); //同上 //sqlQuery var list3 = db.SqlQuery <Student>("select * from Student WHERE 1=1"); //同上 } }
public List <PayMethodListDTO> GetList(out int total, PayMethodSearchDTO req) { var companyId = OperatorProvider.Provider.GetCurrent().CompanyId.ToInt(); using (var db = new SqlSugarClient(Connection)) { int totalCount = 0; string order = string.Empty; List <PayMethodListDTO> list = new List <PayMethodListDTO>(); if (req.Sort.Equals("id", StringComparison.OrdinalIgnoreCase) || string.IsNullOrEmpty(req.Sort)) { order = "s1.Id desc"; } else { order = string.Format("{0} {1}", req.Sort, req.Order); } //var data = db.Queryable<R_PayMethod>() // .JoinTable<R_PayMethod>((s1, s2) => s1.Pid == s2.Id && s2.IsDelete == false) // .Select<PayMethodListDTO>("s1.*,s2.Name as ParentName"); var data = db.Sqlable().From <R_PayMethod>("s1") .Join <R_PayMethod>("s2", "s1.Pid", "s2.Id", JoinType.Left) .Where("s1.IsDelete = 0"); data = data.Where("(s1.IsSystem=1 or s1.R_Company_Id=" + companyId + ")"); if (!string.IsNullOrWhiteSpace(req.Name)) { data = data.Where("s1.Name like '%" + req.Name + "%'"); } if (req.Pid > 0) { data = data.Where("s1.Pid=" + req.Pid + ""); } totalCount = data.Count(); list = data.SelectToPageList <PayMethodListDTO>( "s1.*,s2.Name as ParentName", order, (req.offset / req.limit) + 1, req.limit, null); total = totalCount; return(list); } }
public List <OrderDetailCauseListDTO> GetList(out int total, OrderDetailCauseSearch req) { var companyId = OperatorProvider.Provider.GetCurrent().CompanyId.ToInt(); using (var db = new SqlSugarClient(Connection)) { int totalCount = 0; string order = string.Empty; List <OrderDetailCauseListDTO> list = new List <OrderDetailCauseListDTO>(); if (!string.IsNullOrEmpty(req.Sort)) { if (req.Sort.Equals("id", StringComparison.OrdinalIgnoreCase)) { order = "Id desc"; } else { order = string.Format("{0} {1}", req.Sort, req.Order); } } var data = db.Sqlable() .From <R_OrderDetailCause>("") .Where("IsDelete = 0 "); if (req.CauseType > 0) { data = data.Where("CauseType=" + req.CauseType); } totalCount = data.Count(); list = data.SelectToPageList <OrderDetailCauseListDTO>( @"*,(CASE CauseType WHEN 1 THEN '赠送' WHEN 2 THEN '退菜' end) AS CauseTypeName", order, (req.offset / req.limit) + 1, req.limit, null); total = totalCount; return(list); } }
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())) { //ado.GetDataTable pt.Execute(i => { db.GetDataTable(@"select * from (select s.*,row_number() over (order by s.id asc,s.name desc) r from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id where s.id>@id1 and s.id>@id2) t where t.r between @b and @e", new SqlParameter("@id1", "1"), new SqlParameter("@id2", "2"), new SqlParameter("@b", "11"), new SqlParameter("@e", "20")); }, m => { }, "ado.DateTable 纯SQL写法"); //dapper var conn = db.GetConnection(); pt.Execute(i => { conn.Query<Models.Student>(@"select * from (select s.*,row_number() over (order by s.id asc,s.name desc) r from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id left join dbo.Subject sb on sb.sid=s.id where s.id>@id1 and s.id>@id2) t where t.r between @b and @e", new { id1 = 1, id2 = 2, b = 11, e = 20 }).ToList(); }, m => { }, "dapper 纯SQL写法"); //sqlSugar pt.Execute(i => { db.Sqlable().Form("Student", "s") .Join("School", "sc", "sc.id", "s.sch_id", JoinType.INNER) .Join("subject", "sb", "sb.sid", "s.id", JoinType.LEFT).Where("s.id>@id1").Where("s.id>@id2") .SelectToPageList<Models.Student>("s.*", "s.id asc,s.name desc", 2, 10, new { id1=1,id2=2 }); }, m => { }, "sqlSugar SQL语法糖"); } using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities()) { //EF pt.Execute(i => { var reval = (from s in db.Student join sc in db.School on s.sch_id equals sc.id join sb in db.Subject on s.id equals sb.sid into ssb from sb2 in ssb.DefaultIfEmpty() select new { s.id, s.name, s.sch_id, s.sex }).Where(c=>c.id>1).Where(c=>c.id>2).OrderBy(c=>c.id).ThenByDescending(c=>c.name).Skip(10).Take(10).ToList(); }, m => { }, "EF4.0+sql05 LINQ TO SQL"); } 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; } }//关闭数据库连接 }
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; } }//关闭数据库连接 }