public ConditionCollectionContext(CustomDataCommand contextDataCommand)
{
IsFirstCondition = true;
FirstGroupConditionFlags = new Stack <bool>();
AddedParameterNames = new List <string>();
DataCommand = contextDataCommand;
}
public DynamicQuerySqlBuilder(
string querySqlTemplate, CustomDataCommand dataCommand,
PagingInfoEntity pagingInfo, string defaultOrderBy)
{
m_pagingInfo = pagingInfo;
m_dataCommand = dataCommand;
m_querySqlTemplate = querySqlTemplate;
m_defaultOrderBy = defaultOrderBy;
m_conditionConstructor = new ConditionConstructor();
}
/// <summary>
/// 查询数据(包括Realtime)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryDbCommand">查询QueryDB数据的Command</param>
/// <param name="realtimeDbCommand">查询Realtime数据的Command</param>
/// <param name="aliasName">数据主表别名</param>
/// <param name="keyField">数据主表主键</param>
/// <returns></returns>
public static List<T> Query<T>(CustomDataCommand queryDbCommand, CustomDataCommand realtimeDbCommand, string aliasName, string keyField)
where T : class, new()
{
var setting = GetRealtimeSetting();
string sql = @"
#InputSql#
UNION ALL
#RealtimeSql#";
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;
var command = DataCommandManager.CreateCustomDataCommandFromSql(sql, setting.DatabaseName);
//合并参数
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);
});
return command.ExecuteEntityList<T>();
}
public DynamicQuerySqlBuilder(
string querySqlTemplate, CustomDataCommand dataCommand,
QueryFilter pagingInfo, string defaultOrderBy)
: this(dataCommand.CommandText, dataCommand, Covert(pagingInfo), defaultOrderBy)
{
}
public DynamicQuerySqlBuilder(CustomDataCommand dataCommand,
PagingInfoEntity pagingInfo, string defaultOrderBy)
: this(dataCommand.CommandText, dataCommand, pagingInfo, defaultOrderBy)
{
}
public DynamicQuerySqlBuilder(CustomDataCommand dataCommand, string defaultOrderBy)
: this(dataCommand.CommandText, dataCommand, new PagingInfoEntity() { StartRowIndex = 0, MaximumRows = 5000, SortField = string.Empty }, defaultOrderBy)
{
}
public DynamicQuerySqlBuilder(CustomDataCommand dataCommand)
: this(dataCommand.CommandText, dataCommand, new PagingInfoEntity() { StartRowIndex = 0, MaximumRows = 5000, SortField = string.Empty }, string.Empty)
{
this.m_needPaging = false;
}
/// <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;
}