public ExeResEdm GetListByPage(string tableName, PageSerach <T> para)
{
var orderByStr = LambdaToSqlHelper <T> .GetSqlFromLambda(para.OrderBy).OrderbySql;
string whereSql = !string.IsNullOrEmpty(para.StrWhere) ? para.StrWhere : LambdaToSqlHelper <T> .GetWhereFromLambda(para.Filter, DataBaseType.NoSelect);
SearchParam searchParam = new SearchParam()
{
Orderby = orderByStr, PageIndex = para.PageIndex, PageSize = para.PageSize, TableName = tableName, StrWhere = whereSql,
};
ExeResEdm res = GetDTByPage(searchParam);
para.StrWhere = whereSql;
if (res.ErrCode == 0)
{
List <T> list = DtModelConvert <T> .DatatableToList((res.ExeModel as DataTable));
res.ExeModel = list.AsQueryable();
return(res);
}
else
{
return(res);
}
}
public ExeResEdm GetListByPage(string tableName, PageSerach <T> para, DBOperUser dbLogMsg = null)
{
var orderByStr = LambdaToSqlHelper <T> .GetSqlFromLambda(para.OrderBy).OrderbySql;
string whereSql = !string.IsNullOrEmpty(para.StrWhere) ? para.StrWhere : LambdaToSqlHelper <T> .GetWhereFromLambda(para.Filter, DBStoreType.NoSelect);
SearchParam searchParam = new SearchParam()
{
Orderby = orderByStr, PageIndex = para.PageIndex, PageSize = para.PageSize, TableName = tableName, StrWhere = whereSql,
};
ExeResEdm res = GetDTByPage(searchParam);
int curNum = 0;
if (res.ErrCode == 0)
{
List <T> list = DtModelConvert <T> .DatatableToList((res.ExeModel as DataTable));
res.ExeModel = list.AsQueryable();
curNum = list.Count();
res.ExeNum = searchParam.TotalCount;
}
WriteLogMsg(dbLogMsg, LogType.查询, "根据[" + DtModelConvert <T> .SerializeToString(searchParam) + "]获取了分页数据，返回了"
+ curNum + "/" + searchParam.TotalCount + "条记录", tableName);
return(res);
}
/// <summary>
/// 查询 list
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="unitOfWork"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static async Task <List <T> > Query <T>(IUnitOfWork unitOfWork, Expression <Func <T, bool> > whereLambda, SqlQuery sql = null) where T : class
{
var db = unitOfWork.DbConnection;
if (sql == null)
{
sql = SqlQuery <T> .Builder(unitOfWork);
}
sql._Sql = new StringBuilder(LambdaToSqlHelper <T> .GetWhereByLambda(whereLambda, unitOfWork.DbType));
var result = await db.QueryAsync <T>(sql.QuerySql, sql.Param);
return(result.ToList());
}
public static async Task <DataTable> QueryDataTable <T>(IUnitOfWork unitOfWork, Expression <Func <T, bool> > whereLambda, SqlQuery sql = null) where T : class
{
var db = unitOfWork.DbConnection;
if (sql == null)
{
sql = SqlQuery <T> .Builder(unitOfWork);
}
sql._Sql = new StringBuilder(LambdaToSqlHelper <T> .GetWhereByLambda(whereLambda, unitOfWork.DbType));
DataTable dataTable = new DataTable();
var result = await unitOfWork.DbConnection.ExecuteReaderAsync(sql.QuerySql, sql.Param);
dataTable.Load(result);
return(dataTable);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="unitOfWork"></param>
/// <param name="whereLambda"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static async Task <T> SingleOrDefault <T>(IUnitOfWork unitOfWork, Expression <Func <T, bool> > whereLambda, SqlQuery sql = null) where T : class
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
var db = unitOfWork.DbConnection;
if (sql == null)
{
sql = SqlQuery <T> .Builder(unitOfWork);
}
sql._Sql = new StringBuilder(LambdaToSqlHelper <T> .GetWhereByLambda(whereLambda, unitOfWork.DbType));
var result = await db.QueryFirstOrDefaultAsync <T>(sql.QuerySql);
return(result);
}
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="unitOfWork"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static async Task <int> Delete <T>(IUnitOfWork unitOfWork, Expression <Func <T, bool> > whereLambda, SqlQuery sql = null) where T : class
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
var db = unitOfWork.DbConnection;
if (sql == null)
{
sql = SqlQuery <T> .Builder(unitOfWork);
}
sql._Sql = new StringBuilder(LambdaToSqlHelper <T> .GetWhereByLambda(whereLambda, unitOfWork.DbType));
var result = await db.ExecuteAsync(sql.DeleteSql, sql.Param);
stopwatch.Stop();
return(result);
}
static void Main(string[] args)
{
/*
* Expression<Func<Person, bool>> expression = p => p.Name == "张三" && p.Name != "李四";
* MyVisitor myVisitor = new MyVisitor();
* myVisitor.Visit(expression);
* Console.WriteLine(myVisitor.GetSqlString());
*/
Expression <Func <Person, bool> > expression = p => p.Name == "张三" && p.Name != "李四";
Console.WriteLine(LambdaToSqlHelper.GetSqlFromExpression(expression));
/*
* var data = new List<ScoreClass>
* {
* new ScoreClass {CourseName = "数学", StudentName = "学生A", Score = 60},
* new ScoreClass {CourseName = "数学", StudentName = "学生B", Score = 65},
* new ScoreClass {CourseName = "数学", StudentName = "学生C", Score = 70},
* new ScoreClass {CourseName = "数学", StudentName = "学生D", Score = 75},
* new ScoreClass {CourseName = "数学", StudentName = "学生E", Score = 80},
* new ScoreClass {CourseName = "数学", StudentName = "学生F", Score = 81},
* new ScoreClass {CourseName = "数学", StudentName = "学生G", Score = 82},
* new ScoreClass {CourseName = "数学", StudentName = "学生H", Score = 83},
* new ScoreClass {CourseName = "数学", StudentName = "学生I", Score = 84}
* };
* // 按照Score降序排序取第一个（5条数据）
* var page = new PageRequest()
* {
* Direction = "desc",
* PageIndex = 1,
* PageSize = 5,
* SortBy = "Score"
* };
* var result = Pagination.DataPagination(data.AsQueryable(), page);
*
* Console.WriteLine($"分页结果：\n{string.Join("\n", result.Items.Select(e => $"{e.StudentName} {e.CourseName} {e.Score}"))}");
*/
}
