Google.Cloud.Spanner.Data - Class SpannerConnection (5.0.0-beta05)

string connectionString = "Data Source=projects/my-project/instances/my-instance/databases/my-db";
SpannerConnection connection = new SpannerConnection(connectionString);
Console.WriteLine(connection.Project);
Console.WriteLine(connection.SpannerInstance);
Console.WriteLine(connection.Database);

using (SpannerConnection connection = new SpannerConnection(connectionString))
{
    SpannerCommand createDbCmd = connection.CreateDdlCommand($"CREATE DATABASE {databaseName}");
    await createDbCmd.ExecuteNonQueryAsync();

    SpannerCommand createTableCmd = connection.CreateDdlCommand(
        @"CREATE TABLE TestTable (
                                Key                STRING(MAX) NOT NULL,
                                StringValue        STRING(MAX),
                                Int64Value         INT64,
                              ) PRIMARY KEY (Key)");
    await createTableCmd.ExecuteNonQueryAsync();
}

using (SpannerConnection connection = new SpannerConnection(connectionString))
{
    await connection.OpenAsync();

    // If the transaction is aborted, RunWithRetriableTransactionAsync will
    // retry the whole unit of work with a fresh transaction each time.
    // Please be aware that the whole unit of work needs to be prepared
    // to be called more than once.
    await connection.RunWithRetriableTransactionAsync(async transaction =>
    {
        // Read the first two keys in the database.
        List<string> keys = new List<string>();
        SpannerCommand selectCmd = connection.CreateSelectCommand("SELECT * FROM TestTable");
        selectCmd.Transaction = transaction;
        using (SpannerDataReader reader = await selectCmd.ExecuteReaderAsync())
        {
            while (keys.Count < 3 && await reader.ReadAsync())
            {
                keys.Add(reader.GetFieldValue<string>("Key"));
            }
        }

        // Update the Int64Value of keys[0]
        // Include the primary key and update columns.
        SpannerCommand updateCmd = connection.CreateUpdateCommand("TestTable");
        updateCmd.Parameters.Add("Key", SpannerDbType.String, keys[0]);
        updateCmd.Parameters.Add("Int64Value", SpannerDbType.Int64, 0L);
        updateCmd.Transaction = transaction;
        await updateCmd.ExecuteNonQueryAsync();

        // Delete row for keys[1]
        SpannerCommand deleteCmd = connection.CreateDeleteCommand("TestTable");
        deleteCmd.Parameters.Add("Key", SpannerDbType.String, keys[1]);
        deleteCmd.Transaction = transaction;
        await deleteCmd.ExecuteNonQueryAsync();
    });

using (SpannerConnection connection = new SpannerConnection(connectionString))
{
    await connection.OpenAsync();

    // If the transaction is aborted, RunWithRetriableTransactionAsync will
    // retry the whole unit of work with a fresh transaction each time.
    // Please be aware that the whole unit of work needs to be prepared
    // to be called more than once.
    await connection.RunWithRetriableTransactionAsync(async transaction =>
    {
        // Read the first two keys in the database.
        List<string> keys = new List<string>();
        SpannerCommand selectCmd = connection.CreateSelectCommand("SELECT * FROM TestTable");
        selectCmd.Transaction = transaction;
        using (SpannerDataReader reader = await selectCmd.ExecuteReaderAsync())
        {
            while (keys.Count < 3 && await reader.ReadAsync())
            {
                keys.Add(reader.GetFieldValue<string>("Key"));
            }
        }

        // Update the Int64Value of keys[0]
        // Include the primary key and update columns.
        SpannerCommand updateCmd = connection.CreateUpdateCommand("TestTable");
        updateCmd.Parameters.Add("Key", SpannerDbType.String, keys[0]);
        updateCmd.Parameters.Add("Int64Value", SpannerDbType.Int64, 0L);
        updateCmd.Transaction = transaction;
        await updateCmd.ExecuteNonQueryAsync();

        // Delete row for keys[1]
        SpannerCommand deleteCmd = connection.CreateDeleteCommand("TestTable");
        deleteCmd.Parameters.Add("Key", SpannerDbType.String, keys[1]);
        deleteCmd.Transaction = transaction;
        await deleteCmd.ExecuteNonQueryAsync();
    });

using (SpannerConnection connection = new SpannerConnection(connectionString))
{
    await connection.OpenAsync();

    // If the transaction is aborted, RunWithRetriableTransactionAsync will
    // retry the whole unit of work with a fresh transaction each time.
    // Please be aware that the whole unit of work needs to be prepared
    // to be called more than once.
    await connection.RunWithRetriableTransactionAsync(async transaction =>
    {
        // Read the first two keys in the database.
        List<string> keys = new List<string>();
        SpannerCommand selectCmd = connection.CreateSelectCommand("SELECT * FROM TestTable");
        selectCmd.Transaction = transaction;
        using (SpannerDataReader reader = await selectCmd.ExecuteReaderAsync())
        {
            while (keys.Count < 3 && await reader.ReadAsync())
            {
                keys.Add(reader.GetFieldValue<string>("Key"));
            }
        }

        // Update the Int64Value of keys[0]
        // Include the primary key and update columns.
        SpannerCommand updateCmd = connection.CreateUpdateCommand("TestTable");
        updateCmd.Parameters.Add("Key", SpannerDbType.String, keys[0]);
        updateCmd.Parameters.Add("Int64Value", SpannerDbType.Int64, 0L);
        updateCmd.Transaction = transaction;
        await updateCmd.ExecuteNonQueryAsync();

        // Delete row for keys[1]
        SpannerCommand deleteCmd = connection.CreateDeleteCommand("TestTable");
        deleteCmd.Parameters.Add("Key", SpannerDbType.String, keys[1]);
        deleteCmd.Transaction = transaction;
        await deleteCmd.ExecuteNonQueryAsync();
    });

using (SpannerConnection connection = new SpannerConnection(connectionString))
{
    SessionPool.SessionPoolSegmentStatistics stats = connection.GetSessionPoolSegmentStatistics();
    if (stats is null)
    {
        Console.WriteLine("No session pool for this connection string yet");
    }
    else
    {
        // Access individual properties...
        Console.WriteLine($"Database name: {stats.DatabaseName}");
        Console.WriteLine($"Active sessions: {stats.ActiveSessionCount}");
        Console.WriteLine($"Pooled sessions: {stats.PoolCount}");
        // ... or just use the overridden ToString method to log all the statistics in one go:
        Console.WriteLine(stats);
    }
}

using (SpannerConnection connection = new SpannerConnection(connectionString))
{
    await connection.OpenAsync();

    // If the transaction is aborted, RunWithRetriableTransactionAsync will
    // retry the whole unit of work with a fresh transaction each time.
    await connection.RunWithRetriableTransactionAsync(async transaction =>
    {
        SpannerCommand cmd = connection.CreateInsertCommand("TestTable");
        SpannerParameter keyParameter = cmd.Parameters.Add("Key", SpannerDbType.String);
        SpannerParameter stringValueParameter = cmd.Parameters.Add("StringValue", SpannerDbType.String);
        SpannerParameter int64ValueParameter = cmd.Parameters.Add("Int64Value", SpannerDbType.Int64);
        cmd.Transaction = transaction;

        // This executes 5 distinct insert commands using the retriable transaction.
        // The mutations will be effective once the transaction has committed successfully.
        for (int i = 0; i < 5; i++)
        {
            keyParameter.Value = Guid.NewGuid().ToString("N");
            stringValueParameter.Value = $"StringValue{i}";
            int64ValueParameter.Value = i;
            int rowsAffected = await cmd.ExecuteNonQueryAsync();
            Console.WriteLine($"{rowsAffected} rows written...");
        }
    });
}

using (SpannerConnection connection = new SpannerConnection(connectionString))
{
    await connection.OpenAsync();

    // If the transaction is aborted, RunWithRetriableTransactionAsync will
    // retry the whole unit of work with a fresh transaction each time.
    await connection.RunWithRetriableTransactionAsync(async transaction =>
    {
        SpannerCommand cmd = connection.CreateInsertCommand("TestTable");
        SpannerParameter keyParameter = cmd.Parameters.Add("Key", SpannerDbType.String);
        SpannerParameter stringValueParameter = cmd.Parameters.Add("StringValue", SpannerDbType.String);
        SpannerParameter int64ValueParameter = cmd.Parameters.Add("Int64Value", SpannerDbType.Int64);
        cmd.Transaction = transaction;

        // This executes 5 distinct insert commands using the retriable transaction.
        // The mutations will be effective once the transaction has committed successfully.
        for (int i = 0; i < 5; i++)
        {
            keyParameter.Value = Guid.NewGuid().ToString("N");
            stringValueParameter.Value = $"StringValue{i}";
            int64ValueParameter.Value = i;
            int rowsAffected = await cmd.ExecuteNonQueryAsync();
            Console.WriteLine($"{rowsAffected} rows written...");
        }
    });
}

// When your application is shutting down. Note that any pending or future requests
// for sessions will fail.
using (SpannerConnection connection = new SpannerConnection(connectionString))
{
    await connection.ShutdownSessionPoolAsync();
}

// This would usually be executed during application start-up, before any Spanner
// operations are performed. It can be used at any time, however. It is purely passive:
// it doesn't modify the session pool or trigger any other actions.
using (SpannerConnection connection = new SpannerConnection(connectionString))
{
    await connection.WhenSessionPoolReady();
}