public void CommandIncludesRequestAndTransactionTag()
{
var requestTag1 = "request-tag-1";
var requestTag2 = "request-tag-2";
var transactionTag = "transaction-tag-1";
Mock <SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupExecuteStreamingSql()
.SetupCommitAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
SpannerTransaction transaction = connection.BeginTransaction();
transaction.Tag = transactionTag;
var command1 = connection.CreateSelectCommand("SELECT * FROM FOO");
command1.Transaction = transaction;
command1.Tag = requestTag1;
using (var reader = command1.ExecuteReader())
{
Assert.True(reader.HasRows);
}
var command2 = connection.CreateSelectCommand("SELECT * FROM FOO");
command2.Transaction = transaction;
command2.Tag = requestTag2;
using (var reader = command2.ExecuteReader())
{
Assert.True(reader.HasRows);
}
// Execute a statement without a request tag on the same transaction.
var command3 = connection.CreateSelectCommand("SELECT * FROM FOO");
command3.Transaction = transaction;
using (var reader = command3.ExecuteReader())
{
Assert.True(reader.HasRows);
}
transaction.Commit();
spannerClientMock.Verify(client => client.ExecuteStreamingSql(
It.Is <ExecuteSqlRequest>(request => request.RequestOptions.RequestTag == requestTag1 && request.RequestOptions.TransactionTag == transactionTag),
It.IsAny <CallSettings>()), Times.Once());
spannerClientMock.Verify(client => client.ExecuteStreamingSql(
It.Is <ExecuteSqlRequest>(request => request.RequestOptions.RequestTag == requestTag2 && request.RequestOptions.TransactionTag == transactionTag),
It.IsAny <CallSettings>()), Times.Once());
spannerClientMock.Verify(client => client.ExecuteStreamingSql(
It.Is <ExecuteSqlRequest>(request => request.RequestOptions.RequestTag == "" && request.RequestOptions.TransactionTag == transactionTag),
It.IsAny <CallSettings>()), Times.Once());
spannerClientMock.Verify(client => client.CommitAsync(
It.Is <CommitRequest>(request => request.RequestOptions.RequestTag == "" && request.RequestOptions.TransactionTag == transactionTag),
It.IsAny <CallSettings>()), Times.Once());
}
public async Task BadDbName()
{
string connectionString = $"Data Source=projects/{_testFixture.TestProjectName}/instances/"
+ $"{_testFixture.TestInstanceName}/databases/badjuju";
// ReSharper disable once RedundantAssignment
int rowsRead = -1;
var exceptionCaught = false;
try
{
using (var connection = new SpannerConnection(connectionString))
{
var cmd = connection.CreateSelectCommand(
$"SELECT * FROM {_testFixture.TestTable} WHERE Key = 'k1'");
using (var reader = await cmd.ExecuteReaderAsync())
{
rowsRead = 0;
while (await reader.ReadAsync())
{
rowsRead++;
}
}
}
}
catch (SpannerException e)
{
exceptionCaught = true;
Assert.Equal(ErrorCode.NotFound, e.ErrorCode);
Assert.False(e.IsTransientSpannerFault());
}
Assert.True(exceptionCaught);
Assert.Equal(-1, rowsRead);
}
public async Task <List <Venue> > QueryDataWithNumericParameterAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
using var cmd = connection.CreateSelectCommand(
"SELECT VenueId, Revenue FROM Venues WHERE Revenue < @maxRevenue",
new SpannerParameterCollection
{
{ "maxRevenue", SpannerDbType.Numeric, SpannerNumeric.Parse("100000") }
});
var venues = new List <Venue>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
venues.Add(new Venue
{
VenueId = reader.GetFieldValue <int>("VenueId"),
Revenue = reader.GetFieldValue <SpannerNumeric>("Revenue")
});
}
return(venues);
}
public void ClientCreatedWithEmulatorDetection()
{
Mock <SpannerClient> spannerClientMock = SetupExecuteStreamingSql();
var spannerClient = spannerClientMock.Object;
var sessionPoolOptions = new SessionPoolOptions
{
MaintenanceLoopDelay = TimeSpan.Zero
};
var sessionPoolManager = new SessionPoolManager(
sessionPoolOptions, spannerClient.Settings.Logger,
(_o, _s, _l) =>
{
Assert.True(_o.UsesEmulator);
return(Task.FromResult(spannerClient));
});
SpannerConnectionStringBuilder builder = new SpannerConnectionStringBuilder
{
DataSource = DatabaseName.Format(SpannerClientHelpers.ProjectId, SpannerClientHelpers.Instance, SpannerClientHelpers.Database),
SessionPoolManager = sessionPoolManager,
EmulatorDetection = EmulatorDetection.EmulatorOrProduction,
EnvironmentVariableProvider = key => key == "SPANNER_EMULATOR_HOST" ? "localhost" : null
};
var connection = new SpannerConnection(builder);
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
using (var reader = command.ExecuteReader())
{
// Do nothing.
}
}
public async Task <List <Album> > QueryDataWithIndexAsync(string projectId, string instanceId, string databaseId,
string startTitle, string endTitle)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
using var cmd = connection.CreateSelectCommand(
"SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
+ "{FORCE_INDEX=AlbumsByAlbumTitle} "
+ $"WHERE AlbumTitle >= @startTitle "
+ $"AND AlbumTitle < @endTitle",
new SpannerParameterCollection
{
{ "startTitle", SpannerDbType.String, startTitle },
{ "endTitle", SpannerDbType.String, endTitle }
});
var albums = new List <Album>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
albums.Add(new Album
{
AlbumId = reader.GetFieldValue <int>("AlbumId"),
AlbumTitle = reader.GetFieldValue <string>("AlbumTitle"),
MarketingBudget = reader.IsDBNull(reader.GetOrdinal("MarketingBudget")) ? 0 : reader.GetFieldValue <long>("MarketingBudget")
});
}
return(albums);
}
public async Task <List <Venue> > QueryWithTimestampAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
// Initialize a DateTime timestamp variable to use for querying.
DateTime exampleTimestamp = DateTime.UtcNow;
using var connection = new SpannerConnection(connectionString);
var cmd = connection.CreateSelectCommand("SELECT VenueId, VenueName, LastUpdateTime FROM Venues WHERE LastUpdateTime < @ExampleTimestamp");
cmd.Parameters.Add("ExampleTimestamp", SpannerDbType.Timestamp, exampleTimestamp);
var venues = new List <Venue>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
venues.Add(new Venue
{
VenueId = reader.GetFieldValue <int>("VenueId"),
VenueName = reader.GetFieldValue <string>("VenueName"),
LastUpdateTime = reader["LastUpdateTime"] != DBNull.Value ? reader.GetFieldValue <DateTime?>("LastUpdateTime") : null
});
}
return(venues);
}
public void RunWithRetryableTransactionWithTransactionTag()
{
var transactionTag = "retryable-tx-tag";
Mock <SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupExecuteStreamingSql()
.SetupCommitAsync_Fails(1, StatusCode.Aborted, exceptionRetryDelay: TimeSpan.FromMilliseconds(0))
.SetupRollbackAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
connection.Builder.SessionPoolManager.SpannerSettings.Scheduler = new NoOpScheduler();
connection.RunWithRetriableTransaction(tx =>
{
tx.Tag = transactionTag;
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
command.Transaction = tx;
command.Tag = null;
using (var reader = command.ExecuteReader())
{
Assert.True(reader.HasRows);
}
});
spannerClientMock.Verify(client => client.ExecuteStreamingSql(
It.Is <ExecuteSqlRequest>(request => request.RequestOptions.TransactionTag == transactionTag),
It.IsAny <CallSettings>()), Times.Exactly(2));
spannerClientMock.Verify(client => client.CommitAsync(
It.Is <CommitRequest>(request => request.RequestOptions.TransactionTag == transactionTag),
It.IsAny <CallSettings>()), Times.Exactly(2));
}
public void TransactionTagCannotBeSetAfterCommandExecution()
{
var transactionTag = "transaction-tag-1";
Mock <SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupExecuteStreamingSql()
.SetupCommitAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
SpannerTransaction transaction = connection.BeginTransaction();
// Execute a command on the transaction without a transaction tag.
var command1 = connection.CreateSelectCommand("SELECT * FROM FOO");
command1.Transaction = transaction;
using (var reader = command1.ExecuteReader())
{
Assert.True(reader.HasRows);
}
Assert.Throws <InvalidOperationException>(() => transaction.Tag = transactionTag);
transaction.Commit();
spannerClientMock.Verify(client => client.ExecuteStreamingSql(
It.Is <ExecuteSqlRequest>(request => request.RequestOptions.RequestTag == "" && request.RequestOptions.TransactionTag == ""),
It.IsAny <CallSettings>()), Times.Once());
spannerClientMock.Verify(client => client.CommitAsync(
It.Is <CommitRequest>(request => request.RequestOptions.RequestTag == "" && request.RequestOptions.TransactionTag == ""),
It.IsAny <CallSettings>()), Times.Once());
}
public async Task <List <int> > QueryDataWithStructAsync(string projectId, string instanceId, string databaseId)
{
// [START spanner_create_struct_with_data]
var nameStruct = new SpannerStruct
{
{ "FirstName", SpannerDbType.String, "Elena" },
{ "LastName", SpannerDbType.String, "Campbell" },
};
// [END spanner_create_struct_with_data]
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
var singerIds = new List <int>();
using var connection = new SpannerConnection(connectionString);
using var cmd = connection.CreateSelectCommand(
"SELECT SingerId FROM Singers "
+ "WHERE STRUCT<FirstName STRING, LastName STRING>"
+ "(FirstName, LastName) = @name");
cmd.Parameters.Add("name", nameStruct.GetSpannerDbType(), nameStruct);
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
singerIds.Add(reader.GetFieldValue <int>("SingerId"));
}
return(singerIds);
}
public void CommandHasOptimizerVersionSetOnCommand()
{
Mock<SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupExecuteStreamingSql();
var cmdOptimizerVersion = "3";
// Optimizer version set at a command level has higher precedence
// than version set through the connection or the environment
// variable.
const string envOptimizerVersion = "2";
RunActionWithEnvOptimizerVersion(() =>
{
const string connOptimizerVersion = "1";
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
var queryOptions = QueryOptions.Empty.WithOptimizerVersion(connOptimizerVersion);
connection.QueryOptions = queryOptions;
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
command.QueryOptions = QueryOptions.Empty.WithOptimizerVersion(cmdOptimizerVersion);
using (var reader = command.ExecuteReader())
{
Assert.True(reader.HasRows);
}
}, envOptimizerVersion);
spannerClientMock.Verify(client => client.ExecuteStreamingSql(
It.Is<ExecuteSqlRequest>(request => request.QueryOptions.OptimizerVersion == cmdOptimizerVersion),
It.IsAny<CallSettings>()), Times.Once());
}
public async Task <string> GetDatabaseDefaultLeaderFromInformationSchemaAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
var cmd =
connection.CreateSelectCommand(
@"SELECT
s.OPTION_NAME,
s.OPTION_VALUE
FROM
INFORMATION_SCHEMA.DATABASE_OPTIONS s
WHERE
s.OPTION_NAME = 'default_leader'");
var defaultLeader = string.Empty;
Console.WriteLine($"Default leader for {databaseId}:");
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
defaultLeader = reader.GetFieldValue <string>("OPTION_VALUE");
Console.WriteLine(defaultLeader);
}
return(defaultLeader);
}
public async Task <List <Venue> > QueryWithIntAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
// Initialize a long variable to use for querying.
long exampleValue = 3000;
using var connection = new SpannerConnection(connectionString);
var cmd = connection.CreateSelectCommand("SELECT VenueId, VenueName, Capacity FROM Venues WHERE Capacity >= @ExampleValue");
cmd.Parameters.Add("ExampleValue", SpannerDbType.Int64, exampleValue);
var venues = new List <Venue>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
venues.Add(new Venue
{
VenueId = reader.GetFieldValue <int>("VenueId"),
VenueName = reader.GetFieldValue <string>("VenueName"),
Capacity = reader.GetFieldValue <int>("Capacity")
});
}
return(venues);
}
public async Task <List <Venue> > QueryWithArrayAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
// Initialize a list of dates to use for querying.
var exampleList = new List <DateTime>
{
DateTime.Parse("2020-10-01"),
DateTime.Parse("2020-11-01")
};
using var connection = new SpannerConnection(connectionString);
var cmd = connection.CreateSelectCommand(
"SELECT VenueId, VenueName, AvailableDate FROM Venues v, "
+ "UNNEST(v.AvailableDates) as AvailableDate "
+ "WHERE AvailableDate in UNNEST(@ExampleArray)");
cmd.Parameters.Add("ExampleArray", SpannerDbType.ArrayOf(SpannerDbType.Date), exampleList);
var venues = new List <Venue>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
venues.Add(new Venue
{
VenueId = reader.GetFieldValue <int>("VenueId"),
VenueName = reader.GetFieldValue <string>("VenueName"),
AvailableDates = new List <DateTime> {
reader.GetFieldValue <DateTime>("AvailableDate")
}
});
}
return(venues);
}
public async Task <List <Venue> > QueryWithFloatAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
// Initialize a float variable to use for querying.
float exampleFloat = 0.8f;
using var connection = new SpannerConnection(connectionString);
var cmd = connection.CreateSelectCommand("SELECT VenueId, VenueName, PopularityScore FROM Venues WHERE PopularityScore > @ExampleFloat");
cmd.Parameters.Add("ExampleFloat", SpannerDbType.Float64, exampleFloat);
var venues = new List <Venue>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
venues.Add(new Venue
{
VenueId = reader.GetFieldValue <int>("VenueId"),
VenueName = reader.GetFieldValue <string>("VenueName"),
PopularityScore = reader.GetFieldValue <float>("PopularityScore")
});
}
return(venues);
}
public async Task <List <Album> > CreateConnectionWithQueryOptionsAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString)
{
// Set query options on the connection.
QueryOptions = QueryOptions.Empty
.WithOptimizerVersion("1")
// The list of available statistics packages for the database can
// be found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
// table.
.WithOptimizerStatisticsPackage("latest")
};
var albums = new List <Album>();
var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
albums.Add(new Album
{
SingerId = reader.GetFieldValue <int>("SingerId"),
AlbumId = reader.GetFieldValue <int>("AlbumId"),
AlbumTitle = reader.GetFieldValue <string>("AlbumTitle")
});
}
return(albums);
}
public void CommitPriorityCanBeSetAfterCommandExecution()
{
var priority = Priority.Medium;
Mock <SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupExecuteStreamingSql()
.SetupCommitAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
SpannerTransaction transaction = connection.BeginTransaction();
// Execute a command on the transaction.
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
command.Transaction = transaction;
using (var reader = command.ExecuteReader())
{
Assert.True(reader.HasRows);
}
// Verify that we can set the commit priority after a command has been executed.
transaction.CommitPriority = priority;
transaction.Commit();
spannerClientMock.Verify(client => client.CommitAsync(
It.Is <CommitRequest>(request => request.RequestOptions.Priority == PriorityConverter.ToProto(priority)),
It.IsAny <CallSettings>()), Times.Once());
}
public async Task <List <Album> > CreateConnectionWithQueryOptionsAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString)
{
// Set query options on the connection.
QueryOptions = QueryOptions.Empty
.WithOptimizerVersion("1")
.WithOptimizerStatisticsPackage("auto_20191128_14_47_22UTC")
};
var albums = new List <Album>();
var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
albums.Add(new Album
{
SingerId = reader.GetFieldValue <int>("SingerId"),
AlbumId = reader.GetFieldValue <int>("AlbumId"),
AlbumTitle = reader.GetFieldValue <string>("AlbumTitle")
});
}
return(albums);
}
public async Task <List <Venue> > QueryWithBoolAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
bool exampleBool = true;
using var connection = new SpannerConnection(connectionString);
var cmd = connection.CreateSelectCommand(
"SELECT VenueId, VenueName, OutdoorVenue FROM Venues "
+ "WHERE OutdoorVenue = @ExampleBool");
cmd.Parameters.Add("ExampleBool", SpannerDbType.Bool, exampleBool);
var venues = new List <Venue>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
venues.Add(new Venue
{
VenueId = reader.GetFieldValue <int>("VenueId"),
VenueName = reader.GetFieldValue <string>("VenueName"),
OutdoorVenue = reader.GetFieldValue <bool>("OutdoorVenue")
});
}
return(venues);
}
public void TagsCanBeSetToNull()
{
Mock <SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupExecuteStreamingSql()
.SetupCommitAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
SpannerTransaction transaction = connection.BeginTransaction();
transaction.Tag = null;
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
command.Transaction = transaction;
command.Tag = null;
using (var reader = command.ExecuteReader())
{
Assert.True(reader.HasRows);
}
transaction.Commit();
spannerClientMock.Verify(client => client.ExecuteStreamingSql(
It.Is <ExecuteSqlRequest>(request => request.RequestOptions.RequestTag == "" && request.RequestOptions.TransactionTag == ""),
It.IsAny <CallSettings>()), Times.Once());
spannerClientMock.Verify(client => client.CommitAsync(
It.Is <CommitRequest>(request => request.RequestOptions.RequestTag == "" && request.RequestOptions.TransactionTag == ""),
It.IsAny <CallSettings>()), Times.Once());
}
public async Task <List <Venue> > QueryDataWithJsonParameterAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
// If you are using .NET Core 3.1 or later, you can use System.Text.Json for serialization instead.
var jsonValue = JsonConvert.SerializeObject(new { rating = 9 });
// Get all venues with rating 9.
using var cmd = connection.CreateSelectCommand(
@"SELECT VenueId, VenueDetails
FROM Venues
WHERE JSON_VALUE(VenueDetails, '$.rating') = JSON_VALUE(@details, '$.rating')",
new SpannerParameterCollection
{
{ "details", SpannerDbType.Json, jsonValue }
});
var venues = new List <Venue>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
venues.Add(new Venue
{
VenueId = reader.GetFieldValue <int>("VenueId"),
VenueDetails = reader.GetFieldValue <string>("VenueDetails")
});
}
return(venues);
}
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...");
}
static async Task MainAsync()
{
string projectId = "YOUR-GOOGLE-PROJECT-ID";
string instanceId = "my-instance";
string databaseId = "my-database";
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}/"
+ $"databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
// Execute a simple SQL statement.
var cmd = connection.CreateSelectCommand(
@"SELECT ""Hello World"" as test");
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine(
reader.GetFieldValue <string>("test"));
}
}
}
}
public async Task <List <Album> > RequestTagAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
using var cmd = connection.CreateSelectCommand(
$"SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
// Sets the request tag to "app=concert,env=dev,action=select".
// This request tag will only be set on this request.
cmd.Tag = "app=concert,env=dev,action=select";
var albums = new List <Album>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
var album = new Album
{
SingerId = reader.GetFieldValue <int>("SingerId"),
AlbumId = reader.GetFieldValue <int>("AlbumId"),
AlbumTitle = reader.GetFieldValue <string>("AlbumTitle")
};
albums.Add(album);
Console.WriteLine($"SingerId: {album.SingerId}, AlbumId: {album.AlbumId}, AlbumTitle: {album.AlbumTitle}");
}
return(albums);
}
public void CommandHasConnectionQueryOptions()
{
const string connOptimizerVersion = "1";
const string connOptimizerStatisticsPackage = "stats_package_1";
Mock <SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupExecuteStreamingSql();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
connection.QueryOptions = QueryOptions.Empty
.WithOptimizerVersion(connOptimizerVersion)
.WithOptimizerStatisticsPackage(connOptimizerStatisticsPackage);
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
using (var reader = command.ExecuteReader())
{
Assert.True(reader.HasRows);
}
spannerClientMock.Verify(client => client.ExecuteStreamingSql(
It.Is <ExecuteSqlRequest>(request =>
request.QueryOptions.OptimizerVersion == connOptimizerVersion &&
request.QueryOptions.OptimizerStatisticsPackage == connOptimizerStatisticsPackage),
It.IsAny <CallSettings>()), Times.Once());
}
public void CommandHasOptimizerVersionSetOnCommand()
{
const string envOptimizerVersion = "2";
RunActionWithEnvOptimizerVersion(() =>
{
var cmdOptimizerVersion = "3";
// Optimizer version set at a command level has higher precedence
// than version set through the connection or the environment
// variable.
Mock <SpannerClient> spannerClientMock = SetupExecuteStreamingSql(cmdOptimizerVersion);
const string connOptimizerVersion = "1";
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
var queryOptions = QueryOptions.Empty.WithOptimizerVersion(connOptimizerVersion);
connection.QueryOptions = queryOptions;
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
command.QueryOptions = QueryOptions.Empty.WithOptimizerVersion(cmdOptimizerVersion);
using (var reader = command.ExecuteReader())
{
// Do nothing.
}
}, envOptimizerVersion);
}
public void CommandPriorityDefaultsToUnspecified()
{
var connection = new SpannerConnection("Data Source=projects/p/instances/i/databases/d");
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
Assert.Equal(Priority.Unspecified, command.Priority);
}
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 void CommitIncludesPriority()
{
var commitPriority = Priority.Medium;
var commandPriority = Priority.High;
Mock <SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync()
.SetupExecuteStreamingSql()
.SetupCommitAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
SpannerTransaction transaction = connection.BeginTransaction();
transaction.CommitPriority = commitPriority;
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
command.Transaction = transaction;
command.Priority = commandPriority;
using (var reader = command.ExecuteReader())
{
Assert.True(reader.HasRows);
}
transaction.Commit();
spannerClientMock.Verify(client => client.ExecuteStreamingSql(
It.Is <ExecuteSqlRequest>(request => request.RequestOptions.Priority == PriorityConverter.ToProto(commandPriority)),
It.IsAny <CallSettings>()), Times.Once());
spannerClientMock.Verify(client => client.CommitAsync(
It.Is <CommitRequest>(request => request.RequestOptions.Priority == PriorityConverter.ToProto(commitPriority)),
It.IsAny <CallSettings>()), Times.Once());
}
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 <List <Venue> > QueryWithDateAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
// Initialize a Date variable to use for querying.
DateTime exampleDate = new DateTime(2019, 01, 01);
using var connection = new SpannerConnection(connectionString);
var cmd = connection.CreateSelectCommand("SELECT VenueId, VenueName, LastContactDate FROM Venues WHERE LastContactDate < @ExampleDate");
cmd.Parameters.Add("ExampleDate", SpannerDbType.Date, exampleDate);
var venues = new List <Venue>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
venues.Add(new Venue
{
VenueId = reader.GetFieldValue <int>("VenueId"),
VenueName = reader.GetFieldValue <string>("VenueName"),
LastContactDate = reader.GetFieldValue <DateTime>("LastContactDate")
});
}
return(venues);
}
