private async Task FillSampleData(string testTable)
{
using (var connection = new SpannerConnection(ConnectionString))
{
await connection.OpenAsync();
using (var tx = await connection.BeginTransactionAsync())
{
var cmd = connection.CreateInsertCommand(
testTable,
new SpannerParameterCollection
{
{ "Key", SpannerDbType.String },
{ "StringValue", SpannerDbType.String }
});
cmd.Transaction = tx;
for (var i = 0; i < TestTableRowCount - 1; ++i)
{
cmd.Parameters["Key"].Value = "k" + i;
cmd.Parameters["StringValue"].Value = "v" + i;
await cmd.ExecuteNonQueryAsync();
}
// And one extra row, with a null value.
cmd.Parameters["Key"].Value = "kNull";
cmd.Parameters["StringValue"].Value = DBNull.Value;
await cmd.ExecuteNonQueryAsync();
await tx.CommitAsync();
}
}
}
public static async Task InsertPlayersAsync(string projectId,
string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
Int64 numberOfPlayers = 0;
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
using (var tx = await connection.BeginTransactionAsync())
{
// Execute a SQL statement to get current number of records
// in the Players table.
var cmd = connection.CreateSelectCommand(
@"SELECT Count(PlayerId) as PlayerCount FROM Players");
cmd.Transaction = tx;
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.
using (cmd = connection.CreateInsertCommand(
"Players", new SpannerParameterCollection
{
{ "PlayerId", SpannerDbType.String },
{ "PlayerName", SpannerDbType.String }
}))
{
cmd.Transaction = tx;
for (var x = 1; x <= 100; x++)
{
numberOfPlayers++;
cmd.Parameters["PlayerId"].Value =
Math.Abs(Guid.NewGuid().GetHashCode());
cmd.Parameters["PlayerName"].Value =
$"Player {numberOfPlayers}";
cmd.ExecuteNonQuery();
}
}
await tx.CommitAsync();
}
}
Console.WriteLine("Done inserting player records...");
}
private async Task FillOrderData()
{
using (var connection = new SpannerConnection(ConnectionString))
{
await connection.OpenAsync();
for (var i = 0; i < NumPartitionReadRows / 5000; i++)
{
using (var tx = await connection.BeginTransactionAsync())
using (var cmd = connection.CreateInsertCommand("Orders", new SpannerParameterCollection
{
{ "OrderID", SpannerDbType.String },
{ "OrderDate", SpannerDbType.Timestamp },
{ "Product", SpannerDbType.String }
}))
{
cmd.Transaction = tx;
for (var x = 1; x < 5000; x++)
{
cmd.Parameters["OrderID"].Value = Guid.NewGuid().ToString();
cmd.Parameters["OrderDate"].Value = DateTime.Now.Subtract(TimeSpan.FromDays(x));
cmd.Parameters["Product"].Value = $"Widget#{x}";
cmd.ExecuteNonQuery();
}
await tx.CommitAsync();
}
}
}
}
public async Task <int> WriteWithTransactionUsingDmlCoreAsync(string projectId, string instanceId, string databaseId)
{
// This sample transfers 200,000 from the MarketingBudget
// field of the second Album to the first Album. Make sure to run
// the AddColumnAsyncSample and WriteDataToNewColumnAsyncSample first,
// in that order.
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
decimal transferAmount = 200000;
decimal secondBudget = 0;
// Create connection to Cloud Spanner.
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
// Create a readwrite transaction that we'll assign
// to each SpannerCommand.
using var transaction = await connection.BeginTransactionAsync();
// Create statement to select the second album's data.
var cmdLookup = connection.CreateSelectCommand("SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
cmdLookup.Transaction = transaction;
// Execute the select query.
using var reader1 = await cmdLookup.ExecuteReaderAsync();
while (await reader1.ReadAsync())
{
// Read the second album's budget.
secondBudget = reader1.GetFieldValue <decimal>("MarketingBudget");
// Confirm second Album's budget is sufficient and
// if not raise an exception. Raising an exception
// will automatically roll back the transaction.
if (secondBudget < transferAmount)
{
throw new Exception($"The second album's budget {secondBudget} is less than the amount to transfer.");
}
}
// Update second album to remove the transfer amount.
secondBudget -= transferAmount;
SpannerCommand cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = @MarketingBudget WHERE SingerId = 2 and AlbumId = 2");
cmd.Parameters.Add("MarketingBudget", SpannerDbType.Int64, secondBudget);
cmd.Transaction = transaction;
var rowCount = await cmd.ExecuteNonQueryAsync();
// Update first album to add the transfer amount.
cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget + @MarketingBudgetIncrement WHERE SingerId = 1 and AlbumId = 1");
cmd.Parameters.Add("MarketingBudgetIncrement", SpannerDbType.Int64, transferAmount);
cmd.Transaction = transaction;
rowCount += await cmd.ExecuteNonQueryAsync();
await transaction.CommitAsync();
Console.WriteLine("Transaction complete.");
return(rowCount);
}
private async Task IncrementByOneAsync(SpannerConnection connection, bool orphanTransaction = false)
{
var retrySettings = RetrySettings.FromExponentialBackoff(
maxAttempts: int.MaxValue,
initialBackoff: TimeSpan.FromMilliseconds(250),
maxBackoff: TimeSpan.FromSeconds(5),
backoffMultiplier: 1.5,
retryFilter: ignored => false,
RetrySettings.RandomJitter);
TimeSpan nextDelay = TimeSpan.Zero;
SpannerException spannerException;
DateTime deadline = DateTime.UtcNow.AddSeconds(30);
while (true)
{
spannerException = null;
try
{
// We use manually created transactions here so the tests run on .NET Core.
using (var transaction = await connection.BeginTransactionAsync())
{
long current;
using (var cmd = connection.CreateSelectCommand($"SELECT Int64Value FROM {_fixture.TableName} WHERE K=@k"))
{
cmd.Parameters.Add("k", SpannerDbType.String, _key);
cmd.Transaction = transaction;
var fetched = await cmd.ExecuteScalarAsync().ConfigureAwait(false);
current = fetched is DBNull ? 0L : (long)fetched;
}
using (var cmd = connection.CreateUpdateCommand(_fixture.TableName))
{
cmd.Parameters.Add("k", SpannerDbType.String, _key);
cmd.Parameters.Add("Int64Value", SpannerDbType.Int64, current + 1);
cmd.Transaction = transaction;
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
if (!orphanTransaction)
{
await transaction.CommitAsync().ConfigureAwait(false);
}
}
}
return;
}
// Keep trying for up to 30 seconds
catch (SpannerException ex) when(ex.IsRetryable && DateTime.UtcNow < deadline)
{
nextDelay = retrySettings.NextBackoff(nextDelay);
await Task.Delay(retrySettings.BackoffJitter.GetDelay(nextDelay));
spannerException = ex;
}
}
}
private static async Task BatchInsertPlayersAsync(string projectId,
string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
long playerStartingPlanetDollars = 1000000;
// Batch insert 249,900 player records into the Players table.
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
for (int i = 0; i < 100; i++)
{
// For details on transaction isolation, see the "Isolation" section in:
// https://cloud.google.com/spanner/docs/transactions#read-write_transactions
using (var tx = await connection.BeginTransactionAsync())
using (var cmd = connection.CreateInsertCommand("Players", new SpannerParameterCollection
{
{ "PlayerId", SpannerDbType.String },
{ "PlayerName", SpannerDbType.String },
{ "PlanetDollars", SpannerDbType.Int64 }
}))
{
cmd.Transaction = tx;
for (var x = 1; x < 2500; x++)
{
string nameSuffix = Guid.NewGuid().ToString().Substring(0, 8);
cmd.Parameters["PlayerId"].Value = Guid.NewGuid().ToString("N");
cmd.Parameters["PlayerName"].Value = $"Player-{nameSuffix}";
cmd.Parameters["PlanetDollars"].Value = playerStartingPlanetDollars;
try
{
cmd.ExecuteNonQuery();
}
catch (SpannerException ex) {
Console.WriteLine($"Spanner Exception: {ex.Message}");
// Decrement x and retry
x--;
continue;
}
}
await tx.CommitAsync();
}
}
}
Console.WriteLine("Done inserting sample records...");
}
private async Task IncrementByOneAsync(SpannerConnection connection, bool orphanTransaction = false)
{
SpannerException spannerException;
do
{
spannerException = null;
try
{
//We'll do manually created transactions here so the tests run on .net core
using (var transaction = await connection.BeginTransactionAsync())
{
long current;
using (var cmd =
connection.CreateSelectCommand(
"SELECT Int64Value FROM TX WHERE K=@k",
new SpannerParameterCollection {
{ "k", SpannerDbType.String, _key }
}))
{
cmd.Transaction = transaction;
var fetched = await cmd.ExecuteScalarAsync().ConfigureAwait(false);
current = fetched is DBNull ? 0L : (long)fetched;
}
using (var cmd = connection.CreateUpdateCommand(
"TX",
new SpannerParameterCollection
{
{ "k", SpannerDbType.String, _key },
{ "Int64Value", SpannerDbType.Int64, current + 1 }
}))
{
cmd.Transaction = transaction;
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
if (!orphanTransaction)
{
await transaction.CommitAsync().ConfigureAwait(false);
}
}
}
}
catch (SpannerException ex)
{
spannerException = ex;
}
} while (spannerException?.IsRetryable ?? false);
}
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();
Random r = new Random();
var cmdLookup = connection.CreateSelectCommand("SELECT * FROM Players");
using (var reader = await cmdLookup.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
using (var tx = await connection.BeginTransactionAsync())
using (var cmd = connection.CreateInsertCommand("Scores", new SpannerParameterCollection
{
{ "PlayerId", SpannerDbType.String },
{ "Score", SpannerDbType.Int64 },
{ "Timestamp", SpannerDbType.Timestamp }
}))
{
cmd.Transaction = tx;
for (var x = 1; x <= 4; x++)
{
DateTime randomTimestamp = DateTime.Now
.AddYears(r.Next(-1, 1))
.AddMonths(r.Next(-12, 1))
.AddDays(r.Next(-10, 1))
.AddSeconds(r.Next(-60, 0))
.AddMilliseconds(r.Next(-100000, 0));
cmd.Parameters["PlayerId"].Value = reader.GetFieldValue <int>("PlayerId");
// Insert random value for score between 10000 and 1000000.
cmd.Parameters["Score"].Value = r.Next(1000, 1000001);
// Insert random past timestamp value into Timestamp column.
cmd.Parameters["Timestamp"].Value = randomTimestamp.ToString("o");
cmd.ExecuteNonQuery();
}
await tx.CommitAsync();
}
}
}
}
Console.WriteLine("Done inserting score records...");
}
public async Task TransactionAsync()
{
await _fixture.EnsureTestDatabaseAsync().ConfigureAwait(false);
// Sample: TransactionAsync
// Additional: BeginTransactionAsync
var retryPolicy = new RetryPolicy <SpannerFaultDetectionStrategy>(RetryStrategy.DefaultExponential);
await retryPolicy.ExecuteAsync(
async() =>
{
using (var connection =
new SpannerConnection(
$"Data Source=projects/{_projectId}/instances/{_instanceName}/databases/{_databaseName}"))
{
await connection.OpenAsync();
using (var transaction = await connection.BeginTransactionAsync())
{
var cmd = connection.CreateInsertCommand(
"TestTable", new SpannerParameterCollection
{
{ "Key", SpannerDbType.String },
{ "StringValue", SpannerDbType.String },
{ "Int64Value", SpannerDbType.Int64 }
});
cmd.Transaction = transaction;
// This executes a single transactions with alls row written at once during CommitAsync().
// If a transient fault occurs, this entire method is re-run.
for (var i = 0; i < 5; i++)
{
cmd.Parameters["Key"].Value = Guid.NewGuid().ToString("N");
cmd.Parameters["StringValue"].Value = $"StringValue{i}";
cmd.Parameters["Int64Value"].Value = i;
await cmd.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
}
});
// End sample
}
public static async Task InsertPlanetAsync(
string projectId, string instanceId, string databaseId, string planetName,
long planetValue)
{
// Insert Planet Code
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
long planetStartingAvailableShares = 100000000000;
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
using (var tx = await connection.BeginTransactionAsync())
{
using (var cmd = connection.CreateInsertCommand(
"Planets", new SpannerParameterCollection
{
{ "PlanetId", SpannerDbType.String },
{ "PlanetName", SpannerDbType.String },
{ "PlanetValue", SpannerDbType.Int64 },
{ "SharesAvailable", SpannerDbType.Int64 }
}))
{
cmd.Transaction = tx;
cmd.Parameters["PlanetId"].Value =
Math.Abs(Guid.NewGuid().GetHashCode());
cmd.Parameters["PlanetName"].Value = planetName;
cmd.Parameters["PlanetValue"].Value = planetValue;
cmd.Parameters["SharesAvailable"].Value = planetStartingAvailableShares;
cmd.ExecuteNonQuery();
}
await tx.CommitAsync();
}
}
}
public async Task AbortedThrownCorrectly()
{
// connection 1 starts a transaction and reads
// connection 2 starts a transaction and reads the same row
// connection 1 writes and commits
// connection 2 reads again -- abort should be thrown.
// Note: deeply nested using statements to ensure that we dispose of everything even in the case of failure,
// but we manually dispose of both tx1 and connection1.
using (var connection1 = new SpannerConnection(_fixture.ConnectionString))
{
using (var connection2 = new SpannerConnection(_fixture.ConnectionString))
{
await Task.WhenAll(connection1.OpenAsync(), connection2.OpenAsync());
using (var tx1 = await connection1.BeginTransactionAsync())
{
// TX1 READ
using (var cmd = CreateSelectAllCommandForKey(connection1))
{
cmd.Transaction = tx1;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX2 START
using (var tx2 = await connection2.BeginTransactionAsync())
{
// TX2 READ
using (var cmd = CreateSelectAllCommandForKey(connection2))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX1 WRITE/COMMIT
using (var cmd = connection1.CreateUpdateCommand(_fixture.TableName))
{
cmd.Parameters.Add("k", SpannerDbType.String, _key);
cmd.Parameters.Add("Int64Value", SpannerDbType.Int64, 0);
cmd.Transaction = tx1;
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
await tx1.CommitAsync().ConfigureAwait(false);
tx1.Dispose();
}
connection1.Dispose();
// TX2 READ AGAIN/THROWS
using (var cmd = CreateSelectAllCommandForKey(connection2))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
var thrownException = await Assert.ThrowsAsync <SpannerException>(() => reader.ReadAsync());
Assert.True(thrownException.IsRetryable);
}
}
}
}
}
}
}
public async Task AbortedThrownCorrectly()
{
// connection 1 starts a transaction and reads
// connection 2 starts a transaction and reads the same row
// connection 1 writes and commits
// connection 2 reads again -- abort should be thrown.
var connection1 = new SpannerConnection(_fixture.ConnectionString);
var connection2 = new SpannerConnection(_fixture.ConnectionString);
await Task.WhenAll(connection1.OpenAsync(), connection2.OpenAsync());
var tx1 = await connection1.BeginTransactionAsync();
// TX1 READ
using (var cmd = CreateSelectAllCommandForKey(connection1))
{
cmd.Transaction = tx1;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX2 START
var tx2 = await connection2.BeginTransactionAsync();
// TX2 READ
using (var cmd = CreateSelectAllCommandForKey(connection2))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX1 WRITE/COMMIT
using (var cmd = connection1.CreateUpdateCommand(_fixture.TableName))
{
cmd.Parameters.Add("k", SpannerDbType.String, _key);
cmd.Parameters.Add("Int64Value", SpannerDbType.Int64, 0);
cmd.Transaction = tx1;
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
await tx1.CommitAsync().ConfigureAwait(false);
tx1.Dispose();
}
connection1.Dispose();
// TX2 READ AGAIN/THROWS
var thrownException = await Assert.ThrowsAsync <SpannerException>(
async() =>
{
using (var cmd = CreateSelectAllCommandForKey(connection2))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
}).ConfigureAwait(false);
tx2.Dispose();
connection2.Dispose();
Assert.True(thrownException?.IsRetryable ?? false);
}
public async Task AbortedThrownCorrectly()
{
await WriteSampleRowsAsync();
// connection 1 starts a transaction and reads
// connection 2 starts a transaction and reads the same row
// connection 1 writes and commits
// connection 2 reads again -- abort should be thrown.
var connection1 = new SpannerConnection(_testFixture.ConnectionString);
var connection2 = new SpannerConnection(_testFixture.ConnectionString);
await Task.WhenAll(connection1.OpenAsync(), connection2.OpenAsync());
var tx1 = await connection1.BeginTransactionAsync();
// TX1 READ
using (var cmd = connection1.CreateSelectCommand(
"SELECT * FROM TX WHERE K=@k",
new SpannerParameterCollection {
{ "k", _key, SpannerDbType.String }
}))
{
cmd.Transaction = tx1;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX2 START
var tx2 = await connection2.BeginTransactionAsync();
// TX2 READ
using (var cmd = connection2.CreateSelectCommand(
"SELECT * FROM TX WHERE K=@k",
new SpannerParameterCollection {
{ "k", _key, SpannerDbType.String }
}))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
// TX1 WRITE/COMMIT
using (var cmd = connection1.CreateUpdateCommand(
"TX",
new SpannerParameterCollection
{
{ "k", _key, SpannerDbType.String },
{ "Int64Value", 0, SpannerDbType.Int64 }
}))
{
cmd.Transaction = tx1;
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
await tx1.CommitAsync().ConfigureAwait(false);
tx1.Dispose();
}
connection1.Dispose();
// TX2 READ AGAIN/THROWS
SpannerException thrownException = null;
try
{
using (var cmd = connection2.CreateSelectCommand(
"SELECT * FROM TX WHERE K=@k",
new SpannerParameterCollection {
{ "k", _key, SpannerDbType.String }
}))
{
cmd.Transaction = tx2;
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
}
}
}
catch (SpannerException ex)
{
thrownException = ex;
}
finally
{
tx2.Dispose();
connection2.Dispose();
}
Assert.True(thrownException?.IsRetryable ?? false);
}
private async Task WriteSampleRowsAsync(SpannerConnection connection)
{
if (string.IsNullOrEmpty(_key))
{
_key = Guid.NewGuid().ToString();
SpannerCommand insupdate;
// 1st update
await connection.OpenAsync();
using (var tx = await connection.BeginTransactionAsync())
{
insupdate = connection.CreateInsertOrUpdateCommand(
"TX",
new SpannerParameterCollection
{
{ "K", SpannerDbType.String, _key },
{ "StringValue", SpannerDbType.String, Guid.NewGuid().ToString() }
});
insupdate.Transaction = tx;
await insupdate.ExecuteNonQueryAsync();
var timestamp = await tx.CommitAsync();
_history.Add(
new HistoryEntry
{
Value = insupdate.Parameters[1].Value.ToString(),
Timestamp = timestamp.GetValueOrDefault()
});
}
await Task.Delay(250);
// 2nd update
using (var tx = await connection.BeginTransactionAsync())
{
insupdate.Transaction = tx;
insupdate.CommandText = "UPDATE TX";
insupdate.Parameters[1].Value = Guid.NewGuid().ToString();
await insupdate.ExecuteNonQueryAsync();
var timestamp = await tx.CommitAsync();
_history.Add(
new HistoryEntry
{
Value = insupdate.Parameters[1].Value.ToString(),
Timestamp = timestamp.GetValueOrDefault()
});
}
await Task.Delay(250);
// 3rd update
using (var tx = await connection.BeginTransactionAsync())
{
insupdate.Transaction = tx;
insupdate.Parameters[1].Value = Guid.NewGuid().ToString();
await insupdate.ExecuteNonQueryAsync();
var timestamp = await tx.CommitAsync();
_history.Add(
new HistoryEntry
{
Value = insupdate.Parameters[1].Value.ToString(),
Timestamp = timestamp.GetValueOrDefault()
});
}
}
}
public async Task ReadWriteTransaction()
{
decimal initialBudget1 = 1225250.00m;
decimal initialBudget2 = 2250198.28m;
_fixture.SpannerMock.AddOrUpdateStatementResult(
"SELECT MarketingBudget FROM Albums WHERE SingerId = 1 AND AlbumId = 1",
StatementResult.CreateSingleColumnResultSet(new V1.Type {
Code = TypeCode.Numeric
}, "MarketingBudget", initialBudget1));
_fixture.SpannerMock.AddOrUpdateStatementResult(
"SELECT MarketingBudget FROM Albums WHERE SingerId = 2 AND AlbumId = 2",
StatementResult.CreateSingleColumnResultSet(new V1.Type {
Code = TypeCode.Numeric
}, "MarketingBudget", initialBudget2));
string connectionString = $"Data Source=projects/p1/instances/i1/databases/d1;Host={_fixture.Host};Port={_fixture.Port}";
decimal transferAmount = 200000;
decimal secondBudget = 0;
decimal firstBudget = 0;
using var connection = new SpannerConnection(connectionString, ChannelCredentials.Insecure);
await connection.OpenAsync();
using (var transaction = await connection.BeginTransactionAsync())
{
// Create statement to select the second album's data.
var cmdLookup = connection.CreateSelectCommand(
"SELECT MarketingBudget FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
cmdLookup.Transaction = transaction;
// Excecute the select query.
using (var reader = await cmdLookup.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
secondBudget = reader.GetNumeric(reader.GetOrdinal("MarketingBudget")).ToDecimal(LossOfPrecisionHandling.Throw);
}
}
// Read the first album's budget.
cmdLookup = connection.CreateSelectCommand(
"SELECT MarketingBudget FROM Albums WHERE SingerId = 1 AND AlbumId = 1");
cmdLookup.Transaction = transaction;
using (var reader = await cmdLookup.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
firstBudget = reader.GetNumeric(reader.GetOrdinal("MarketingBudget")).ToDecimal(LossOfPrecisionHandling.Throw);
}
}
// Specify update command parameters.
var cmd = connection.CreateUpdateCommand("Albums",
new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64 },
{ "AlbumId", SpannerDbType.Int64 },
{ "MarketingBudget", SpannerDbType.Numeric },
});
cmd.Transaction = transaction;
// Update second album to remove the transfer amount.
secondBudget -= transferAmount;
cmd.Parameters["SingerId"].Value = 2;
cmd.Parameters["AlbumId"].Value = 2;
cmd.Parameters["MarketingBudget"].Value = secondBudget;
await cmd.ExecuteNonQueryAsync();
// Update first album to add the transfer amount.
firstBudget += transferAmount;
cmd.Parameters["SingerId"].Value = 1;
cmd.Parameters["AlbumId"].Value = 1;
cmd.Parameters["MarketingBudget"].Value = firstBudget;
await cmd.ExecuteNonQueryAsync();
await transaction.CommitAsync();
}
// Assert that the correct updates were sent.
Stack <IMessage> requests = new Stack <IMessage>(_fixture.SpannerMock.Requests);
Assert.Equal(typeof(CommitRequest), requests.Peek().GetType());
CommitRequest commit = (CommitRequest)requests.Pop();
Assert.Equal(2, commit.Mutations.Count);
Mutation update1 = commit.Mutations.Last();
Assert.Equal(Mutation.OperationOneofCase.Update, update1.OperationCase);
Assert.Equal("Albums", update1.Update.Table);
Assert.Equal("1", update1.Update.Values.ElementAt(0).Values.ElementAt(0).StringValue);
Assert.Equal(
SpannerNumeric.FromDecimal(initialBudget1 + transferAmount, LossOfPrecisionHandling.Throw),
SpannerNumeric.Parse(update1.Update.Values.ElementAt(0).Values.ElementAt(2).StringValue));
Mutation update2 = commit.Mutations.First();
Assert.Equal(Mutation.OperationOneofCase.Update, update2.OperationCase);
Assert.Equal("Albums", update2.Update.Table);
Assert.Equal("2", update2.Update.Values.ElementAt(0).Values.ElementAt(0).StringValue);
Assert.Equal(
SpannerNumeric.FromDecimal(initialBudget2 - transferAmount, LossOfPrecisionHandling.Throw),
SpannerNumeric.Parse(update2.Update.Values.ElementAt(0).Values.ElementAt(2).StringValue));
}
public static async Task RunPlanetAuctionAsync(
string projectId, string instanceId, string databaseId, bool showConsoleOutput)
{
/*
* - Get Random Planet with SharesAvailable > 0
* - Get Cost/Share Amount
* - Get Random Player with PlanetDollars > Cost/Share Amount
* - Subtract Planet's Available Shares
* - Subtract Player's PlanetDollars
* - Insert entry into Transaction table
*/
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection =
new SpannerConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction =
await connection.BeginTransactionAsync())
{
long planetId = 0;
string planetName = "";
long sharesAvailable = 0;
long costPerShare = 0;
string playerId = "";
string playerName = "";
long planetDollars = 0;
// Create statement to select a random planet
var cmd = connection.CreateSelectCommand(
"SELECT PlanetId, PlanetName, SharesAvailable, DIV(PlanetValue, SharesAvailable) as ShareCost "
+ "FROM (SELECT * FROM Planets TABLESAMPLE BERNOULLI (10 PERCENT)) "
+ "WHERE SharesAvailable > 0 LIMIT 1");
//cmd.Transaction = transaction;
// Excecute the select query.
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
// Read the planet's ID.
planetId = reader.GetFieldValue <long>("PlanetId");
// Read the planet's Name.
planetName = reader.GetFieldValue <string>("PlanetName");
// Read the planet's shares available.
sharesAvailable = reader.GetFieldValue <long>("SharesAvailable");
// Read the planet's cost per share.
costPerShare = reader.GetFieldValue <long>("ShareCost");
}
}
if (showConsoleOutput)
{
Console.WriteLine($"Planet: {planetName}");
Console.WriteLine($"Planet sharesAvailable:{sharesAvailable}");
Console.WriteLine($"Planet costPerShare: {costPerShare.ToString("N0")}");
}
// Create statement to select a random player.
cmd = connection.CreateSelectCommand(
"SELECT PlayerId, PlayerName, PlanetDollars FROM "
+ "(SELECT * FROM Players TABLESAMPLE BERNOULLI (10 PERCENT)) "
+ "WHERE PlanetDollars >= @costPerShare LIMIT 1",
new SpannerParameterCollection {
{ "costPerShare", SpannerDbType.Int64 }
});
cmd.Parameters["costPerShare"].Value = costPerShare;
//cmd.Transaction = transaction;
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
playerId = reader.GetFieldValue <string>("PlayerId");
playerName = reader.GetFieldValue <string>("PlayerName");
planetDollars = reader.GetFieldValue <long>("PlanetDollars");
}
}
if (showConsoleOutput)
{
Console.WriteLine($"1 Share of {planetName} sold to {playerName} "
+ $"for {costPerShare.ToString("N0")} Planet Dollars");
Console.WriteLine($"{playerName} now has {planetDollars.ToString("N0")} Planet Dollars");
}
if (planetId != 0 && playerId != "")
{
// Subtract 1 from planet's shares available.
using (cmd = connection.CreateUpdateCommand(
"Planets", new SpannerParameterCollection
{
{ "PlanetId", SpannerDbType.Int64 },
{ "SharesAvailable", SpannerDbType.Int64 },
}))
{
cmd.Transaction = transaction;
sharesAvailable--;
cmd.Parameters["PlanetId"].Value = planetId;
cmd.Parameters["SharesAvailable"].Value = sharesAvailable;
await cmd.ExecuteNonQueryAsync();
}
// Subtract cost per share from player's planet dollars.
using (cmd = connection.CreateUpdateCommand(
"Players", new SpannerParameterCollection
{
{ "PlayerId", SpannerDbType.String },
{ "PlanetDollars", SpannerDbType.Int64 },
}))
{
cmd.Transaction = transaction;
planetDollars -= costPerShare;
cmd.Parameters["PlayerId"].Value = playerId;
cmd.Parameters["PlanetDollars"].Value = planetDollars;
await cmd.ExecuteNonQueryAsync();
}
// Insert record of transaction in Transactions table.
using (cmd = connection.CreateInsertCommand(
"Transactions", new SpannerParameterCollection
{
{ "PlanetId", SpannerDbType.Int64 },
{ "PlayerId", SpannerDbType.String },
{ "TimeStamp", SpannerDbType.Timestamp },
{ "Amount", SpannerDbType.Int64 }
}))
{
cmd.Transaction = transaction;
cmd.Parameters["PlanetId"].Value = planetId;
cmd.Parameters["PlayerId"].Value = playerId;
cmd.Parameters["TimeStamp"].Value = SpannerParameter.CommitTimestamp;
cmd.Parameters["Amount"].Value = costPerShare;
await cmd.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
else
{
_failedTransactions++;
if (showConsoleOutput)
{
Console.WriteLine("PlanetId or PlayerId was invalid.");
}
}
}
if (showConsoleOutput)
{
Console.WriteLine("1 Transaction complete");
}
}
}
public async Task <IActionResult> Index(Form sendForm)
{
var model = new HomeIndex();
model.Content = sendForm.Content;
model.SavedNewContent = true;
// Spanner connection string.
string connectionString =
$"Data Source=projects/{_options.ProjectId}/instances/{_options.InstanceId}"
+ $"/databases/{_options.DatabaseId}";
// Insert Player if PlayerID not present in sent form data.
string playerId = "";
if (string.IsNullOrEmpty(sendForm.PlayerId))
{
// Insert Player Code
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
using (var tx = await connection.BeginTransactionAsync())
{
using (var cmd = connection.CreateInsertCommand(
"Players", new SpannerParameterCollection
{
{ "PlayerId", SpannerDbType.String },
{ "PlayerName", SpannerDbType.String },
{ "PlanetDollars", SpannerDbType.Int64 }
}))
{
cmd.Transaction = tx;
playerId = Guid.NewGuid().ToString("N");
cmd.Parameters["PlayerId"].Value = playerId;
cmd.Parameters["PlayerName"].Value = sendForm.Content;
cmd.Parameters["PlanetDollars"].Value = 1000000;
cmd.ExecuteNonQuery();
}
await tx.CommitAsync();
}
}
model.PlayerId = playerId;
}
else
{
model.PlayerId = sendForm.PlayerId;
playerId = sendForm.PlayerId;
}
// Submit transaction for Player purchase of 1 planet share.
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction =
await connection.BeginTransactionAsync())
{
long planetId = 0;
string planetName = "";
long sharesAvailable = 0;
long costPerShare = 0;
//string playerId = playerId;
string playerName = "";
long planetDollars = 0;
// Create statement to select a random planet
var cmd = connection.CreateSelectCommand(
"SELECT PlanetId, PlanetName, SharesAvailable, DIV(PlanetValue, SharesAvailable) as ShareCost "
+ "FROM (SELECT * FROM Planets TABLESAMPLE BERNOULLI (10 PERCENT)) "
+ "WHERE SharesAvailable > 0 LIMIT 1");
// Excecute the select query.
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
// Read the planet's ID.
planetId = reader.GetFieldValue <long>("PlanetId");
// Read the planet's Name.
planetName = reader.GetFieldValue <string>("PlanetName");
// Read the planet's shares available.
sharesAvailable = reader.GetFieldValue <long>("SharesAvailable");
// Read the planet's cost per share.
costPerShare = reader.GetFieldValue <long>("ShareCost");
}
}
// Create statement to select player details.
cmd = connection.CreateSelectCommand(
"SELECT PlayerId, PlayerName, PlanetDollars FROM Players "
+ "WHERE PlayerId = @playerId",
new SpannerParameterCollection {
{ "playerId", SpannerDbType.String }
});
cmd.Parameters["playerId"].Value = playerId;
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
playerId = reader.GetFieldValue <string>("PlayerId");
playerName = reader.GetFieldValue <string>("PlayerName");
planetDollars = reader.GetFieldValue <long>("PlanetDollars");
}
}
if (planetDollars >= costPerShare && planetId != 0)
{
// Subtract 1 from planet's shares available.
using (cmd = connection.CreateUpdateCommand(
"Planets", new SpannerParameterCollection
{
{ "PlanetId", SpannerDbType.Int64 },
{ "SharesAvailable", SpannerDbType.Int64 },
}))
{
cmd.Transaction = transaction;
sharesAvailable--;
cmd.Parameters["PlanetId"].Value = planetId;
cmd.Parameters["SharesAvailable"].Value = sharesAvailable;
await cmd.ExecuteNonQueryAsync();
}
// Subtract cost per share from player's planet dollars.
using (cmd = connection.CreateUpdateCommand(
"Players", new SpannerParameterCollection
{
{ "PlayerId", SpannerDbType.String },
{ "PlanetDollars", SpannerDbType.Int64 },
}))
{
cmd.Transaction = transaction;
planetDollars -= costPerShare;
cmd.Parameters["PlayerId"].Value = playerId;
cmd.Parameters["PlanetDollars"].Value = planetDollars;
await cmd.ExecuteNonQueryAsync();
}
// Insert record of transaction in Transactions table.
using (cmd = connection.CreateInsertCommand(
"Transactions", new SpannerParameterCollection
{
{ "PlanetId", SpannerDbType.Int64 },
{ "PlayerId", SpannerDbType.String },
{ "TimeStamp", SpannerDbType.Timestamp },
{ "Amount", SpannerDbType.Int64 }
}))
{
cmd.Transaction = transaction;
cmd.Parameters["PlanetId"].Value = planetId;
cmd.Parameters["PlayerId"].Value = playerId;
cmd.Parameters["TimeStamp"].Value = SpannerParameter.CommitTimestamp;
cmd.Parameters["Amount"].Value = costPerShare;
await cmd.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
model.Status = $"1 Share of {planetName} sold to {playerName} "
+ $"for {costPerShare.ToString("N0")} Planet Dollars. "
+ $"{playerName} now has {planetDollars.ToString("N0")} Planet Dollars.";
}
else
{
if (planetId == 0)
{
model.Status = "Failed to acquire a valid Planet share. Please retry.";
}
else
{
// Player doesn't have enough Planet Dollars to purchase planet share
model.Status = $"{planetDollars.ToString("N0")} Planet Dollars is not enough to purchase a share of {planetName}";
}
}
}
}
return(View(model));
}
public async Task <int> ReadWriteWithTransactionCoreAsync(string projectId, string instanceId, string databaseId)
{
// This sample transfers 200,000 from the MarketingBudget
// field of the second Album to the first Album. Make sure to run
// the Add Column and Write Data To New Column samples first,
// in that order.
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
decimal transferAmount = 200000;
decimal secondBudget = 0;
decimal firstBudget = 0;
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
using var cmdLookup1 = connection.CreateSelectCommand("SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
cmdLookup1.Transaction = transaction;
using (var reader = await cmdLookup1.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
// Read the second album's budget.
secondBudget = reader.GetFieldValue <decimal>("MarketingBudget");
// Confirm second Album's budget is sufficient and
// if not raise an exception. Raising an exception
// will automatically roll back the transaction.
if (secondBudget < transferAmount)
{
throw new Exception($"The second album's budget {secondBudget} contains less than the amount to transfer.");
}
}
}
// Read the first album's budget.
using var cmdLookup2 = connection.CreateSelectCommand("SELECT * FROM Albums WHERE SingerId = 1 and AlbumId = 1");
cmdLookup2.Transaction = transaction;
using (var reader = await cmdLookup2.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
firstBudget = reader.GetFieldValue <decimal>("MarketingBudget");
}
}
// Specify update command parameters.
using var cmdUpdate = connection.CreateUpdateCommand("Albums", new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64 },
{ "AlbumId", SpannerDbType.Int64 },
{ "MarketingBudget", SpannerDbType.Int64 },
});
cmdUpdate.Transaction = transaction;
// Update second album to remove the transfer amount.
secondBudget -= transferAmount;
cmdUpdate.Parameters["SingerId"].Value = 2;
cmdUpdate.Parameters["AlbumId"].Value = 2;
cmdUpdate.Parameters["MarketingBudget"].Value = secondBudget;
var rowCount = await cmdUpdate.ExecuteNonQueryAsync();
// Update first album to add the transfer amount.
firstBudget += transferAmount;
cmdUpdate.Parameters["SingerId"].Value = 1;
cmdUpdate.Parameters["AlbumId"].Value = 1;
cmdUpdate.Parameters["MarketingBudget"].Value = firstBudget;
rowCount += await cmdUpdate.ExecuteNonQueryAsync();
await transaction.CommitAsync();
Console.WriteLine("Transaction complete.");
return(rowCount);
}