Getting started with Spanner in C#

Objectives

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

Costs

This tutorial uses Spanner, which is a billable component of theGoogle Cloud. For information on the cost of using Spanner, seePricing.

Before you begin

Complete the steps described inSet up, which cover creating andsetting a default Google Cloud project, enabling billing, enabling theCloud Spanner API, and setting up OAuth 2.0 to get authentication credentials to usethe Cloud Spanner API.

In particular, make sure that you rungcloud authapplication-default loginto set up your local development environment with authenticationcredentials.

Note: If you don't plan to keep the resources that you create in this tutorial,consider creating a new Google Cloud project instead of selecting an existingproject. After you finish the tutorial, you can delete the project, removing allresources associated with the project.

Prepare your local C# environment

  1. Set theGOOGLE_PROJECT_ID environment variable to your Google Cloud projectID.

    1. First, setGOOGLE_PROJECT_ID for the current PowerShell session:

      $env:GOOGLE_PROJECT_ID="MY_PROJECT_ID"

    2. Then, setGOOGLE_PROJECT_ID for all processes created after thiscommand:

      [Environment]::SetEnvironmentVariable("GOOGLE_PROJECT_ID","MY_PROJECT_ID","User")

  2. Download credentials.

    1. Go to theCredentials page in the Google Cloud console.

      Go to the Credentials page

    2. ClickCreate credentials and chooseService account key.

    3. Under "Service account", chooseCompute Engine default serviceaccount, and leaveJSON selected under "Key type". ClickCreate. Your computer downloads a JSON file.

  3. Set up credentials. For a file namedFILENAME.json inCURRENT_USER'sDownloads directory, located on theC drive, run the following commands tosetGOOGLE_APPLICATION_CREDENTIALS to point to the JSON key:

    1. First, to setGOOGLE_APPLICATION_CREDENTIALS for this PowerShellsession:

      $env:GOOGLE_APPLICATION_CREDENTIALS="C:\Users\CURRENT_USER\Downloads\FILENAME.json"

    2. Then, to setGOOGLE_APPLICATION_CREDENTIALS for all processes createdafter this command:

      [Environment]::SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS","C:\Users\CURRENT_USER\Downloads\FILENAME.json","User")

  4. Clone the sample app repository to your local machine:

    gitclonehttps://github.com/GoogleCloudPlatform/dotnet-docs-samples

    Alternatively, you candownload thesampleas a zip file and extract it.

  5. OpenSpanner.sln, located in thedotnet-docs-samples\spanner\apidirectory of the downloaded repository, with Visual Studio 2017 or later, thenbuild it.

  6. Change to the directory within the downloaded repository that contains thecompiled application. For example:

    cddotnet-docs-samples\spanner\api\Spanner

Create an instance

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

SeeCreate an instanceto learn how to create a Spanner instance using any of thefollowing methods. You can name your instancetest-instance to use it withother topics in this document that reference an instance namedtest-instance.

  • The Google Cloud CLI
  • The Google Cloud console
  • A client library (C++, C#, Go, Java, Node.js, PHP, Python, or Ruby)

Look through sample files

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

Take a look through theSpanner .NET GitHub repository,which shows how to create a database and modify a database schema. The data usesthe example schema shown in theSchema and data model page.

Create a database

You should see:

The following code creates a database and two tables in the database.Note: The subsequent code samples use these two tables. If you don't executethis code, then create the tables by using the Google Cloud console or thegcloud CLI. For more information, see theexample schema.

GoogleSQL

usingGoogle.Cloud.Spanner.Data;usingSystem.Threading.Tasks;publicclassCreateDatabaseAsyncSample{publicasyncTaskCreateDatabaseAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}";usingvarconnection=newSpannerConnection(connectionString);varcreateDatabase=$"CREATE DATABASE `{databaseId}`";// Define create table statement for table #1.varcreateSingersTable=@"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.varcreateAlbumsTable=@"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";usingvarcreateDbCommand=connection.CreateDdlCommand(createDatabase,createSingersTable,createAlbumsTable);awaitcreateDbCommand.ExecuteNonQueryAsync();}}

PostgreSQL

