/// <summary>
/// 接近Sql的编程模式
/// </summary>
private void SqlableDemo()
{
using (var db = SugarDao.GetInstance())
{
//---------Sqlable,创建多表查询---------//
//多表查询
List <School> dataList = 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<@id")
.Where("1=1")//可以多个WHERE
.OrderBy("id")
.SelectToList <School /*新的Model我这里没有所以写的School*/>("st.*", new { id = 1 });
//多表分页
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);
//多表分页WHERE加子查询
List <School> dataPageList2 = 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 and s.id in (select 1 )" /*这里面写子查询都可以*/)
.SelectToPageList <School>("st.*", "s.id", 1, 10);
//--------转成List Dynmaic 或者 Json-----//
//不分页
var list1 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.Inner).SelectToDynamic("*", new { id = 1 });
var list2 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.Inner).SelectToJson("*", new { id = 1 });
var list3 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.Inner).SelectToDataTable("*", new { id = 1 });
//分页
var list4 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.Inner).SelectToPageDynamic("s.*", "l.id", 1, 10, new { id = 1 });
var list5 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.Inner).SelectToPageTable("s.*", "l.id", 1, 10, new { id = 1 });
var list6 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.Inner).SelectToPageDynamic("s.*", "l.id", 1, 10, new { id = 1 });
//--------拼接-----//
Sqlable sable = db.Sqlable().From <Student>("s").Join <School>("l", "s.sch_id", "l.id", JoinType.Inner);
string name = "a";
int id = 1;
if (!string.IsNullOrEmpty(name))
{
sable = sable.Where("s.name=@name");
}
if (!string.IsNullOrEmpty(name))
{
sable = sable.Where("s.id=@id or s.id=100");
}
if (id > 0)
{
sable = sable.Where("l.id in(1)");//where加子查询
}
var pars = new { id = id, name = name };
int pageCount = sable.Count(pars);
var list7 = sable.SelectToPageList <Student>("s.*", "l.id desc", 1, 20, pars);
}
}
/// <summary>
/// 处理SQL条件
/// </summary>
/// <param name="orderInfoQuery"></param>
/// <param name="pars"></param>
/// <returns></returns>
private string HandleQueryBySqlable(OrderInfoQuery orderInfoQuery, out object pars)
{
Dictionary <string, object> paramsDictionary = new Dictionary <string, object>();
var sqlTable = new Sqlable();
sqlTable.Sql = new StringBuilder();
if (!string.IsNullOrWhiteSpace(orderInfoQuery.Buyers))
{
sqlTable = sqlTable.Where("buyers_name = @Buyers");
paramsDictionary.Add("Buyers", orderInfoQuery.Buyers);
}
if (!string.IsNullOrWhiteSpace(orderInfoQuery.BuyersPhone))
{
sqlTable = sqlTable.Where("buyers_phone = @BuyersPhone");
paramsDictionary.Add("BuyersPhone", orderInfoQuery.BuyersPhone);
}
if (!string.IsNullOrWhiteSpace(orderInfoQuery.OrderNumber))
{
sqlTable = sqlTable.Where("order_number = @OrderNumber");
paramsDictionary.Add("OrderNumber", orderInfoQuery.OrderNumber);
}
if (!string.IsNullOrWhiteSpace(orderInfoQuery.WaybillNumber))
{
sqlTable = sqlTable.Where("waybill_number = @WaybillNumber");
paramsDictionary.Add("WaybillNumber", orderInfoQuery.WaybillNumber);
}
if (!string.IsNullOrWhiteSpace(orderInfoQuery.OtherOrderNumber))
{
sqlTable = sqlTable.Where("other_transaction_code = @OtherOrderNumber");
paramsDictionary.Add("OtherOrderNumber", orderInfoQuery.OtherOrderNumber);
}
if (orderInfoQuery.IsUse.HasValue)
{
sqlTable = sqlTable.Where("is_use = @IsUse");
paramsDictionary.Add("IsUse", orderInfoQuery.IsUse);
}
if (orderInfoQuery.IsCompleteComment.HasValue)
{
sqlTable = sqlTable.Where("is_complete_comment = @IsCompleteComment");
paramsDictionary.Add("IsCompleteComment", orderInfoQuery.IsCompleteComment);
}
if (orderInfoQuery.OrderType.HasValue)
{
sqlTable = sqlTable.Where("order_type = @OrderType");
paramsDictionary.Add("OrderType", (int)orderInfoQuery.OrderType.Value);
}
if (orderInfoQuery.OrderRightsStatus.HasValue)
{
sqlTable = sqlTable.Where("rights_status = @OrderRightsStatus");
paramsDictionary.Add("OrderRightsStatus", (int)orderInfoQuery.OrderRightsStatus.Value);
}
if (orderInfoQuery.OrderLogisticsMode.HasValue)
{
sqlTable = sqlTable.Where("logistics_type = @OrderLogisticsMode");
paramsDictionary.Add("OrderLogisticsMode", (int)orderInfoQuery.OrderLogisticsMode.Value);
}
if (orderInfoQuery.OrderInfoStatus.HasValue)
{
sqlTable = sqlTable.Where("status = @Status");
paramsDictionary.Add("Status", (int)orderInfoQuery.OrderInfoStatus.Value);
}
if (orderInfoQuery.OrderPayType.HasValue)
{
sqlTable = sqlTable.Where("pay_type = @PayType");
paramsDictionary.Add("PayType", (int)orderInfoQuery.OrderPayType.Value);
}
if (orderInfoQuery.RealPriceLowerLimit.HasValue)
{
sqlTable = sqlTable.Where("real_price >= @RealPriceLowerLimit");
paramsDictionary.Add("RealPriceLowerLimit", orderInfoQuery.RealPriceLowerLimit);
}
if (orderInfoQuery.RealPriceUpperLimit.HasValue)
{
sqlTable = sqlTable.Where("real_price <= @RealPriceUpperLimit");
paramsDictionary.Add("RealPriceUpperLimit", orderInfoQuery.RealPriceUpperLimit);
}
if (orderInfoQuery.OrderTimeLowerLimit.HasValue)
{
sqlTable = sqlTable.Where("dete_created >= @OrderTimeLowerLimit");
paramsDictionary.Add("OrderTimeLowerLimit", orderInfoQuery.OrderTimeLowerLimit.Value);
}
if (orderInfoQuery.OrderTimeUpperLimit.HasValue)
{
sqlTable = sqlTable.Where("dete_created <= @OrderTimeUpperLimit");
paramsDictionary.Add("OrderTimeUpperLimit", orderInfoQuery.OrderTimeUpperLimit.Value);
}
pars = paramsDictionary;
foreach (var item in sqlTable.Where)
{
sqlTable.Sql.Append(item);
}
return(sqlTable.Sql.ToString().TrimStart(" AND".ToCharArray()));
}