/// <summary>
/// 自定义查询实体列表
/// </summary>
/// <param name="sqlCustom">指定SQL查询语句，适合多表关联，不包含Where</param>
/// <param name="queryBuliders">查询条件组</param>
/// <param name="sort">排序</param>
/// <returns></returns>
public virtual List <T> GetList <T>(string sqlCustom, List <QueryBuilder> queryBuliders, SortCondition sort = null)
{
try
{
var sql = new StringBuilder();
sql.AppendFormat(" {0} where 1=1", sqlCustom);
var p = new DynamicParameters();
foreach (var queryBulider in queryBuliders.Where(queryBulider => queryBulider != null && queryBulider.WhereItems.Count > 0))
{
SqlOperators.BuildParameters(queryBulider, ref p);
string whereSql = SqlOperators.BuildWhere(queryBulider.WhereItems, queryBulider.IsAnd);
//重构后代码
sql.AppendFormat(queryBulider.IsAnd ? " {0} {1}" : " {0} ( {1} )",
queryBulider.IsGroupAnd ? "And" : "Or",
whereSql == string.Empty ? "1=1" : whereSql);
}
if (sort != null)
{
sql.AppendFormat(" Order by {0} {1} ", sort.Field, sort.ListSortDirection == ListSortDirection.Ascending ? "Asc" : "Desc");
}
Console.WriteLine(sql.ToString());
System.Diagnostics.Debug.WriteLine(sql.ToString());
return(_mydb.Query <T>(sql.ToString(), p).ToList());
}catch (Exception)
{
throw;
}
finally
{
//_mydb.Dispose();
}
// return _table.All().ToList();
}
/// <summary>
/// 自定义获取分页实体列表
/// </summary>
/// <typeparam name="T">自定义实体</typeparam>
/// <param name="withTables">自定义 with table as （） 的查询语句</param>
/// <param name="sqlTable">自定义查询的 Table SQL 语句，不包含 Where条件</param>
/// <param name="queryBuliders">查询条件</param>
/// <param name="sort">排序</param>
/// <param name="intPageSize">分页大小</param>
/// <param name="intCurrentIndex">当前页</param>
/// <param name="total">总数</param>
/// <returns></returns>
public List <T> QueryPage <T>(string withTables, string sqlTable, List <QueryBuilder> queryBuliders, SortCondition sort, int intPageSize, int intCurrentIndex, out int total) where T : class
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat(" {0} where 1=1", sqlTable);
var p = new DynamicParameters();
foreach (var queryBulider in queryBuliders.Where(queryBulider => queryBulider != null && queryBulider.WhereItems.Count > 0))
{
SqlOperators.BuildParameters(queryBulider, ref p);
string whereSql = SqlOperators.BuildWhere(queryBulider.WhereItems, queryBulider.IsAnd);
//重构后代码
sql.AppendFormat(queryBulider.IsAnd ? " {0} {1}" : " {0} ( {1} )",
queryBulider.IsGroupAnd ? "And" : "Or", whereSql == string.Empty ? "1=1" : whereSql);
}
var pageSql = withTables + _mydb.GetPageSelString(sql.ToString(), sort, "*", intPageSize, intCurrentIndex);
var countSql = string.Format("SELECT COUNT(*) DataCount FROM ({0}) T ", sql);
Console.WriteLine(withTables + sql.ToString());
System.Diagnostics.Debug.WriteLine(withTables + sql.ToString());
total = _mydb.ExecuteScalar <int>(withTables + countSql, p);
return(_mydb.Query <T>(pageSql, p).ToList());
//return db.Query<T>(sqlstr)
}