Prepare your local ADO.NET environment

1. Download and install .NET on your development machine if it isn't already installed.

2. Clone the sample repository to your local machine:

   git clone https://github.com/googleapis/dotnet-spanner-entity-framework.git
   

3. Change to the directory that contains the Spanner ADO.NET driver sample code:

   cd dotnet-spanner-entity-framework/spanner-ado-net/spanner-ado-net-getting-started-guide
   

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 ADO.NET.

Take a look through the SampleRunner.cs file, which shows how to use Spanner. The code shows how to create and use a new database. The data uses the example schema shown in the Schema and data model page.

Create a database

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

gcloud spanner databases create example-db --instance=test-instance \
  --database-dialect=POSTGRESQL

You should see:

Creating database...done.

Create tables

The following code creates two tables in the database.

Run the sample with the following command:

dotnet run createtables projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run createtablespg projects/PROJECT_ID/instances/test-instance/databases/example-db

The next step is to write data to your database.

Create a connection

Before you can do reads or writes, you must create a connection to interact with Spanner. The database name and other connection properties are specified in the ADO.NET connection string.

Write data with DML

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

You use the DbCommand#ExecuteNonQuery method to execute a DML statement.

Run the sample with the following command:

dotnet run dmlwrite projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run dmlwritepg projects/PROJECT_ID/instances/test-instance/databases/example-db

The result should show:

4 records inserted.

Write data with mutations

You can also insert data using mutations.

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

The following code shows how to write data using mutations:

Run the following example using the write argument:

dotnet run write projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run writepg projects/PROJECT_ID/instances/test-instance/databases/example-db

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 ADO.NET driver.

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'

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql='SELECT singer_id, album_id, album_title 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 ADO.NET driver

In addition to executing a SQL statement on the command line, you can issue the same SQL statement programmatically using the Spanner ADO.NET driver.

The following methods are used to execute a SQL query:

• The ExecuteReader method in the DbCommand class: use this to execute a SQL statement that returns rows, such as a query or a DML statement with a THEN RETURN clause.
• The DbDataReader class: use this to access the data returned by a SQL statement.

The following example uses the ExecuteReaderAsync method:

Run the example with the following command:

dotnet run query projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run querypg projects/PROJECT_ID/instances/test-instance/databases/example-db

The result should show:

1 1 Total Junk
1 2 Go, Go, Go
2 1 Green
2 2 Forever Hold Your Peace
2 3 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.

The Spanner ADO.NET driver supports both positional and named query parameters. A ? in a SQL statement indicates a positional query parameter. Add query parameter values to the Parameters of the DbCommand. For example:

Run the example with the following command:

dotnet run querywithparameter projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run querywithparameterpg projects/PROJECT_ID/instances/test-instance/databases/example-db

The result shows:

12 Melissa 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 ADO.NET driver.

On the command line

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

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

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='alter table albums add column marketing_budget bigint'

You should see:

Schema updating...done.

Use the Spanner ADO.NET driver

Use the ExecuteNonQueryAsync method to modify the schema:

Run the example with the following command:

dotnet run addcolumn projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run addcolumnpg projects/PROJECT_ID/instances/test-instance/databases/example-db

The result shows:

Added MarketingBudget column.

Execute a DDL batch

We recommend that you execute multiple schema modifications in one batch. Use the ADO.NET CreateBatch method to create a batch. The following example creates two tables in one batch:

Run the example with the following command:

dotnet run ddlbatch projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run ddlbatchpg projects/PROJECT_ID/instances/test-instance/databases/example-db

The result shows:

Added Venues and Concerts tables.

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 example with the following command:

dotnet run update projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run updatepg projects/PROJECT_ID/instances/test-instance/databases/example-db

The result shows:

Updated 2 albums

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

The following example uses the ExecuteReaderAsync method to execute a query:

To execute this query, run the following command:

dotnet run querymarketingbudget projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run querymarketingbudgetpg projects/PROJECT_ID/instances/test-instance/databases/example-db

You should see:

1 1 100000
1 2 null
2 1 null
2 2 500000
2 3 null

Update data

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

Call connection.BeginTransactionAsync() to execute read-write transactions in ADO.NET.

Run the example with the following command:

dotnet run writewithtransactionusingdml projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run writewithtransactionusingdmlpg projects/PROJECT_ID/instances/test-instance/databases/example-db

Transaction tags and request tags

Use transaction tags and request tags to troubleshoot transactions and queries in Spanner. You can set tags on Transaction objects to send transaction tags, and DbCommand objects to send request tags to Spanner. For example:

Run the example with the following command:

dotnet run tags projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run tagspg projects/PROJECT_ID/instances/test-instance/databases/example-db

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. Call connection.BeginReadOnlyTransactionAsync() to execute a read-only transaction.

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

Run the example with the following command:

dotnet run readonlytransaction projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run readonlytransactionpg projects/PROJECT_ID/instances/test-instance/databases/example-db

The result shows:

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

Partitioned DML

Partitioned Data Manipulation Language (DML) is designed for the following types of bulk updates and deletes:

• Periodic cleanup and garbage collection.
• Backfilling new columns with default values.

Run the example with the following command:

dotnet run pdml projects/PROJECT_ID/instances/test-instance/databases/example-db

dotnet run pdmlpg projects/PROJECT_ID/instances/test-instance/databases/example-db

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

1. Go to the Spanner 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 the Database details page, click Delete.

5. 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

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

   Go to the Instances page

2. Click your instance.

3. Click Delete.

4. Confirm that you want to delete the instance and click Delete.