usingGoogle.Cloud.Spanner.Admin.Database.V1;usingGoogle.Cloud.Spanner.Common.V1;usingSystem;usingSystem.Threading.Tasks;publicclassCreateDatabaseAsyncPostgresSample{publicasyncTaskCreateDatabaseAsyncPostgres(stringprojectId,stringinstanceId,stringdatabaseId){DatabaseAdminClientdatabaseAdminClient=awaitDatabaseAdminClient.CreateAsync();// Create the CreateDatabaseRequest with PostgreSQL dialect and execute it.// There cannot be Extra DDL statements while creating PostgreSQL.varcreateDatabaseRequest=newCreateDatabaseRequest{ParentAsInstanceName=InstanceName.FromProjectInstance(projectId,instanceId),CreateStatement=$"CREATE DATABASE \"{databaseId}\"",DatabaseDialect=DatabaseDialect.Postgresql};varcreateOperation=awaitdatabaseAdminClient.CreateDatabaseAsync(createDatabaseRequest);// Wait until the operation has finished.Console.WriteLine("Waiting for the database to be created.");varcompletedResponse=awaitcreateOperation.PollUntilCompletedAsync();if(completedResponse.IsFaulted){Console.WriteLine($"Error while creating PostgreSQL database: {completedResponse.Exception}");throwcompletedResponse.Exception;}// PostgreSQL Database is created. Now, we can create the tables.// Define create table statement for table #1 in PostgreSQL syntax.varcreateSingersTable=@"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.varcreateAlbumsTable=@"CREATE TABLE Albums (            AlbumId bigint NOT NULL PRIMARY KEY,            SingerId bigint NOT NULL REFERENCES Singers (SingerId),            AlbumTitle text,            MarketingBudget BIGINT)";DatabaseNamedatabaseName=DatabaseName.FromProjectInstanceDatabase(projectId,instanceId,databaseId);// Create UpdateDatabaseRequest to create the tables.varupdateDatabaseRequest=newUpdateDatabaseDdlRequest{DatabaseAsDatabaseName=databaseName,Statements={createSingersTable,createAlbumsTable}};varupdateOperation=awaitdatabaseAdminClient.UpdateDatabaseDdlAsync(updateDatabaseRequest);// Wait until the operation has finished.Console.WriteLine("Waiting for the tables to be created.");varupdateResponse=awaitupdateOperation.PollUntilCompletedAsync();if(updateResponse.IsFaulted){Console.WriteLine($"Error while updating database: {updateResponse.Exception}");throwupdateResponse.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 aSpannerConnection:

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;namespaceGoogleCloudSamples.Spanner{publicclassQuickStart{staticasyncTaskMainAsync(){stringprojectId="YOUR-PROJECT-ID";stringinstanceId="my-instance";stringdatabaseId="my-database";stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/"+$"databases/{databaseId}";// Create connection to Cloud Spanner.using(varconnection=newSpannerConnection(connectionString)){// Execute a simple SQL statement.varcmd=connection.CreateSelectCommand(@"SELECT ""Hello World"" as test");using(varreader=awaitcmd.ExecuteReaderAsync()){while(awaitreader.ReadAsync()){Console.WriteLine(reader.GetFieldValue<string>("test"));}}}}publicstaticvoidMain(string[]args){MainAsync().Wait();}}}

You can think of aSpannerConnection as a database connection: all of yourinteractions with Spanner must go through aSpannerConnection.

Read more in theSpannerConnection reference.

Write data with DML

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

You use theExecuteNonQueryAsync() method to execute a DML statement.

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassWriteUsingDmlCoreAsyncSample{publicasyncTask<int>WriteUsingDmlCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();SpannerCommandcmd=connection.CreateDmlCommand("INSERT Singers (SingerId, FirstName, LastName) VALUES "+"(12, 'Melissa', 'Garcia'), "+"(13, 'Russell', 'Morales'), "+"(14, 'Jacqueline', 'Long'), "+"(15, 'Dylan', 'Shaw')");introwCount=awaitcmd.ExecuteNonQueryAsync();Console.WriteLine($"{rowCount} row(s) inserted...");returnrowCount;}}

Run the sample using thewriteUsingDml argument.

dotnetrunwriteUsingDml$env:GOOGLE_PROJECT_IDtest-instanceexample-db

You should see:

4row(s)inserted...
Note: There are limits to commit size. SeeCRUD limitfor more information.

Write data with mutations

You can also insert data usingmutations.

You can insert data using theconnection.CreateInsertCommand()method, which creates a newSpannerCommand to insert rows into a table. TheSpannerCommand.ExecuteNonQueryAsync()method adds new rows to the table.

This code shows how to insert data using mutations:

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;publicclassInsertDataAsyncSample{publicclassSinger{publicintSingerId{get;set;}publicstringFirstName{get;set;}publicstringLastName{get;set;}}publicclassAlbum{publicintSingerId{get;set;}publicintAlbumId{get;set;}publicstringAlbumTitle{get;set;}}publicasyncTaskInsertDataAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";List<Singer>singers=newList<Singer>{newSinger{SingerId=1,FirstName="Marc",LastName="Richards"},newSinger{SingerId=2,FirstName="Catalina",LastName="Smith"},newSinger{SingerId=3,FirstName="Alice",LastName="Trentor"},newSinger{SingerId=4,FirstName="Lea",LastName="Martin"},newSinger{SingerId=5,FirstName="David",LastName="Lomond"},};List<Album>albums=newList<Album>{newAlbum{SingerId=1,AlbumId=1,AlbumTitle="Total Junk"},newAlbum{SingerId=1,AlbumId=2,AlbumTitle="Go, Go, Go"},newAlbum{SingerId=2,AlbumId=1,AlbumTitle="Green"},newAlbum{SingerId=2,AlbumId=2,AlbumTitle="Forever Hold your Peace"},newAlbum{SingerId=2,AlbumId=3,AlbumTitle="Terrified"},};// Create connection to Cloud Spanner.usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();awaitconnection.RunWithRetriableTransactionAsync(asynctransaction=>{awaitTask.WhenAll(singers.Select(singer=>{// Insert rows into the Singers table.usingvarcmd=connection.CreateInsertCommand("Singers",newSpannerParameterCollection{{"SingerId",SpannerDbType.Int64,singer.SingerId},{"FirstName",SpannerDbType.String,singer.FirstName},{"LastName",SpannerDbType.String,singer.LastName}});cmd.Transaction=transaction;returncmd.ExecuteNonQueryAsync();}));awaitTask.WhenAll(albums.Select(album=>{// Insert rows into the Albums table.usingvarcmd=connection.CreateInsertCommand("Albums",newSpannerParameterCollection{{"SingerId",SpannerDbType.Int64,album.SingerId},{"AlbumId",SpannerDbType.Int64,album.AlbumId},{"AlbumTitle",SpannerDbType.String,album.AlbumTitle}});cmd.Transaction=transaction;returncmd.ExecuteNonQueryAsync();}));});Console.WriteLine("Data inserted.");}}

Run the sample using theinsertSampleData argument.

dotnetruninsertSampleData$env:GOOGLE_PROJECT_IDtest-instanceexample-db

You should see:

Inserteddata.
Note: There are limits to commit size. SeeCRUD limitfor more information.

Query data using SQL

Spanner supports a SQL interface for reading data, which you canaccess on the command line using the Google Cloud CLI orprogrammatically usingthe Spanner client library for C#.

On the command line

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

gcloudspannerdatabasesexecute-sqlexample-db--instance=test-instance\--sql='SELECTSingerId,AlbumId,AlbumTitleFROMAlbums'
Note: For the GoogleSQL reference, seeQuery syntax in GoogleSQLand for PostgreSQL reference, seePostgreSQL lexical structure and syntax.

The result shows:

SingerIdAlbumIdAlbumTitle11TotalJunk12Go,Go,Go21Green22ForeverHoldYourPeace23Terrified

Use the Spanner client library for C#

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

UseExecuteReaderAsync()to run the SQL query.

usingGoogle.Cloud.Spanner.Data;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;publicclassQuerySampleDataAsyncSample{publicclassAlbum{publicintSingerId{get;set;}publicintAlbumId{get;set;}publicstringAlbumTitle{get;set;}}publicasyncTask<List<Album>>QuerySampleDataAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";varalbums=newList<Album>();usingvarconnection=newSpannerConnection(connectionString);usingvarcmd=connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");usingvarreader=awaitcmd.ExecuteReaderAsync();while(awaitreader.ReadAsync()){albums.Add(newAlbum{AlbumId=reader.GetFieldValue<int>("AlbumId"),SingerId=reader.GetFieldValue<int>("SingerId"),AlbumTitle=reader.GetFieldValue<string>("AlbumTitle")});}returnalbums;}}

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

dotnetrunquerySampleData$env:GOOGLE_PROJECT_IDtest-instanceexample-db

You should see the following result:

SingerId:1AlbumId:1AlbumTitle:TotalJunkSingerId:1AlbumId:2AlbumTitle:Go,Go,GoSingerId:2AlbumId:1AlbumTitle:GreenSingerId:2AlbumId:2AlbumTitle:ForeverHoldyourPeaceSingerId:2AlbumId:3AlbumTitle:Terrified

Query using a SQL parameter

If your application has a frequently executed query, you can improve its performanceby parameterizing it. The resulting parametric query can be cached and reused, whichreduces compilation costs. For more information, seeUse query parameters to speed up frequently executed queries.

Here is an example of using a parameter in theWHERE clause toquery records containing a specific value forLastName.

usingGoogle.Cloud.Spanner.Data;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;publicclassQueryWithParameterAsyncSample{publicclassSinger{publicintSingerId{get;set;}publicstringFirstName{get;set;}publicstringLastName{get;set;}}publicasyncTask<List<Singer>>QueryWithParameterAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);usingvarcmd=connection.CreateSelectCommand($"SELECT SingerId, FirstName, LastName FROM Singers WHERE LastName = @lastName",newSpannerParameterCollection{{"lastName",SpannerDbType.String,"Garcia"}});varsingers=newList<Singer>();usingvarreader=awaitcmd.ExecuteReaderAsync();while(awaitreader.ReadAsync()){singers.Add(newSinger{SingerId=reader.GetFieldValue<int>("SingerId"),FirstName=reader.GetFieldValue<string>("FirstName"),LastName=reader.GetFieldValue<string>("LastName")});}returnsingers;}}

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

dotnetrunqueryWithParameter$env:GOOGLE_PROJECT_IDtest-instanceexample-db

You should see the following result:

SingerId:12FirstName:MelissaLastName:Garcia

Update the database schema

Assume you need to add a new column calledMarketingBudget to theAlbumstable. Adding a new column to an existing table requires an update to yourdatabase schema. Spanner supports schema updates to a database while thedatabase continues to serve traffic. Schema updates don't require taking thedatabase offline and they don't lock entire tables or columns; you can continuewriting data to the database during the schema update. Read more about supportedschema updates and schema change performance inMake schema updates.

Add a column

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

On the command line

Use the followingALTER TABLE command toadd the new column to the table:

GoogleSQL

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='ALTERTABLEAlbumsADDCOLUMNMarketingBudgetINT64'

PostgreSQL

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='ALTERTABLEAlbumsADDCOLUMNMarketingBudgetBIGINT'

You should see:

Schemaupdating...done.

Use the Spanner client library for C#

UseCreateDdlCommand()to modify the schema:

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassAddColumnAsyncSample{publicasyncTaskAddColumnAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";stringalterStatement="ALTER TABLE Albums ADD COLUMN MarketingBudget INT64";usingvarconnection=newSpannerConnection(connectionString);usingvarupdateCmd=connection.CreateDdlCommand(alterStatement);awaitupdateCmd.ExecuteNonQueryAsync();Console.WriteLine("Added the MarketingBudget column.");}}

Run the sample using theaddColumn command.

dotnetrunaddColumn$env:GOOGLE_PROJECT_IDtest-instanceexample-db

You should see:

AddedtheMarketingBudgetcolumn.

Write data to the new column

The following code writes data to the new column. It setsMarketingBudget to100000 for the row keyed byAlbums(1, 1) and to500000 for the row keyedbyAlbums(2, 2).

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassUpdateDataAsyncSample{publicasyncTask<int>UpdateDataAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);varrowCount=0;SpannerCommandcmd=connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = @MarketingBudget "+"WHERE SingerId = 1 and AlbumId = 1");cmd.Parameters.Add("MarketingBudget",SpannerDbType.Int64,100000);rowCount+=awaitcmd.ExecuteNonQueryAsync();cmd=connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = @MarketingBudget "+"WHERE SingerId = 2 and AlbumId = 2");cmd.Parameters.Add("MarketingBudget",SpannerDbType.Int64,500000);rowCount+=awaitcmd.ExecuteNonQueryAsync();Console.WriteLine("Data Updated.");returnrowCount;}}

