/// <summary> /// 查询分页数据(包括Realtime) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="queryDbCommand">查询QueryDB数据的Command</param> /// <param name="realtimeDbCommand">查询Realtime数据的Command</param> /// <param name="pageIndex">页码</param> /// <param name="pageSize">每页数据</param> /// <param name="sortField">排序字段</param> /// <param name="aliasName">数据主表别名</param> /// <param name="keyField">数据主表主键</param> /// <param name="totalCount">总记录</param> /// <returns></returns> public static List <T> Query <T>(CustomDataCommand queryDbCommand, CustomDataCommand realtimeDbCommand, string aliasName, string keyField, PagingInfoEntity pagingInfo, out int totalCount) where T : class, new() { var setting = GetRealtimeSetting(); string pagingSql = @" SELECT @TotalCount = COUNT(1) FROM ( #InputSql# UNION ALL #RealtimeSql# ) result SELECT * FROM( SELECT TOP (@EndNumber) ROW_NUMBER() OVER(ORDER BY #SortColumnName#) AS RowNumber, * FROM ( #InputSql# UNION ALL #RealtimeSql# ) unionResult ) result WHERE RowNumber > @StartNumber"; DbParameter[] parameters = queryDbCommand.GetDbParameterList().ToArray(); DbParameter[] realtimeParams = realtimeDbCommand.GetDbParameterList().ToArray(); string inputSql = queryDbCommand.CommandText; inputSql += string.Format(" AND NOT EXISTS(SELECT TOP 1 1 FROM {0} AS r WHERE r.BusinessDataType = '{1}' AND r.BusinessKey = {2}.{3})", setting.TableName, typeof(T).FullName, aliasName, keyField); string realtimeSql, unionSql = string.Empty; realtimeSql = realtimeDbCommand.CommandText; pagingSql = pagingSql.Replace("#SortColumnName#", pagingInfo.SortField); pagingSql = pagingSql.Replace("#InputSql#", inputSql).Replace("#RealtimeSql#", realtimeSql); var command = DataCommandManager.CreateCustomDataCommandFromSql(pagingSql, setting.DatabaseName); command.AddInputParameter("@StartNumber", DbType.Int32, pagingInfo.StartRowIndex); command.AddInputParameter("@EndNumber", DbType.Int32, pagingInfo.StartRowIndex + pagingInfo.MaximumRows); command.AddOutParameter("@TotalCount", DbType.Int32, 4); //合并参数 List <DbParameter> list = new List <DbParameter>(); if (parameters != null) { foreach (var p in parameters) { if (list.FirstOrDefault(k => k.ParameterName.Trim().ToUpper() == p.ParameterName.Trim().ToUpper()) == null) { list.Add(p); } } } if (realtimeParams != null) { foreach (var p in realtimeParams) { if (list.FirstOrDefault(k => k.ParameterName.Trim().ToUpper() == p.ParameterName.Trim().ToUpper()) == null) { list.Add(p); } } } list.ForEach(p => { command.AddInputParameter(p.ParameterName, p.DbType, p.Value); }); var result = command.ExecuteEntityList <T>(); totalCount = int.Parse(command.GetParameterValue("@TotalCount").ToString()); return(result); }
public DynamicQuerySqlBuilder(CustomDataCommand dataCommand, PagingInfoEntity pagingInfo, string defaultOrderBy) : this(dataCommand.CommandText, dataCommand, pagingInfo, defaultOrderBy) { }