public async Task AddExtendedQueryTagsAsync(IEnumerable <AddExtendedQueryTagEntry> extendedQueryTagEntries, CancellationToken cancellationToken = default)
{
if (_schemaInformation.Current < SchemaVersionConstants.SupportExtendedQueryTagSchemaVersion)
{
throw new BadRequestException(DicomSqlServerResource.SchemaVersionNeedsToBeUpgraded);
}
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
IEnumerable <AddExtendedQueryTagsInputTableTypeV1Row> rows = extendedQueryTagEntries.Select(ToAddExtendedQueryTagsInputTableTypeV1Row);
VLatest.AddExtendedQueryTags.PopulateCommand(sqlCommandWrapper, new VLatest.AddExtendedQueryTagsTableValuedParameters(rows));
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException ex)
{
switch (ex.Number)
{
case SqlErrorCodes.Conflict:
throw new ExtendedQueryTagsAlreadyExistsException();
default:
throw new DataStoreException(ex);
}
}
}
}
public async Task GivenATransactionScope_WhenReadingAfterComplete_TheValuesShouldBeAvailable()
{
var newId = Guid.NewGuid().ToString();
var searchParamHash = new string("RandomSearchParam").ComputeHash();
using (var transactionScope = _fixture.SqlTransactionHandler.BeginTransaction())
{
using (SqlConnectionWrapper connectionWrapperWithTransaction = await _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(CancellationToken.None, true))
using (SqlCommandWrapper sqlCommandWrapper = connectionWrapperWithTransaction.CreateSqlCommand())
{
sqlCommandWrapper.CommandText = @"
INSERT INTO Resource
VALUES(97, @newId, 1, 0, 5095719085917680001, 0, null, CAST('test' AS VARBINARY(MAX)), 0, @searchParamHash)";
sqlCommandWrapper.Parameters.Add(new SqlParameter {
ParameterName = "newId", Value = newId
});
sqlCommandWrapper.Parameters.Add(new SqlParameter {
ParameterName = "searchParamHash", Value = searchParamHash
});
await sqlCommandWrapper.ExecuteNonQueryAsync(CancellationToken.None);
}
transactionScope.Complete();
}
// Outside of the transaction scope, the resource should not be found
using (SqlConnectionWrapper connectionWrapperWithTransaction = await _fixture.SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(CancellationToken.None, false))
{
await VerifyCommandResults(connectionWrapperWithTransaction, newId, true);
}
}
public override async Task ReindexInstanceAsync(DicomDataset instance, long watermark, IEnumerable <QueryTag> queryTags, CancellationToken cancellationToken = default)
{
EnsureArg.IsNotNull(instance, nameof(instance));
EnsureArg.IsNotNull(queryTags, nameof(queryTags));
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
var rows = ExtendedQueryTagDataRowsBuilder.Build(instance, queryTags, Version);
VLatest.IndexInstanceV2TableValuedParameters parameters = new VLatest.IndexInstanceV2TableValuedParameters(
rows.StringRows,
rows.LongRows,
rows.DoubleRows,
rows.DateTimeWithUtcRows,
rows.PersonNameRows);
VLatest.IndexInstanceV2.PopulateCommand(sqlCommandWrapper, watermark, parameters);
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException ex)
{
throw ex.Number switch
{
SqlErrorCodes.NotFound => new InstanceNotFoundException(),
SqlErrorCodes.Conflict => new PendingInstanceException(),
_ => new DataStoreException(ex),
};
}
}
}
private static async Task VerifyCommandResults(SqlConnectionWrapper connectionWrapper, string newId, bool shouldFind, string tableHints = "")
{
using (SqlCommandWrapper sqlCommandWrapper = connectionWrapper.CreateSqlCommand())
{
sqlCommandWrapper.CommandText = $@"
SELECT *
FROM resource {tableHints}
WHERE ResourceId = @newId";
sqlCommandWrapper.Parameters.Add(new SqlParameter {
ParameterName = "newId", Value = newId
});
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CancellationToken.None))
{
if (shouldFind)
{
while (reader.Read())
{
Assert.Equal(newId, reader["resourceId"]);
}
}
else
{
Assert.False(reader.HasRows);
}
}
}
}
public void PopulateCommand(SqlCommandWrapper command, System.Int32 limit, System.Int64 offset)
{
command.CommandType = global::System.Data.CommandType.StoredProcedure;
command.CommandText = "dbo.GetChangeFeed";
_limit.AddParameter(command.Parameters, limit);
_offset.AddParameter(command.Parameters, offset);
}
public override async Task <IReadOnlyList <int> > CompleteReindexingAsync(IReadOnlyCollection <int> queryTagKeys, CancellationToken cancellationToken = default)
{
EnsureArg.HasItems(queryTagKeys, nameof(queryTagKeys));
using SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken);
using SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand();
IEnumerable <ExtendedQueryTagKeyTableTypeV1Row> rows = queryTagKeys.Select(x => new ExtendedQueryTagKeyTableTypeV1Row(x));
VLatest.CompleteReindexing.PopulateCommand(sqlCommandWrapper, rows);
try
{
var keys = new List <int>();
using SqlDataReader reader = await sqlCommandWrapper.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
keys.Add(reader.ReadRow(VLatest.ExtendedQueryTagString.TagKey));
}
return(keys);
}
catch (SqlException ex)
{
throw new DataStoreException(ex);
}
}
public async Task UpdateSearchParameterIndicesBatchAsync(IReadOnlyCollection <ResourceWrapper> resources, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.BulkReindexResources.PopulateCommand(
sqlCommandWrapper,
_bulkReindexResourcesTvpGeneratorVLatest.Generate(resources.ToList()));
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException e)
{
switch (e.Number)
{
// TODO: we should attempt to reindex resources that failed to be reindexed
case SqlErrorCodes.PreconditionFailed:
throw new PreconditionFailedException(string.Format(Core.Resources.ReindexingResourceVersionConflict));
case SqlErrorCodes.NotFound:
throw new ResourceNotFoundException(string.Format(Core.Resources.ReindexingResourceNotFound));
default:
_logger.LogError(e, "Error from SQL database on reindex");
throw;
}
}
}
}
private string RetrieveXmlFromDatabase(SqlCommandWrapper sqlCommand)
{
string actualOutput = "";
XmlReader reader = null;
try
{
reader = sqlDatabase.ExecuteXmlReader(sqlCommand);
reader.MoveToContent();
for (string value = reader.ReadOuterXml(); value != null && value.Length != 0; value = reader.ReadOuterXml())
{
actualOutput += value;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
sqlCommand.Command.Connection.Close();
}
return(actualOutput);
}
public override async Task <IReadOnlyList <ExtendedQueryTagStoreEntry> > GetExtendedQueryTagsAsync(Guid operationId, CancellationToken cancellationToken = default)
{
var results = new List <ExtendedQueryTagStoreEntry>();
using (SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetExtendedQueryTagsByOperation.PopulateCommand(sqlCommandWrapper, operationId);
using (SqlDataReader reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
(int tagKey, string tagPath, string tagVR, string tagPrivateCreator, int tagLevel, int tagStatus, byte queryStatus, int errorCount) = reader.ReadRow(
VLatest.ExtendedQueryTag.TagKey,
VLatest.ExtendedQueryTag.TagPath,
VLatest.ExtendedQueryTag.TagVR,
VLatest.ExtendedQueryTag.TagPrivateCreator,
VLatest.ExtendedQueryTag.TagLevel,
VLatest.ExtendedQueryTag.TagStatus,
VLatest.ExtendedQueryTag.QueryStatus,
VLatest.ExtendedQueryTag.ErrorCount);
results.Add(new ExtendedQueryTagStoreEntry(tagKey, tagPath, tagVR, tagPrivateCreator, (QueryTagLevel)tagLevel, (ExtendedQueryTagStatus)tagStatus, (QueryStatus)queryStatus, errorCount));
}
}
}
return(results);
}
public async Task <IReadOnlyCollection <ExportJobOutcome> > AcquireExportJobsAsync(ushort maximumNumberOfConcurrentJobsAllowed, TimeSpan jobHeartbeatTimeoutThreshold, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapper(true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
var jobHeartbeatTimeoutThresholdInSeconds = Convert.ToInt64(jobHeartbeatTimeoutThreshold.TotalSeconds);
VLatest.AcquireExportJobs.PopulateCommand(
sqlCommandWrapper,
jobHeartbeatTimeoutThresholdInSeconds,
maximumNumberOfConcurrentJobsAllowed);
var acquiredJobs = new List <ExportJobOutcome>();
using (SqlDataReader sqlDataReader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
(string rawJobRecord, byte[] rowVersion) = sqlDataReader.ReadRow(VLatest.ExportJob.RawJobRecord, VLatest.ExportJob.JobVersion);
acquiredJobs.Add(CreateExportJobOutcome(rawJobRecord, rowVersion));
}
}
return(acquiredJobs);
}
}
public void PopulateCommand(SqlCommandWrapper command, System.Int32 version, System.String status)
{
command.CommandType = global::System.Data.CommandType.StoredProcedure;
command.CommandText = "dbo.UpsertSchemaVersion";
_version.AddParameter(command.Parameters, version);
_status.AddParameter(command.Parameters, status);
}
private async Task <List <ExtendedQueryTagStoreJoinEntry> > GetAllExtendedQueryTagsAsync(CancellationToken cancellationToken = default)
{
var results = new List <ExtendedQueryTagStoreJoinEntry>();
using (SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
// V2 version allows NULL to get all tags
V2.GetExtendedQueryTag.PopulateCommand(sqlCommandWrapper, null);
var executionTimeWatch = Stopwatch.StartNew();
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
(int tagKey, string tagPath, string tagVR, string tagPrivateCreator, int tagLevel, int tagStatus) = reader.ReadRow(
V2.ExtendedQueryTag.TagKey,
V2.ExtendedQueryTag.TagPath,
V2.ExtendedQueryTag.TagVR,
V2.ExtendedQueryTag.TagPrivateCreator,
V2.ExtendedQueryTag.TagLevel,
V2.ExtendedQueryTag.TagStatus);
results.Add(new ExtendedQueryTagStoreJoinEntry(tagKey, tagPath, tagVR, tagPrivateCreator, (QueryTagLevel)tagLevel, (ExtendedQueryTagStatus)tagStatus, QueryStatus.Enabled, 0));
}
executionTimeWatch.Stop();
Logger.StoredProcedureSucceeded(nameof(V2.GetExtendedQueryTag), executionTimeWatch);
}
}
return(results);
}
public override async Task AddExtendedQueryTagErrorAsync(
int tagKey,
ValidationErrorCode errorCode,
long watermark,
CancellationToken cancellationToken = default)
{
EnsureArg.EnumIsDefined(errorCode, nameof(errorCode));
using SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken);
using SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand();
VLatest.AddExtendedQueryTagError.PopulateCommand(
sqlCommandWrapper,
tagKey,
(short)errorCode,
watermark);
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException e)
{
if (e.Number == SqlErrorCodes.NotFound)
{
throw new ExtendedQueryTagNotFoundException(
string.Format(
CultureInfo.InvariantCulture,
DicomSqlServerResource.ExtendedQueryTagNotFoundWhenAddingError,
tagKey));
}
throw new DataStoreException(e);
}
}
public override async Task DeleteExtendedQueryTagAsync(string tagPath, string vr, CancellationToken cancellationToken = default)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
V2.DeleteExtendedQueryTag.PopulateCommand(sqlCommandWrapper, tagPath, (byte)ExtendedQueryTagLimit.ExtendedQueryTagVRAndDataTypeMapping[vr]);
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException ex)
{
switch (ex.Number)
{
case SqlErrorCodes.NotFound:
throw new ExtendedQueryTagNotFoundException(
string.Format(CultureInfo.InvariantCulture, DicomSqlServerResource.ExtendedQueryTagNotFound, tagPath));
case SqlErrorCodes.PreconditionFailed:
throw new ExtendedQueryTagBusyException(
string.Format(CultureInfo.InvariantCulture, DicomSqlServerResource.ExtendedQueryTagIsBusy, tagPath));
default:
throw new DataStoreException(ex);
}
}
}
}
public override async Task <PartitionEntry> GetPartitionAsync(string partitionName, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetPartition.PopulateCommand(sqlCommandWrapper, partitionName);
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
if (await reader.ReadAsync(cancellationToken))
{
(int rPartitionKey, string rPartitionName, DateTimeOffset rCreatedDate) = reader.ReadRow(
VLatest.Partition.PartitionKey,
VLatest.Partition.PartitionName,
VLatest.Partition.CreatedDate);
return(new PartitionEntry(
rPartitionKey,
rPartitionName,
rCreatedDate));
}
}
}
return(null);
}
public override async Task <IEnumerable <PartitionEntry> > GetPartitionsAsync(CancellationToken cancellationToken)
{
var results = new List <PartitionEntry>();
using (SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetPartitions.PopulateCommand(sqlCommandWrapper);
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
(int rPartitionKey, string rPartitionName, DateTimeOffset rCreatedDate) = reader.ReadRow(
VLatest.Partition.PartitionKey,
VLatest.Partition.PartitionName,
VLatest.Partition.CreatedDate);
results.Add(new PartitionEntry(
rPartitionKey,
rPartitionName,
rCreatedDate));
}
}
return(results);
}
}
// TODO: Make cancellation token an input.
public async Task <IReadOnlyCollection <ResourceSearchParameterStatus> > GetSearchParameterStatuses()
{
// If the search parameter table in SQL does not yet contain status columns
if (_schemaInformation.Current < SchemaVersionConstants.SearchParameterStatusSchemaVersion)
{
// Get status information from file.
return(await _filebasedSearchParameterStatusDataStore.GetSearchParameterStatuses());
}
using (IScoped <SqlConnectionWrapperFactory> scopedSqlConnectionWrapperFactory = _scopedSqlConnectionWrapperFactory())
using (SqlConnectionWrapper sqlConnectionWrapper = await scopedSqlConnectionWrapperFactory.Value.ObtainSqlConnectionWrapperAsync(CancellationToken.None, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetSearchParamStatuses.PopulateCommand(sqlCommandWrapper);
var parameterStatuses = new List <ResourceSearchParameterStatus>();
using (SqlDataReader sqlDataReader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, CancellationToken.None))
{
while (await sqlDataReader.ReadAsync())
{
(string uri, string stringStatus, DateTimeOffset? lastUpdated, bool?isPartiallySupported) = sqlDataReader.ReadRow(
VLatest.SearchParam.Uri,
VLatest.SearchParam.Status,
VLatest.SearchParam.LastUpdated,
VLatest.SearchParam.IsPartiallySupported);
if (string.IsNullOrEmpty(stringStatus) || lastUpdated == null || isPartiallySupported == null)
{
// These columns are nullable because they are added to dbo.SearchParam in a later schema version.
// They should be populated as soon as they are added to the table and should never be null.
throw new NullReferenceException(Resources.SearchParameterStatusShouldNotBeNull);
}
var status = Enum.Parse <SearchParameterStatus>(stringStatus, true);
var resourceSearchParameterStatus = new ResourceSearchParameterStatus()
{
Uri = new Uri(uri),
Status = status,
IsPartiallySupported = (bool)isPartiallySupported,
LastUpdated = (DateTimeOffset)lastUpdated,
};
if (SqlServerSortingValidator.SupportedParameterUris.Contains(resourceSearchParameterStatus.Uri))
{
resourceSearchParameterStatus.SortStatus = SortParameterStatus.Enabled;
}
else
{
resourceSearchParameterStatus.SortStatus = SortParameterStatus.Supported;
}
parameterStatuses.Add(resourceSearchParameterStatus);
}
}
return(parameterStatuses);
}
}
public override async Task <ExtendedQueryTagStoreJoinEntry> GetExtendedQueryTagAsync(string path, CancellationToken cancellationToken = default)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await ConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.GetExtendedQueryTag.PopulateCommand(sqlCommandWrapper, path);
var executionTimeWatch = Stopwatch.StartNew();
using (var reader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
if (!await reader.ReadAsync(cancellationToken))
{
throw new ExtendedQueryTagNotFoundException(string.Format(DicomCoreResource.ExtendedQueryTagNotFound, path));
}
(int tagKey, string tagPath, string tagVR, string tagPrivateCreator, int tagLevel, int tagStatus, byte queryStatus, int errorCount, Guid? operationId) = reader.ReadRow(
VLatest.ExtendedQueryTag.TagKey,
VLatest.ExtendedQueryTag.TagPath,
VLatest.ExtendedQueryTag.TagVR,
VLatest.ExtendedQueryTag.TagPrivateCreator,
VLatest.ExtendedQueryTag.TagLevel,
VLatest.ExtendedQueryTag.TagStatus,
VLatest.ExtendedQueryTag.QueryStatus,
VLatest.ExtendedQueryTag.ErrorCount,
VLatest.ExtendedQueryTagOperation.OperationId.AsNullable());
executionTimeWatch.Stop();
Logger.StoredProcedureSucceeded(nameof(VLatest.GetExtendedQueryTag), executionTimeWatch);
return(new ExtendedQueryTagStoreJoinEntry(tagKey, tagPath, tagVR, tagPrivateCreator, (QueryTagLevel)tagLevel, (ExtendedQueryTagStatus)tagStatus, (QueryStatus)queryStatus, errorCount, operationId));
}
}
}
/// <summary>
/// <para>Executes the <see cref="SqlCommandWrapper"/> and returns an <see cref="XmlReader"/>.</para>
/// </summary>
/// <param name="command">
/// <para>The <see cref="SqlCommandWrapper"/> to execute.</para>
/// </param>
/// <returns>
/// <para>An <see cref="XmlReader"/> object.</para>
/// </returns>
public XmlReader ExecuteXmlReader(SqlCommandWrapper command)
{
IDbConnection connection = OpenConnection();
PrepareCommand(command, connection);
SqlCommand sqlCommand = command.Command as SqlCommand;
return DoExecuteXmlReader(sqlCommand);
}
public async Task DeleteExtendedQueryTagAsync(string tagPath, string vr, CancellationToken cancellationToken = default)
{
if (_schemaInformation.Current < SchemaVersionConstants.SupportExtendedQueryTagSchemaVersion)
{
throw new BadRequestException(DicomSqlServerResource.SchemaVersionNeedsToBeUpgraded);
}
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.DeleteExtendedQueryTag.PopulateCommand(sqlCommandWrapper, tagPath, (byte)ExtendedQueryTagLimit.ExtendedQueryTagVRAndDataTypeMapping[vr]);
try
{
await sqlCommandWrapper.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException ex)
{
switch (ex.Number)
{
case SqlErrorCodes.NotFound:
throw new ExtendedQueryTagNotFoundException(
string.Format(CultureInfo.InvariantCulture, DicomSqlServerResource.ExtendedQueryTagNotFound, tagPath));
case SqlErrorCodes.PreconditionFailed:
throw new ExtendedQueryTagBusyException(
string.Format(CultureInfo.InvariantCulture, DicomSqlServerResource.ExtendedQueryTagIsBusy, tagPath));
default:
throw new DataStoreException(ex);
}
}
}
}
public void PopulateCommand(SqlCommandWrapper command, System.Int32 count, System.Int32 maxRetries)
{
command.CommandType = global::System.Data.CommandType.StoredProcedure;
command.CommandText = "dbo.RetrieveDeletedInstance";
_count.AddParameter(command.Parameters, count);
_maxRetries.AddParameter(command.Parameters, maxRetries);
}
public void PopulateCommand(SqlCommandWrapper command, System.String tagPath, System.Byte dataType)
{
command.CommandType = global::System.Data.CommandType.StoredProcedure;
command.CommandText = "dbo.DeleteExtendedQueryTag";
_tagPath.AddParameter(command.Parameters, tagPath);
_dataType.AddParameter(command.Parameters, dataType);
}
public override async Task <IReadOnlyList <WatermarkRange> > GetInstanceBatchesAsync(
int batchSize,
int batchCount,
IndexStatus indexStatus,
long?maxWatermark = null,
CancellationToken cancellationToken = default)
{
EnsureArg.IsGt(batchSize, 0, nameof(batchSize));
EnsureArg.IsGt(batchCount, 0, nameof(batchCount));
using SqlConnectionWrapper sqlConnectionWrapper = await SqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken);
using SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand();
VLatest.GetInstanceBatches.PopulateCommand(sqlCommandWrapper, batchSize, batchCount, (byte)indexStatus, maxWatermark);
try
{
var batches = new List <WatermarkRange>();
using SqlDataReader reader = await sqlCommandWrapper.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
batches.Add(new WatermarkRange(reader.GetInt64(0), reader.GetInt64(1)));
}
return(batches);
}
catch (SqlException ex)
{
throw new DataStoreException(ex);
}
}
public async Task UpsertStatuses(IReadOnlyCollection <ResourceSearchParameterStatus> statuses, CancellationToken cancellationToken)
{
EnsureArg.IsNotNull(statuses, nameof(statuses));
if (!statuses.Any())
{
return;
}
if (_schemaInformation.Current < SchemaVersionConstants.SearchParameterStatusSchemaVersion)
{
throw new BadRequestException(Resources.SchemaVersionNeedsToBeUpgraded);
}
using (IScoped <SqlConnectionWrapperFactory> scopedSqlConnectionWrapperFactory = _scopedSqlConnectionWrapperFactory())
using (SqlConnectionWrapper sqlConnectionWrapper = await scopedSqlConnectionWrapperFactory.Value.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.UpsertSearchParams.PopulateCommand(sqlCommandWrapper, _updateSearchParamsTvpGenerator.Generate(statuses.ToList()));
using (SqlDataReader sqlDataReader = await sqlCommandWrapper.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
// The upsert procedure returns the search parameters that were new.
(short searchParamId, string searchParamUri) = sqlDataReader.ReadRow(VLatest.SearchParam.SearchParamId, VLatest.SearchParam.Uri);
// Add the new search parameters to the FHIR model dictionary.
_fhirModel.TryAddSearchParamIdToUriMapping(searchParamUri, searchParamId);
}
}
}
}
public async Task <int> IncrementDeletedInstanceRetryAsync(VersionedInstanceIdentifier versionedInstanceIdentifier, DateTimeOffset cleanupAfter, CancellationToken cancellationToken = default)
{
await _sqlServerIndexSchema.EnsureInitialized();
using (SqlConnectionWrapper sqlConnectionWrapper = _sqlConnectionFactoryWrapper.ObtainSqlConnectionWrapper(true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
VLatest.IncrementDeletedInstanceRetry.PopulateCommand(
sqlCommandWrapper,
versionedInstanceIdentifier.StudyInstanceUid,
versionedInstanceIdentifier.SeriesInstanceUid,
versionedInstanceIdentifier.SopInstanceUid,
versionedInstanceIdentifier.Version,
cleanupAfter);
try
{
return((int)(await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken)));
}
catch (SqlException ex)
{
throw new DataStoreException(ex);
}
}
}
public async Task <UpsertOutcome> UpsertAsync(ResourceWrapper resource, WeakETag weakETag, bool allowCreate, bool keepHistory, CancellationToken cancellationToken)
{
int?eTag = weakETag == null
? (int?)null
: (int.TryParse(weakETag.VersionId, out var parsedETag) ? parsedETag : -1); // Set the etag to a sentinel value to enable expected failure paths when updating with both existing and nonexistent resources.
var resourceMetadata = new ResourceMetadata(
resource.CompartmentIndices,
resource.SearchIndices?.ToLookup(e => _searchParameterTypeMap.GetSearchValueType(e)),
resource.LastModifiedClaims);
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
using (var stream = new RecyclableMemoryStream(_memoryStreamManager))
{
CompressedRawResourceConverter.WriteCompressedRawResource(stream, resource.RawResource.Data);
stream.Seek(0, 0);
PopulateUpsertResourceCommand(sqlCommandWrapper, resource, resourceMetadata, allowCreate, keepHistory, eTag, stream);
try
{
var newVersion = (int?)await sqlCommandWrapper.ExecuteScalarAsync(cancellationToken);
if (newVersion == null)
{
// indicates a redundant delete
return(null);
}
resource.Version = newVersion.ToString();
return(new UpsertOutcome(resource, newVersion == 1 ? SaveOutcomeType.Created : SaveOutcomeType.Updated));
}
catch (SqlException e)
{
switch (e.Number)
{
case SqlErrorCodes.PreconditionFailed:
throw new PreconditionFailedException(string.Format(Core.Resources.ResourceVersionConflict, weakETag?.VersionId));
case SqlErrorCodes.NotFound:
if (weakETag != null)
{
throw new ResourceNotFoundException(string.Format(Core.Resources.ResourceNotFoundByIdAndVersion, resource.ResourceTypeName, resource.ResourceId, weakETag.VersionId));
}
goto default;
case SqlErrorCodes.MethodNotAllowed:
throw new MethodNotAllowedException(Core.Resources.ResourceCreationNotAllowed);
default:
_logger.LogError(e, "Error from SQL database on upsert");
throw;
}
}
}
}
public async Task <TaskInfo> ResetAsync(string taskId, TaskResultData taskResultData, string runId, CancellationToken cancellationToken)
{
using (SqlConnectionWrapper sqlConnectionWrapper = await _sqlConnectionWrapperFactory.ObtainSqlConnectionWrapperAsync(cancellationToken, true))
using (SqlCommandWrapper sqlCommandWrapper = sqlConnectionWrapper.CreateSqlCommand())
{
try
{
VLatest.ResetTask.PopulateCommand(sqlCommandWrapper, taskId, runId, JsonConvert.SerializeObject(taskResultData));
SqlDataReader sqlDataReader = await sqlCommandWrapper.ExecuteReaderAsync(cancellationToken);
if (!sqlDataReader.Read())
{
return(null);
}
var taskInfoTable = VLatest.TaskInfo;
_ = sqlDataReader.Read(taskInfoTable.TaskId, 0);
string queueId = sqlDataReader.Read(taskInfoTable.QueueId, 1);
short status = sqlDataReader.Read(taskInfoTable.Status, 2);
short taskTypeId = sqlDataReader.Read(taskInfoTable.TaskTypeId, 3);
string taskRunId = sqlDataReader.Read(taskInfoTable.RunId, 4);
bool isCanceled = sqlDataReader.Read(taskInfoTable.IsCanceled, 5);
short retryCount = sqlDataReader.Read(taskInfoTable.RetryCount, 6);
short maxRetryCount = sqlDataReader.Read(taskInfoTable.MaxRetryCount, 7);
DateTime?heartbeatDateTime = sqlDataReader.Read(taskInfoTable.HeartbeatDateTime, 8);
string inputData = sqlDataReader.Read(taskInfoTable.InputData, 9);
string taskContext = sqlDataReader.Read(taskInfoTable.TaskContext, 10);
string result = sqlDataReader.Read(taskInfoTable.Result, 11);
TaskStatus taskStatus = (TaskStatus)status;
return(taskStatus == TaskStatus.Completed
? throw new TaskAlreadyCompletedException("Task already completed or reach max retry count.")
: new TaskInfo()
{
TaskId = taskId,
QueueId = queueId,
Status = taskStatus,
TaskTypeId = taskTypeId,
RunId = taskRunId,
IsCanceled = isCanceled,
RetryCount = retryCount,
MaxRetryCount = maxRetryCount,
HeartbeatDateTime = heartbeatDateTime,
InputData = inputData,
Context = taskContext,
Result = result,
});
}
catch (SqlException sqlEx)
{
if (sqlEx.Number == SqlErrorCodes.NotFound)
{
throw new TaskNotExistException(sqlEx.Message);
}
throw;
}
}
}
public GIVEN_connectionString()
{
var connectionFactory = System.Data.Entity.Database.DefaultConnectionFactory;
this.connectionString = connectionFactory.CreateConnection(this.dbName).ConnectionString;
this.sut = new SqlCommandWrapper(this.connectionString);
}
public void PopulateCommand(SqlCommandWrapper command, System.String name, System.Int32 maxVersion, System.Int32 minVersion, System.Int32 addMinutesOnTimeout)
{
command.CommandType = global::System.Data.CommandType.StoredProcedure;
command.CommandText = "dbo.UpsertInstanceSchema";
_name.AddParameter(command.Parameters, name);
_maxVersion.AddParameter(command.Parameters, maxVersion);
_minVersion.AddParameter(command.Parameters, minVersion);
_addMinutesOnTimeout.AddParameter(command.Parameters, addMinutesOnTimeout);
}
public void PopulateCommand(SqlCommandWrapper command, System.String studyInstanceUid, System.String seriesInstanceUid, System.String sopInstanceUid, System.Int64 watermark)
{
command.CommandType = global::System.Data.CommandType.StoredProcedure;
command.CommandText = "dbo.DeleteDeletedInstance";
_studyInstanceUid.AddParameter(command.Parameters, studyInstanceUid);
_seriesInstanceUid.AddParameter(command.Parameters, seriesInstanceUid);
_sopInstanceUid.AddParameter(command.Parameters, sopInstanceUid);
_watermark.AddParameter(command.Parameters, watermark);
}
public void PopulateCommand(SqlCommandWrapper command, System.Byte validStatus, System.String studyInstanceUid, System.String seriesInstanceUid, System.String sopInstanceUid)
{
command.CommandType = global::System.Data.CommandType.StoredProcedure;
command.CommandText = "dbo.GetInstance";
_validStatus.AddParameter(command.Parameters, validStatus);
_studyInstanceUid.AddParameter(command.Parameters, studyInstanceUid);
_seriesInstanceUid.AddParameter(command.Parameters, seriesInstanceUid);
_sopInstanceUid.AddParameter(command.Parameters, sopInstanceUid);
}
private async Task<IList<Product>> GetProductsAsync()
{
var sqlCommandWrapper = new SqlCommandWrapper(connectionString, 900);// connection string and timeout
var parameters = new SqlParameter[] { };
return (await sqlCommandWrapper.ExecuteReaderAsync(CommandType.Text, // For stored-procedures no need to pass CommandType param
"Select * From items",
r =>
new Product
{
Id = (int)r["Id"],
FirstName = r["FirstName"].ToString(),
}, parameters)).ToList();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Page.HasParameter("Action") && Page.GetParameter("Action") == "Remove")
{
using (SqlCommandWrapper cmd = new SqlCommandWrapper("Papers", "DELETE FROM Paper_Keyword WHERE intPaperId = @PaperId AND strKeyword = @keyword"))
{
cmd.AddParam("@PaperId", Page.GetIntParameter("PaperId").Value);
string keyword = Page.GetParameter("Keyword");
cmd.AddParam("@Keyword", Page.GetParameter("Keyword"));
cmd.ExecuteNonQuery();
}
Page.SetResponse(true, "keyword successfully removed", "AddKeywords.aspx?PaperId=[[PaperId]]");
}
}
private string RetrieveXmlFromDatabase(SqlCommandWrapper sqlCommand)
{
string actualOutput = "";
XmlReader reader = null;
try
{
reader = sqlDatabase.ExecuteXmlReader(sqlCommand);
reader.MoveToContent();
for (string value = reader.ReadOuterXml(); value != null && value.Length != 0; value = reader.ReadOuterXml())
{
actualOutput += value;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
sqlCommand.Command.Connection.Close();
}
return actualOutput;
}
/// <summary>
/// <para>Executes the <see cref="SqlCommandWrapper"/> in a transaction and returns an <see cref="XmlReader"/>.</para>
/// </summary>
/// <param name="command">
/// <para>The <see cref="SqlCommandWrapper"/> to execute.</para>
/// </param>
/// <param name="transaction">
/// <para>The <see cref="IDbTransaction"/> to execute the command within.</para>
/// </param>
/// <returns>
/// <para>An <see cref="XmlReader"/> object.</para>
/// </returns>
public XmlReader ExecuteXmlReader(SqlCommandWrapper command, IDbTransaction transaction)
{
PrepareCommand(command, transaction);
SqlCommand sqlCommand = command.Command as SqlCommand;
return DoExecuteXmlReader(sqlCommand);
}
