public void AddNonDmlCommand_FromEmpty()
{
var connection = new SpannerConnection();
var command = new SpannerBatchCommand(connection);
Assert.Throws <InvalidOperationException>(() => command.Add("drop database myDatabase"));
}
public void ConnectionConstructor()
{
var connection = new SpannerConnection();
var command = new SpannerBatchCommand(connection);
Assert.Empty(command.Commands);
Assert.Same(connection, command.Connection);
Assert.Null(command.Transaction);
Assert.Equal(SpannerBatchCommandType.None, command.CommandType);
}
public void AddNonDmlCommand_FromValid()
{
var connection = new SpannerConnection();
var command = new SpannerBatchCommand(connection);
command.Add(
"DELETE FROM myTable WHERE Key=@key",
new SpannerParameterCollection {
{ "key", SpannerDbType.Int64, 5 }
});
Assert.Throws <InvalidOperationException>(() => command.Add("drop database myDatabase"));
}
public void TransactionConstructor()
{
var connection = new SpannerConnection();
var pool = new FakeSessionPool();
var session = PooledSession.FromSessionName(pool, new SessionName("project", "instance", "database", "session"));
var transaction = new SpannerTransaction(connection, TransactionMode.ReadWrite, session: session, timestampBound: null);
var command = new SpannerBatchCommand(transaction);
Assert.Empty(command.Commands);
Assert.Same(connection, command.Connection);
Assert.Same(transaction, command.Transaction);
Assert.Equal(SpannerBatchCommandType.None, command.CommandType);
}
public void AddValidCommands(Action <SpannerBatchCommand> addCommandAction, string expectedCommandText, int expectedParametersCount)
{
var connection = new SpannerConnection();
var command = new SpannerBatchCommand(connection);
addCommandAction(command);
Assert.Equal(1, command.Commands.Count);
Assert.Equal(expectedCommandText, command.Commands[0].CommandText, ignoreCase: true);
Assert.Equal(expectedParametersCount, command.Commands[0].Parameters.Count);
Assert.Same(connection, command.Connection);
Assert.Null(command.Transaction);
Assert.Equal(SpannerBatchCommandType.BatchDml, command.CommandType);
}
public static async Task InsertPlayersAsync(string projectId,
string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
long numberOfPlayers = 0;
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
await connection.RunWithRetriableTransactionAsync(async (transaction) =>
{
// Execute a SQL statement to get current number of records
// in the Players table to use as an incrementing value
// for each PlayerName to be inserted.
var cmd = connection.CreateSelectCommand(
@"SELECT Count(PlayerId) as PlayerCount FROM Players");
numberOfPlayers = await cmd.ExecuteScalarAsync <long>();
// Insert 100 player records into the Players table.
SpannerBatchCommand cmdBatch = connection.CreateBatchDmlCommand();
for (int i = 0; i < 100; i++)
{
numberOfPlayers++;
SpannerCommand cmdInsert = connection.CreateDmlCommand(
"INSERT INTO Players "
+ "(PlayerId, PlayerName) "
+ "VALUES (@PlayerId, @PlayerName)",
new SpannerParameterCollection {
{ "PlayerId", SpannerDbType.Int64 },
{ "PlayerName", SpannerDbType.String }
});
cmdInsert.Parameters["PlayerId"].Value =
Math.Abs(Guid.NewGuid().GetHashCode());
cmdInsert.Parameters["PlayerName"].Value =
$"Player {numberOfPlayers}";
cmdBatch.Add(cmdInsert);
}
await cmdBatch.ExecuteNonQueryAsync();
});
}
Console.WriteLine("Done inserting player records...");
}
public async Task <int> UpdateUsingBatchDmlCoreAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
SpannerBatchCommand cmd = connection.CreateBatchDmlCommand();
cmd.Add("INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget) VALUES (1, 3, 'Test Album Title', 10000)");
cmd.Add("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 3");
IEnumerable <long> affectedRows = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"Executed {affectedRows.Count()} " + "SQL statements using Batch DML.");
return(affectedRows.Count());
}
public void AddSeveralCommands()
{
var connection = new SpannerConnection();
var command = new SpannerBatchCommand(connection);
var expectedCommandsInOrder = new List <string>();
foreach (var testCase in ValidCommands)
{
((Action <SpannerBatchCommand>)testCase[0])(command);
expectedCommandsInOrder.Add((string)testCase[1]);
}
Assert.Equal(expectedCommandsInOrder, command.Commands.Select(c => c.CommandText), StringComparer.OrdinalIgnoreCase);
Assert.Same(connection, command.Connection);
Assert.Null(command.Transaction);
Assert.Equal(SpannerBatchCommandType.BatchDml, command.CommandType);
}
public static async Task InsertScoresAsync(
string projectId, string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Insert 4 score records into the Scores table for each player
// in the Players table.
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
await connection.RunWithRetriableTransactionAsync(async (transaction) =>
{
Random r = new Random();
bool playerRecordsFound = false;
SpannerBatchCommand cmdBatch =
connection.CreateBatchDmlCommand();
var cmdLookup =
connection.CreateSelectCommand("SELECT * FROM Players");
using (var reader = await cmdLookup.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
playerRecordsFound = true;
for (int i = 0; i < 4; i++)
{
DateTime randomTimestamp = DateTime.Now
.AddYears(r.Next(-2, 1))
.AddMonths(r.Next(-12, 1))
.AddDays(r.Next(-10, 1))
.AddSeconds(r.Next(-60, 0))
.AddMilliseconds(r.Next(-100000, 0));
SpannerCommand cmdInsert =
connection.CreateDmlCommand(
"INSERT INTO Scores "
+ "(PlayerId, Score, Timestamp) "
+ "VALUES (@PlayerId, @Score, @Timestamp)",
new SpannerParameterCollection {
{ "PlayerId", SpannerDbType.Int64 },
{ "Score", SpannerDbType.Int64 },
{ "Timestamp",
SpannerDbType.Timestamp }
});
cmdInsert.Parameters["PlayerId"].Value =
reader.GetFieldValue <int>("PlayerId");
cmdInsert.Parameters["Score"].Value =
r.Next(1000, 1000001);
cmdInsert.Parameters["Timestamp"].Value =
randomTimestamp.ToString("o");
cmdBatch.Add(cmdInsert);
}
}
if (!playerRecordsFound)
{
Console.WriteLine("Parameter 'scores' is invalid "
+ "since no player records currently exist. First "
+ "insert players then insert scores.");
Environment.Exit((int)ExitCode.InvalidParameter);
}
else
{
await cmdBatch.ExecuteNonQueryAsync();
Console.WriteLine(
"Done inserting score records..."
);
}
}
});
}
}
public static async Task InsertPlayersAsync(string projectId,
string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
using (TransactionScope scope = new TransactionScope(
TransactionScopeAsyncFlowOption.Enabled))
{
Int64 numberOfPlayers = 0;
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
// Execute a SQL statement to get current number of records
// in the Players table to use as an incrementing value
// for each PlayerName to be inserted.
var cmd = connection.CreateSelectCommand(
@"SELECT Count(PlayerId) as PlayerCount FROM Players");
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
long parsedValue;
if (reader["PlayerCount"] != DBNull.Value)
{
bool result = Int64.TryParse(
reader.GetFieldValue <string>("PlayerCount"),
out parsedValue);
if (result)
{
numberOfPlayers = parsedValue;
}
}
}
}
// Insert 100 player records into the Players table.
SpannerBatchCommand cmdBatch = connection.CreateBatchDmlCommand();
for (var x = 1; x <= 100; x++)
{
numberOfPlayers++;
SpannerCommand cmdInsert = connection.CreateDmlCommand(
"INSERT INTO Players "
+ "(PlayerId, PlayerName) "
+ "VALUES (@PlayerId, @PlayerName)",
new SpannerParameterCollection {
{ "PlayerId", SpannerDbType.Int64 },
{ "PlayerName", SpannerDbType.String }
});
cmdInsert.Parameters["PlayerId"].Value =
Math.Abs(Guid.NewGuid().GetHashCode());
cmdInsert.Parameters["PlayerName"].Value =
$"Player {numberOfPlayers}";
cmdBatch.Add(cmdInsert);
}
await cmdBatch.ExecuteNonQueryAsync();
scope.Complete();
}
}
Console.WriteLine("Done inserting player records...");
}
