public async Task BulkUpdatePlayersAsync(IEnumerable <Player> players, CancellationToken cancellationToken)
{
var factory = DbProviderFactories.GetFactory(_settings.DataProviderName);
var connection = factory.CreateConnection();
connection.ConnectionString = _settings.DbConnectionString;
try
{
await connection.OpenAsync(cancellationToken);
var context = new DapperPlusContext
{
Connection = connection
};
context.Entity <Player>().Table($"{_settings.ObjectsSchema}.{nameof(Player)}s");
connection.BulkUpdate(context, players);
}
finally
{
if (connection.State == ConnectionState.Open)
{
await connection.CloseAsync();
}
}
}
private async Task RegisterDisciplines(DapperPlusContext dataContext, CancellationToken cancellationToken)
{
var disciplines = _disciplines.Select(d => new Discipline {
Title = d
});
dataContext.Entity <Discipline>()
.Table($"{nameof(Discipline)}s")
.Identity(x => x.Id);
await dataContext.BulkActionAsync(x => x.BulkInsert(disciplines), cancellationToken);
}
public async Task <OperationResult> GenerateStudentsAndGradesDataLoadAsync(CancellationToken cancellationToken)
{
if (_cacheProvider.Get(_studentsGradesCacheKeyName) != null)
{
return(OperationResult.Success());
}
IDbTransaction transaction = default;
DapperPlusContext context;
using (var conn = new SqlConnection(_connectionString))
{
try
{
conn.Open();
var dataLoadedAlready = await conn.ExecuteScalarAsync(new CommandDefinition("SELECT 1 FROM dbo.StudentsGrades (NOLOCK)", cancellationToken : cancellationToken));
if (dataLoadedAlready != null)
{
return(OperationResult.Success());
}
using (transaction = await conn.BeginTransactionAsync(cancellationToken))
{
context = new DapperPlusContext(transaction.Connection, transaction);
await RegisterDisciplines(context, cancellationToken);
await GenerateStudentsDataLoadAsync(context, cancellationToken);
await GenerateStudentsGradesDataLoadAsync(context, cancellationToken);
transaction.Commit();
}
}
catch (Exception exc)
{
transaction?.Rollback();
return(OperationResult.Failure($"Unable to create students and grades data load. {exc.Message}"));
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
context = null;
}
}
return(OperationResult.Success());
}
private async Task GenerateStudentsGradesDataLoadAsync(DapperPlusContext dataContext, CancellationToken cancellationToken)
{
var sql =
"INSERT dbo.StudentsGrades (StudentId, DisciplineId, Grade)" +
"SELECT " +
" S.Id StudentId " +
" ,D.Id DisciplineId " +
" ,dbo.F_RandomValue() Grade " +
"FROM dbo.Students S " +
"CROSS JOIN dbo.Disciplines D";
await dataContext.Connection.ExecuteAsync(sql, transaction : dataContext.Transaction);
}
private async Task GenerateStudentsDataLoadAsync(DapperPlusContext dataContext, CancellationToken cancellationToken)
{
const int studentCount = 1000;
var studentsDataFaker = new Faker <Student>()
.RuleFor(u => u.FirstName, f => f.Name.FirstName())
.RuleFor(u => u.LastName, f => f.Name.LastName());
var students = studentsDataFaker.Generate(studentCount).Distinct(new Student.StudentComparer());
dataContext.Entity <Student>()
.Table($"{nameof(Student)}s")
.Identity(x => x.Id)
.BatchSize(studentCount);
await dataContext.BulkActionAsync(x => x.BulkInsert(students), cancellationToken);
}
