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); }