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;
}
}//关闭数据库连接
}
