Getting started with Spanner in C#

Objectives

This tutorial walks you through the following steps using the Spanner client library for C#:

Create a Spanner instance and database.
Write, read, and execute SQL queries on data in the database.
Update the database schema.
Update data using a read-write transaction.
Add a secondary index to the database.
Use the index to read and execute SQL queries on data.
Retrieve data using a read-only transaction.

Costs

This tutorial uses Spanner, which is a billable component of the Google Cloud. For information on the cost of using Spanner, see Pricing.

Before you begin

Complete the steps described in Set up, which cover creating and setting a default Google Cloud project, enabling billing, enabling the Cloud Spanner API, and setting up OAuth 2.0 to get authentication credentials to use the Cloud Spanner API.

In particular, make sure that you run gcloud auth application-default login to set up your local development environment with authentication credentials.

Prepare your local C# environment

Set the GOOGLE_PROJECT_ID environment variable to your Google Cloud project ID.
1. First, set GOOGLE_PROJECT_ID for the current PowerShell session:
```
$env:GOOGLE_PROJECT_ID = "MY_PROJECT_ID"
```
2. Then, set GOOGLE_PROJECT_ID for all processes created after this command:
```
[Environment]::SetEnvironmentVariable("GOOGLE_PROJECT_ID", "MY_PROJECT_ID", "User")
```
Download credentials.
1. Go to the Credentials page in the Google Cloud console.
  
  Go to the Credentials page
2. Click Create credentials and choose Service account key.
3. Under "Service account", choose Compute Engine default service account, and leave JSON selected under "Key type". Click Create. Your computer downloads a JSON file.
Set up credentials. For a file named FILENAME.json in CURRENT_USER's Downloads directory, located on the C drive, run the following commands to set GOOGLE_APPLICATION_CREDENTIALS to point to the JSON key:
1. First, to set GOOGLE_APPLICATION_CREDENTIALS for this PowerShell session:
```
$env:GOOGLE_APPLICATION_CREDENTIALS = "C:\Users\CURRENT_USER\Downloads\FILENAME.json"
```
2. Then, to set GOOGLE_APPLICATION_CREDENTIALS for all processes created after this command:
```
[Environment]::SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS", "C:\Users\CURRENT_USER\Downloads\FILENAME.json", "User")
```
Clone the sample app repository to your local machine:
```
git clone https://github.com/GoogleCloudPlatform/dotnet-docs-samples
```
Alternatively, you can download the sample as a zip file and extract it.
Open Spanner.sln, located in the dotnet-docs-samples\spanner\api directory of the downloaded repository, with Visual Studio 2017 or later, then build it.
Change to the directory within the downloaded repository that contains the compiled application. For example:
```
cd dotnet-docs-samples\spanner\api\Spanner
```

Create an instance

When you first use Spanner, you must create an instance, which is an allocation of resources that are used by Spanner databases. When you create an instance, you choose an instance configuration, which determines where your data is stored, and also the number of nodes to use, which determines the amount of serving and storage resources in your instance.

Execute the following command to create a Spanner instance in the region us-central1 with 1 node:

