public static async Task <List <Fortune> > LoadFortunesRows()
{
var fortunes = new List <Fortune>();
using var pooledConnection = await PooledConnections.GetConnection(ConnectionStrings.OdbcConnection);
await pooledConnection.OpenAsync();
var pooledCommand = new PooledCommand("SELECT id, message FROM fortune", pooledConnection);
using (pooledCommand)
{
using var dataReader = await pooledCommand.ExecuteReaderAsync(CommandBehavior.SingleResult);
while (await dataReader.ReadAsync())
{
fortunes.Add(new Fortune
(
id: dataReader.GetInt32(0),
message: dataReader.GetString(1)
));
}
}
fortunes.Add(new Fortune(id: 0, message: "Additional fortune added at request time."));
fortunes.Sort();
return(fortunes);
}
private static (PooledCommand pooledCommand, IDbDataParameter dbDataParameter) CreateReadCommand(PooledConnection pooledConnection)
{
var pooledCommand = new PooledCommand("SELECT id, randomnumber FROM world WHERE id =?", pooledConnection);
var dbDataParameter = pooledCommand.CreateParameter("@Id", DbType.Int32, _random.Next(1, 10001));
return(pooledCommand, dbDataParameter);
}
public static async Task <List <Fortune> > LoadFortunesRows()
{
var fortunes = new List <Fortune>();
var pooledConnection = await PooledConnections.GetConnection(DataProvider.ConnectionString);
pooledConnection.Open();
var pooledCommand = new PooledCommand("SELECT * FROM fortune", pooledConnection);
var dataReader = await pooledCommand.ExecuteReaderAsync(CommandBehavior.SingleResult& CommandBehavior.SequentialAccess);
while (dataReader.Read())
{
fortunes.Add(new Fortune
(
id: dataReader.GetInt32(0),
#if MYSQL
//MariaDB ODBC connector does not correctly support Japanese characters in combination with default ADO.NET;
//as a solution we custom read this string
message: ReadColumn(dataReader, 1)
#else
message: dataReader.GetString(1)
#endif
));
}
dataReader.Close();
pooledCommand.Release();
pooledConnection.Release();
fortunes.Add(new Fortune(id: 0, message: "Additional fortune added at request time."));
fortunes.Sort();
return(fortunes);
}
private static (PooledCommand pooledCommand, IDbDataParameter dbDataParameter) CreateReadCommand(PooledConnection pooledConnection)
{
#if ADO
var pooledCommand = new PooledCommand("SELECT * FROM world WHERE id=@Id", pooledConnection);
#else
var pooledCommand = new PooledCommand("SELECT * FROM world WHERE id=?", pooledConnection);
#endif
return(pooledCommand, pooledCommand.CreateParameter("Id", DbType.Int32, _random.Next(1, 10001)));
}
public static async Task <World[]> ReadMultipleRows(int count)
{
int j = 0;
var ids = PlatformBenchmarks.BatchUpdateString.Ids;
var worlds = new World[count];
string queryString;
if (_queriesMultipleRows[count] != null)
{
queryString = _queriesMultipleRows[count];
}
else
{
var stringBuilder = PlatformBenchmarks.StringBuilderCache.Acquire();
for (int i = 0; i < count; i++)
{
stringBuilder.Append("SELECT * FROM world WHERE id=?;");
}
queryString = _queriesMultipleRows[count] = PlatformBenchmarks.StringBuilderCache.GetStringAndRelease(stringBuilder);
}
var pooledConnection = await PooledConnections.GetConnection(DataProvider.ConnectionString);
pooledConnection.Open();
var pooledCommand = new PooledCommand(queryString, pooledConnection);
for (int i = 0; i < count; i++)
{
pooledCommand.CreateParameter(ids[i], DbType.Int32, _random.Next(1, 10001));
}
var dataReader = await pooledCommand.ExecuteReaderAsync(CommandBehavior.Default& CommandBehavior.SequentialAccess);
do
{
dataReader.Read();
worlds[j] = new World
{
Id = dataReader.GetInt32(0),
RandomNumber = dataReader.GetInt32(1)
};
j++;
} while (await dataReader.NextResultAsync());
dataReader.Close();
pooledCommand.Release();
pooledConnection.Release();
return(worlds);
}
private static async Task <World> ReadSingleRow(PooledCommand pooledCommand)
{
using var dataReader = await pooledCommand.ExecuteReaderAsync(CommandBehavior.SingleRow);
await dataReader.ReadAsync();
return(new World
{
Id = dataReader.GetInt32(0),
RandomNumber = dataReader.GetInt32(1)
});
}
public static async Task <World[]> LoadMultipleUpdatesRows(int count)
{
var worlds = new World[count];
var pooledConnection = await PooledConnections.GetConnection(DataProvider.ConnectionString);
pooledConnection.Open();
var(queryCommand, dbDataParameter) = CreateReadCommand(pooledConnection);
for (int i = 0; i < count; i++)
{
worlds[i] = await ReadSingleRow(queryCommand);
dbDataParameter.Value = _random.Next(1, 10001);
}
queryCommand.Release();
var updateCommand = new PooledCommand(PlatformBenchmarks.BatchUpdateString.Query(count), pooledConnection);
var ids = PlatformBenchmarks.BatchUpdateString.Ids;
var randoms = PlatformBenchmarks.BatchUpdateString.Randoms;
#if !MYSQL
var jds = PlatformBenchmarks.BatchUpdateString.Jds;
#endif
for (int i = 0; i < count; i++)
{
var randomNumber = _random.Next(1, 10001);
updateCommand.CreateParameter(ids[i], DbType.Int32, worlds[i].Id);
updateCommand.CreateParameter(randoms[i], DbType.Int32, randomNumber);
worlds[i].RandomNumber = randomNumber;
}
#if !MYSQL
for (int i = 0; i < count; i++)
{
updateCommand.CreateParameter(jds[i], DbType.Int32, worlds[i].Id);
}
#endif
await updateCommand.ExecuteNonQueryAsync();
updateCommand.Release();
pooledConnection.Release();
return(worlds);
}
private static async Task <World> ReadSingleRow(PooledCommand pooledCommand)
{
var dataReader = await pooledCommand.ExecuteReaderAsync(CommandBehavior.SingleRow& CommandBehavior.SequentialAccess);
dataReader.Read();
var world = new World
{
Id = dataReader.GetInt32(0),
RandomNumber = dataReader.GetInt32(1)
};
dataReader.Close();
return(world);
}
