public async Task<World[]> LoadMultipleUpdatesRows(int count)
{
var results = new World[count];
IDictionary<string, object> parameters = new ExpandoObject();
var updateCommand = new StringBuilder(count);
using (var db = _dbProviderFactory.CreateConnection())
{
db.ConnectionString = _connectionString;
await db.OpenAsync();
for (int i = 0; i < count; i++)
{
results[i] = await ReadSingleRow(db);
}
// postgres has problems with deadlocks when these aren't sorted
Array.Sort<World>(results, (a, b) => a.Id.CompareTo(b.Id));
for (int i = 0; i < count; i++)
{
var randomNumber = _random.Next(1, 10001);
parameters[BatchUpdateString.Strings[i].Random] = randomNumber;
parameters[BatchUpdateString.Strings[i].Id] = results[i].Id;
results[i].RandomNumber = randomNumber;
updateCommand.Append(BatchUpdateString.Strings[i].UpdateQuery);
}
await db.ExecuteAsync(updateCommand.ToString(), parameters);
}
return results;
}
public async Task<World[]> LoadMultipleQueriesRows(int count)
{
var result = new World[count];
for (int i = 0; i < count; i++)
{
var id = _random.Next(1, 10001);
result[i] = await _dbContext.World.FirstAsync(w => w.Id == id);
}
return result;
}
private static async Task<World[]> LoadRows(int count, ApplicationDbContext dbContext)
{
var result = new World[count];
for (int i = 0; i < count; i++)
{
var id = _random.Next(1, 10001);
result[i] = await dbContext.World.FirstAsync(w => w.Id == id);
}
return result;
}
public async Task<World[]> LoadMultipleQueriesRows(int count)
{
var results = new World[count];
using (var db = _dbProviderFactory.CreateConnection())
{
db.ConnectionString = _connectionString;
await db.OpenAsync();
for (int i = 0; i < count; i++)
{
results[i] = await ReadSingleRow(db);
}
}
return results;
}
public async Task<World[]> LoadMultipleQueriesRows(int count)
{
var result = new World[count];
using (var db = _dbProviderFactory.CreateConnection())
using (var cmd = CreateReadCommand(db))
{
db.ConnectionString = _connectionString;
await db.OpenAsync();
for (int i = 0; i < count; i++)
{
result[i] = await ReadSingleRow(db, cmd);
cmd.Parameters["@Id"].Value = _random.Next(1, 10001);
}
}
return result;
}
public async Task<World[]> LoadMultipleQueriesRows(int count)
{
var result = new World[count];
using (var db = _dbProviderFactory.CreateConnection())
{
db.ConnectionString = _connectionString;
await db.OpenAsync();
for (int i = 0; i < count; i++)
{
result[i] = await db.QueryFirstOrDefaultAsync<World>(
"SELECT [Id], [RandomNumber] FROM [World] WHERE [Id] = @Id",
new { Id = _random.Next(1, 10001) });
}
db.Close();
}
return result;
}
private async Task<World> LoadRow()
{
var row = new World();
using (var db = new SqlConnection(_connectionString))
using (var cmd = db.CreateCommand())
{
var id = _random.Next(1, 10001);
cmd.CommandText = "SELECT [Id], [RandomNumber] FROM [World] WHERE [Id] = @Id";
cmd.Parameters.Add(new SqlParameter("@Id", System.Data.SqlDbType.Int) { Value = id });
await db.OpenAsync();
var rdr = await cmd.ExecuteReaderAsync();
await rdr.ReadAsync();
row.Id = rdr.GetInt32(0);
row.RandomNumber = rdr.GetInt32(1);
db.Close();
}
return row;
}
private static async Task<World[]> LoadRows(int count, string connectionString, DbProviderFactory dbProviderFactory)
{
var result = new World[count];
using (var db = dbProviderFactory.CreateConnection())
using (var cmd = db.CreateCommand())
{
db.ConnectionString = connectionString;
await db.OpenAsync();
cmd.CommandText = "SELECT [Id], [RandomNumber] FROM [World] WHERE [Id] = @Id";
var id = cmd.CreateParameter();
id.ParameterName = "@Id";
id.DbType = DbType.Int32;
cmd.Parameters.Add(id);
for (int i = 0; i < count; i++)
{
id.Value = _random.Next(1, 10001);
using (var rdr = await cmd.ExecuteReaderAsync(CommandBehavior.SingleRow))
{
await rdr.ReadAsync();
result[i] = new World
{
Id = rdr.GetInt32(0),
RandomNumber = rdr.GetInt32(1)
};
}
}
db.Close();
}
return result;
}
public async Task<World[]> LoadMultipleUpdatesRows(int count)
{
var results = new World[count];
for (int i = 0; i < count; i++)
{
var id = _random.Next(1, 10001);
var result = await _dbContext.World.AsTracking().FirstAsync(w => w.Id == id);
result.RandomNumber = _random.Next(1, 10001);
results[i] = result;
if(!_dbContext.UseBatchUpdate)
{
await _dbContext.SaveChangesAsync();
}
}
if(_dbContext.UseBatchUpdate)
{
await _dbContext.SaveChangesAsync();
}
return results;
}
public async Task<World[]> LoadMultipleUpdatesRows(int count)
{
var results = new World[count];
var updateCommand = new StringBuilder(count);
using (var db = _dbProviderFactory.CreateConnection())
using (var updateCmd = db.CreateCommand())
using (var queryCmd = CreateReadCommand(db))
{
db.ConnectionString = _connectionString;
await db.OpenAsync();
for (int i = 0; i < count; i++)
{
results[i] = await ReadSingleRow(db, queryCmd);
queryCmd.Parameters["@Id"].Value = _random.Next(1, 10001);
}
// postgres has problems with deadlocks when these aren't sorted
Array.Sort<World>(results, (a, b) => a.Id.CompareTo(b.Id));
for(int i = 0; i < count; i++)
{
var id = updateCmd.CreateParameter();
id.ParameterName = BatchUpdateString.Strings[i].Id;
id.DbType = DbType.Int32;
updateCmd.Parameters.Add(id);
var random = updateCmd.CreateParameter();
random.ParameterName = BatchUpdateString.Strings[i].Random;
id.DbType = DbType.Int32;
updateCmd.Parameters.Add(random);
var randomNumber = _random.Next(1, 10001);
id.Value = results[i].Id;
random.Value = randomNumber;
results[i].RandomNumber = randomNumber;
updateCommand.Append(BatchUpdateString.Strings[i].UpdateQuery);
}
updateCmd.CommandText = updateCommand.ToString();
await updateCmd.ExecuteNonQueryAsync();
}
return results;
}
