public async Task CreateAndInitializeDatabase(string databaseName, int maximumSupportedSchemaVersion, bool forceIncrementalSchemaUpgrade, SchemaInitializer schemaInitializer = null, CancellationToken cancellationToken = default)
{
var testConnectionString = new SqlConnectionStringBuilder(_initialConnectionString)
{
InitialCatalog = databaseName
}.ToString();
schemaInitializer ??= CreateSchemaInitializer(testConnectionString, maximumSupportedSchemaVersion);
await _dbSetupRetryPolicy.ExecuteAsync(async() =>
{
// Create the database.
await using SqlConnection connection = await _sqlConnectionBuilder.GetSqlConnectionAsync(_masterDatabaseName, cancellationToken);
await connection.OpenAsync(cancellationToken);
await using SqlCommand command = connection.CreateCommand();
command.CommandTimeout = 600;
command.CommandText = @$ "
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '{databaseName}')
BEGIN
CREATE DATABASE {databaseName};
END";
await command.ExecuteNonQueryAsync(cancellationToken);
});
/// <summary>
/// Returns the number of resource change records from a start id and a checkpoint datetime.
/// </summary>
/// <param name="startId">The start id of resource change records to fetch. The start id is inclusive.</param>
/// <param name="lastProcessedDateTime">The last checkpoint datetime.</param>
/// <param name="pageSize">The page size for fetching resource change records.</param>
/// <param name="cancellationToken">Cancellation token.</param>
/// <returns>Resource change data rows.</returns>
/// <exception cref="System.ArgumentOutOfRangeException">Thrown if startId or pageSize is less than zero.</exception>
/// <exception cref="System.InvalidOperationException">Thrown when a method call is invalid for the object's current state.</exception>
/// <exception cref="System.OperationCanceledException">Thrown when the operation is canceled.</exception>
/// <exception cref="System.Threading.Tasks.TaskCanceledException">Thrown when the task is canceled.</exception>
/// <exception cref="Microsoft.Data.SqlClient.SqlException">Thrown when SQL Server returns a warning or error.</exception>
/// <exception cref="System.TimeoutException">Thrown when the time allotted for a process or operation has expired.</exception>
/// <exception cref="System.Exception">Thrown when errors occur during execution.</exception>
public async Task <IReadOnlyCollection <ResourceChangeData> > GetRecordsAsync(long startId, DateTime lastProcessedDateTime, short pageSize, CancellationToken cancellationToken)
{
EnsureArg.IsGte(startId, 1, nameof(startId));
EnsureArg.IsGte(pageSize, 1, nameof(pageSize));
var listResourceChangeData = new List <ResourceChangeData>();
try
{
// The GetRecordsAsync function would be called every second by one agent.
// So, it would be a good option that opens and closes a connection for each call,
// and there is no database connection pooling in the Application at this time.
using (SqlConnection sqlConnection = await _sqlConnectionFactory.GetSqlConnectionAsync(cancellationToken: cancellationToken))
{
await sqlConnection.OpenAsync(cancellationToken);
if (ResourceTypeIdToTypeNameMap.IsEmpty)
{
lock (ResourceTypeIdToTypeNameMap)
{
if (ResourceTypeIdToTypeNameMap.IsEmpty)
{
UpdateResourceTypeMapAsync(sqlConnection);
}
}
}
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
PopulateFetchResourceChangesCommand(sqlCommand, startId, lastProcessedDateTime, pageSize);
using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
(long id, DateTime timestamp, string resourceId, short resourceTypeId, int resourceVersion, byte resourceChangeTypeId) = sqlDataReader.ReadRow(
VLatest.ResourceChangeData.Id,
VLatest.ResourceChangeData.Timestamp,
VLatest.ResourceChangeData.ResourceId,
VLatest.ResourceChangeData.ResourceTypeId,
VLatest.ResourceChangeData.ResourceVersion,
VLatest.ResourceChangeData.ResourceChangeTypeId);
listResourceChangeData.Add(new ResourceChangeData(
id: id,
timestamp: DateTime.SpecifyKind(timestamp, DateTimeKind.Utc),
resourceId: resourceId,
resourceTypeId: resourceTypeId,
resourceVersion: resourceVersion,
resourceChangeTypeId: resourceChangeTypeId,
resourceTypeName: ResourceTypeIdToTypeNameMap[resourceTypeId]));
}
}
return(listResourceChangeData);
}
}
}
catch (Exception ex) when((ex is OperationCanceledException || ex is TaskCanceledException) && cancellationToken.IsCancellationRequested)
{
_logger.LogInformation(ex, Resources.GetRecordsAsyncOperationIsCanceled);
throw;
}
catch (SqlException ex)
{
switch (ex.Number)
{
case SqlErrorCodes.TimeoutExpired:
throw new TimeoutException(ex.Message, ex);
default:
_logger.LogError(ex, string.Format(Resources.SqlExceptionOccurredWhenFetchingResourceChanges, ex.Number));
throw;
}
}
catch (Exception ex)
{
_logger.LogError(ex, Resources.ExceptionOccurredWhenFetchingResourceChanges);
throw;
}
}