public async Task <QueryResult <CommonLog> > QueryByParentId(Guid parentID, string parentActionName, int page, int pageSize)
{
var group = await getHashGroup(parentActionName);
var dbInfo = await StoreInfoHelper.GetHashStoreInfo(group, _storeInfoResolveService, parentID.ToString());
var tableNameCommonlog = getTableName(group.Name, dbInfo.TableNames);
var strConn = await _storeInfoResolveConnectionService.GetRead(dbInfo);
QueryResult <CommonLog> result = new QueryResult <CommonLog>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, strConn, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
})
{
SqlParameter parameter;
command.CommandText = string.Format(@"SELECT @count = COUNT(*)
FROM [dbo].[{1}]
where [parentid]=@parentid
SELECT {0}
FROM [dbo].[{1}]
where [parentid]=@parentid
ORDER BY [sequence] OFFSET (@pagesize * (@currentpage - 1)) ROWS FETCH NEXT @pagesize ROWS ONLY;", StoreHelper.GetCommonLogSelectFields(string.Empty), tableNameCommonlog);
parameter = new SqlParameter("@parentid", SqlDbType.UniqueIdentifier)
{
Value = parentID
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@currentpage", SqlDbType.Int)
{
Value = page
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@pagesize", SqlDbType.Int)
{
Value = pageSize
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@count", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var log = new CommonLog();
StoreHelper.SetCommonLogSelectFields(log, reader, string.Empty);
result.Results.Add(log);
}
await reader.CloseAsync();
result.TotalCount = (int)command.Parameters["@count"].Value;
result.CurrentPage = page;
}
}
});
return(result);
}
public async Task Add(SMessageHistoryListenerDetail detail)
{
var storeInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _messageHistoryListenerDetailHashGroupName, detail.SMessageHistoryID.ToString());
if (!storeInfo.TableNames.TryGetValue(HashEntityNames.SMessageHistoryListenerDetail, out string tableNameListenerDetail))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_messageHistoryListenerDetailHashGroupName, HashEntityNames.SMessageHistoryListenerDetail
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
if (!storeInfo.TableNames.TryGetValue(HashEntityNames.SMessageHistory, out string tableNameHistory))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_messageHistoryListenerDetailHashGroupName, HashEntityNames.SMessageHistory
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _messageQueueConnectionFactory.CreateAllForSMessageHistoryListenerDetail(storeInfo.DBConnectionNames), async (conn, transaction) =>
{
//新增
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
})
{
SqlParameter parameter;
if (detail.ID == Guid.Empty)
{
command.CommandText = string.Format(@"
INSERT INTO {0}
(
[id]
,[SMessageHistoryID]
,[ListenerName]
,[ListenerMode]
,[ListenerFactoryType]
,[ListenerWebUrl]
,[ListenerRealWebUrl]
,[createtime]
,[modifytime]
)
VALUES
(default
, @SMessageHistoryID
, @ListenerName
, @ListenerMode
, @ListenerFactoryType
, @ListenerWebUrl
, @ListenerRealWebUrl
, GETUTCDATE()
, GETUTCDATE());
select @newid =[id] from {0} where [sequence] = SCOPE_IDENTITY()", tableNameListenerDetail);
parameter = new SqlParameter("@newid", SqlDbType.UniqueIdentifier)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(parameter);
}
else
{
command.CommandText = string.Format(@"
INSERT INTO {0}
(
[id]
,[SMessageHistoryID]
,[ListenerName]
,[ListenerMode]
,[ListenerFactoryType]
,[ListenerWebUrl]
,[ListenerRealWebUrl]
,[createtime]
,[modifytime]
)
VALUES
(
@id
, @SMessageHistoryID
, @ListenerName
, @ListenerMode
, @ListenerFactoryType
, @ListenerWebUrl
, @ListenerRealWebUrl
, GETUTCDATE()
, GETUTCDATE());", tableNameListenerDetail);
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = detail.ID
};
command.Parameters.Add(parameter);
}
parameter = new SqlParameter("@SMessageHistoryID", SqlDbType.UniqueIdentifier)
{
Value = detail.SMessageHistoryID
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@ListenerName", SqlDbType.VarChar, 150)
{
Value = detail.ListenerName
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@ListenerMode", SqlDbType.Int)
{
Value = detail.ListenerMode
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@ListenerFactoryType", SqlDbType.VarChar, 150)
{
Value = detail.ListenerFactoryType
};
command.Parameters.Add(parameter);
if (detail.ListenerWebUrl == null)
{
parameter = new SqlParameter("@ListenerWebUrl", SqlDbType.VarChar, 200)
{
Value = DBNull.Value
};
}
else
{
parameter = new SqlParameter("@ListenerWebUrl", SqlDbType.VarChar, 200)
{
Value = detail.ListenerWebUrl
};
}
command.Parameters.Add(parameter);
if (detail.ListenerRealWebUrl == null)
{
parameter = new SqlParameter("@ListenerRealWebUrl", SqlDbType.VarChar, 200)
{
Value = DBNull.Value
};
}
else
{
parameter = new SqlParameter("@ListenerRealWebUrl", SqlDbType.VarChar, 200)
{
Value = detail.ListenerRealWebUrl
};
}
command.Parameters.Add(parameter);
await command.PrepareAsync();
try
{
await command.ExecuteNonQueryAsync();
}
catch (SqlException ex)
{
if (ex.Number == 2601)
{
var fragment = new TextFragment()
{
Code = TextCodes.ExistSMessageHistoryDetailByName,
DefaultFormatting = "消息历史监听明细中存在相同的名称\"{0}\"数据",
ReplaceParameters = new List <object>()
{
detail.ListenerName
}
};
throw new UtilityException((int)Errors.ExistSMessageHistoryDetailByName, fragment);
}
else
{
throw;
}
}
//如果用户未赋值ID则创建成功后返回ID
if (detail.ID == Guid.Empty)
{
detail.ID = (Guid)command.Parameters["@newid"].Value;
}
;
}
});
}
public async Task Add(CommonLog log)
{
var group = await getHashGroup(log.ParentActionName);
var dbInfo = await StoreInfoHelper.GetHashStoreInfo(group, _storeInfoResolveService, log.ParentID.ToString());
var strConn = await _storeInfoResolveConnectionService.GetReadAndWrite(dbInfo);
var tableNameCommonlog = getTableName(group.Name, dbInfo.TableNames);
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, strConn, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran
})
{
SqlParameter parameter;
int length;
if (log.ID == Guid.Empty)
{
command.CommandText = string.Format(@"insert into {0} ([id],[parentid],[prelevelid],[currentlevelid],[contextinfo],[actionname],[parentactionname],[requestbody],[responsebody],[requesturi],[message],[root],[level],[createtime],[modifytime])
values (default,@parentid,@prelevelid,@currentlevelid,@contextinfo,@actionname,@parentactionname,@requestbody,@responsebody,@requesturi,@message,@root,@level,GETUTCDATE(),GETUTCDATE());
SELECT @newid=[id] FROM {0} WHERE [sequence]=SCOPE_IDENTITY()", tableNameCommonlog);
parameter = new SqlParameter("@newid", SqlDbType.UniqueIdentifier)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(parameter);
}
else
{
command.CommandText = string.Format(@"insert into {0} ([id],[parentid],[prelevelid],[currentlevelid],[contextinfo],[actionname],[parentactionname],[requestbody],[responsebody],[requesturi],[message],[root],[level],[createtime],[modifytime])
VALUES (@id,@parentid,@prelevelid,@currentlevelid,@contextinfo,@actionname,@parentactionname,@requestbody,@responsebody,@requesturi,@message,@root,@level,GETUTCDATE(),GETUTCDATE())", tableNameCommonlog);
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = log.ID
};
command.Parameters.Add(parameter);
}
parameter = new SqlParameter("@parentid", SqlDbType.UniqueIdentifier)
{
Value = log.ParentID
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@prelevelid", SqlDbType.UniqueIdentifier)
{
Value = log.PreLevelID
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@currentlevelid", SqlDbType.UniqueIdentifier)
{
Value = log.CurrentLevelID
};
command.Parameters.Add(parameter);
if (log.ContextInfo.Length == 0)
{
length = 10;
}
else
{
length = log.ContextInfo.Length;
}
parameter = new SqlParameter("@contextinfo", SqlDbType.NVarChar, length)
{
Value = log.ContextInfo
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@actionname", SqlDbType.NVarChar, 300)
{
Value = log.ActionName
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@parentactionname", SqlDbType.NVarChar, 300)
{
Value = log.ParentActionName
};
command.Parameters.Add(parameter);
if (log.RequestBody == null)
{
log.RequestBody = string.Empty;
}
if (log.RequestBody.Length == 0)
{
length = 10;
}
else
{
length = log.RequestBody.Length;
}
parameter = new SqlParameter("@requestbody", SqlDbType.NVarChar, length)
{
Value = log.RequestBody
};
command.Parameters.Add(parameter);
if (log.ResponseBody.Length == 0)
{
length = 10;
}
else
{
length = log.ResponseBody.Length;
}
if (log.ResponseBody == null)
{
log.ResponseBody = string.Empty;
}
parameter = new SqlParameter("@responsebody", SqlDbType.NVarChar, length)
{
Value = log.ResponseBody
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@requesturi", SqlDbType.NVarChar, 500)
{
Value = log.RequestUri
};
command.Parameters.Add(parameter);
if (log.Message.Length == 0)
{
length = 10;
}
else
{
length = log.Message.Length;
}
parameter = new SqlParameter("@message", SqlDbType.NVarChar, length)
{
Value = log.Message
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@root", SqlDbType.Bit)
{
Value = log.Root
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@level", SqlDbType.Int)
{
Value = log.Level
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
await command.ExecuteNonQueryAsync();
if (log.ID == Guid.Empty)
{
log.ID = (Guid)command.Parameters["@newid"].Value;
}
}
});
}
public async Task QueryByResource(string resourceType, string resourceKey, Guid resourceId, Func <WorkflowStepUserAction, Task> callback)
{
var dbInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _hashGroupName, resourceType, resourceKey);
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowStepUserAction, out string tableNameStepUserAction))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowStepUserAction
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowStep, out string tableNameStep))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowStep
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowResource, out string tableNameResource))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowResource
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
List <WorkflowStepUserAction> listAction = new List <WorkflowStepUserAction>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, dbInfo.DBConnectionNames.Read, async (conn, transaction) =>
{
int sequence = 0;
int pageSize = 500;
while (true)
{
listAction.Clear();
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
CommandText = string.Format(@"SELECT {0}
FROM {1} AS action
INNER JOIN {2} AS step ON action.stepid=step.id
INNER JOIN {3} AS resource ON resource.id = step.resourceid
WHERE resource.id=@resourceId ORDER BY action.sequence OFFSET @sequence ROWS FETCH NEXT @pagesize ROWS ONLY;", StoreHelper.GetWorkflowStepUserActionStoreSelectFields("action")
, tableNameStepUserAction, tableNameStep, tableNameResource),
Transaction = sqlTran
})
{
var parameter = new SqlParameter("@resourceId", SqlDbType.UniqueIdentifier)
{
Value = resourceId
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@pagesize", SqlDbType.Int)
{
Value = pageSize
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@sequence", SqlDbType.Int)
{
Value = sequence
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var workflowStepUserAction = new WorkflowStepUserAction();
StoreHelper.SetWorkflowStepUserActionStoreSelectFields(workflowStepUserAction, reader, "action");
listAction.Add(workflowStepUserAction);
}
await reader.CloseAsync();
}
}
foreach (var actionItem in listAction)
{
await callback(actionItem);
}
if (listAction.Count != pageSize)
{
break;
}
else
{
sequence += listAction.Count;
}
}
});
}
public async Task <SMessageHistoryListenerDetail> QueryByName(Guid historyId, string name)
{
var storeInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _messageHistoryListenerDetailHashGroupName, historyId.ToString());
if (!storeInfo.TableNames.TryGetValue(HashEntityNames.SMessageHistoryListenerDetail, out string tableNameListenerDetail))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_messageHistoryListenerDetailHashGroupName, HashEntityNames.SMessageHistoryListenerDetail
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
if (!storeInfo.TableNames.TryGetValue(HashEntityNames.SMessageHistory, out string tableNameHistory))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_messageHistoryListenerDetailHashGroupName, HashEntityNames.SMessageHistory
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
SMessageHistoryListenerDetail smessageHistoryListenerDetail = null;
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _messageQueueConnectionFactory.CreateReadForSMessageHistoryListenerDetail(storeInfo.DBConnectionNames), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
CommandText = string.Format(@"SELECT {0},{1}
FROM {2} as d join {3} as h on (d.smessagehistoryid = h.id )
WHERE [ListenerName]=@listenername and SMessageHistoryID = @historyId",
StoreHelper.GetSMessageHistoryListenerDetailSelectFields("d"), StoreHelper.GetSMessageHistorySelectFields("h"),
tableNameListenerDetail, tableNameHistory),
Transaction = sqlTran,
})
{
var parameter = new SqlParameter("@listenername", SqlDbType.NVarChar, 100)
{
Value = name
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@historyId", SqlDbType.UniqueIdentifier)
{
Value = historyId
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
using (reader = await command.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
smessageHistoryListenerDetail = new SMessageHistoryListenerDetail();
StoreHelper.SetSMessageHistoryListenerDetailSelectFields(smessageHistoryListenerDetail, reader, "d");
smessageHistoryListenerDetail.SMessageHistory = new SMessageHistory();
StoreHelper.SetSMessageHistorySelectFields(smessageHistoryListenerDetail.SMessageHistory, reader, "h");
}
await reader.CloseAsync();
}
}
});
return(smessageHistoryListenerDetail);
}
/// <summary>
/// 新增用户动作
/// </summary>
/// <param name="resourceType">连接数据库类型</param>
/// <param name="resourceKey">连接数据库key</param>
/// <param name="action"></param>
/// <returns></returns>
public async Task Add(string resourceType, string resourceKey, WorkflowStepUserAction action)
{
var dbInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _hashGroupName, resourceType, resourceKey);
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowStepUserAction, out string tableNameStepUserAction))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowStepUserAction
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, dbInfo.DBConnectionNames.ReadAndWrite, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran
})
{
SqlParameter parameter;
//判断用户是否提交ID，如果ID为空
if (action.ID == Guid.Empty)
{
command.CommandText = string.Format(@"INSERT INTO {0} ([id],[stepid],[userkey],[result],[createtime],[modifytime])
VALUES (default,@stepid,@userkey,@result,GETUTCDATE(),GETUTCDATE());
SELECT @newid=[id] FROM {0} WHERE [sequence]=SCOPE_IDENTITY()", tableNameStepUserAction);
parameter = new SqlParameter("@newid", SqlDbType.UniqueIdentifier)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(parameter);
}
else
{
command.CommandText = string.Format(@"INSERT INTO {0} ([id],[stepid],[userkey],[result],[createtime],[modifytime])
VALUES (@id,@stepid,@userkey,@result,GETUTCDATE(),GETUTCDATE())", tableNameStepUserAction);
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = action.ID
};
command.Parameters.Add(parameter);
}
parameter = new SqlParameter("@stepid", SqlDbType.UniqueIdentifier)
{
Value = action.StepID
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@userkey", SqlDbType.NVarChar, 256)
{
Value = action.UserKey
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@result", SqlDbType.Int)
{
Value = action.Result
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
try
{
await command.ExecuteNonQueryAsync();
}
catch (SqlException ex)
{
if (ex == null)
{
throw;
}
if (ex.Number == 2601)
{
var fragment = new TextFragment()
{
Code = TextCodes.ExistWorkflowStepUserActionByUserKey,
DefaultFormatting = "工作流用户动作中已存在相同关键字\"{0}\"数据",
ReplaceParameters = new List <object>()
{
action.UserKey
}
};
throw new UtilityException((int)Errors.ExistWorkflowStepUserActionByUserKey, fragment);
}
else
{
throw;
}
}
if (action.ID == Guid.Empty)
{
action.ID = (Guid)command.Parameters["@newid"].Value;
}
}
});
}
/// <summary>
/// 查询指定步骤下面指定用户信息的用户动作
/// </summary>
/// <param name="resourceType"></param>
/// <param name="resourceKey"></param>
/// <param name="stepId"></param>
/// <param name="userKey"></param>
/// <returns></returns>
public async Task <WorkflowStepUserAction> QueryByStepAndUser(string resourceType, string resourceKey, Guid stepId, string userKey)
{
var dbInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _hashGroupName, resourceType, resourceKey);
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowStepUserAction, out string tableNameStepUserAction))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowStepUserAction
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowStep, out string tableNameStep))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowStep
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowResource, out string tableNameResource))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowResource
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
WorkflowStepUserAction result = null;
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, dbInfo.DBConnectionNames.Read, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
CommandText = string.Format(@"SELECT {0} FROM {1} WHERE stepid =@stepid AND userkey=@userkey ;", StoreHelper.GetWorkflowStepUserActionStoreSelectFields(string.Empty), tableNameStepUserAction),
Transaction = sqlTran
})
{
var parameter = new SqlParameter("@stepid", SqlDbType.UniqueIdentifier)
{
Value = stepId
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@userkey", SqlDbType.NVarChar, 256)
{
Value = userKey
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
result = new WorkflowStepUserAction();
StoreHelper.SetWorkflowStepUserActionStoreSelectFields(result, reader, string.Empty);
}
await reader.CloseAsync();
}
}
});
return(result);
}
/// <summary>
/// 查询指定步骤下的所有用户动作
/// </summary>
/// <param name="resourceType"></param>
/// <param name="resourceKey"></param>
/// <param name="stepId"></param>
/// <param name="callback"></param>
/// <returns></returns>
public async Task QueryByStep(string resourceType, string resourceKey, Guid stepId, Func <WorkflowStepUserAction, Task> callback)
{
var dbInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _hashGroupName, resourceType, resourceKey);
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowStepUserAction, out string tableNameStepUserAction))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowStepUserAction
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
List <WorkflowStepUserAction> listAction = new List <WorkflowStepUserAction>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, dbInfo.DBConnectionNames.Read, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
CommandText = string.Format(@"SELECT {0} FROM {1} WHERE stepid =@stepid ORDER BY sequence", StoreHelper.GetWorkflowStepUserActionStoreSelectFields(string.Empty), tableNameStepUserAction),
Transaction = sqlTran
})
{
var parameter = new SqlParameter("@stepid", SqlDbType.UniqueIdentifier)
{
Value = stepId
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var workflowStepUserAction = new WorkflowStepUserAction();
StoreHelper.SetWorkflowStepUserActionStoreSelectFields(workflowStepUserAction, reader, string.Empty);
listAction.Add(workflowStepUserAction);
}
await reader.CloseAsync();
}
}
foreach (var actionItem in listAction)
{
await callback(actionItem);
}
});
}
/// <summary>
/// 根据type和Key查询
/// </summary>
/// <param name="type"></param>
/// <param name="key"></param>
/// <returns></returns>
public async Task <WorkflowResource> QueryByKey(string type, string key)
{
var dbInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _hashGroupName, type, key);
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowResource, out string tableName))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName, HashEntityNames.WorkflowResource
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
WorkflowResource result = null;
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, dbInfo.DBConnectionNames.Read, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (var command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = string.Format(@"SELECT {0}
FROM {1}
WHERE [type] = @type
AND [key] = @key
ORDER BY createtime DESC ", StoreHelper.GetWorkflowResourceStoreSelectFields(string.Empty),
tableName)
})
{
var parameter = new SqlParameter("@type", SqlDbType.NVarChar, 256)
{
Value = type
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@key", SqlDbType.NVarChar, 256)
{
Value = key
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
result = new WorkflowResource();
StoreHelper.SetWorkflowResourceStoreSelectFields(result, reader, string.Empty);
}
reader.Close();
}
}
});
return(result);
}
public async Task Add(SMessageHistory history)
{
var storeInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _messageHistoryHashGroupName, history.ID.ToString());
if (!storeInfo.TableNames.TryGetValue(HashEntityNames.SMessageHistory, out string tableName))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_messageHistoryHashGroupName, HashEntityNames.SMessageHistory
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _messageQueueConnectionFactory.CreateAllForSMessageHistory(storeInfo.DBConnectionNames), async (conn, transaction) =>
{
//新增
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
})
{
SqlParameter parameter;
if (history.ID == Guid.Empty)
{
command.CommandText = string.Format(@"
INSERT INTO {0}
(
[id]
,[key]
,[type]
,[data]
,[originalmessageid]
,[delaymessageid]
,[status]
,[createtime]
,[modifytime]
)
VALUES
(default
, @key
, @type
, @data
, @originalmessageid
, @delaymessageid
, @status
, GETUTCDATE()
, GETUTCDATE());
select @newid =[id] from {0} where [sequence] = SCOPE_IDENTITY()", tableName);
parameter = new SqlParameter("@newid", SqlDbType.UniqueIdentifier)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(parameter);
}
else
{
command.CommandText = string.Format(@"
INSERT INTO {0}
(
[id]
,[key]
,[type]
,[data]
,[originalmessageid]
,[delaymessageid]
,[status]
,[createtime]
,[modifytime]
)
VALUES
(
@id
, @key
, @type
, @data
, @originalmessageid
, @delaymessageid
, @status
, GETUTCDATE()
, GETUTCDATE());", tableName);
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = history.ID
};
command.Parameters.Add(parameter);
}
parameter = new SqlParameter("@key", SqlDbType.VarChar, 150)
{
Value = history.Key
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@type", SqlDbType.VarChar, 150)
{
Value = history.Type
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@data", SqlDbType.VarChar, history.Data.Length)
{
Value = history.Data
};
command.Parameters.Add(parameter);
if (history.OriginalMessageID.HasValue)
{
parameter = new SqlParameter("@originalmessageid", SqlDbType.UniqueIdentifier)
{
Value = history.OriginalMessageID
};
}
else
{
parameter = new SqlParameter("@originalmessageid", SqlDbType.UniqueIdentifier)
{
Value = DBNull.Value
};
}
command.Parameters.Add(parameter);
if (history.DelayMessageID.HasValue)
{
parameter = new SqlParameter("@delaymessageid", SqlDbType.UniqueIdentifier)
{
Value = history.DelayMessageID
};
}
else
{
parameter = new SqlParameter("@delaymessageid", SqlDbType.UniqueIdentifier)
{
Value = DBNull.Value
};
}
command.Parameters.Add(parameter);
parameter = new SqlParameter("@status", SqlDbType.Int)
{
Value = history.Status
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
await command.ExecuteNonQueryAsync();
//如果用户未赋值ID则创建成功后返回ID
if (history.ID == Guid.Empty)
{
history.ID = (Guid)command.Parameters["@newid"].Value;
}
;
}
});
}
