/// <summary>
/// 根据主键加载数据,如果需要查询Realtime表，请传入参数id
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id">主键</param>
/// <param name="queryDbCommand">查询QueryDB的DataCommand</param>
/// <returns></returns>
public static T Load<T>(DataCommand queryDbCommand, string id) where T : class, new()
{
if (!string.IsNullOrEmpty(id))
{
var setting = GetRealtimeSetting();
string sql = @"SELECT
BusinessData
FROM #TableName# WITH(NOLOCK)
WHERE BusinessKey = @BusinessKey
AND BusinessDataType = @BusinessDataType";
sql = sql.Replace("#TableName#", setting.TableName);
var command = DataCommandManager.CreateCustomDataCommandFromSql(sql, setting.DatabaseName);
string xml = command.ExecuteScalar<string>();
if (!string.IsNullOrEmpty(xml))
{
var result = SerializationUtility.XmlDeserialize<T>(xml);
return result;
}
}
return queryDbCommand.ExecuteEntity<T>();
}
/// <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;
}
/// <summary>
/// 修改Realtime数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">数据</param>
/// <param name="keyGetter">获取数据主键的表达式，如订单数据的订单编号</param>
public static void Update<T>(T data, Func<T, string> keyGetter)
{
var setting = GetRealtimeSetting();
string sql = @"
DECLARE @TranCounterNew int
BEGIN TRY
BEGIN
SELECT @TranCounterNew = @@TRANCOUNT
IF @TranCounterNew = 0
BEGIN TRANSACTION
IF EXISTS(
SELECT TOP 1 1
FROM #TableName# WITH(NOLOCK)
WHERE BusinessKey = @BusinessKey
AND BusinessDataType = @BusinessDataType)
BEGIN
INSERT INTO #TableName#_History
(
BusinessKey,
BusinessDataType,
ChangeType,
BusinessData,
CreateDate
)
SELECT
BusinessKey
,BusinessDataType
,ChangeType
,BusinessData
,GETDATE()
FROM #TableName# WITH(NOLOCK)
WHERE BusinessKey = @BusinessKey
AND BusinessDataType = @BusinessDataType
END
UPDATE #TableName# SET
ChangeType = 'U',
BusinessData = @BusinessData
WHERE BusinessKey = @BusinessKey
AND BusinessDataType = @BusinessDataType
IF XACT_STATE() = 1 AND @TranCounterNew = 0
COMMIT TRANSACTION;
END
END TRY
BEGIN CATCH
BEGIN
IF XACT_STATE() <> 0
BEGIN
IF @TranCounterNew = 0
ROLLBACK TRANSACTION
END
END
END Catch";
sql = sql.Replace("#TableName#", setting.TableName);
string businessKey = keyGetter(data);
var command = DataCommandManager.CreateCustomDataCommandFromSql(sql, setting.DatabaseName);
command.AddInputParameter("@BusinessKey", DbType.String, businessKey);
command.AddInputParameter("@BusinessData", DbType.String, SerializationUtility.XmlSerialize(data));
command.AddInputParameter("@BusinessDataType", DbType.String, typeof(T).FullName);
command.ExecuteNonQuery();
}