Class SpannerConnection (3.6.0)

public sealed class SpannerConnection : DbConnection, IComponent, IDbConnection, IDisposable

Namespace

Assembly

Google.Cloud.Spanner.Data.dll

Constructors

SpannerConnection()

public SpannerConnection()

SpannerConnection(SpannerConnectionStringBuilder)

public SpannerConnection(SpannerConnectionStringBuilder connectionStringBuilder)

SpannerConnection(String, ChannelCredentials)

public SpannerConnection(string connectionString, ChannelCredentials credentials = null)

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

Properties

ConnectionString

public override string ConnectionString { get; set; }

Database

public override string Database { get; }

DataSource

public override string DataSource { get; }

DbProviderFactory

protected override DbProviderFactory DbProviderFactory { get; }

EnlistInTransaction

public bool EnlistInTransaction { get; set; }

LogCommitStats

public bool LogCommitStats { get; }

Project

public string Project { get; }

QueryOptions

public QueryOptions QueryOptions { get; set; }

ServerVersion

public override string ServerVersion { get; }

SpannerInstance

public string SpannerInstance { get; }

State

public override ConnectionState State { get; }

Methods

BeginDbTransaction(IsolationLevel)

protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)

BeginReadOnlyTransaction()

public SpannerTransaction BeginReadOnlyTransaction()

BeginReadOnlyTransaction(TimestampBound)

public SpannerTransaction BeginReadOnlyTransaction(TimestampBound targetReadTimestamp)

BeginReadOnlyTransaction(TransactionId)

public SpannerTransaction BeginReadOnlyTransaction(TransactionId transactionId)

BeginReadOnlyTransactionAsync(TimestampBound, CancellationToken)

public Task<SpannerTransaction> BeginReadOnlyTransactionAsync(TimestampBound targetReadTimestamp, CancellationToken cancellationToken = default(CancellationToken))

BeginReadOnlyTransactionAsync(CancellationToken)

public Task<SpannerTransaction> BeginReadOnlyTransactionAsync(CancellationToken cancellationToken = default(CancellationToken))

BeginTransaction()

public SpannerTransaction BeginTransaction()

BeginTransactionAsync(CancellationToken)

public Task<SpannerTransaction> BeginTransactionAsync(CancellationToken cancellationToken = default(CancellationToken))

ChangeDatabase(String)

public override void ChangeDatabase(string newDataSource)

Close()

public override void Close()

CreateBatchDmlCommand()

public SpannerBatchCommand CreateBatchDmlCommand()

CreateCommandWithPartition(CommandPartition, SpannerTransaction)

public SpannerCommand CreateCommandWithPartition(CommandPartition partition, SpannerTransaction transaction)

CreateDbCommand()

protected override DbCommand CreateDbCommand()

CreateDdlCommand(String, String[])

public SpannerCommand CreateDdlCommand(string ddlStatement, params string[] extraDdlStatements)

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

CreateDeleteCommand(String, SpannerParameterCollection)

public SpannerCommand CreateDeleteCommand(string databaseTable, SpannerParameterCollection primaryKeys = null)

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");
        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);
        await updateCmd.ExecuteNonQueryAsync();

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

CreateDmlCommand(String, SpannerParameterCollection)

public SpannerCommand CreateDmlCommand(string dmlStatement, SpannerParameterCollection dmlParameters = null)

CreateInsertCommand(String, SpannerParameterCollection)

public SpannerCommand CreateInsertCommand(string databaseTable, SpannerParameterCollection insertedColumns = null)

CreateInsertOrUpdateCommand(String, SpannerParameterCollection)

public SpannerCommand CreateInsertOrUpdateCommand(string databaseTable, SpannerParameterCollection insertUpdateColumns = null)

CreateSelectCommand(String, SpannerParameterCollection)

public SpannerCommand CreateSelectCommand(string sqlQueryStatement, SpannerParameterCollection selectParameters = null)

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");
        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);
        await updateCmd.ExecuteNonQueryAsync();

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

CreateUpdateCommand(String, SpannerParameterCollection)

public SpannerCommand CreateUpdateCommand(string databaseTable, SpannerParameterCollection updateColumns = null)

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");
        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);
        await updateCmd.ExecuteNonQueryAsync();

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

Dispose(Boolean)

protected override void Dispose(bool disposing)

EnlistTransaction(Transaction)

public override void EnlistTransaction(Transaction transaction)

GetSessionPoolDatabaseStatistics()

public SessionPool.DatabaseStatistics GetSessionPoolDatabaseStatistics()

using (SpannerConnection connection = new SpannerConnection(connectionString))
{
    SessionPool.DatabaseStatistics stats = connection.GetSessionPoolDatabaseStatistics();
    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 read-only sessions: {stats.ReadPoolCount}");
        Console.WriteLine($"Pooled read-write sessions: {stats.ReadWritePoolCount}");
        // ... or just use the overridden ToString method to log all the statistics in one go:
        Console.WriteLine(stats);
    }
}

Open()

public override void Open()

OpenAsReadOnly(TimestampBound)

public void OpenAsReadOnly(TimestampBound timestampBound = null)

OpenAsReadOnly(TransactionId)

public void OpenAsReadOnly(TransactionId transactionId)

OpenAsReadOnlyAsync(TimestampBound, CancellationToken)

public Task OpenAsReadOnlyAsync(TimestampBound timestampBound = null, CancellationToken cancellationToken = default(CancellationToken))

OpenAsync(CancellationToken)

public override Task OpenAsync(CancellationToken cancellationToken)

RunWithRetriableTransaction(Action<SpannerTransaction>)

public void RunWithRetriableTransaction(Action<SpannerTransaction> work)

RunWithRetriableTransaction<TResult>(Func<SpannerTransaction, TResult>)

public TResult RunWithRetriableTransaction<TResult>(Func<SpannerTransaction, TResult> work)

RunWithRetriableTransactionAsync(Func<SpannerTransaction, Task>, CancellationToken)

public Task RunWithRetriableTransactionAsync(Func<SpannerTransaction, Task> asyncWork, CancellationToken cancellationToken = default(CancellationToken))

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

RunWithRetriableTransactionAsync<TResult>(Func<SpannerTransaction, Task<TResult>>, CancellationToken)

public Task<TResult> RunWithRetriableTransactionAsync<TResult>(Func<SpannerTransaction, Task<TResult>> asyncWork, CancellationToken cancellationToken = default(CancellationToken))

ShutdownSessionPoolAsync(CancellationToken)

public Task ShutdownSessionPoolAsync(CancellationToken cancellationToken = default(CancellationToken))

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

WhenSessionPoolReady(CancellationToken)

public Task WhenSessionPoolReady(CancellationToken cancellationToken = default(CancellationToken))

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