/// <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); }
/// <summary> /// Returns a proper bind variable name based upon back end db type. /// NOTE: For SQLServer this is the same as ParamName, but not so for oracle. /// </summary> /// <param name="variableName"></param> /// <returns></returns> public string BuildBindVariableName(string variableName) { return(_daMgr.BuildBindVariableName(variableName)); }