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