Run the sample using thewriteDataToNewColumn command.

dotnetrunwriteDataToNewColumn$env:GOOGLE_PROJECT_IDtest-instanceexample-db

You should see:

Updateddata.

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

Here's the code to execute the query:

usingGoogle.Cloud.Spanner.Data;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;publicclassQueryNewColumnAsyncSample{publicclassAlbum{publicintSingerId{get;set;}publicintAlbumId{get;set;}publiclongMarketingBudget{get;set;}}publicasyncTask<List<Album>>QueryNewColumnAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";varalbums=newList<Album>();usingvarconnection=newSpannerConnection(connectionString);usingvarcmd=connection.CreateSelectCommand("SELECT * FROM Albums");usingvarreader=awaitcmd.ExecuteReaderAsync();while(awaitreader.ReadAsync()){albums.Add(newAlbum{SingerId=reader.GetFieldValue<int>("SingerId"),AlbumId=reader.GetFieldValue<int>("AlbumId"),MarketingBudget=reader.IsDBNull(reader.GetOrdinal("MarketingBudget"))?0:reader.GetFieldValue<long>("MarketingBudget")});}returnalbums;}}

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

dotnetrunqueryNewColumn$env:GOOGLE_PROJECT_IDtest-instanceexample-db

You should see:

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

Update data

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

You use theExecuteNonQueryAsync() method to execute a DML statement.

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassWriteWithTransactionUsingDmlCoreAsyncSample{publicasyncTask<int>WriteWithTransactionUsingDmlCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){// 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.stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";decimaltransferAmount=200000;decimalsecondBudget=0;// Create connection to Cloud Spanner.usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();// Create a readwrite transaction that we'll assign// to each SpannerCommand.usingvartransaction=awaitconnection.BeginTransactionAsync();// Create statement to select the second album's data.varcmdLookup=connection.CreateSelectCommand("SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");cmdLookup.Transaction=transaction;// Execute the select query.usingvarreader1=awaitcmdLookup.ExecuteReaderAsync();while(awaitreader1.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){thrownewException($"The second album's budget {secondBudget} is less than the amount to transfer.");}}// Update second album to remove the transfer amount.secondBudget-=transferAmount;SpannerCommandcmd=connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = @MarketingBudget  WHERE SingerId = 2 and AlbumId = 2");cmd.Parameters.Add("MarketingBudget",SpannerDbType.Int64,secondBudget);cmd.Transaction=transaction;varrowCount=awaitcmd.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+=awaitcmd.ExecuteNonQueryAsync();awaittransaction.CommitAsync();Console.WriteLine("Transaction complete.");returnrowCount;}}

Run the sample using thewriteWithTransactionUsingDml argument.

dotnetrunwriteWithTransactionUsingDml$env:GOOGLE_PROJECT_IDtest-instanceexample-db

You should see:

Transactioncomplete.
Note: You can alsoupdate data using mutations.

Use a secondary index

Suppose you wanted to fetch all rows ofAlbums that haveAlbumTitle valuesin a certain range. You could read all values from theAlbumTitle column usinga SQL statement or a read call, and then discard the rows that don't meet thecriteria, but doing this full table scan is expensive, especially for tableswith a lot of rows. Instead you can speed up the retrieval of rows whensearching by non-primary key columns by creating asecondary index on the table.

Adding a secondary index to an existing table requires a schema update. Likeother schema updates, Spanner supports adding an index while thedatabase continues to serve traffic. Spanner automatically backfills theindex 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 indexedtable during this process. For more details, seeAdd a secondary index.

After you add a secondary index, Spanner automatically uses it forSQL queries that are likely to run faster with the index. If you use the readinterface, 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 orprogrammatically using the Spanner client library for C#.

On the command line

Use the followingCREATE INDEX commandto add an index to the database:

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)'

You should see:

Schemaupdating...done.

Using the Spanner client library for C#

UseCreateDdlCommand() to add an index:

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassAddIndexAsyncSample{publicasyncTaskAddIndexAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";stringcreateStatement="CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)";usingvarconnection=newSpannerConnection(connectionString);usingvarcreateCmd=connection.CreateDdlCommand(createStatement);awaitcreateCmd.ExecuteNonQueryAsync();Console.WriteLine("Added the AlbumsByAlbumTitle index.");}}

Run the sample using theaddIndex command.

dotnetrunaddIndex$env:GOOGLE_PROJECT_IDtest-instanceexample-db

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

AddedtheAlbumsByAlbumTitleindex.

Add an index for index-only reads

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

Create an alternate definition ofAlbumsByAlbumTitle that stores a copy ofMarketingBudget in the index.

On the command line

GoogleSQL

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='CREATEINDEXAlbumsByAlbumTitle2ONAlbums(AlbumTitle)STORING(MarketingBudget)

PostgreSQL

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='CREATEINDEXAlbumsByAlbumTitle2ONAlbums(AlbumTitle)INCLUDE(MarketingBudget)

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

Schemaupdating...done.

Using the Spanner client library for C#

UseCreateDdlCommand()to add an index with aSTORING clause:

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassAddStoringIndexAsyncSample{publicasyncTaskAddStoringIndexAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";stringcreateStatement="CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)";usingvarconnection=newSpannerConnection(connectionString);usingvarcreateCmd=connection.CreateDdlCommand(createStatement);awaitcreateCmd.ExecuteNonQueryAsync();Console.WriteLine("Added the AlbumsByAlbumTitle2 index.");}}

Run the sample using theaddStoringIndex command.

dotnetrunaddStoringIndex$env:GOOGLE_PROJECT_IDtest-instanceexample-db

You should see:

AddedtheAlbumsByAlbumTitle2index.

Now you can execute a read that fetches allAlbumId,AlbumTitle, andMarketingBudget columns from theAlbumsByAlbumTitle2 index:

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

usingGoogle.Cloud.Spanner.Data;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;publicclassQueryDataWithStoringIndexAsyncSample{publicclassAlbum{publicintAlbumId{get;set;}publicstringAlbumTitle{get;set;}publiclong?MarketingBudget{get;set;}}publicasyncTask<List<Album>>QueryDataWithStoringIndexAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);varcmd=connection.CreateSelectCommand("SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "+"{FORCE_INDEX=AlbumsByAlbumTitle2}");varalbums=newList<Album>();usingvarreader=awaitcmd.ExecuteReaderAsync();while(awaitreader.ReadAsync()){albums.Add(newAlbum{AlbumId=reader.GetFieldValue<int>("AlbumId"),AlbumTitle=reader.GetFieldValue<string>("AlbumTitle"),MarketingBudget=reader.IsDBNull(reader.GetOrdinal("MarketingBudget"))?0:reader.GetFieldValue<long>("MarketingBudget")});}returnalbums;}}

