/// <summary>
/// 执行分页查询
/// </summary>
/// <param name="sql">分页数据查询语句</param>
/// <param name="sql_dataCount">数据总数查询语句</param>
/// <param name="propConverter">返回实体属性转换委托</param>
/// <returns></returns>
public async Task <PagingResult <T> > ExcuteAsync(
string sql = @"SELECT * FROM {0} {1} ORDER BY {2} OFFSET {3} ROW FETCH NEXT {4} ROW ONLY",
string sql_dataCount = "SELECT COUNT(1) FROM {0} {1}",
Func <PropertyInfo, Object, Object> propConverter = null)
{
#region filters
#region custom
var WhereBuilder = new List <string>();
var WhereParameters = new List <SqlParameter>();
where?.Invoke(WhereBuilder, WhereParameters);
#endregion
#region createDate
if (AutoFilter_CreateDate)
{
#region 根据时间过滤
#region 大于等于 开始时间 && 小于等于 结束时间
if (value.startTime.HasValue && value.endTime.HasValue)
{
WhereBuilder.Add(" CreateDate >= '" + value.startTime.Value.ToString("yyyy-MM-dd") + " 00:00:00' AND CreateDate <= '" + value.endTime.Value.ToString("yyyy-MM-dd") + " 23:59:59'");
}
#endregion
#region 大于等于开始时间
else if (value.startTime.HasValue)
{
WhereBuilder.Add(" CreateDate >= '" + value.startTime.Value.ToString("yyyy-MM-dd") + " 00:00:00'");
}
#endregion
#region 小于等于结束时间
else if (value.endTime.HasValue)
{
WhereBuilder.Add(" CreateDate <= '" + value.endTime.Value.ToString("yyyy-MM-dd") + " 23:59:59'");
}
#endregion
#endregion
}
#endregion
var Where = WhereBuilder.Count > 0 ?
" WHERE " + string.Join(" AND ", WhereBuilder) :
string.Empty;
#endregion
#region orderBy
var OrderBy = Columns[TableName][0] + " DESC ";
if (!string.IsNullOrWhiteSpace(value.orderby))
{
if (Columns[TableName].Contains(value.orderby) || OrderByFieldsExtension.Contains(value.orderby))
{
OrderBy = " " + value.orderby + " " + (!value.asc.Value ? "DESC" : "ASC");
}
}
#endregion
sql_dataCount = string.Format(sql_dataCount, TableName, Where);
var total = 0;
sql = string.Format(sql, TableName, Where, OrderBy,
value.skip,
value.take);
var entities = new List <T>();
var entityType = typeof(T);
using (var connection = db.Database.GetDbConnection())
{
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
using (var command = connection.CreateCommand())
{
command.CommandText = sql_dataCount;
command.Parameters.AddRange(WhereParameters.ToArray());
var _total = command.ExecuteScalar();
if (_total != null)
{
total = int.Parse(_total.ToString());
}
}
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
//fix error:The SqlParameter is already contained by another SqlParameterCollection.
var clonedParameters = new SqlParameter[WhereParameters.Count];
for (int i = 0, j = WhereParameters.Count; i < j; i++)
{
clonedParameters[i] = (SqlParameter)((ICloneable)WhereParameters[i]).Clone();
}
command.Parameters.AddRange(clonedParameters);
using (var reader = await command.ExecuteReaderAsync())
{
while (reader.Read())
{
var item = new T();
for (var i = 0; i < Columns[TableName].Count; i++)
{
var PropertyName = Columns[TableName][i];
object PropertyValue = null;
try
{
PropertyValue = reader[PropertyName];
}
catch { }
if (PropertyValue != null && PropertyValue != DBNull.Value)
{
var Property = entityType.GetProperty(PropertyName);
try
{
if (propConverter != null)
{
var _PropertyValue = propConverter.Invoke(Property, PropertyValue);
Property.SetValue(item, _PropertyValue);
}
else
{
Property.SetValue(item, PropertyValue);
}
}
catch (Exception ex)
{
throw ex;
}
}
}
entities.Add(item);
}
}
command.Parameters.Clear();
}
}
var result = new PagingResult <T>(entities,
total, value.skip.Value, value.take.Value);
return(result);
}
public async Task <PagingResult <T> > Excute(
string sql = @"SELECT * FROM {0} {1} ORDER BY {2} OFFSET {3} ROW FETCH NEXT {4} ROW ONLY",
string sql_dataCount = "SELECT COUNT(1) FROM {0} {1}")
{
#region filters
#region custom
var WhereBuilder = new List <string>();
var WhereParameters = new List <SqlParameter>();
where?.Invoke(WhereBuilder, WhereParameters);
#endregion
#region createDate
if (AutoFilter_CreateDate)
{
#region 根据时间过滤
#region 大于等于 开始时间 && 小于等于 结束时间
if (value.startTime.HasValue && value.endTime.HasValue)
{
WhereBuilder.Add(" CreateDate >= '" + value.startTime.Value.ToString("yyyy-MM-dd") + " 00:00:00' AND CreateDate <= '" + value.endTime.Value.ToString("yyyy-MM-dd") + " 23:59:59'");
}
#endregion
#region 大于等于开始时间
else if (value.startTime.HasValue)
{
WhereBuilder.Add(" CreateDate >= '" + value.startTime.Value.ToString("yyyy-MM-dd") + " 00:00:00'");
}
#endregion
#region 小于等于结束时间
else if (value.endTime.HasValue)
{
WhereBuilder.Add(" CreateDate <= '" + value.endTime.Value.ToString("yyyy-MM-dd") + " 23:59:59'");
}
#endregion
#endregion
}
#endregion
var Where = WhereBuilder.Count > 0 ?
" WHERE " + string.Join(" AND ", WhereBuilder) :
string.Empty;
#endregion
#region orderBy
var OrderBy = " ID DESC ";
if (!string.IsNullOrWhiteSpace(value.orderby) &&
(Columns[TableName].Contains(value.orderby) ||
OrderByFieldsExtension.Contains(value.orderby)))
{
OrderBy = " " + value.orderby + " " + (!value.asc.Value ? "DESC" : "ASC");
}
#endregion
sql_dataCount = string.Format(sql_dataCount, TableName, Where);
var total = 0;
sql = string.Format(sql, TableName, Where, OrderBy, value.skip, value.take);
var entities = new List <T>();
using (var connection = db.Database.GetDbConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = sql_dataCount;
command.Parameters.AddRange(WhereParameters.ToArray());
var _total = command.ExecuteScalar();
if (_total != null)
{
total = int.Parse(_total.ToString());
}
}
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
command.Parameters.AddRange(WhereParameters.ToArray());
using (var reader = await command.ExecuteReaderAsync())
{
while (reader.Read())
{
var item = new T();
for (var i = 0; i < Columns[TableName].Count; i++)
{
if (reader[Columns[TableName][i]] != DBNull.Value)
{
var Property = item.GetType().GetProperty(
Columns[TableName][i]);
var PropertyValue = reader[Columns[TableName][i]];
Property.SetValue(item, PropertyValue);
}
}
entities.Add(item);
}
}
}
}
var result = new PagingResult <T>(entities,
total, value.skip.Value, value.take.Value);
return(result);
}
