示例#1
0
     /// <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>());
     }
示例#2
0
        /// <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);
        }
示例#3
0
        /// <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();
        }