/// <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)
}
/// <summary>
/// 创建查询语句的动态参数
/// </summary>
/// <param name="queryBuilder"></param>
/// <param name="p"></param>
public static void BuildParameters(QueryBuilder queryBuilder, ref DynamicParameters p)
{
List <string> formatFieldList = new List <string>();
foreach (var w in queryBuilder.WhereItems)
{
var fields = Regex.Split(w.Field, "::", RegexOptions.IgnoreCase);
var field = fields.Length > 1 ? fields[1].Trim() : fields[0].Trim();
if (string.IsNullOrEmpty(w.renParameter))
{
var t = formatFieldList.Where(a => a.Contains(field));
if (t.Count() > 0)
{
field = field + t.Count();
}
}
else
{
field = w.renParameter;
}
formatFieldList.Add(field);
var firstVals = Regex.Split(w.FirstVal, "::", RegexOptions.IgnoreCase);
var firstVal = firstVals.Length > 1 ? firstVals[1] : firstVals[0];
if (w.OperationMethod == SqlOperators.OperationMethod.Between)
{
var secondVals = Regex.Split(w.SecondVal, "::", RegexOptions.IgnoreCase);
var secondVal = secondVals.Length > 1 ? secondVals[1] : secondVals[0];
p.Add(field, firstVal);
p.Add(field + "end", secondVal);
}
else if (w.OperationMethod == SqlOperators.OperationMethod.In)
{
var inParameterValList = w.FirstVal.Split(',');
string formatParam = string.Empty;
int index = 0;
foreach (var inVal in inParameterValList)
{
formatParam = field + (index == 0 ? "" : index.ToString());
p.Add(formatParam, inVal);
index++;
}
}
else
{
p.Add(field, SqlOperators.CreateParam(w.OperationMethod, firstVal));
}
}
}
/// <summary>
/// 获取实体列表
/// </summary>
/// <param name="queryBuliders">查询条件组</param>
/// <param name="sort"></param>
/// <returns></returns>
public virtual List <TEntity> GetList(List <QueryBuilder> queryBuliders, SortCondition sort = null)
{
try
{
var sql = new StringBuilder();
sql.AppendFormat("select * from {0} where 1=1", TableName);
var p = new DynamicParameters();
foreach (var queryBulider in queryBuliders.Where(queryBulider => queryBulider != null && queryBulider.WhereItems.Count > 0))
{
//重构前代码
//if (queryBulider.IsAnd)
//{
// sql.AppendFormat(" {0} {1}", queryBulider.IsGroupAnd ? "And" : "Or", SqlOperators.BuildWhere(queryBulider.WhereItems, queryBulider.IsAnd));
//}
//else
//{
// sql.AppendFormat(" {0} ( {1} )", queryBulider.IsGroupAnd ? "And" : "Or", SqlOperators.BuildWhere(queryBulider.WhereItems, queryBulider.IsAnd));
//}
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");
}
//测试输出生成的SQL语句
Console.WriteLine(sql.ToString());
System.Diagnostics.Debug.WriteLine(sql.ToString());
return(_mydb.Query <TEntity>(sql.ToString(), p).ToList());
}catch (Exception)
{
throw;
}
finally
{
//_mydb.Dispose();
}
// return _table.All().ToList();
}
/// <summary>
/// 查询当前表的分页数据
/// </summary>
/// <param name="queryBuliders">查询条件集合</param>
/// <param name="sort">排序条件</param>
/// <param name="intPageSize">分页大小</param>
/// <param name="intCurrentIndex">当前页</param>
/// <param name="total">总数</param>
/// <returns></returns>
public virtual List <TEntity> QueryPage(List <QueryBuilder> queryBuliders, SortCondition sort, int intPageSize, int intCurrentIndex, out int total)
{
try
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("select * from {0} where 1=1", TableName);
//if (queryBulider != null && queryBulider.WhereItems.Count > 0)
//{
// sql.AppendFormat(" where {0}", SqlOperators.BuildWhere(queryBulider.WhereItems, queryBulider.IsAnd));
//}
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 = _mydb.GetPageSelString(sql.ToString(), sort, "*", intPageSize, intCurrentIndex);
var countSql = string.Format("SELECT COUNT(*) DataCount FROM ({0}) T ", sql);
Console.WriteLine(pageSql.ToString());
System.Diagnostics.Debug.WriteLine(pageSql.ToString());
total = _mydb.ExecuteScalar <int>(countSql, p);
return(_mydb.Query <TEntity>(pageSql, p).ToList());
}
catch (Exception ex)
{
throw ex;
}
finally
{
//_mydb.Dispose();
}
//return db.Query<T>(sqlstr)
}