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