/// <summary> /// Looks up jobs in DMS for the datasets in the list /// </summary> /// <param name="datasets">The data set to find jobs for.</param> /// <param name="toolNameFilter">Optional: tool name to match; use % for a wildcard</param> /// <returns>Dictionary where key is Dataset ID and value is a list of jobs for the dataset</returns> public Dictionary <int, List <UdtJobInfo> > GetJobsByDataset(IEnumerable <UdtDatasetInfo> datasets, string toolNameFilter) { var dctJobs = new Dictionary <int, List <UdtJobInfo> >(); try { // Construct a comma-separated list of Dataset IDs var datasetIdList = new StringBuilder(); foreach (var dataset in datasets) { if (datasetIdList.Length > 0) { datasetIdList.Append(","); } datasetIdList.Append(dataset.DatasetId); } if (datasetIdList.ToString() == "0") { // datasets only contains one item, and it has dataset ID 0; no point in querying the database return(dctJobs); } var sql = " SELECT Job, Dataset_ID, Tool, Job_Finish, Folder," + " Parameter_File, Settings_File," + " [Protein Collection List], [Organism DB]" + " FROM V_Mage_Analysis_Jobs" + " WHERE Dataset_ID IN (" + datasetIdList + ")"; if (!string.IsNullOrWhiteSpace(toolNameFilter)) { sql += " AND Tool Like '" + CheckFilter(toolNameFilter) + "'"; } using (var connection = new SqlConnection(connectionString)) { connection.Open(); var retryCount = MaxRetries; if (retryCount < 1) { retryCount = 1; } while (retryCount > 0) { try { var cmd = new SqlCommand(sql, connection); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { // "Job, Dataset_ID, Tool, Job_Finish, Folder," + // " Parameter_File, Settings_File," + // " [Organism DB], [Protein Collection List]" + var jobInfo = new UdtJobInfo { Job = reader.GetInt32(0), DatasetId = reader.GetInt32(1), Tool = GetDBString(reader, 2), Completed = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3), JobFolderPath = GetDBString(reader, 4), ParameterFile = GetDBString(reader, 5), SettingsFile = GetDBString(reader, 6), ProteinCollection = GetDBString(reader, 7), OrganismDb = GetDBString(reader, 8) }; if (dctJobs.TryGetValue(jobInfo.DatasetId, out var jobsForDataset)) { jobsForDataset.Add(jobInfo); } else { jobsForDataset = new List <UdtJobInfo> { jobInfo }; dctJobs.Add(jobInfo.DatasetId, jobsForDataset); } } } break; } catch (Exception ex) { retryCount -= 1; var msg = "Exception querying database in GetJobs: " + ex.Message; msg += ", RetryCount = " + retryCount; Console.WriteLine(msg); // Delay for 3 second before trying again System.Threading.Thread.Sleep(3000); } } // while } // using } catch (Exception ex) { var msg = "Exception connecting to database in GetJobs: " + ex.Message + "; ConnectionString: " + connectionString; Console.WriteLine(msg); } return(dctJobs); }
/// <summary> /// Looks up jobs in DMS for the datasets in the list /// </summary> /// <param name="datasets">The data set to find jobs for.</param> /// <param name="toolNameFilter">Optional: tool name to match; use % for a wildcard</param> /// <returns>Dictionary where key is Dataset ID and value is a list of jobs for the dataset</returns> public Dictionary<int, List<UdtJobInfo>> GetJobsByDataset(IEnumerable<UdtDatasetInfo> datasets, string toolNameFilter) { var dctJobs = new Dictionary<int, List<UdtJobInfo>>(); try { // Construct a comma-separated list of Dataset IDs var datasetIdList = new StringBuilder(); foreach (var dataset in datasets) { if (datasetIdList.Length > 0) { datasetIdList.Append(","); } datasetIdList.Append(dataset.DatasetId); } string sql = " SELECT Job, Dataset_ID, Tool, Job_Finish, Folder," + " Parameter_File, Settings_File," + " [Protein Collection List], [Organism DB]" + " FROM V_Mage_Analysis_Jobs" + " WHERE Dataset_ID IN (" + datasetIdList + ")"; if (!string.IsNullOrWhiteSpace(toolNameFilter)) { sql += " AND Tool Like '" + this.CheckFilter(toolNameFilter) + "'"; } int retryCount = MaxRetries; if (retryCount < 1) { retryCount = 1; } while (retryCount > 0) { try { using (var connection = new SqlConnection(this.connectionString)) { connection.Open(); var cmd = new SqlCommand(sql, connection); var reader = cmd.ExecuteReader(); while (reader.Read()) { // "Job, Dataset_ID, Tool, Job_Finish, Folder," + // " Parameter_File, Settings_File," + // " [Organism DB], [Protein Collection List]" + var jobInfo = new UdtJobInfo { Job = reader.GetInt32(0), DatasetId = reader.GetInt32(1), Tool = this.GetDBString(reader, 2), Completed = reader.GetDateTime(3), JobFolderPath = this.GetDBString(reader, 4), ParameterFile = this.GetDBString(reader, 5), SettingsFile = this.GetDBString(reader, 6), ProteinCollection = this.GetDBString(reader, 7), OrganismDb = this.GetDBString(reader, 8) }; List<UdtJobInfo> jobsForDataset; if (dctJobs.TryGetValue(jobInfo.DatasetId, out jobsForDataset)) { jobsForDataset.Add(jobInfo); } else { jobsForDataset = new List<UdtJobInfo> { jobInfo }; dctJobs.Add(jobInfo.DatasetId, jobsForDataset); } } } break; } catch (Exception ex) { retryCount -= 1; string msg = "Exception querying database in GetJobs: " + ex.Message; msg += ", RetryCount = " + retryCount; Console.WriteLine(msg); // Delay for 3 second before trying again System.Threading.Thread.Sleep(3000); } } } catch (Exception ex) { string msg = "Exception connecting to database in GetJobs: " + ex.Message + "; ConnectionString: " + this.connectionString; Console.WriteLine(msg); } return dctJobs; }