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