Run the sample using thequeryDataWithStoringIndex command.

dotnetrunqueryDataWithStoringIndex$env:GOOGLE_PROJECT_IDtest-instanceexample-db

You should see output similar to:

AlbumId:2AlbumTitle:ForeverHoldyourPeaceMarketingBudget:300000AlbumId:2AlbumTitle:Go,Go,GoMarketingBudget:300000

Retrieve data using read-only transactions

Suppose you want to execute more than one read at the same timestamp.Read-onlytransactions observe a consistentprefix of the transaction commit history, so your application always getsconsistent data.Use the .NET framework'sTransactionScope()along withOpenAsReadOnlyAsync()for executing read-only transactions.

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

.NET Standard 2.0

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;usingSystem.Transactions;publicclassQueryDataWithTransactionAsyncSample{publicclassAlbum{publicintSingerId{get;set;}publicintAlbumId{get;set;}publicstringAlbumTitle{get;set;}}publicasyncTask<List<Album>>QueryDataWithTransactionAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";varalbums=newList<Album>();usingTransactionScopescope=newTransactionScope(TransactionScopeAsyncFlowOption.Enabled);usingvarconnection=newSpannerConnection(connectionString);// Opens the connection so that the Spanner transaction included in the TransactionScope// is read-only TimestampBound.Strong.awaitconnection.OpenAsync(SpannerTransactionCreationOptions.ReadOnly,options:null,cancellationToken:default);usingvarcmd=connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");// Read #1.using(varreader=awaitcmd.ExecuteReaderAsync()){while(awaitreader.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(varreader=awaitcmd.ExecuteReaderAsync()){while(awaitreader.ReadAsync()){albums.Add(newAlbum{AlbumId=reader.GetFieldValue<int>("AlbumId"),SingerId=reader.GetFieldValue<int>("SingerId"),AlbumTitle=reader.GetFieldValue<string>("AlbumTitle")});}}scope.Complete();Console.WriteLine("Transaction complete.");returnalbums;}}

