public void EphemeralTransactionIncludesPriorityOnBatchDmlAndCommit() { var priority = Priority.Medium; Mock <SpannerClient> spannerClientMock = SpannerClientHelpers .CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict); spannerClientMock .SetupBatchCreateSessionsAsync() .SetupBeginTransactionAsync() .SetupExecuteBatchDmlAsync() .SetupCommitAsync(); SpannerConnection connection = SpannerCommandTests.BuildSpannerConnection(spannerClientMock); var command = connection.CreateBatchDmlCommand(); command.Add("UPDATE FOO SET BAR=1 WHERE TRUE"); command.Priority = priority; command.ExecuteNonQuery(); spannerClientMock.Verify(client => client.ExecuteBatchDmlAsync( It.Is <ExecuteBatchDmlRequest>(request => request.RequestOptions.Priority == PriorityConverter.ToProto(priority)), It.IsAny <CallSettings>()), Times.Once()); spannerClientMock.Verify(client => client.CommitAsync( It.Is <CommitRequest>(request => request.RequestOptions.Priority == PriorityConverter.ToProto(priority)), It.IsAny <CallSettings>()), Times.Once()); }
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 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..."); }