/// <summary>
/// Looks up data stored in the Catalogue with a query matching on any of the provided uids. All values must be supplied if the Catalogue has a column of the corresponding name (i.e. if Catalogue has SeriesInstanceUID you must supply <paramref name="seriesuid"/>)
/// </summary>
/// <param name="studyuid"></param>
/// <param name="seriesuid"></param>
/// <param name="imageuid"></param>
/// <returns></returns>
public bool DoLookup(string studyuid, string seriesuid, string imageuid)
{
string sql = _queryBuilder.SQL;
using (var con = _server.GetConnection())
{
con.Open();
using (var cmd = _server.GetCommand(sql, con))
{
//Add the current row UIDs to the parameters of the command
if (_studyFilter != null)
{
_server.AddParameterWithValueToCommand(QueryToExecuteColumnSet.DefaultStudyIdColumnName, cmd, studyuid);
}
if (_seriesFilter != null)
{
_server.AddParameterWithValueToCommand(QueryToExecuteColumnSet.DefaultSeriesIdColumnName, cmd, seriesuid);
}
if (_instanceFilter != null)
{
_server.AddParameterWithValueToCommand(QueryToExecuteColumnSet.DefaultInstanceIdColumnName, cmd, imageuid);
}
using (var r = cmd.ExecuteReader())
{
//if we can read a record then we have an entry in the blacklist
return(r.Read());
}
}
}
}
private void CheckDatasetExists(ICheckNotifier notifier, string dataSetID)
{
using (var conn = _server.GetConnection())
{
conn.Open();
var cmd = _server.GetCommand("SELECT 1 FROM DataSet WHERE dataSetID=@dsID", conn);
_server.AddParameterWithValueToCommand("@dsID", cmd, dataSetID);
var found = cmd.ExecuteScalar();
if (found != null)
{
notifier.OnCheckPerformed(new CheckEventArgs("Found default dataset: " + dataSetID, CheckResult.Success, null));
return;
}
if (notifier.OnCheckPerformed(new CheckEventArgs("Did not find default dataset '" + dataSetID + "'.", CheckResult.Fail, null, "Create the dataset '" + dataSetID + "'")))
{
cmd = _server.GetCommand("INSERT INTO DataSet (dataSetID, name) VALUES (@dsID, @dsID)", conn);
_server.AddParameterWithValueToCommand("@dsID", cmd, dataSetID);
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Marks that the data load ended
/// </summary>
public void CloseAndMarkComplete()
{
lock (oLock)
{
//prevent double closing
if (_isClosed)
{
return;
}
_endTime = DateTime.Now;
using (var con = _server.BeginNewTransactedConnection())
{
try
{
DbCommand cmdUpdateToClosed =
_server.GetCommand("UPDATE DataLoadRun SET endTime=@endTime WHERE ID=@ID",
con);
_server.AddParameterWithValueToCommand("@endTime", cmdUpdateToClosed, DateTime.Now);
_server.AddParameterWithValueToCommand("@ID", cmdUpdateToClosed, ID);
int rowsAffected = cmdUpdateToClosed.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new Exception(
"Error closing off DataLoad in database, the update command resulted in " +
rowsAffected + " rows being affected (expected 1) - will try to rollback");
}
con.ManagedTransaction.CommitAndCloseConnection();
_isClosed = true;
}
catch (Exception)
{
//if something goes wrong with the update, roll it back
con.ManagedTransaction.AbandonAndCloseConnection();
throw;
}
//once a record has been commited to the database it is redundant and no further attempts to read/change it should be made by anyone
foreach (TableLoadInfo t in this.TableLoads.Values)
{
//close any table loads that have not yet completed
if (!t.IsClosed)
{
t.CloseAndArchive();
}
}
}
}
}
public override string GetSubstitutionFor(string toSwap, out string reason)
{
reason = null;
// If the cached key matches, return the last value
if (string.Equals(toSwap, _lastKey) && _lastVal != null)
{
_logger.Debug("Using cached swap value");
CacheHit++;
Success++;
return(_lastVal);
}
CacheMiss++;
// Else fall through to the database lookup
using (new TimeTracker(DatabaseStopwatch))
using (DbConnection con = _server.GetConnection())
{
con.Open();
string sql = string.Format("SELECT {0} FROM {1} WHERE {2}=@val",
_options.ReplacementColumnName,
_swapTable.GetFullyQualifiedName(),
_options.SwapColumnName);
DbCommand cmd = _server.GetCommand(sql, con);
_server.AddParameterWithValueToCommand("@val", cmd, toSwap);
object result = cmd.ExecuteScalar();
if (result == DBNull.Value || result == null)
{
reason = "No match found for '" + toSwap + "'";
Fail++;
return(null);
}
_lastKey = toSwap;
_lastVal = result.ToString();
++Success;
return(_lastVal);
}
}
public void Update(UIDMapping mapping)
{
var table = _database.ExpectTable(_tableName);
var sql = "UPDATE " + table.GetFullyQualifiedName() + " SET " +
"PrivateUID = @PrivateUID, " +
"ProjectNumber = @ProjectNumber, " +
"UIDType = @UIDType, " +
"IsExternalReference = @IsExternalReference " +
"WHERE ReleaseUID = @ReleaseUID";
using (var conn = GetConnection())
{
conn.Open();
var cmd = _server.GetCommand(sql, conn);
_server.AddParameterWithValueToCommand("@PrivateUID", cmd, mapping.PrivateUID);
_server.AddParameterWithValueToCommand("@ProjectNumber", cmd, mapping.ProjectNumber);
_server.AddParameterWithValueToCommand("@UIDType", cmd, mapping.UIDType);
_server.AddParameterWithValueToCommand("@IsExternalReference", cmd, mapping.IsExternalReference);
_server.AddParameterWithValueToCommand("@ReleaseUID", cmd, mapping.ReleaseUID);
cmd.ExecuteNonQuery();
}
}
public override int UploadImpl(DataTable dt)
{
//don't run an insert if there are 0 rows
if (dt.Rows.Count == 0)
{
return(0);
}
var syntaxHelper = _server.GetQuerySyntaxHelper();
var tt = syntaxHelper.TypeTranslater;
//if the column name is a reserved keyword e.g. "Comment" we need to give it a new name
Dictionary <DataColumn, string> parameterNames = syntaxHelper.GetParameterNamesFor(dt.Columns.Cast <DataColumn>().ToArray(), c => c.ColumnName);
int affectedRows = 0;
var mapping = GetMapping(dt.Columns.Cast <DataColumn>());
var dateColumns = new HashSet <DataColumn>();
var sql = string.Format("INSERT INTO " + TargetTable.GetFullyQualifiedName() + "({0}) VALUES ({1})",
string.Join(",", mapping.Values.Select(c => '"' + c.GetRuntimeName() + '"')),
string.Join(",", mapping.Keys.Select(c => parameterNames[c]))
);
using (OracleCommand cmd = (OracleCommand)_server.GetCommand(sql, Connection))
{
//send all the data at once
cmd.ArrayBindCount = dt.Rows.Count;
foreach (var kvp in mapping)
{
var p = _server.AddParameterWithValueToCommand(parameterNames[kvp.Key], cmd, DBNull.Value);
p.DbType = tt.GetDbTypeForSQLDBType(kvp.Value.DataType.SQLType);
if (p.DbType == DbType.DateTime)
{
dateColumns.Add(kvp.Key);
}
}
var values = new Dictionary <DataColumn, List <object> >();
foreach (DataColumn c in mapping.Keys)
{
values.Add(c, new List <object>());
}
foreach (DataRow dataRow in dt.Rows)
{
//populate parameters for current row
foreach (var col in mapping.Keys)
{
var val = dataRow[col];
if (val is string && string.IsNullOrWhiteSpace((string)val))
{
val = null;
}
else
if (val == null || val == DBNull.Value)
{
val = null;
}
else if (dateColumns.Contains(col))
{
if (val is string s)
{
val = (DateTime)DateTimeDecider.Parse(s);
}
else
{
val = Convert.ToDateTime(dataRow[col]);
}
}
values[col].Add(val);
}
}
foreach (DataColumn col in mapping.Keys)
{
var param = cmd.Parameters[parameterNames[col]];
param.Value = values[col].ToArray();
}
//send query
affectedRows += cmd.ExecuteNonQuery();
}
return(affectedRows);
}
