/// <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); }