public static PagedResult <T> PagedQueryWithAction <T>(this IDbConnection db, int pageSize, int pageNumber, Action <SqlLam <T> > action = null) where T : class
{
var sqllam = new SqlLam <T>(db.GetAdapter());
if (action != null)
{
action(sqllam);
}
var countSqlam = Clone(sqllam).Count();
var countRet = db.Query <int>(countSqlam.SqlString, countSqlam.Parameters).FirstOrDefault();
//var sqlString = sqllam.SqlString;
//var param = sqllam.Parameters;
//string parameterString = GetParameterString(sqllam.Parameters);
var sqlstring = sqllam.QueryPage(pageSize, pageNumber);
var retlist = db.Query <T>(sqlstring, sqllam.Parameters);
// return new Tuple<IEnumerable<T>, int>(retlist,countRet);
return(new PagedResult <T>(retlist, countRet, pageSize, pageNumber));
}
public ActionResult List()
{
SqlLam <UserEntity> sql = new SqlLam <UserEntity>().Where(u => u.F_Id == "a" && u.F_NickName == "a");
sql.And(u => u.F_MobilePhone == "123").OrderBy(u => u.F_RoleId);
var data = new { SQL = sql.QueryPage(10, 2), Parameters = sql.Parameters };
return(Json(data, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// Delete
/// </summary>
/// <returns></returns>
public SqlLam <Users> GetSqlLamQueryPage()
{
Users u = new Users();
u.Sex = 2;
var sql = new SqlLam <Users>(adapter).Where(m => m.Money > 2000);
string sqlText = sql.QueryPage(10, 1);
var list = DBHelper.Query <Users>(sqlText, sql.Parameters);
return(sql);
}
public IEnumerable <TReturn> GetPageList <TReturn>(LayuiPaginationIn p)
{
int totalCount = 0;
sql.QueryPage(p.page, p.limit);
var reader = client.GetReader(sql.GetSql(), sql.GetParameters());
var list = reader.Read <TReturn>();
totalCount = reader.Read <int>().FirstOrDefault();
p.total = totalCount;
return(list);
}
public IEnumerable <dynamic> GetDynamicPageList <TResult>(PaginationIn p, SqlLam <TResult> sql)
{
p.WatchStart();
int totalCount = 0;
sql.QueryPage(p.page, p.rows);
var reader = client.GetReader(sql.GetSql(), sql.GetParameters());
var list = reader.Read();
totalCount = reader.Read <int>().FirstOrDefault();
p.records = totalCount;
p.WatchEnd();
return(list);
}
static void Main5(string[] args)
{
//GlobalConfig.UseDb(ProviderType.Oracle);
SqlLam <User> sql = new SqlLam <User>("u", ProviderType.SQLite);
sql.QueryPage(2).OrderBy(m => m.F_Email);
string sqlString = sql.GetSql();
Console.WriteLine(string.Format("SQL: {0}", sqlString));
var ps = sql.GetParameters();
foreach (var item in ps)
{
Console.WriteLine(string.Format("Key: {0}, Value: {1}", item.Key, item.Value));
}
//测试字典
Console.Read();
}
public static PagedResult <T> PagedQuery <T>(this IDbConnection db, int pageSize, int pageNumber, Expression <Func <T, bool> > whereExpression = null, Expression <Func <T, object> > groupByexpression = null, params Expression <Func <T, object> >[] orderbyExpressions)
where T : class
{
var sqllam = new SqlLam <T>(db.GetAdapter());
var countSqlam = new SqlLam <T>(db.GetAdapter());
if (whereExpression != null)
{
sqllam = sqllam.Where(whereExpression);
countSqlam = countSqlam.Where(whereExpression);
}
if (orderbyExpressions != null && orderbyExpressions.Length > 0)
{
sqllam = sqllam.OrderBy(orderbyExpressions);
}
if (groupByexpression != null)
{
sqllam = sqllam.GroupBy(groupByexpression);
}
countSqlam = countSqlam.Count();
var countRet = db.Query <int>(countSqlam.SqlString, countSqlam.Parameters).FirstOrDefault();
//var sqlString = sqllam.SqlString;
//var param = sqllam.Parameters;
//string parameterString = GetParameterString(sqllam.Parameters);
var sqlstring = sqllam.QueryPage(pageSize, pageNumber);
var retlist = db.Query <T>(sqlstring, sqllam.Parameters);
return(new PagedResult <T>(retlist, countRet, pageSize, pageNumber));
}