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 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 ReadOnlyTxSelect()
{
string connectionString = $"Data Source=projects/p1/instances/i1/databases/d1;Host={_fixture.Host};Port={_fixture.Port}";
using var connection = new SpannerConnection(connectionString, ChannelCredentials.Insecure);
using var transaction = await connection.BeginReadOnlyTransactionAsync();
SpannerCommand cmd = connection.CreateSelectCommand("SELECT 1");
cmd.Transaction = transaction;
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
Assert.Equal(1, reader.GetInt64(0));
}
}
public async Task <List <Album> > QueryDataWithTransactionCoreAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
var albums = new List <Album>();
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
// Open a new read only transaction.
using var transaction = await connection.BeginReadOnlyTransactionAsync();
using var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
cmd.Transaction = transaction;
// Read #1.
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine("SingerId : " + reader.GetFieldValue <string>("SingerId")
+ " AlbumId : " + reader.GetFieldValue <string>("AlbumId")
+ " AlbumTitle : " + reader.GetFieldValue <string>("AlbumTitle"));
}
}
// Read #2. Even if changes occur in-between the reads,
// the transaction ensures that Read #1 and Read #2
// return the same data.
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
albums.Add(new Album
{
AlbumId = reader.GetFieldValue <int>("AlbumId"),
SingerId = reader.GetFieldValue <int>("SingerId"),
AlbumTitle = reader.GetFieldValue <string>("AlbumTitle")
});
}
}
Console.WriteLine("Transaction complete.");
return(albums);
}
public async Task <(int RowsRead, int Partitions)> BatchReadRecordsAsync(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 transaction = await connection.BeginReadOnlyTransactionAsync();
transaction.DisposeBehavior = DisposeBehavior.CloseResources;
using var cmd = connection.CreateSelectCommand("SELECT SingerId, FirstName, LastName FROM Singers");
cmd.Transaction = transaction;
var partitions = await cmd.GetReaderPartitionsAsync();
var transactionId = transaction.TransactionId;
await Task.WhenAll(partitions.Select(x => DistributedReadWorkerAsync(x, transactionId)));
Console.WriteLine($"Done reading! Total rows read: {_rowsRead:N0} with {_partitionCount} partition(s)");
return(RowsRead : _rowsRead, Partitions : _partitionCount);
}