gcloud spanner instances create test-instance --config=regional-us-central1 `
    --description="Test Instance" --nodes=1

Note that this creates an instance with the following characteristics:

Instance ID test-instance
Display name Test Instance
Instance configuration regional-us-central1 (Regional configurations store data in one region, while multi-region configurations distribute data across multiple regions. For more information, see About instances.)
Node count of 1 (node_count corresponds to the amount of serving and storage resources available to databases in the instance. Learn more in Nodes and processing units.)

You should see:

Creating instance...done.

Look through sample files

The samples repository contains a sample that shows how to use Spanner with C#.

Take a look through the Spanner .NET GitHub repository, which shows how to create a database and modify a database schema. The data uses the example schema shown in the Schema and data model page.

Create a database

You should see:

The following code creates a database and two tables in the database.

GoogleSQL


using Google.Cloud.Spanner.Data;
using System.Threading.Tasks;

public class CreateDatabaseAsyncSample
{
    public async Task CreateDatabaseAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}";

        using var connection = new SpannerConnection(connectionString);
        var createDatabase = $"CREATE DATABASE `{databaseId}`";
        // Define create table statement for table #1.
        var createSingersTable =
            @"CREATE TABLE Singers (
                SingerId INT64 NOT NULL,
                FirstName STRING(1024),
                LastName STRING(1024),
                ComposerInfo BYTES(MAX),
                FullName STRING(2048) AS (ARRAY_TO_STRING([FirstName, LastName], "" "")) STORED
            ) PRIMARY KEY (SingerId)";
        // Define create table statement for table #2.
        var createAlbumsTable =
            @"CREATE TABLE Albums (
                SingerId INT64 NOT NULL,
                AlbumId INT64 NOT NULL,
                AlbumTitle STRING(MAX)
            ) PRIMARY KEY (SingerId, AlbumId),
            INTERLEAVE IN PARENT Singers ON DELETE CASCADE";

        using var createDbCommand = connection.CreateDdlCommand(createDatabase, createSingersTable, createAlbumsTable);
        await createDbCommand.ExecuteNonQueryAsync();
    }
}

PostgreSQL


using Google.Cloud.Spanner.Admin.Database.V1;
using Google.Cloud.Spanner.Common.V1;
using System;
using System.Threading.Tasks;

public class CreateDatabaseAsyncPostgresSample
{
    public async Task CreateDatabaseAsyncPostgres(string projectId, string instanceId, string databaseId)
    {
        DatabaseAdminClient databaseAdminClient = await DatabaseAdminClient.CreateAsync();

        // Create the CreateDatabaseRequest with PostgreSQL dialect and execute it.
        // There cannot be Extra DDL statements while creating PostgreSQL.
        var createDatabaseRequest = new CreateDatabaseRequest
        {
            ParentAsInstanceName = InstanceName.FromProjectInstance(projectId, instanceId),
            CreateStatement = $"CREATE DATABASE \"{databaseId}\"",
            DatabaseDialect = DatabaseDialect.Postgresql
        };

        var createOperation = await databaseAdminClient.CreateDatabaseAsync(createDatabaseRequest);

        // Wait until the operation has finished.
        Console.WriteLine("Waiting for the database to be created.");
        var completedResponse = await createOperation.PollUntilCompletedAsync();
        if (completedResponse.IsFaulted)
        {
            Console.WriteLine($"Error while creating PostgreSQL database: {completedResponse.Exception}");
            throw completedResponse.Exception;
        }

        // PostgreSQL Database is created. Now, we can create the tables.
        // Define create table statement for table #1 in PostgreSQL syntax.
        var createSingersTable = @"CREATE TABLE Singers (
            SingerId bigint NOT NULL PRIMARY KEY,
            FirstName varchar(1024),
            LastName varchar(1024),
            Rating numeric,
            SingerInfo bytea,
            FullName character varying(2048) GENERATED ALWAYS AS (FirstName || ' ' || LastName) STORED)";

        // Define create table statement for table #2 in PostgreSQL syntax.
        var createAlbumsTable = @"CREATE TABLE Albums (
            AlbumId bigint NOT NULL PRIMARY KEY,
            SingerId bigint NOT NULL REFERENCES Singers (SingerId),
            AlbumTitle text,
            MarketingBudget BIGINT)";

        DatabaseName databaseName = DatabaseName.FromProjectInstanceDatabase(projectId, instanceId, databaseId);

        // Create UpdateDatabaseRequest to create the tables. 
        var updateDatabaseRequest = new UpdateDatabaseDdlRequest
        {
            DatabaseAsDatabaseName = databaseName,
            Statements = { createSingersTable, createAlbumsTable }
        };

        var updateOperation = await databaseAdminClient.UpdateDatabaseDdlAsync(updateDatabaseRequest);
        // Wait until the operation has finished.
        Console.WriteLine("Waiting for the tables to be created.");
        var updateResponse = await updateOperation.PollUntilCompletedAsync();
        if (updateResponse.IsFaulted)
        {
            Console.WriteLine($"Error while updating database: {updateResponse.Exception}");
            throw updateResponse.Exception;
        }
    }
}

The next step is to write data to your database.

Create a database client

Before you can do reads or writes, you must create a SpannerConnection:


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

namespace GoogleCloudSamples.Spanner
{
    public class QuickStart
    {
        static async Task MainAsync()
        {
            string projectId = "YOUR-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 static void Main(string[] args)
        {
            MainAsync().Wait();
        }
    }
}

You can think of a SpannerConnection as a database connection: all of your interactions with Spanner must go through a SpannerConnection.

Read more in the SpannerConnection reference.

Write data with DML

You can insert data using Data Manipulation Language (DML) in a read-write transaction.

You use the ExecuteNonQueryAsync() method to execute a DML statement.


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class WriteUsingDmlCoreAsyncSample
{
    public async Task<int> WriteUsingDmlCoreAsync(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();

        SpannerCommand cmd = connection.CreateDmlCommand(
            "INSERT Singers (SingerId, FirstName, LastName) VALUES "
               + "(12, 'Melissa', 'Garcia'), "
               + "(13, 'Russell', 'Morales'), "
               + "(14, 'Jacqueline', 'Long'), "
               + "(15, 'Dylan', 'Shaw')");
        int rowCount = await cmd.ExecuteNonQueryAsync();

        Console.WriteLine($"{rowCount} row(s) inserted...");
        return rowCount;
    }
}

Run the sample using the writeUsingDml argument.

dotnet run writeUsingDml $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

4 row(s) inserted...

Write data with mutations

You can also insert data using mutations.

You can insert data using the connection.CreateInsertCommand() method, which creates a new SpannerCommand to insert rows into a table. The SpannerCommand.ExecuteNonQueryAsync() method adds new rows to the table.

This code shows how to insert data using mutations:


using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

public class InsertDataAsyncSample
{
    public class Singer
    {
        public int SingerId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public class Album
    {
        public int SingerId { get; set; }
        public int AlbumId { get; set; }
        public string AlbumTitle { get; set; }
    }

    public async Task InsertDataAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        List<Singer> singers = new List<Singer>
        {
            new Singer { SingerId = 1, FirstName = "Marc", LastName = "Richards" },
            new Singer { SingerId = 2, FirstName = "Catalina", LastName = "Smith" },
            new Singer { SingerId = 3, FirstName = "Alice", LastName = "Trentor" },
            new Singer { SingerId = 4, FirstName = "Lea", LastName = "Martin" },
            new Singer { SingerId = 5, FirstName = "David", LastName = "Lomond" },
        };
        List<Album> albums = new List<Album>
        {
            new Album { SingerId = 1, AlbumId = 1, AlbumTitle = "Total Junk" },
            new Album { SingerId = 1, AlbumId = 2, AlbumTitle = "Go, Go, Go" },
            new Album { SingerId = 2, AlbumId = 1, AlbumTitle = "Green" },
            new Album { SingerId = 2, AlbumId = 2, AlbumTitle = "Forever Hold your Peace" },
            new Album { SingerId = 2, AlbumId = 3, AlbumTitle = "Terrified" },
        };

        // Create connection to Cloud Spanner.
        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        await connection.RunWithRetriableTransactionAsync(async transaction =>
        {
            await Task.WhenAll(singers.Select(singer =>
            {
                // Insert rows into the Singers table.
                using var cmd = connection.CreateInsertCommand("Singers", new SpannerParameterCollection
                {
                        { "SingerId", SpannerDbType.Int64, singer.SingerId },
                        { "FirstName", SpannerDbType.String, singer.FirstName },
                        { "LastName", SpannerDbType.String, singer.LastName }
                });
                cmd.Transaction = transaction;
                return cmd.ExecuteNonQueryAsync();
            }));

            await Task.WhenAll(albums.Select(album =>
            {
                // Insert rows into the Albums table.
                using var cmd = connection.CreateInsertCommand("Albums", new SpannerParameterCollection
                {
                        { "SingerId", SpannerDbType.Int64, album.SingerId },
                        { "AlbumId", SpannerDbType.Int64, album.AlbumId },
                        { "AlbumTitle", SpannerDbType.String,album.AlbumTitle }
                });
                cmd.Transaction = transaction;
                return cmd.ExecuteNonQueryAsync();
            }));
        });
        Console.WriteLine("Data inserted.");
    }
}

Run the sample using the insertSampleData argument.

dotnet run insertSampleData $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

Inserted data.

Query data using SQL

Spanner supports a SQL interface for reading data, which you can access on the command line using the Google Cloud CLI or programmatically using the Spanner client library for C#.

On the command line

Execute the following SQL statement to read the values of all columns from the Albums table:

gcloud spanner databases execute-sql example-db --instance=test-instance `
    --sql='SELECT SingerId, AlbumId, AlbumTitle FROM Albums'

