/// <summary>
/// Builds the DbCommand that returns the list of dependent tasks
/// and their dependent tasks for a given WaitTaskQueueCode
/// </summary>
/// <param name="daMgr">DataAccessMgr object</param>
/// <returns>DbCommand Object with DbNull Parameter values</returns>
static DbCommand GetDependentTasksCmd(DataAccessMgr daMgr)
{
StringBuilder sb = new StringBuilder();
// we do not have any helper functions to build a recursive query; so we
// are building this manually.
sb.AppendFormat("WITH Dependencies ({0}, {1}, Level){2} AS{2} ({2}"
, TaskProcessing.Constants.TaskQueueCode
, TaskProcessing.Constants.WaitTaskQueueCode
, Environment.NewLine);
sb.AppendFormat("SELECT tpq.{0}, {1}, 1 AS Level{2}"
, TaskProcessing.Constants.TaskQueueCode
, TaskProcessing.Constants.WaitTaskQueueCode
, Environment.NewLine);
sb.AppendFormat("FROM {0}.{1} tpq{2}"
, DataAccess.Constants.SCHEMA_CORE
, TaskProcessing.Constants.TaskProcessingQueue
, Environment.NewLine);
sb.AppendFormat("INNER JOIN {0}.{1} td{2}"
, DataAccess.Constants.SCHEMA_CORE
, TaskProcessing.Constants.TaskDependencies
, Environment.NewLine);
sb.AppendFormat("ON tpq.{0} = td.{0}{1}UNION ALL{1}"
, TaskProcessing.Constants.TaskQueueCode
, Environment.NewLine);
sb.AppendFormat("SELECT d.{0}, td.{1}, Level + 1{2}"
, TaskProcessing.Constants.TaskQueueCode
, TaskProcessing.Constants.WaitTaskQueueCode
, Environment.NewLine);
sb.AppendFormat("FROM Dependencies d{0}"
, Environment.NewLine);
sb.AppendFormat("INNER JOIN {0}.{1} td{2}"
, DataAccess.Constants.SCHEMA_CORE
, TaskProcessing.Constants.TaskDependencies
, Environment.NewLine);
sb.AppendFormat("ON d.{0} = td.{1}{2})"
, TaskProcessing.Constants.WaitTaskQueueCode
, TaskProcessing.Constants.TaskQueueCode
, Environment.NewLine);
sb.AppendFormat("SELECT {0}, {1}, Level{2}"
, TaskProcessing.Constants.TaskQueueCode
, TaskProcessing.Constants.WaitTaskQueueCode
, Environment.NewLine);
sb.AppendFormat("FROM Dependencies d{0}"
, Environment.NewLine);
sb.AppendFormat("WHERE {0} = {1}{2};"
, TaskProcessing.Constants.WaitTaskQueueCode
, daMgr.BuildBindVariableName(TaskProcessing.Constants.WaitTaskQueueCode)
, Environment.NewLine);
DbCommand dbCmd = daMgr.BuildSelectDbCommand(sb.ToString(), null);
daMgr.AddNewParameterToCollection(dbCmd.Parameters, TaskProcessing.Constants.WaitTaskQueueCode
, DbType.Int32
, null
, 0
, ParameterDirection.Input
, DBNull.Value);
return(dbCmd);
}