forked from PNNL-Comp-Mass-Spec/MASIC
-
Notifications
You must be signed in to change notification settings - Fork 0
/
clsDatabaseAccess.cs
244 lines (211 loc) · 9.4 KB
/
clsDatabaseAccess.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
using System;
using System.IO;
using System.Linq;
using MASIC.Options;
using PRISMDatabaseUtils;
namespace MASIC
{
/// <summary>
/// Class for obtaining data from the DMS database
/// </summary>
public class clsDatabaseAccess : clsMasicEventNotifier
{
// ReSharper disable UnusedMember.Global
/// <summary>
/// DMS connection string
/// </summary>
/// <remarks>
/// The GUI uses this (see frmMain)
/// </remarks>
public const string DATABASE_CONNECTION_STRING_DEFAULT = "Data Source=gigasax;Initial Catalog=DMS5;User=DMSReader;Password=dms4fun";
/// <summary>
/// Query for obtaining dataset names and IDs
/// </summary>
/// <remarks>
/// The GUI uses this (see frmMain)
/// </remarks>
public const string DATABASE_DATASET_INFO_QUERY_DEFAULT = "Select Dataset, ID FROM V_Dataset_Export";
// ReSharper restore UnusedMember.Global
private readonly MASICOptions mOptions;
/// <summary>
/// Constructor
/// </summary>
/// <param name="masicOptions"></param>
public clsDatabaseAccess(MASICOptions masicOptions)
{
mOptions = masicOptions;
}
/// <summary>
/// Lookup the dataset ID given the dataset name
/// First contacts the database using the specified connection string and query
/// If not found, looks for the dataset name in the file specified by mDatasetLookupFilePath
/// </summary>
/// <param name="inputFilePath"></param>
/// <param name="datasetLookupFilePath"></param>
/// <param name="defaultDatasetID"></param>
public int LookupDatasetID(
string inputFilePath,
string datasetLookupFilePath,
int defaultDatasetID)
{
var datasetName = Path.GetFileNameWithoutExtension(inputFilePath);
int newDatasetID;
// ReSharper disable once CommentTypo
// Data Source=gigasax;Initial Catalog=DMS5;User=DMSReader;Password=...
if (!string.IsNullOrWhiteSpace(mOptions.DatabaseConnectionString))
{
var datasetFoundInDB = GetDatasetIDFromDatabase(mOptions, datasetName, out newDatasetID);
if (datasetFoundInDB)
{
return newDatasetID;
}
}
if (!string.IsNullOrWhiteSpace(datasetLookupFilePath))
{
var datasetFoundInFile = GetDatasetIDFromFile(datasetLookupFilePath, datasetName, out newDatasetID);
if (datasetFoundInFile)
{
return newDatasetID;
}
}
return defaultDatasetID;
}
/// <summary>
/// Attempt to lookup the Dataset ID in the database
/// </summary>
/// <param name="masicOptions"></param>
/// <param name="datasetName"></param>
/// <param name="newDatasetID"></param>
/// <returns>Dataset ID</returns>
private bool GetDatasetIDFromDatabase(MASICOptions masicOptions, string datasetName, out int newDatasetID)
{
const string avoidErrorMessage =
"To avoid seeing this message in the future, clear the 'SQL Server Connection String' field " +
"on the Advanced tab and save a new settings file. Alternatively, edit a MASIC parameter file " +
"to have <item key=\"ConnectionString\" value=\"\" /> ";
newDatasetID = 0;
try
{
var applicationName = string.Format("MASIC_{0}", datasetName);
var connectionStringToUse = DbToolsFactory.AddApplicationNameToConnectionString(masicOptions.DatabaseConnectionString, applicationName);
var dbTools = DbToolsFactory.GetDBTools(connectionStringToUse);
var queryingSingleDataset = false;
for (var iteration = 1; iteration <= 2; iteration++)
{
var sqlQuery = masicOptions.DatasetInfoQuerySql;
if (string.IsNullOrEmpty(sqlQuery))
{
sqlQuery = "Select Dataset, ID FROM V_Dataset_Export";
}
if (sqlQuery.StartsWith("SELECT Dataset", StringComparison.OrdinalIgnoreCase))
{
// Add a where clause to the query
if (iteration == 1)
{
sqlQuery += " WHERE Dataset = '" + datasetName + "'";
queryingSingleDataset = true;
}
else
{
sqlQuery += " WHERE Dataset Like '" + datasetName + "%'";
}
}
var success = dbTools.GetQueryResults(sqlQuery, out var results);
if (success)
{
// Find the row in the lstResults that matches fileNameCompare
foreach (var datasetItem in results)
{
if (string.Equals(datasetItem[0], datasetName, StringComparison.OrdinalIgnoreCase))
{
// Match found
if (int.TryParse(datasetItem[1], out newDatasetID))
{
return true;
}
ReportError("Error converting Dataset ID '" + datasetItem[1] + "' to an integer", clsMASIC.MasicErrorCodes.InvalidDatasetID);
break;
}
}
if (results.Count > 0)
{
try
{
if (queryingSingleDataset || results.First()[0].StartsWith(datasetName))
{
if (int.TryParse(results.First()[1], out newDatasetID))
{
return true;
}
}
}
catch (Exception)
{
// Ignore errors here
}
}
}
}
return false;
}
catch (NullReferenceException)
{
ReportError("Error connecting to database: " + masicOptions.DatabaseConnectionString + Environment.NewLine + avoidErrorMessage, clsMASIC.MasicErrorCodes.InvalidDatasetID);
return false;
}
catch (Exception ex)
{
ReportError("Error connecting to database: " + masicOptions.DatabaseConnectionString + Environment.NewLine + avoidErrorMessage, ex, clsMASIC.MasicErrorCodes.InvalidDatasetID);
return false;
}
}
/// <summary>
/// Lookup the dataset ID in the dataset lookup file
/// This is a comma, space, or tab delimited file with two columns: Dataset Name and Dataset ID
/// </summary>
/// <param name="datasetLookupFilePath"></param>
/// <param name="datasetName"></param>
/// <param name="newDatasetId"></param>
private bool GetDatasetIDFromFile(string datasetLookupFilePath, string datasetName, out int newDatasetId)
{
var delimiterList = new[] { ' ', ',', '\t' };
newDatasetId = 0;
try
{
using var reader = new StreamReader(datasetLookupFilePath);
while (!reader.EndOfStream)
{
var dataLine = reader.ReadLine();
if (string.IsNullOrWhiteSpace(dataLine))
{
continue;
}
if (dataLine.Length < datasetName.Length)
{
continue;
}
var dataValues = dataLine.Split(delimiterList);
if (dataValues.Length < 2)
{
continue;
}
if (!string.Equals(dataValues[0], datasetName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
if (int.TryParse(dataValues[1], out newDatasetId))
{
return true;
}
ReportError("Error converting Dataset ID '" + dataValues[1] + "' to an integer", clsMASIC.MasicErrorCodes.InvalidDatasetID);
}
return false;
}
catch (Exception ex)
{
ReportError("Error reading the dataset lookup file", ex, clsMASIC.MasicErrorCodes.InvalidDatasetLookupFilePath);
return false;
}
}
}
}