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);
}
