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_IDenvironment variable to your Google Cloud project ID.-
First, set
GOOGLE_PROJECT_IDfor the current PowerShell session:$env :GOOGLE_PROJECT_ID = " MY_PROJECT_ID "
-
Then, set
GOOGLE_PROJECT_IDfor all processes created after this command:[ Environment ] ::SetEnvironmentVariable ( "GOOGLE_PROJECT_ID" , " MY_PROJECT_ID " , "User" )
-
-
Download credentials.
-
Go to the Credentialspage in the Google Cloud console.
-
Click Create credentialsand choose Service account key.
-
Under "Service account", choose Compute Engine default service account, and leave JSONselected under "Key type". Click Create. Your computer downloads a JSON file.
-
-
Set up credentials. For a file named
FILENAME .jsoninCURRENT_USER's Downloads directory, located on theCdrive, run the following commands to setGOOGLE_APPLICATION_CREDENTIALSto point to the JSON key:-
First, to set
GOOGLE_APPLICATION_CREDENTIALSfor this PowerShell session:$env :GOOGLE_APPLICATION_CREDENTIALS = " C :\Users\ CURRENT_USER \Downloads\ FILENAME .json"
-
Then, to set
GOOGLE_APPLICATION_CREDENTIALSfor 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-samplesAlternatively, you can download the sample as a zip file and extract it.
-
Open
Spanner.sln, located in thedotnet-docs-samples\spanner\apidirectory 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.
See Create an instance
to learn how to create a Spanner instance using any of the
following methods. You can name your instance test-instance
to use it with
other topics in this document that reference an instance named test-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 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
PostgreSQL
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
:
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.
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:
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 shows:
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.
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
.
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#
UseCreateDdlCommand()
to modify the schema:
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)
.
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:
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.
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:
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#
UseCreateDdlCommand()
to add an index with a STORING
clause:
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:
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
.NET Standard 1.5
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 Instancespage in the Google Cloud console.
-
Click the instance.
-
Click the database that you want to delete.
-
In the Database detailspage, 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 Instancespage in the Google Cloud console.
-
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 .