The result should be:

SingerId AlbumId AlbumTitle
1        1       Total Junk
1        2       Go, Go, Go
2        1       Green
2        2       Forever Hold Your Peace
2        3       Terrified

Use the Spanner client library for C#

In addition to executing a SQL statement on the command line, you can issue the same SQL statement programmatically using the Spanner client library for C#.

Use ExecuteReaderAsync() to run the SQL query.


using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QuerySampleDataAsyncSample
{
    public class Album
    {
        public int SingerId { get; set; }
        public int AlbumId { get; set; }
        public string AlbumTitle { get; set; }
    }

    public async Task<List<Album>> QuerySampleDataAsync(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);
        using var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");

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

Here's how to issue the query and access the data:

dotnet run querySampleData $env:GOOGLE_PROJECT_ID test-instance example-db

You should see the following result:

SingerId: 1 AlbumId: 1 AlbumTitle: Total Junk
SingerId: 1 AlbumId: 2 AlbumTitle: Go, Go, Go
SingerId: 2 AlbumId: 1 AlbumTitle: Green
SingerId: 2 AlbumId: 2 AlbumTitle: Forever Hold your Peace
SingerId: 2 AlbumId: 3 AlbumTitle: Terrified

Query using a SQL parameter

If your application has a frequently executed query, you can improve its performance by parameterizing it. The resulting parametric query can be cached and reused, which reduces compilation costs. For more information, see Use query parameters to speed up frequently executed queries.

Here is an example of using a parameter in the WHERE clause to query records containing a specific value for LastName.


using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QueryWithParameterAsyncSample
{
    public class Singer
    {
        public int SingerId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public async Task<List<Singer>> QueryWithParameterAsync(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, FirstName, LastName FROM Singers WHERE LastName = @lastName",
            new SpannerParameterCollection { { "lastName", SpannerDbType.String, "Garcia" } });

        var singers = new List<Singer>();
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            singers.Add(new Singer
            {
                SingerId = reader.GetFieldValue<int>("SingerId"),
                FirstName = reader.GetFieldValue<string>("FirstName"),
                LastName = reader.GetFieldValue<string>("LastName")
            });
        }
        return singers;
    }
}

