public async Task QueryByEntityType(string entityType, Func <CommonSignConfiguration, Task> callback) { var configurationList = new List <CommonSignConfiguration>(); await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _dbConnectionFactory.CreateReadForWorkflow(), async (conn, transaction) => { long?sequence = null; const int pageSize = 1000; while (true) { configurationList.Clear(); SqlTransaction sqlTran = null; if (transaction != null) { sqlTran = (SqlTransaction)transaction; } await using (var command = new SqlCommand() { Connection = (SqlConnection)conn, CommandType = CommandType.Text, Transaction = sqlTran, }) { if (!sequence.HasValue) { command.CommandText = string.Format(@"SELECT TOP (@pagesize) {0} FROM [CommonSignConfiguration] WHERE [entitytype] = @entitytype ORDER BY [sequence]", StoreHelper.GetCommonSignConfigurationSelectFields(string.Empty)); } else { command.CommandText = string.Format(@"SELECT TOP (@pagesize) {0} FROM [CommonSignConfiguration] WHERE [entitytype] = @entitytype AND [sequence] > @sequence ORDER BY [sequence];", StoreHelper.GetCommonSignConfigurationSelectFields(string.Empty)); } var parameter = new SqlParameter("@entitytype", SqlDbType.NVarChar, 500) { Value = entityType }; command.Parameters.Add(parameter); parameter = new SqlParameter("@pagesize", SqlDbType.Int) { Value = pageSize }; command.Parameters.Add(parameter); if (sequence.HasValue) { parameter = new SqlParameter("@sequence", SqlDbType.BigInt) { Value = sequence }; command.Parameters.Add(parameter); } await command.PrepareAsync(); SqlDataReader reader = null; await using (reader = await command.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { var date = new CommonSignConfiguration(); StoreHelper.SetCommonSignConfigurationSelectFields(date, reader, string.Empty); sequence = (long)reader["sequence"]; configurationList.Add(date); } await reader.CloseAsync(); } } foreach (var workflowStep in configurationList) { await callback(workflowStep); } if (configurationList.Count != pageSize) { break; } } }); }
public async Task <CommonSignConfigurationNode> QueryByConfigurationName(Guid configurationId, string name) { CommonSignConfigurationNode result = null; await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _dbConnectionFactory.CreateReadForWorkflow(), 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},{1} FROM [CommonSignConfigurationNode] AS cnode INNER JOIN [CommonSignConfiguration] AS config ON cnode.[configurationid] = config.[id] WHERE cnode.[configurationid] = @configurationid AND cnode.[name] = @name ORDER BY cnode.sequence DESC; ", StoreHelper.GetCommonSignConfigurationNodeSelectFields("cnode"), StoreHelper.GetCommonSignConfigurationSelectFields("config")) }) { var parameter = new SqlParameter("@configurationid", SqlDbType.UniqueIdentifier) { Value = configurationId }; command.Parameters.Add(parameter); parameter = new SqlParameter("@name", SqlDbType.NVarChar, 500) { Value = name }; command.Parameters.Add(parameter); await command.PrepareAsync(); SqlDataReader reader = null; await using (reader = await command.ExecuteReaderAsync()) { if (await reader.ReadAsync()) { result = new CommonSignConfigurationNode(); StoreHelper.SetCommonSignConfigurationNodeSelectFields(result, reader, "cnode"); result.Configuration = new CommonSignConfiguration(); StoreHelper.SetCommonSignConfigurationSelectFields(result.Configuration, reader, "config"); } await reader.CloseAsync(); } } }); return(result); }
public async Task QueryByNode(Guid nodeId, Func <CommonSignConfigurationNodeAction, Task> callback) { var resultList = new List <CommonSignConfigurationNodeAction>(); await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _dbConnectionFactory.CreateReadForWorkflow(), async (conn, transaction) => { long?sequence = null; const int pageSize = 1000; while (true) { resultList.Clear(); SqlTransaction sqlTran = null; if (transaction != null) { sqlTran = (SqlTransaction)transaction; } await using (var command = new SqlCommand() { Connection = (SqlConnection)conn, CommandType = CommandType.Text, Transaction = sqlTran, }) { if (!sequence.HasValue) { command.CommandText = string.Format(@"SELECT TOP (@pagesize) {0},{1},{2} FROM [CommonSignConfigurationNodeAction] AS nodeaction INNER JOIN [CommonSignConfigurationNode] AS cnode ON nodeaction.nodeid = cnode.id LEFT JOIN [CommonSignConfiguration] AS nodeconfig ON nodeconfig.id = cnode.configurationid WHERE nodeaction.[nodeid] = @nodeid ORDER BY nodeaction.[sequence];", StoreHelper.GetCommonSignConfigurationNodeActionSelectFields("nodeaction"), StoreHelper.GetCommonSignConfigurationNodeSelectFields("cnode"), StoreHelper.GetCommonSignConfigurationSelectFields("nodeconfig")); } else { command.CommandText = string.Format(@"SELECT TOP (@pagesize) {0},{1},{2} FROM [CommonSignConfigurationNodeAction] AS nodeaction INNER JOIN [CommonSignConfigurationNode] AS cnode ON nodeaction.nodeid = cnode.id LEFT JOIN [CommonSignConfiguration] AS nodeconfig ON nodeconfig.id = cnode.configurationid WHERE nodeaction.[nodeid] = @nodeid AND nodeaction.[sequence] > @sequence ORDER BY nodeaction.[sequence];", StoreHelper.GetCommonSignConfigurationNodeActionSelectFields("nodeaction"), StoreHelper.GetCommonSignConfigurationNodeSelectFields("cnode"), StoreHelper.GetCommonSignConfigurationSelectFields("nodeconfig")); } var parameter = new SqlParameter("@nodeid", SqlDbType.UniqueIdentifier) { Value = nodeId }; command.Parameters.Add(parameter); parameter = new SqlParameter("@pagesize", SqlDbType.Int) { Value = pageSize }; command.Parameters.Add(parameter); if (sequence.HasValue) { parameter = new SqlParameter("@sequence", SqlDbType.BigInt) { Value = sequence }; command.Parameters.Add(parameter); } await command.PrepareAsync(); SqlDataReader reader = null; await using (reader = await command.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { var data = new CommonSignConfigurationNodeAction(); StoreHelper.SetCommonSignConfigurationNodeActionSelectFields(data, reader, "nodeaction"); if (reader[string.Format("{0}id", "cnode")] != DBNull.Value) { data.Node = new CommonSignConfigurationNode(); StoreHelper.SetCommonSignConfigurationNodeSelectFields(data.Node, reader, "cnode"); } if (reader[string.Format("{0}id", "nodeconfig")] != DBNull.Value) { data.Node.Configuration = new CommonSignConfiguration(); StoreHelper.SetCommonSignConfigurationSelectFields(data.Node.Configuration, reader, "nodeconfig"); } sequence = (long)reader["nodeactionsequence"]; resultList.Add(data); } await reader.CloseAsync(); } } foreach (var workflowStep in resultList) { await callback(workflowStep); } if (resultList.Count != pageSize) { break; } } }); }
/// <summary> /// 查询审核实体的字段信息 /// </summary> /// <param name="entityName">审核实体名称</param> /// <param name="entityColumnName">审核实体字段名称</param> /// <param name="entityKey">审核实体主键</param> /// <returns>审核实体的字段信息</returns> public async Task <string> QueryAuditStatusByKey(string entityName, string entityColumnName, Dictionary <string, string> entityKey) { string result = null; await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _dbConnectionFactory.CreateReadForWorkflow(), async (conn, transaction) => { SqlTransaction sqlTran = null; if (transaction != null) { sqlTran = (SqlTransaction)transaction; } var conditionStr = string.Empty; foreach (var item in entityKey) { conditionStr = $"{conditionStr}{item.Key} = @{item.Key} AND "; } await using (var command = new SqlCommand() { Connection = (SqlConnection)conn, CommandType = CommandType.Text, Transaction = sqlTran, CommandText = $@"SELECT {entityColumnName} FROM {entityName} WHERE {conditionStr.TrimEnd('A', 'N', 'D', ' ')} " }) { foreach (var item in entityKey) { var parameter = new SqlParameter($"@{item.Key}", SqlDbType.NVarChar, 1000) { Value = item.Value }; command.Parameters.Add(parameter); } await command.PrepareAsync(); var executeObj = await command.ExecuteScalarAsync(); result = executeObj?.ToString(); } }); return(result); }