.NET Standard 1.5

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;publicclassQueryDataWithTransactionCoreAsyncSample{publicclassAlbum{publicintSingerId{get;set;}publicintAlbumId{get;set;}publicstringAlbumTitle{get;set;}}publicasyncTask<List<Album>>QueryDataWithTransactionCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";varalbums=newList<Album>();usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();// Open a new read only transaction.usingvartransaction=awaitconnection.BeginTransactionAsync(SpannerTransactionCreationOptions.ReadOnly,transactionOptions:null,cancellationToken:default);usingvarcmd=connection.CreateSelectCommand("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");cmd.Transaction=transaction;// Read #1.using(varreader=awaitcmd.ExecuteReaderAsync()){while(awaitreader.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(varreader=awaitcmd.ExecuteReaderAsync()){while(awaitreader.ReadAsync()){albums.Add(newAlbum{AlbumId=reader.GetFieldValue<int>("AlbumId"),SingerId=reader.GetFieldValue<int>("SingerId"),AlbumTitle=reader.GetFieldValue<string>("AlbumTitle")});}}Console.WriteLine("Transaction complete.");returnalbums;}}

Run the sample using thequeryDataWithTransaction command.

dotnetrunqueryDataWithTransaction$env:GOOGLE_PROJECT_IDtest-instanceexample-db

You should see output similar to:

SingerId:2AlbumId:2AlbumTitle:ForeverHoldyourPeaceSingerId:1AlbumId:2AlbumTitle:Go,Go,GoSingerId:2AlbumId:1AlbumTitle:GreenSingerId:2AlbumId:3AlbumTitle:TerrifiedSingerId:1AlbumId:1AlbumTitle:TotalJunkSingerId:2AlbumId:2AlbumTitle:ForeverHoldyourPeaceSingerId:1AlbumId:2AlbumTitle:Go,Go,GoSingerId:2AlbumId:1AlbumTitle:GreenSingerId:2AlbumId:3AlbumTitle:TerrifiedSingerId:1AlbumId:1AlbumTitle:TotalJunk

Cleanup

To avoid incurring additional charges to your Cloud Billing account for theresources used in this tutorial, drop the database and delete the instance thatyou 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 wouldstill incur charges for the instance).

On the command line

gcloudspannerdatabasesdeleteexample-db--instance=test-instance

Using the Google Cloud console

  1. Go to theSpanner Instances page in the Google Cloud console.

    Go to the Instances page

  2. Click the instance.

  3. Click the database that you want to delete.

  4. In theDatabase details page, clickDelete.

  5. Confirm that you want to delete the database and clickDelete.

Delete the instance

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

On the command line

gcloudspannerinstancesdeletetest-instance

Using the Google Cloud console

  1. Go to theSpanner Instances page in the Google Cloud console.

    Go to the Instances page

  2. Click your instance.

  3. ClickDelete.

  4. Confirm that you want to delete the instance and clickDelete.

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-09 UTC.