Here's how to issue the query with a parameter and access the data:

dotnet run queryWithParameter $env:GOOGLE_PROJECT_ID test-instance example-db

You should see the following result:

SingerId : 12 FirstName : Melissa LastName : Garcia

Update the database schema

Assume you need to add a new column called MarketingBudget to the Albums table. Adding a new column to an existing table requires an update to your database schema. Spanner supports schema updates to a database while the database continues to serve traffic. Schema updates don't require taking the database offline and they don't lock entire tables or columns; you can continue writing data to the database during the schema update. Read more about supported schema updates and schema change performance in Make schema updates.

Add a column

You can add a column on the command line using the Google Cloud CLI or programmatically using the Spanner client library for C#.

On the command line

Use the following ALTER TABLE command to add the new column to the table:

GoogleSQL

gcloud spanner databases ddl update example-db --instance=test-instance `
    --ddl='ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'

PostgreSQL

gcloud spanner databases ddl update example-db --instance=test-instance `
    --ddl='ALTER TABLE Albums ADD COLUMN MarketingBudget BIGINT'

You should see:

Schema updating...done.

Use the Spanner client library for C#

Use CreateDdlCommand() to modify the schema:


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class AddColumnAsyncSample
{
    public async Task AddColumnAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        string alterStatement = "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64";

        using var connection = new SpannerConnection(connectionString);
        using var updateCmd = connection.CreateDdlCommand(alterStatement);
        await updateCmd.ExecuteNonQueryAsync();
        Console.WriteLine("Added the MarketingBudget column.");
    }
}

Run the sample using the addColumn command.

dotnet run addColumn $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

