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