/// <summary>
/// 获取gridtable数据源
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sable"></param>
/// <param name="pars">查询参数</param>
/// <param name="selectFields">查询字段</param>
/// <param name="whereObj">参数条件</param>
/// <returns></returns>
public static JsonResultModel <T> GetWidgetsSource <T>(Sqlable sable, GridSearchParams pars, string selectFields = "*", object whereObj = null) where T : class
{
var query = System.Web.HttpContext.Current.Request.QueryString;
Type type = typeof(T);
string tableName = type.Name;
sable = sable.Where(BuildQuery(query));
string orderBy = null;
if (!string.IsNullOrEmpty(pars.sortorder))
{
orderBy = (pars.sortdatafield + " " + pars.sortorder);
}
else
{
orderBy = "getdate()";
}
var rows = sable.SelectToPageList <T>(selectFields, orderBy, pars.pagenum + 1, pars.pagesize, whereObj);
var result = new JsonResultModel <T>()
{
TotalRows = sable.Count(whereObj),
Rows = rows
};
return(result);
}
/// <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 (select top 10 id from school)");//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);
}
}