/// <summary> /// 获取Where比较条件 /// </summary> /// <param name="whereCompare"></param> /// <param name="sampleRow"></param> /// <returns></returns> private string GetWhereCompare(string whereCompare, List <OrderByDictionary> orderByTypes, DataRow sampleRow, string unqueField, string unqueValue, bool isReverse = false, bool isEqual = false) { List <string> reval = new List <string>(); var ordersCount = orderByTypes.Count; /* * 排序筛选逻辑: * 例如: num desc, createTime asc * num>x * num=x and createTime<xx * num=x and createTime=xx and unqueField<unqueValue * 以此类推 */ //算出没有unqueField的部分 for (int i = 0; i < ordersCount; i++) { List <string> revalChild = new List <string>(); for (int j = 0; j <= i; j++) { var isLastEach = j == i; OrderByDictionary currentOrderType = orderByTypes[j]; var currentSymbol = isReverse ? currentOrderType.SymbolReverse : currentOrderType.Symbol; var fieldValue = sampleRow[currentOrderType.OrderByField]; fieldValue = FormatTimeByDataBase(fieldValue); if (isLastEach) { revalChild.Add(string.Format(" {0}{1}'{2}' ", currentOrderType.OrderByField, currentSymbol, fieldValue)); } else { revalChild.Add(string.Format(" {0}='{1}' ", currentOrderType.OrderByField, fieldValue)); } } reval.Add(" ( " + string.Join(" AND ", revalChild) + " ) "); } //算出unqueField的部分 List <string> revalChildLast = new List <string>(); for (int i = 0; i < ordersCount; i++) { OrderByDictionary currentOrderType = orderByTypes[i]; var fieldValue = sampleRow[currentOrderType.OrderByField]; fieldValue = FormatTimeByDataBase(fieldValue); revalChildLast.Add(string.Format(" {0}='{1}' ", currentOrderType.OrderByField, fieldValue)); } var isSymbol = isReverse ? ">" : "<"; if (isEqual) { isSymbol = isReverse ? ">=" : "<="; } revalChildLast.Add(string.Format("{0}{1}'{2}' ", unqueField, isSymbol, unqueValue)); reval.Add(" ( " + string.Join(" AND ", revalChildLast) + " ) "); //将unqueField部分和非unqueField部分进行OR拼接 return(string.Join(" OR ", reval)); }
/// <summary> /// 排序 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <param name="sortField"></param> /// <param name="orderByType"></param> /// <returns></returns> public static List <DataRow> OrderByDataRow(this IEnumerable <DataRow> list, List <OrderByDictionary> orderByTypes, OrderByDictionary orderByUnqueField) { orderByTypes.Add(orderByUnqueField); var dt = list.AsEnumerable().CopyToDataTable(); var guidType = typeof(Guid); var sqlGuidType = typeof(SqlGuid); System.Data.DataTable dtByConvertGuidToSqlGuid = new System.Data.DataTable(); foreach (DataColumn it in dt.Columns) { var isGuid = it.DataType == guidType; if (isGuid) { dtByConvertGuidToSqlGuid.Columns.Add(it.ColumnName, sqlGuidType); } else { dtByConvertGuidToSqlGuid.Columns.Add(it.ColumnName, it.DataType); } } //将dataTable中guid换转成sqlguid,这样排序才会和SQL一致 dtByConvertGuidToSqlGuid.Load(dt.CreateDataReader(), System.Data.LoadOption.OverwriteChanges); var view = dtByConvertGuidToSqlGuid.AsDataView(); view.Sort = string.Join(",", orderByTypes.Select(it => string.Format(" {0} {1} ", it.OrderByField, it.IsAsc ? "ASC" : "DESC"))); var reval = view.ToTable().AsEnumerable().ToList(); orderByTypes.Remove(orderByUnqueField); return(reval); }