Added the MarketingBudget column.

Write data to the new column

The following code writes data to the new column. It sets MarketingBudget to 100000 for the row keyed by Albums(1, 1) and to 500000 for the row keyed by Albums(2, 2).


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class UpdateDataAsyncSample
{
    public async Task<int> UpdateDataAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);

        var rowCount = 0;
        SpannerCommand cmd = connection.CreateDmlCommand(
            "UPDATE Albums SET MarketingBudget = @MarketingBudget "
            + "WHERE SingerId = 1 and AlbumId = 1");
        cmd.Parameters.Add("MarketingBudget", SpannerDbType.Int64, 100000);
        rowCount += await cmd.ExecuteNonQueryAsync();

        cmd = connection.CreateDmlCommand(
            "UPDATE Albums SET MarketingBudget = @MarketingBudget "
            + "WHERE SingerId = 2 and AlbumId = 2");
        cmd.Parameters.Add("MarketingBudget", SpannerDbType.Int64, 500000);
        rowCount += await cmd.ExecuteNonQueryAsync();

        Console.WriteLine("Data Updated.");
        return rowCount;
    }
}

Run the sample using the writeDataToNewColumn command.

dotnet run writeDataToNewColumn $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

Updated data.

You can also execute a SQL query to fetch the values that you just wrote.

Here's the code to execute the query:


using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QueryNewColumnAsyncSample
{
    public class Album
    {
        public int SingerId { get; set; }
        public int AlbumId { get; set; }
        public long MarketingBudget { get; set; }
    }

    public async Task<List<Album>> QueryNewColumnAsync(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);
        using var cmd = connection.CreateSelectCommand("SELECT * 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"),
                MarketingBudget = reader.IsDBNull(reader.GetOrdinal("MarketingBudget")) ? 0 : reader.GetFieldValue<long>("MarketingBudget")
            });
        }
        return albums;
    }
}

To execute this query, run the sample using the queryNewColumn argument.

dotnet run queryNewColumn $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

SingerId : 1 AlbumId : 1 MarketingBudget : 100000
SingerId : 1 AlbumId : 2 MarketingBudget :
SingerId : 2 AlbumId : 1 MarketingBudget :
SingerId : 2 AlbumId : 2 MarketingBudget : 500000
SingerId : 2 AlbumId : 3 MarketingBudget :

Update data

You can update data using DML in a read-write transaction.

