public async Task <long> LogCommitStatsAsync(string projectId, string instanceId, string databaseId)
{
// Commit statistics are logged at level Info by the default logger.
// This sample uses a custom logger to access the commit statistics.
// See https://googleapis.github.io/google-cloud-dotnet/docs/Google.Cloud.Spanner.Data/logging.html
// for more information on how to use loggers.
var logger = new CommitStatsSampleLogger();
var options = new SessionPoolOptions();
var poolManager = SessionPoolManager.Create(options, logger);
var connectionStringBuilder = new SpannerConnectionStringBuilder
{
ConnectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}",
// Set LogCommitStats to true to enable logging commit statistics for all transactions on the connection.
// LogCommitStats can also be enabled/disabled for individual Spanner transactions.
LogCommitStats = true,
SessionPoolManager = poolManager,
};
using var connection = new SpannerConnection(connectionStringBuilder);
await connection.OpenAsync();
using var cmd = connection.CreateDmlCommand("INSERT Singers (SingerId, FirstName, LastName) VALUES (110, 'Virginia', 'Watson')");
var rowCount = await cmd.ExecuteNonQueryAsync();
var mutationCount = logger._lastCommitResponse.CommitStats.MutationCount;
Console.WriteLine($"{rowCount} row(s) inserted...");
Console.WriteLine($"{mutationCount} mutation(s) in transaction...");
return(mutationCount);
}
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();
}
}
}
}
async Task RefillMarketingBudgetsAsync(int firstAlbumBudget,
int secondAlbumBudget)
{
string connectionString =
$"Data Source=projects/{_fixture.ProjectId}/instances/{_fixture.InstanceId}"
+ $"/databases/{_fixture.DatabaseId}";
// Create connection to Cloud Spanner.
using (var connection =
new SpannerConnection(connectionString))
{
await connection.OpenAsync();
var cmd = connection.CreateUpdateCommand("Albums",
new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64 },
{ "AlbumId", SpannerDbType.Int64 },
{ "MarketingBudget", SpannerDbType.Int64 },
});
for (int i = 1; i <= 2; ++i)
{
cmd.Parameters["SingerId"].Value = i;
cmd.Parameters["AlbumId"].Value = i;
cmd.Parameters["MarketingBudget"].Value = i == 1 ?
firstAlbumBudget : secondAlbumBudget;
await cmd.ExecuteNonQueryAsync();
}
}
}
public async Task InsertDataAsync()
{
await _fixture.EnsureTestDatabaseAsync().ConfigureAwait(false);
// Sample: InsertDataAsync
using (var connection = new SpannerConnection(
$"Data Source=projects/{_projectId}/instances/{_instanceName}/databases/{_databaseName}"))
{
await connection.OpenAsync();
var cmd = connection.CreateInsertCommand(
"TestTable", new SpannerParameterCollection
{
{ "Key", SpannerDbType.String },
{ "StringValue", SpannerDbType.String },
{ "Int64Value", SpannerDbType.Int64 }
});
// This executes 5 distinct transactions with one row written per transaction.
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;
int rowsAffected = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowsAffected} rows written...");
}
}
// End sample
}
private async Task InsertStructDataAsync()
{
List <Singer> singers = new List <Singer>
{
new Singer {
SingerId = 6, FirstName = "Elena", LastName = "Campbell"
},
new Singer {
SingerId = 7, FirstName = "Gabriel", LastName = "Wright"
},
new Singer {
SingerId = 8, FirstName = "Benjamin", LastName = "Martinez"
},
new Singer {
SingerId = 9, FirstName = "Hannah", LastName = "Harris"
}
};
using var connection = new SpannerConnection(ConnectionString);
await connection.OpenAsync();
var rows = await Task.WhenAll(singers.Select(singer =>
{
var cmd = connection.CreateInsertCommand("Singers",
new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64, singer.SingerId },
{ "FirstName", SpannerDbType.String, singer.FirstName },
{ "LastName", SpannerDbType.String, singer.LastName }
});
return(cmd.ExecuteNonQueryAsync());
}));
}
private async Task <SpannerConnection> CreateAndOpen()
{
var connection = new SpannerConnection(_testFixture.ConnectionString);
await connection.OpenAsync();
return(connection);
}
public async Task <List <Album> > ReadStaleDataAsync(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();
var staleness = TimestampBound.OfExactStaleness(TimeSpan.FromSeconds(15));
using var transaction = await connection.BeginReadOnlyTransactionAsync(staleness);
using var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, MarketingBudget FROM Albums");
cmd.Transaction = transaction;
var albums = new List <Album>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
albums.Add(new Album
{
SingerId = reader.GetFieldValue <int>("SingerId"),
AlbumId = reader.GetFieldValue <int>("AlbumId"),
MarketingBudget = reader.IsDBNull(reader.GetOrdinal("MarketingBudget")) ? 0 : reader.GetFieldValue <long>("MarketingBudget")
});
}
return(albums);
}
public async Task UpdateDataWithNumericAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
List <Venue> venues = new List <Venue>
{
new Venue {
VenueId = 4, Revenue = SpannerNumeric.Parse("35000")
},
new Venue {
VenueId = 19, Revenue = SpannerNumeric.Parse("104500")
},
new Venue {
VenueId = 42, Revenue = SpannerNumeric.Parse("99999999999999999999999999999.99")
},
};
// Create connection to Cloud Spanner.
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
await Task.WhenAll(venues.Select(venue =>
{
// Update rows in the Venues table.
using var cmd = connection.CreateUpdateCommand("Venues", new SpannerParameterCollection
{
{ "VenueId", SpannerDbType.Int64, venue.VenueId },
{ "Revenue", SpannerDbType.Numeric, venue.Revenue }
});
return(cmd.ExecuteNonQueryAsync());
}));
Console.WriteLine("Data updated.");
}
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 static async Task <T> ExecuteCommandAsync <T>(
SpannerConnection connection, Func <DbCommand, Task <T> > executeAsync, string sql, bool useTransaction,
IReadOnlyList <object> parameters)
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
await connection.OpenAsync().ConfigureAwait(false);
try
{
using (var transaction = useTransaction ? connection.BeginTransaction() : null)
{
T result;
using (var command = CreateCommand(connection, sql, parameters))
{
result = await executeAsync(command).ConfigureAwait(false);
}
transaction?.Commit();
return(result);
}
}
finally
{
if (connection.State == ConnectionState.Closed &&
connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
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 async Task <int> InsertStructSampleDataAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
List <Singer> singers = new List <Singer> {
new Singer {
SingerId = 6, FirstName = "Elena", LastName = "Campbell"
},
new Singer {
SingerId = 7, FirstName = "Gabriel", LastName = "Wright"
},
new Singer {
SingerId = 8, FirstName = "Benjamin", LastName = "Martinez"
},
new Singer {
SingerId = 9, FirstName = "Hannah", LastName = "Harris"
}
};
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
var rows = await Task.WhenAll(singers.Select(singer =>
{
var cmd = connection.CreateInsertCommand("Singers",
new SpannerParameterCollection {
{ "SingerId", SpannerDbType.Int64, singer.SingerId },
{ "FirstName", SpannerDbType.String, singer.FirstName },
{ "LastName", SpannerDbType.String, singer.LastName }
});
return(cmd.ExecuteNonQueryAsync());
}));
return(rows.Sum());
}
public async Task DistributedReadAsync()
{
int numRows;
using (var connection = await _testFixture.GetTestDatabaseConnectionAsync())
using (var cmd = connection.CreateSelectCommand("SELECT COUNT(*) FROM Orders"))
{
numRows = await cmd.ExecuteScalarAsync <int>();
}
using (var connection = new SpannerConnection(_testFixture.ConnectionString))
{
await connection.OpenAsync();
using (var transaction = await connection.BeginReadOnlyTransactionAsync())
using (var cmd = connection.CreateSelectCommand("SELECT * FROM Orders"))
{
transaction.DisposeBehavior = DisposeBehavior.CloseResources;
cmd.Transaction = transaction;
var partitions = await cmd.GetReaderPartitionsAsync(1000);
var transactionId = transaction.TransactionId;
//we simulate a serialization/deserialization step in the call to the subtask.
await Task.WhenAll(partitions.Select(
x => DistributedReadWorkerAsync(CommandPartition.FromBase64String(x.ToBase64String()),
TransactionId.FromBase64String(transactionId.ToBase64String()))))
.ConfigureAwait(false);
}
Assert.Equal(numRows, _rowsRead);
}
}
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);
}
public async Task <CurrencyRate> GetRate(string from, string to)
{
await using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
var result = await GetRatesImpl(connection, from, to);
return(result.FirstOrDefault());
}
public async Task <CurrencyRate[]> GetRates(string from)
{
await using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
var result = await GetRatesImpl(connection, from);
return(result.ToArray());
}
private static async Task Can_use_an_existing_closed_connection_test(bool openConnection)
{
var serviceProvider = new ServiceCollection()
.AddEntityFrameworkSpanner()
.BuildServiceProvider();
using (var store = SpannerTestStore.GetNorthwindStore())
{
var openCount = 0;
var closeCount = 0;
var disposeCount = 0;
using (var connection = new SpannerConnection(store.ConnectionString))
{
if (openConnection)
{
await connection.OpenAsync();
}
connection.StateChange += (_, a) =>
{
if (a.CurrentState == ConnectionState.Open)
{
openCount++;
}
else if (a.CurrentState == ConnectionState.Closed)
{
closeCount++;
}
};
connection.Disposed += (_, __) => disposeCount++;
using (var context = new NorthwindContext(serviceProvider, connection))
{
Assert.Equal(91, await context.Customers.CountAsync());
}
if (openConnection)
{
Assert.Equal(ConnectionState.Open, connection.State);
Assert.Equal(0, openCount);
Assert.Equal(0, closeCount);
}
else
{
Assert.Equal(ConnectionState.Closed, connection.State);
Assert.Equal(1, openCount);
Assert.Equal(1, closeCount);
}
Assert.Equal(0, disposeCount);
}
}
}
public async Task <int> DeleteUsingDmlCoreAsync(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();
using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Alice'");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) deleted...");
return(rowCount);
}
public async Task <int> UpdateUsingDmlWithTimestampCoreAsync(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();
using var cmd = connection.CreateDmlCommand("UPDATE Albums SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) updated...");
return(rowCount);
}
public async Task <long> DeleteUsingPartitionedDmlCoreAsync(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();
using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE SingerId > 10");
long rowCount = await cmd.ExecutePartitionedUpdateAsync();
Console.WriteLine($"{rowCount} row(s) deleted...");
return(rowCount);
}
public async Task <int> UpdateUsingDmlCoreAsync(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();
using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 1");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) updated...");
return(rowCount);
}
public async Task <int> InsertUsingDmlCoreAsync(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();
using var cmd = connection.CreateDmlCommand("INSERT Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) inserted...");
return(rowCount);
}
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...");
}
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 <int> DeleteDataAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
var albums = new List <Album>
{
new Album {
SingerId = 2, AlbumId = 1, AlbumTitle = "Green"
},
new Album {
SingerId = 2, AlbumId = 3, AlbumTitle = "Terrified"
},
};
int rowCount = 0;
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
// Delete individual rows from the Albums table.
await Task.WhenAll(albums.Select(async album =>
{
var cmd = connection.CreateDeleteCommand("Albums", new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64, album.SingerId },
{ "AlbumId", SpannerDbType.Int64, album.AlbumId }
});
rowCount += await cmd.ExecuteNonQueryAsync();
}));
Console.WriteLine("Deleted individual rows in Albums.");
// Delete a range of rows from the Singers table where the column key is >=3 and <5.
var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE SingerId >= 3 AND SingerId < 5");
rowCount += await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) deleted from Singers.");
// Delete remaining Singers rows, which will also delete the remaining
// Albums rows since it was defined with ON DELETE CASCADE.
cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE true");
rowCount += await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) deleted from Singers.");
}
return(rowCount);
}
public async Task RefillMarketingBudgetsAsync(int firstAlbumBudget, int secondAlbumBudget)
{
using var connection = new SpannerConnection(ConnectionString);
await connection.OpenAsync();
for (int i = 1; i <= 2; ++i)
{
var cmd = connection.CreateUpdateCommand("Albums", new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64, i },
{ "AlbumId", SpannerDbType.Int64, i },
{ "MarketingBudget", SpannerDbType.Int64, i == 1 ? firstAlbumBudget : secondAlbumBudget },
});
await cmd.ExecuteNonQueryAsync();
}
}
public async Task SetActualRates(ICollection <CurrencyRate> rates)
{
await using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
var actualRates = await GetRatesImpl(connection);
var tasks = new List <Task>();
foreach (var newRate in rates)
{
var actualRate = actualRates.FirstOrDefault(r => r.Key == newRate.Key);
if (actualRate == null)
{
var insertActualRate = connection.CreateInsertCommand("actual_rates");
insertActualRate.Parameters.Add("currency_from", SpannerDbType.String, newRate.CurrencyFrom);
insertActualRate.Parameters.Add("currency_to", SpannerDbType.String, newRate.CurrencyTo);
insertActualRate.Parameters.Add("provider_name", SpannerDbType.String, newRate.ProviderName);
insertActualRate.Parameters.Add("expiration_time", SpannerDbType.Timestamp, newRate.ExpirationTime);
insertActualRate.Parameters.Add("original_published_time", SpannerDbType.Timestamp, newRate.OriginalPublishedTime);
insertActualRate.Parameters.Add("time_of_receipt", SpannerDbType.Timestamp, newRate.TimeOfReceipt);
insertActualRate.Parameters.Add("value", SpannerDbType.Int64, newRate.Value);
tasks.Add(insertActualRate.ExecuteNonQueryAsync());
}
else
{
actualRate.Update(newRate, mapper);
var cmd = connection.CreateDmlCommand(
"UPDATE actual_rates " +
"SET expiration_time = @expiration_time, " +
"original_published_time = @original_published_time, " +
"provider_name = @provider_name, " +
"time_of_receipt = @time_of_receipt, " +
"value = @value " +
"WHERE currency_from = @currency_from and currency_to = @currency_to");
cmd.Parameters.Add("provider_name", SpannerDbType.String, newRate.ProviderName);
cmd.Parameters.Add("expiration_time", SpannerDbType.Timestamp, newRate.ExpirationTime);
cmd.Parameters.Add("original_published_time", SpannerDbType.Timestamp, newRate.OriginalPublishedTime);
cmd.Parameters.Add("time_of_receipt", SpannerDbType.Timestamp, newRate.TimeOfReceipt);
cmd.Parameters.Add("value", SpannerDbType.Int64, newRate.Value);
tasks.Add(cmd.ExecuteNonQueryAsync());
}
}
await Task.WhenAll(tasks);
}
public async Task ReadUpdateDeleteAsync()
{
await _fixture.EnsureTestDatabaseAsync().ConfigureAwait(false);
// Sample: ReadUpdateDeleteAsync
using (var connection = new SpannerConnection(
$"Data Source=projects/{_projectId}/instances/{_instanceName}/databases/{_databaseName}"))
{
await connection.OpenAsync();
// Read the first two keys in the database.
var keys = new List <string>();
var selectCmd = connection.CreateSelectCommand("SELECT * FROM TestTable");
using (var reader = await selectCmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
if (keys.Count < 3)
{
keys.Add(reader.GetFieldValue <string>("Key"));
continue;
}
break;
}
}
// Update the Int64Value of keys[0]
// Include the primary key and update columns.
var updateCmd = connection.CreateUpdateCommand(
"TestTable", new SpannerParameterCollection
{
{ "Key", SpannerDbType.String, keys[0] },
{ "Int64Value", SpannerDbType.Int64, 0L }
});
await updateCmd.ExecuteNonQueryAsync();
// Delete row for keys[1]
var deleteCmd = connection.CreateDeleteCommand(
"TestTable", new SpannerParameterCollection
{
{ "Key", SpannerDbType.String, keys[1] }
});
await deleteCmd.ExecuteNonQueryAsync();
}
// End sample
}
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 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...");
}