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());
}));
}
static async Task InsertRepeatedlyAsync()
{
int errors = 0;
for (int i = 0; i < InsertCount; i++)
{
if (i % LogInsertFrequency == 0)
{
Log($"Test {i}");
}
try
{
using (var connection = new SpannerConnection(ConnectionString))
{
var cmd = connection.CreateInsertCommand(Table);
cmd.Parameters.Add("K", SpannerDbType.String, Guid.NewGuid().ToString());
await cmd.ExecuteNonQueryAsync();
}
}
catch (SpannerException e) when(e.ErrorCode == ErrorCode.Aborted)
{
Log($"Aborted transaction at test {i}");
errors++;
}
}
Log($"Total errors over {InsertCount} insertions: {errors}");
}
public async Task ParallelMutationCommandsOnAmbientTransaction_OnlyCreateOneSpannerTransactionAsync()
{
Mock <SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupCommitAsync();
using (TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
var tables = new string[] { "TAB1", "TAB2", "TAB3" };
await Task.WhenAll(tables.Select(table =>
{
using var cmd = connection.CreateInsertCommand(table);
return(cmd.ExecuteNonQueryAsync());
}));
scope.Complete();
}
spannerClientMock.Verify(client => client.CommitAsync(
It.IsAny <CommitRequest>(), It.IsAny <CallSettings>()), Times.Once());
spannerClientMock.Verify(client => client.CommitAsync(
It.Is <CommitRequest>(request => request.Mutations.Count == 3),
It.IsAny <CallSettings>()), Times.Once());
}
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());
}
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();
}
}
}
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 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 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 CreateWithExtrasDrop2()
{
string dbName = GenerateDatabaseName();
var builder = new SpannerConnectionStringBuilder(_fixture.Database.NoDbConnectionString);
using (var connection = new SpannerConnection(builder))
{
var createCmd = connection.CreateDdlCommand(
$"CREATE DATABASE {dbName}");
await createCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
using (var connection = new SpannerConnection(builder.WithDatabase(dbName)))
{
const string tableCreate1 = @"CREATE TABLE TX1 (
K STRING(MAX) NOT NULL,
StringValue STRING(MAX),
) PRIMARY KEY (K)";
const string tableCreate2 = @"CREATE TABLE TX2 (
K STRING(MAX) NOT NULL,
StringValue STRING(MAX),
) PRIMARY KEY (K)";
var updateCmd = connection.CreateDdlCommand(tableCreate1, tableCreate2);
await updateCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
var cmd = connection.CreateInsertCommand("TX2", new SpannerParameterCollection
{
{ "K", SpannerDbType.String, "key" },
{ "StringValue", SpannerDbType.String, "value" }
});
await cmd.ExecuteNonQueryAsync();
cmd = connection.CreateSelectCommand("SELECT * FROM TX2");
using (var reader = await cmd.ExecuteReaderAsync())
{
await reader.ReadAsync();
Assert.Equal("key", reader.GetFieldValue <string>("K"));
Assert.Equal("value", reader.GetFieldValue <string>("StringValue"));
}
}
using (var connection = new SpannerConnection(builder))
{
var dropCommand = connection.CreateDdlCommand($"DROP DATABASE {dbName}");
await dropCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
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 TransactionScopeAsync()
{
await _fixture.EnsureTestDatabaseAsync().ConfigureAwait(false);
// Sample: TransactionScopeAsync
var retryPolicy = new RetryPolicy <SpannerFaultDetectionStrategy>(RetryStrategy.DefaultExponential);
await retryPolicy.ExecuteAsync(
async() =>
{
using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
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 a single transactions with alls row written at once during scope.Complete().
// 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();
}
scope.Complete();
await Task.Yield();
}
}
});
// End sample
//TODO(benwu): Remove Task.Yield once grpc# fixes threadpool issue.
}
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 async Task CreateVenuesTableAndInsertDataAsync()
{
string connectionString = $"Data Source=projects/{ProjectId}/instances/{InstanceId}/" +
$"databases/{DatabaseId}";
// Create connection to Cloud Spanner.
using var connection = new SpannerConnection(connectionString);
// Define create table statement for Venues.
string createTableStatement =
@"CREATE TABLE Venues (
VenueId INT64 NOT NULL,
VenueName STRING(1024),
) PRIMARY KEY (VenueId)";
using var cmd = connection.CreateDdlCommand(createTableStatement);
await cmd.ExecuteNonQueryAsync();
List <Venue> venues = new List <Venue>
{
new Venue {
VenueId = 4, VenueName = "Venue 4"
},
new Venue {
VenueId = 19, VenueName = "Venue 19"
},
new Venue {
VenueId = 42, VenueName = "Venue 42"
},
};
await Task.WhenAll(venues.Select(venue =>
{
// Insert rows into the Venues table.
using var cmd = connection.CreateInsertCommand("Venues", new SpannerParameterCollection
{
{ "VenueId", SpannerDbType.Int64 },
{ "VenueName", SpannerDbType.String }
});
cmd.Parameters["VenueId"].Value = venue.VenueId;
cmd.Parameters["VenueName"].Value = venue.VenueName;
return(cmd.ExecuteNonQueryAsync());
}));
}
public async Task CreateWithExtrasDrop()
{
string dbName = "t_" + Guid.NewGuid().ToString("N").Substring(0, 28);
using (var connection = new SpannerConnection(_testFixture.NoDbConnectionString))
{
const string tableCreate = @"CREATE TABLE TX (
K STRING(MAX) NOT NULL,
StringValue STRING(MAX),
) PRIMARY KEY (K)";
var createCmd = connection.CreateDdlCommand(
$"CREATE DATABASE {dbName}",
tableCreate);
await createCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
using (var connection = new SpannerConnection($"{_testFixture.NoDbConnectionString}/databases/{dbName}"))
{
var cmd = connection.CreateInsertCommand("TX", new SpannerParameterCollection
{
{ "K", SpannerDbType.String, "key" },
{ "StringValue", SpannerDbType.String, "value" }
});
await cmd.ExecuteNonQueryAsync();
cmd = connection.CreateSelectCommand("SELECT * FROM TX");
using (var reader = await cmd.ExecuteReaderAsync())
{
await reader.ReadAsync();
Assert.Equal("key", reader.GetFieldValue <string>("K"));
Assert.Equal("value", reader.GetFieldValue <string>("StringValue"));
}
}
using (var connection = new SpannerConnection(_testFixture.NoDbConnectionString))
{
var dropCommand = connection.CreateDdlCommand($"DROP DATABASE {dbName}");
await dropCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public void MutationCommandIncludesPriority()
{
Mock <SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupCommitAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
var command = connection.CreateInsertCommand("FOO");
command.Priority = Priority.High;
command.ExecuteNonQuery();
spannerClientMock.Verify(client => client.CommitAsync(
It.Is <CommitRequest>(request => request.RequestOptions.Priority == RequestOptions.Types.Priority.High),
It.IsAny <CallSettings>()), Times.Once());
}
public async Task CommandTimeout()
{
var values = new SpannerParameterCollection
{
{ "StringValue", SpannerDbType.String, "abc" },
{ "K", SpannerDbType.String, _lastKey = UniqueString() }
};
var e = await Assert.ThrowsAsync <SpannerException>(
async() =>
{
using (var connection = new SpannerConnection($"{_fixture.ConnectionString};{nameof(SpannerSettings.AllowImmediateTimeouts)}=true"))
{
var cmd = connection.CreateInsertCommand(_fixture.TableName, values);
cmd.CommandTimeout = 0;
await cmd.ExecuteNonQueryAsync();
}
}).ConfigureAwait(false);
SpannerAssert.IsTimeout(e);
}
public async Task <int> DatabaseWorkAsync(SpannerConnection connection, SpannerTransaction transaction)
{
_callTimes.Add(_scheduler.Clock.GetCurrentDateTimeUtc());
var insert = connection.CreateInsertCommand("table_1",
new SpannerParameterCollection
{
new SpannerParameter("column_1", SpannerDbType.Int64, 10)
});
insert.Transaction = transaction;
var dml = transaction.CreateBatchDmlCommand();
dml.Add("UPDATE table_1 SET column_1 = column1 + 5");
await insert.ExecuteNonQueryAsync();
await dml.ExecuteNonQueryAsync();
return(_callTimes.Count);
}
private SpannerCommand CreateSpannerCommand(
SpannerConnection spannerConnection,
SpannerTransaction transaction,
ModificationCommand modificationCommand)
{
SpannerCommand cmd;
switch (modificationCommand.EntityState)
{
case EntityState.Deleted:
cmd = spannerConnection.CreateDeleteCommand(modificationCommand.TableName);
break;
case EntityState.Modified:
cmd = spannerConnection.CreateUpdateCommand(modificationCommand.TableName);
break;
case EntityState.Added:
cmd = spannerConnection.CreateInsertCommand(modificationCommand.TableName);
break;
default:
throw new NotSupportedException(
$"Modification type {modificationCommand.EntityState} is not supported.");
}
cmd.Logger = new SpannerLogBridge <DbLoggerCategory.Database.Command>(_logger);
cmd.Transaction = transaction;
foreach (var columnModification in modificationCommand.ColumnModifications)
{
cmd.Parameters.Add(
_typeMapper.GetMapping(columnModification.Property).CreateParameter(cmd,
columnModification.ColumnName,
columnModification.UseOriginalValueParameter
? columnModification.OriginalValue
: columnModification.Value, columnModification.Property.IsNullable));
}
return(cmd);
}
public async Task AddRatesToHistory(ICollection <CurrencyRateHistoryItem> historyItems)
{
await using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
await DefineLastIdIfNeed(connection);
foreach (var hi in historyItems)
{
SetId(hi);
}
// Insert rows into the Singers table.
var cmd = connection.CreateInsertCommand("rates_history",
new SpannerParameterCollection
{
{ "id", SpannerDbType.Int64 },
{ "currency_from", SpannerDbType.String },
{ "currency_to", SpannerDbType.String },
{ "provider_name", SpannerDbType.String },
{ "expiration_time", SpannerDbType.Timestamp },
{ "original_published_time", SpannerDbType.Timestamp },
{ "time_of_receipt", SpannerDbType.Timestamp },
{ "value", SpannerDbType.Int64 }
});
await Task.WhenAll(historyItems.Select(hi =>
{
cmd.Parameters["id"].Value = hi.Id;
cmd.Parameters["currency_from"].Value = hi.CurrencyFrom;
cmd.Parameters["currency_to"].Value = hi.CurrencyTo;
cmd.Parameters["provider_name"].Value = hi.ProviderName;
cmd.Parameters["expiration_time"].Value = hi.ExpirationTime;
cmd.Parameters["original_published_time"].Value = hi.OriginalPublishedTime;
cmd.Parameters["time_of_receipt"].Value = hi.TimeOfReceipt;
cmd.Parameters["value"].Value = hi.Value;
return(cmd.ExecuteNonQueryAsync());
}));
}
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 <int> WriteDataWithTimestampAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
List <Performance> performances = new List <Performance>
{
new Performance {
SingerId = 1, VenueId = 4, EventDate = DateTime.Parse("2017-10-05"), Revenue = 11000
},
new Performance {
SingerId = 1, VenueId = 19, EventDate = DateTime.Parse("2017-11-02"), Revenue = 15000
},
new Performance {
SingerId = 2, VenueId = 42, EventDate = DateTime.Parse("2017-12-23"), Revenue = 7000
},
};
// Create connection to Cloud Spanner.
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
// Insert rows into the Performances table.
var rowCountAarray = await Task.WhenAll(performances.Select(performance =>
{
var cmd = connection.CreateInsertCommand("Performances", new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64, performance.SingerId },
{ "VenueId", SpannerDbType.Int64, performance.VenueId },
{ "EventDate", SpannerDbType.Date, performance.EventDate },
{ "Revenue", SpannerDbType.Int64, performance.Revenue },
{ "LastUpdateTime", SpannerDbType.Timestamp, SpannerParameter.CommitTimestamp },
});
return(cmd.ExecuteNonQueryAsync());
}));
return(rowCountAarray.Sum());
}
public async Task CommandTimeout()
{
await _testFixture.EnsureTestDatabaseAsync();
var values = new SpannerParameterCollection
{
{ "StringValue", SpannerDbType.String, "abc" },
{ "K", SpannerDbType.String, _lastKey = UniqueString() }
};
var e = await Assert.ThrowsAsync <SpannerException>(
async() =>
{
using (var connection = new SpannerConnection($"{_testFixture.ConnectionString};{nameof(SpannerSettings.AllowImmediateTimeouts)}=true"))
{
var cmd = connection.CreateInsertCommand("T", values);
cmd.CommandTimeout = 0;
await cmd.ExecuteNonQueryAsync();
}
}).ConfigureAwait(false);
Assert.Equal(ErrorCode.DeadlineExceeded, e.ErrorCode);
Assert.False(e.IsTransientSpannerFault());
}
public async Task <IActionResult> Create([FromBody] Offering item)
{
// Insert a new item.
using (var connection =
new SpannerConnection(
$"Data Source=projects/{_myProject}/instances/{_spannerInstanceId}/databases/{_myDatabase}"))
{
await connection.OpenAsync();
item.OfferingId = Guid.NewGuid().ToString("N");
var cmd = connection.CreateInsertCommand(
"Offerings", new SpannerParameterCollection
{
{ "OfferingId", SpannerDbType.String, item.OfferingId },
{ "OfferingName", SpannerDbType.String, item.OfferingName },
{ "CourseDurationInYears", SpannerDbType.Int64, item.CourseDurationInYears },
{ "TotalCourseFee", SpannerDbType.Int64, item.TotalCourseFee }
});
await cmd.ExecuteNonQueryAsync();
}
return(CreatedAtRoute("GetOfferingById", new { OfferingId = item.OfferingId }, item));
}
static void InsertRepeatedly(string table)
{
// Execute 10001 tests so that we log the end as well.
for (int i = 0; i < 10001; i++)
{
if (i % 1000 == 0)
{
Log($"Test {i}");
}
try
{
using (var connection = new SpannerConnection(ConnectionString))
{
var cmd = connection.CreateInsertCommand(table);
cmd.Parameters.Add("K", SpannerDbType.String, Guid.NewGuid().ToString());
cmd.ExecuteNonQuery();
}
}
catch (SpannerException e) when(e.ErrorCode == ErrorCode.Aborted)
{
Log($"Aborted transaction at test {i}");
}
}
}
public async Task <IActionResult> Create([FromBody] Book item)
{
// Insert a new item.
using (var connection =
new SpannerConnection(
$"Data Source=projects/{_myProject}/instances/myspanner/databases/books"))
{
await connection.OpenAsync();
item.Id = Guid.NewGuid().ToString("N");
var cmd = connection.CreateInsertCommand(
"bookTable", new SpannerParameterCollection
{
{ "ID", SpannerDbType.String, item.Id },
{ "Title", SpannerDbType.String, item.Title },
{ "Author", SpannerDbType.String, item.Author },
{ "PublishDate", SpannerDbType.Date, item.PublishDate }
});
await cmd.ExecuteNonQueryAsync();
}
return(CreatedAtRoute("GetBookById", new { id = item.Id }, item));
}
public async Task InsertDataAsync(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 = 1, FirstName = "Marc", LastName = "Richards"
},
new Singer {
SingerId = 2, FirstName = "Catalina", LastName = "Smith"
},
new Singer {
SingerId = 3, FirstName = "Alice", LastName = "Trentor"
},
new Singer {
SingerId = 4, FirstName = "Lea", LastName = "Martin"
},
new Singer {
SingerId = 5, FirstName = "David", LastName = "Lomond"
},
};
List <Album> albums = new List <Album>
{
new Album {
SingerId = 1, AlbumId = 1, AlbumTitle = "Total Junk"
},
new Album {
SingerId = 1, AlbumId = 2, AlbumTitle = "Go, Go, Go"
},
new Album {
SingerId = 2, AlbumId = 1, AlbumTitle = "Green"
},
new Album {
SingerId = 2, AlbumId = 2, AlbumTitle = "Forever Hold your Peace"
},
new Album {
SingerId = 2, AlbumId = 3, AlbumTitle = "Terrified"
},
};
// Create connection to Cloud Spanner.
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
await connection.RunWithRetriableTransactionAsync(async transaction =>
{
await Task.WhenAll(singers.Select(singer =>
{
// Insert rows into the Singers table.
using var cmd = connection.CreateInsertCommand("Singers", new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64, singer.SingerId },
{ "FirstName", SpannerDbType.String, singer.FirstName },
{ "LastName", SpannerDbType.String, singer.LastName }
});
cmd.Transaction = transaction;
return(cmd.ExecuteNonQueryAsync());
}));
await Task.WhenAll(albums.Select(album =>
{
// Insert rows into the Albums table.
using var cmd = connection.CreateInsertCommand("Albums", new SpannerParameterCollection
{
{ "SingerId", SpannerDbType.Int64, album.SingerId },
{ "AlbumId", SpannerDbType.Int64, album.AlbumId },
{ "AlbumTitle", SpannerDbType.String, album.AlbumTitle }
});
cmd.Transaction = transaction;
return(cmd.ExecuteNonQueryAsync());
}));
});
Console.WriteLine("Data inserted.");
}
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));
}