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); }