You use the ExecuteNonQueryAsync() method to execute a DML statement.


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class WriteWithTransactionUsingDmlCoreAsyncSample
{
    public async Task<int> WriteWithTransactionUsingDmlCoreAsync(string projectId, string instanceId, string databaseId)
    {
        // This sample transfers 200,000 from the MarketingBudget
        // field of the second Album to the first Album. Make sure to run
        // the AddColumnAsyncSample and WriteDataToNewColumnAsyncSample first,
        // in that order.
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        decimal transferAmount = 200000;
        decimal secondBudget = 0;

        // Create connection to Cloud Spanner.
        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        // Create a readwrite transaction that we'll assign
        // to each SpannerCommand.
        using var transaction = await connection.BeginTransactionAsync();
        // Create statement to select the second album's data.
        var cmdLookup = connection.CreateSelectCommand("SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
        cmdLookup.Transaction = transaction;
        // Execute the select query.
        using var reader1 = await cmdLookup.ExecuteReaderAsync();
        while (await reader1.ReadAsync())
        {
            // Read the second album's budget.
            secondBudget = reader1.GetFieldValue<decimal>("MarketingBudget");
            // Confirm second Album's budget is sufficient and
            // if not raise an exception. Raising an exception
            // will automatically roll back the transaction.
            if (secondBudget < transferAmount)
            {
                throw new Exception($"The second album's budget {secondBudget} is less than the amount to transfer.");
            }
        }

        // Update second album to remove the transfer amount.
        secondBudget -= transferAmount;
        SpannerCommand cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = @MarketingBudget  WHERE SingerId = 2 and AlbumId = 2");
        cmd.Parameters.Add("MarketingBudget", SpannerDbType.Int64, secondBudget);
        cmd.Transaction = transaction;
        var rowCount = await cmd.ExecuteNonQueryAsync();

        // Update first album to add the transfer amount.
        cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget + @MarketingBudgetIncrement WHERE SingerId = 1 and AlbumId = 1");
        cmd.Parameters.Add("MarketingBudgetIncrement", SpannerDbType.Int64, transferAmount);
        cmd.Transaction = transaction;
        rowCount += await cmd.ExecuteNonQueryAsync();

        await transaction.CommitAsync();

        Console.WriteLine("Transaction complete.");
        return rowCount;
    }
}

Run the sample using the writeWithTransactionUsingDml argument.

dotnet run writeWithTransactionUsingDml $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

Transaction complete.

Use a secondary index

Suppose you wanted to fetch all rows of Albums that have AlbumTitle values in a certain range. You could read all values from the AlbumTitle column using a SQL statement or a read call, and then discard the rows that don't meet the criteria, but doing this full table scan is expensive, especially for tables with a lot of rows. Instead you can speed up the retrieval of rows when searching by non-primary key columns by creating a secondary index on the table.

Adding a secondary index to an existing table requires a schema update. Like other schema updates, Spanner supports adding an index while the database continues to serve traffic. Spanner automatically backfills the index with your existing data. Backfills might take a few minutes to complete, but you don't need to take the database offline or avoid writing to the indexed table during this process. For more details, see Add a secondary index.

After you add a secondary index, Spanner automatically uses it for SQL queries that are likely to run faster with the index. If you use the read interface, you must specify the index that you want to use.

Add a secondary index

You can add an index on the command line using the gcloud CLI or programmatically using the Spanner client library for C#.

On the command line

Use the following CREATE INDEX command to add an index to the database:

gcloud spanner databases ddl update example-db --instance=test-instance `
    --ddl='CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)'

You should see:

Schema updating...done.

Using the Spanner client library for C#

Use CreateDdlCommand() to add an index:


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class AddIndexAsyncSample
{
    public async Task AddIndexAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        string createStatement = "CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)";

        using var connection = new SpannerConnection(connectionString);
        using var createCmd = connection.CreateDdlCommand(createStatement);
        await createCmd.ExecuteNonQueryAsync();
        Console.WriteLine("Added the AlbumsByAlbumTitle index.");
    }
}

Run the sample using the addIndex command.

  dotnet run addIndex $env:GOOGLE_PROJECT_ID test-instance example-db

Adding an index can take a few minutes. After the index is added, you should see:

  Added the AlbumsByAlbumTitle index.

Add an index for index-only reads

You might have noticed that the previous read example doesn't include reading the MarketingBudget column. This is because Spanner's read interface doesn't support the ability to join an index with a data table to look up values that are not stored in the index.

Create an alternate definition of AlbumsByAlbumTitle that stores a copy of MarketingBudget in the index.

On the command line

GoogleSQL

gcloud spanner databases ddl update example-db --instance=test-instance `
    --ddl='CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)

PostgreSQL

gcloud spanner databases ddl update example-db --instance=test-instance `
    --ddl='CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) INCLUDE (MarketingBudget)

Adding an index can take a few minutes. After the index is added, you should see:

Schema updating...done.

Using the Spanner client library for C#

Use CreateDdlCommand() to add an index with a STORING clause:


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class AddStoringIndexAsyncSample
{
    public async Task AddStoringIndexAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        string createStatement = "CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)";

        using var connection = new SpannerConnection(connectionString);
        using var createCmd = connection.CreateDdlCommand(createStatement);
        await createCmd.ExecuteNonQueryAsync();
        Console.WriteLine("Added the AlbumsByAlbumTitle2 index.");
    }
}

Run the sample using the addStoringIndex command.

dotnet run addStoringIndex $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

Added the AlbumsByAlbumTitle2 index.

Now you can execute a read that fetches all AlbumId, AlbumTitle, and MarketingBudget columns from the AlbumsByAlbumTitle2 index:

Read data using the storing index you created by executing a query that explicitly specifies the index:


using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QueryDataWithStoringIndexAsyncSample
{
    public class Album
    {
        public int AlbumId { get; set; }
        public string AlbumTitle { get; set; }
        public long? MarketingBudget { get; set; }
    }

    public async Task<List<Album>> QueryDataWithStoringIndexAsync(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 AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
            + "{FORCE_INDEX=AlbumsByAlbumTitle2}");

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

Run the sample using the queryDataWithStoringIndex command.

dotnet run queryDataWithStoringIndex $env:GOOGLE_PROJECT_ID test-instance example-db

You should see output similar to:

AlbumId : 2 AlbumTitle : Forever Hold your Peace MarketingBudget : 300000
AlbumId : 2 AlbumTitle : Go, Go, Go MarketingBudget : 300000

Retrieve data using read-only transactions

Suppose you want to execute more than one read at the same timestamp. Read-only transactions observe a consistent prefix of the transaction commit history, so your application always gets consistent data. Use the .NET framework's TransactionScope() along with OpenAsReadOnlyAsync() for executing read-only transactions.

The following shows how to run a query and perform a read in the same read-only transaction:

.NET Standard 2.0


using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Transactions;

public class QueryDataWithTransactionAsyncSample
{
    public class Album
    {
        public int SingerId { get; set; }
        public int AlbumId { get; set; }
        public string AlbumTitle { get; set; }
    }

    public async Task<List<Album>> QueryDataWithTransactionAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        var albums = new List<Album>();
        using TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
        using var connection = new SpannerConnection(connectionString);

        // Opens the connection so that the Spanner transaction included in the TransactionScope
        // is read-only TimestampBound.Strong.
        await connection.OpenAsync(SpannerTransactionCreationOptions.ReadOnly, options: null, cancellationToken: default);
        using var cmd = connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");

        // 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")
                });
            }
        }
        scope.Complete();
        Console.WriteLine("Transaction complete.");
        return albums;
    }
}

.NET Standard 1.5


using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

public class QueryDataWithTransactionCoreAsyncSample
{
    public class Album
    {
        public int SingerId { get; set; }
        public int AlbumId { get; set; }
        public string AlbumTitle { get; set; }
    }

    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.BeginTransactionAsync(SpannerTransactionCreationOptions.ReadOnly, cancellationToken: default);
        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;
    }
}

Run the sample using the queryDataWithTransaction command.

dotnet run queryDataWithTransaction $env:GOOGLE_PROJECT_ID test-instance example-db

You should see output similar to:

SingerId : 2 AlbumId : 2 AlbumTitle : Forever Hold your Peace
SingerId : 1 AlbumId : 2 AlbumTitle : Go, Go, Go
SingerId : 2 AlbumId : 1 AlbumTitle : Green
SingerId : 2 AlbumId : 3 AlbumTitle : Terrified
SingerId : 1 AlbumId : 1 AlbumTitle : Total Junk
SingerId : 2 AlbumId : 2 AlbumTitle : Forever Hold your Peace
SingerId : 1 AlbumId : 2 AlbumTitle : Go, Go, Go
SingerId : 2 AlbumId : 1 AlbumTitle : Green
SingerId : 2 AlbumId : 3 AlbumTitle : Terrified
SingerId : 1 AlbumId : 1 AlbumTitle : Total Junk

Cleanup

To avoid incurring additional charges to your Cloud Billing account for the resources used in this tutorial, drop the database and delete the instance that you created.

Delete the database

If you delete an instance, all databases within it are automatically deleted. This step shows how to delete a database without deleting an instance (you would still incur charges for the instance).

On the command line

gcloud spanner databases delete example-db --instance=test-instance

Using the Google Cloud console

Go to the Spanner Instances page in the Google Cloud console.

Go to the Instances page
Click the instance.
Click the database that you want to delete.
In the Database details page, click Delete.
Confirm that you want to delete the database and click Delete.

Delete the instance

Deleting an instance automatically drops all databases created in that instance.

On the command line

gcloud spanner instances delete test-instance

Using the Google Cloud console

Go to the Spanner Instances page in the Google Cloud console.

Go to the Instances page
Click your instance.
Click Delete.
Confirm that you want to delete the instance and click Delete.

What's next

Try the preview release of Spanner database provider for Entity Framework Core.

Learn how to access Spanner with a virtual machine instance.
Learn about authorization and authentication credentials in Authenticate to Cloud services using client libraries.
Learn more about Spanner Schema design best practices.