開始使用 ADO.NET 中的 Spanner

目標

本教學課程將逐步引導您使用 Spanner ADO.NET 驅動程式完成下列步驟：

  • 建立 Spanner 執行個體和資料庫。
  • 對資料庫中的資料進行寫入和讀取，以及執行 SQL 查詢。
  • 更新資料庫結構定義。
  • 使用讀取/寫入交易來更新資料。
  • 將次要索引新增至資料庫。
  • 使用索引對資料執行讀取作業和 SQL 查詢。
  • 使用唯讀交易擷取資料。

費用

本教學課程使用 Spanner，這是Google Cloud的計費元件。如要瞭解 Spanner 的使用費用，請參閱「定價」。

事前準備

完成「設定」一節所述的步驟，包括建立及設定預設 Google Cloud 專案、啟用計費功能、啟用 Cloud Spanner API，以及設定 OAuth 2.0，以取得使用 Cloud Spanner API 的驗證憑證。

請務必執行 gcloud auth application-default login，使用驗證憑證設定本機開發環境。

準備本機 ADO.NET 環境

  1. 如果開發機器尚未安裝 .NET，請下載並安裝。

  2. 將範例存放區複製到本機電腦中：

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

  3. 變更為包含 Spanner ADO.NET 驅動程式範例程式碼的目錄：

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

建立執行個體

首次使用 Spanner 時，必須建立執行個體，也就是 Spanner 資料庫所用的資源分配單位。建立執行個體時，請選擇「執行個體設定」以決定資料儲存的位置，再選擇要使用的節點數量以決定執行個體的服務和儲存空間資源量。

請參閱「建立執行個體」，瞭解如何使用下列任一方法建立 Spanner 執行個體。您可以將執行個體命名為 test-instance，以便與本文件中參照名為 test-instance 的執行個體的其他主題搭配使用。

  • Google Cloud CLI
  • Google Cloud 控制台
  • 用戶端程式庫 (C++、C#、Go、Java、Node.js、PHP、Python、Ruby 或 Rust)

瀏覽範例檔案

範例存放區包含一個範例，說明如何搭配使用 Spanner 和 ADO.NET。

查看 SampleRunner.cs 檔案，瞭解如何使用 Spanner。該檔案中的程式碼會顯示如何建立及使用新資料庫。資料會使用「結構定義與資料模型」頁面中顯示的範例結構定義。

建立資料庫

GoogleSQL 

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

PostgreSQL

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

如下所示：

Creating database...done.

製作表格

下列程式碼會在資料庫中建立兩個資料表。

GoogleSQL 

public static async Task CreateTables(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Create two tables in one batch on Spanner.
    var batch = connection.CreateBatch();
    batch.BatchCommands.Add("CREATE TABLE Singers (" +
                            "  SingerId   INT64 NOT NULL, " +
                            "  FirstName  STRING(1024), " +
                            "  LastName   STRING(1024), " +
                            "  SingerInfo BYTES(MAX) " +
                            ") PRIMARY KEY (SingerId)");
    batch.BatchCommands.Add("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");
    await batch.ExecuteNonQueryAsync();
    Console.WriteLine("Created Singers & Albums tables");
}

PostgreSQL

public static async Task CreateTables(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Create two tables in one batch on Spanner.
    var batch = connection.CreateBatch();
    batch.BatchCommands.Add("create table singers (" +
                            "  singer_id   bigint not null primary key, " +
                            "  first_name  varchar(1024), " +
                            "  last_name   varchar(1024), " +
                            "  singer_info bytea" +
                            ")");
    batch.BatchCommands.Add("create table albums (" +
                            "  singer_id     bigint not null, " +
                            "  album_id      bigint not null, " +
                            "  album_title   varchar, " +
                            "  primary key (singer_id, album_id)" +
                            ") interleave in parent singers on delete cascade");
    await batch.ExecuteNonQueryAsync();
    Console.WriteLine("Created Singers & Albums tables");
}

使用下列指令執行範例：

GoogleSQL 

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

PostgreSQL

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

下一個步驟是將資料寫入資料庫。

建立連線

您必須先建立連線，才能與 Spanner 互動，執行讀取或寫入作業。資料庫名稱和其他連線屬性是在 ADO.NET 連線字串中指定。

GoogleSQL 

/// <summary>
/// Create an ADO.NET connection to a Spanner database.
/// </summary>
/// <param name="connectionString">
/// A connection string in the format
/// 'Data Source=projects/my-project/instances/my-instance/databases/my-database'.
/// </param>
public static async Task CreateConnection(string connectionString)
{
    // Use a SpannerConnectionStringBuilder to construct a connection string.
    // The SpannerConnectionStringBuilder contains properties for the most
    // used connection string variables.
    var builder = new SpannerConnectionStringBuilder(connectionString)
    {
        // Sets the default isolation level that should be used for all
        // read/write transactions on this connection.
        DefaultIsolationLevel = IsolationLevel.RepeatableRead,

        // The Options property can be used to set any connection property
        // as a key-value pair.
        Options = "statement_cache_size=2000"
    };

    await using var connection = new SpannerConnection(builder.ConnectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT 'Hello World' as Message";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"Greeting from Spanner: {reader.GetString(0)}");
    }
}

PostgreSQL

/// <summary>
/// Create an ADO.NET connection to a Spanner PostgreSQL database.
/// </summary>
/// <param name="connectionString">
/// A connection string in the format
/// 'Data Source=projects/my-project/instances/my-instance/databases/my-database'.
/// </param>
public static async Task CreateConnection(string connectionString)
{
    // Use a SpannerConnectionStringBuilder to construct a connection string.
    // The SpannerConnectionStringBuilder contains properties for the most
    // used connection string variables.
    var builder = new SpannerConnectionStringBuilder(connectionString)
    {
        // Sets the default isolation level that should be used for all
        // read/write transactions on this connection.
        DefaultIsolationLevel = IsolationLevel.RepeatableRead,

        // The Options property can be used to set any connection property
        // as a key-value pair.
        Options = "statement_cache_size=2000"
    };

    await using var connection = new SpannerConnection(builder.ConnectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT 'Hello World' as Message";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"Greeting from Spanner: {reader.GetString(0)}");
    }
}

使用 DML 寫入資料

您可以在讀寫交易中使用 data manipulation language (DML) 插入資料。

您可以使用 DbCommand#ExecuteNonQuery 方法執行 DML 陳述式。

GoogleSQL 

public static async Task WriteDataWithDml(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Add 4 rows in one statement.
    // The ADO.NET driver supports positional query parameters.
    await using var command = connection.CreateCommand();
    command.CommandText = "INSERT INTO Singers (SingerId, FirstName, LastName) " +
                          "VALUES (?, ?, ?), (?, ?, ?), " +
                          "       (?, ?, ?), (?, ?, ?)";
    command.Parameters.Add(12);
    command.Parameters.Add("Melissa");
    command.Parameters.Add("Garcia");

    command.Parameters.Add(13);
    command.Parameters.Add("Russel");
    command.Parameters.Add("Morales");

    command.Parameters.Add(14);
    command.Parameters.Add("Jacqueline");
    command.Parameters.Add("Long");

    command.Parameters.Add(15);
    command.Parameters.Add("Dylan");
    command.Parameters.Add("Shaw");

    var affected = await command.ExecuteNonQueryAsync();
    Console.WriteLine($"{affected} record(s) inserted.");
}

PostgreSQL

public static async Task WriteDataWithDml(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Add 4 rows in one statement.
    // The ADO.NET driver supports positional query parameters.
    await using var command = connection.CreateCommand();
    command.CommandText = "insert into singers (singer_id, first_name, last_name) " +
                          "VALUES (?, ?, ?), (?, ?, ?), " +
                          "       (?, ?, ?), (?, ?, ?)";
    command.Parameters.Add(12);
    command.Parameters.Add("Melissa");
    command.Parameters.Add("Garcia");

    command.Parameters.Add(13);
    command.Parameters.Add("Russel");
    command.Parameters.Add("Morales");

    command.Parameters.Add(14);
    command.Parameters.Add("Jacqueline");
    command.Parameters.Add("Long");

    command.Parameters.Add(15);
    command.Parameters.Add("Dylan");
    command.Parameters.Add("Shaw");

    var affected = await command.ExecuteNonQueryAsync();
    Console.WriteLine($"{affected} record(s) inserted.");
}

使用下列指令執行範例：

GoogleSQL 

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

PostgreSQL

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

結果應顯示：

4 records inserted.

使用變異寫入資料

您也可以使用變異來插入資料。

您可以使用 batch.CreateInsertCommand() 方法插入資料，這會建立新的 SpannerBatchCommand，將資料列插入資料表。SpannerBatchCommand.ExecuteNonQueryAsync() 方法會在資料表中新增資料列。

下列程式碼說明如何使用異動寫入資料：

GoogleSQL 

struct Singer
{
    internal long SingerId;
    internal string FirstName;
    internal string LastName;
}

struct Album
{
    internal long SingerId;
    internal long AlbumId;
    internal string Title;
}

public static async Task WriteDataWithMutations(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    Singer[] singers =
    [
        new() {SingerId=1, FirstName = "Marc", LastName = "Richards"},
        new() {SingerId=2, FirstName = "Catalina", LastName = "Smith"},
        new() {SingerId=3, FirstName = "Alice", LastName = "Trentor"},
        new() {SingerId=4, FirstName = "Lea", LastName = "Martin"},
        new() {SingerId=5, FirstName = "David", LastName = "Lomond"},
    ];
    Album[] albums =
    [
        new() {SingerId = 1, AlbumId = 1, Title = "Total Junk"},
        new() {SingerId = 1, AlbumId = 2, Title = "Go, Go, Go"},
        new() {SingerId = 2, AlbumId = 1, Title = "Green"},
        new() {SingerId = 2, AlbumId = 2, Title = "Forever Hold Your Peace"},
        new() {SingerId = 2, AlbumId = 3, Title = "Terrified"},
    ];
    var batch = connection.CreateBatch();
    foreach (var singer in singers)
    {
        // The name of a parameter must correspond with a column name.
        var command = batch.CreateInsertCommand("Singers");
        command.AddParameter("SingerId", singer.SingerId);
        command.AddParameter("FirstName", singer.FirstName);
        command.AddParameter("LastName", singer.LastName);
        batch.BatchCommands.Add(command);
    }
    foreach (var album in albums)
    {
        // The name of a parameter must correspond with a column name.
        var command = batch.CreateInsertCommand("Albums");
        command.AddParameter("SingerId", album.SingerId);
        command.AddParameter("AlbumId", album.AlbumId);
        command.AddParameter("AlbumTitle", album.Title);
        batch.BatchCommands.Add(command);
    }
    var affected = await batch.ExecuteNonQueryAsync();
    Console.WriteLine($"Inserted {affected} rows.");
}

PostgreSQL

struct Singer
{
    internal long SingerId;
    internal string FirstName;
    internal string LastName;
}

struct Album
{
    internal long SingerId;
    internal long AlbumId;
    internal string Title;
}

public static async Task WriteDataWithMutations(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    Singer[] singers =
    [
        new() {SingerId=1, FirstName = "Marc", LastName = "Richards"},
        new() {SingerId=2, FirstName = "Catalina", LastName = "Smith"},
        new() {SingerId=3, FirstName = "Alice", LastName = "Trentor"},
        new() {SingerId=4, FirstName = "Lea", LastName = "Martin"},
        new() {SingerId=5, FirstName = "David", LastName = "Lomond"},
    ];
    Album[] albums =
    [
        new() {SingerId = 1, AlbumId = 1, Title = "Total Junk"},
        new() {SingerId = 1, AlbumId = 2, Title = "Go, Go, Go"},
        new() {SingerId = 2, AlbumId = 1, Title = "Green"},
        new() {SingerId = 2, AlbumId = 2, Title = "Forever Hold Your Peace"},
        new() {SingerId = 2, AlbumId = 3, Title = "Terrified"},
    ];
    var batch = connection.CreateBatch();
    foreach (var singer in singers)
    {
        // The name of a parameter must correspond with a column name.
        var command = batch.CreateInsertCommand("singers");
        command.AddParameter("singer_id", singer.SingerId);
        command.AddParameter("first_name", singer.FirstName);
        command.AddParameter("last_name", singer.LastName);
        batch.BatchCommands.Add(command);
    }
    foreach (var album in albums)
    {
        // The name of a parameter must correspond with a column name.
        var command = batch.CreateInsertCommand("albums");
        command.AddParameter("singer_id", album.SingerId);
        command.AddParameter("album_id", album.AlbumId);
        command.AddParameter("album_title", album.Title);
        batch.BatchCommands.Add(command);
    }
    var affected = await batch.ExecuteNonQueryAsync();
    Console.WriteLine($"Inserted {affected} rows.");
}

使用 write 引數執行下列範例：

GoogleSQL 

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

PostgreSQL

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

使用 SQL 查詢資料

Spanner 支援讀取資料的 SQL 介面，您可以使用 Google Cloud CLI 在指令列上存取，或使用 Spanner ADO.NET 驅動程式以程式輔助方式存取。

使用指令列

執行下列 SQL 陳述式，讀取 Albums 資料表中所有資料欄的值：

GoogleSQL 

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

GoogleSQL 管道語法

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

PostgreSQL

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql='SELECT singer_id, album_id, album_title FROM albums'

結果顯示：

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

使用 Spanner ADO.NET 驅動程式

除了在指令列執行 SQL 陳述式，您也可以使用 Spanner ADO.NET 驅動程式，透過程式輔助方式發出相同的 SQL 陳述式。

下列方法可用於執行 SQL 查詢：

  • DbCommand 類別中的 ExecuteReader 方法：用於執行會傳回資料列的 SQL 陳述式，例如查詢或含有 THEN RETURN 子句的 DML 陳述式。
  • DbDataReader 類別：用於存取 SQL 陳述式傳回的資料。

以下範例使用 ExecuteReaderAsync 方法：

GoogleSQL 

public static async Task QueryData(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT SingerId, AlbumId, AlbumTitle " +
                          "FROM Albums " +
                          "ORDER BY SingerId, AlbumId";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["SingerId"]} {reader["AlbumId"]} {reader["AlbumTitle"]}");
    }
}

PostgreSQL

public static async Task QueryData(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT singer_id, album_id, album_title " +
                          "FROM albums " +
                          "ORDER BY singer_id, album_id";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["singer_id"]} {reader["album_id"]} {reader["album_title"]}");
    }
}

使用下列指令執行範例：

GoogleSQL 

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

PostgreSQL

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

結果應顯示：

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

使用 SQL 參數查詢

如果應用程式經常執行某個查詢，您可以將其參數化，以提升效能。產生的參數化查詢可以快取並重複使用，進而降低編譯成本。詳情請參閱「使用查詢參數加快頻繁執行的查詢」。

以下範例說明如何在 WHERE 子句中使用參數，查詢包含特定 LastName 值的記錄。

Spanner ADO.NET 驅動程式支援位置和具名查詢參數。SQL 陳述式中的 ? 表示位置查詢參數。將查詢參數值新增至 DbCommand 的 Parameters。例如：

GoogleSQL 

public static async Task QueryDataWithParameter(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT SingerId, FirstName, LastName " +
                          "FROM Singers " +
                          "WHERE LastName = ?";
    command.Parameters.Add("Garcia");
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["SingerId"]} {reader["FirstName"]} {reader["LastName"]}");
    }
}

PostgreSQL

public static async Task QueryDataWithParameter(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT singer_id, first_name, last_name " +
                          "FROM singers " +
                          "WHERE last_name = ?";
    command.Parameters.Add("Garcia");
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["singer_id"]} {reader["first_name"]} {reader["last_name"]}");
    }
}

使用下列指令執行範例：

GoogleSQL 

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

PostgreSQL

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

結果顯示：

12 Melissa Garcia

更新資料庫結構定義

假設您需要在 Albums 資料表中新增名為 MarketingBudget 的資料欄。必須先更新資料庫結構定義，才能新增新資料欄到現有的資料表。Spanner 可支援更新資料庫結構定義，同時讓資料庫持續處理流量。結構定義更新作業不需要讓資料庫離線，也不會鎖定整個資料表或資料欄；您可以在結構定義更新期間，持續將資料寫入資料庫。如要進一步瞭解支援的結構定義更新和結構定義變更效能，請參閱「更新結構定義」。

新增資料欄

您可以使用 Google Cloud CLI 在指令列中新增資料欄，或使用 Spanner ADO.NET 驅動程式，透過程式輔助方式新增資料欄。

使用指令列

使用下列 ALTER 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 marketing_budget bigint'

如下所示：

Schema updating...done.

使用 Spanner ADO.NET 驅動程式

使用 ExecuteNonQueryAsync 方法修改結構定義：

GoogleSQL 

public static async Task AddColumn(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64";
    await command.ExecuteNonQueryAsync();

    Console.WriteLine("Added MarketingBudget column");
}

PostgreSQL

public static async Task AddColumn(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "alter table albums add column marketing_budget bigint";
    await command.ExecuteNonQueryAsync();

    Console.WriteLine("Added marketing_budget column");
}

使用下列指令執行範例：

GoogleSQL 

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

PostgreSQL

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

結果顯示：

Added MarketingBudget column.

執行 DDL 批次

建議您在一個批次中執行多項結構定義修改作業。使用 ADO.NET CreateBatch 方法建立批次。以下範例會在一個批次中建立兩個資料表：

GoogleSQL 

public static async Task DdlBatch(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Executing multiple DDL statements as one batch is
    // more efficient than executing each statement individually.
    var batch = connection.CreateBatch();
    batch.BatchCommands.Add(
        "CREATE TABLE Venues (" +
        "  VenueId     INT64 NOT NULL, " +
        "  Name        STRING(1024), " +
        "  Description JSON, " +
        ") PRIMARY KEY (VenueId)");
    batch.BatchCommands.Add(
        "CREATE TABLE Concerts (" +
        "  ConcertId INT64 NOT NULL, " +
        "  VenueId   INT64 NOT NULL, " +
        "  SingerId  INT64 NOT NULL, " +
        "  StartTime TIMESTAMP, " +
        "  EndTime   TIMESTAMP, " +
        "  CONSTRAINT Fk_Concerts_Venues " +
        "    FOREIGN KEY (VenueId) REFERENCES Venues (VenueId), " +
        "  CONSTRAINT Fk_Concerts_Singers " +
        "    FOREIGN KEY (SingerId) REFERENCES Singers (SingerId), " +
        ") PRIMARY KEY (ConcertId)");
    await batch.ExecuteNonQueryAsync();

    Console.WriteLine("Added Venues and Concerts tables");
}

PostgreSQL

public static async Task DdlBatch(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Executing multiple DDL statements as one batch is
    // more efficient than executing each statement individually.
    var batch = connection.CreateBatch();
    batch.BatchCommands.Add(
        "create table venues (" +
        "  venue_id    bigint not null primary key, " +
        "  name        varchar(1024), " +
        "  description jsonb" +
        ")");
    batch.BatchCommands.Add(
        "create table concerts (" +
        "  concert_id bigint not null primary key, " +
        "  venue_id   bigint not null, " +
        "  singer_id  bigint not null, " +
        "  start_time timestamptz, " +
        "  end_time   timestamptz, " +
        "  constraint fk_concerts_venues foreign key " +
        "    (venue_id) references venues (venue_id), " +
        "  constraint fk_concerts_singers foreign key " +
        "    (singer_id) references singers (singer_id)" +
        ")");
    await batch.ExecuteNonQueryAsync();

    Console.WriteLine("Added Venues and Concerts tables");
}

使用下列指令執行範例：

GoogleSQL 

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

PostgreSQL

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

結果顯示：

Added Venues and Concerts tables.

將資料寫入新資料欄

以下程式碼會將資料寫入新資料欄，這會將索引鍵為 Albums(1, 1) 的資料列 MarketingBudget 設為 100000，並將索引鍵為 Albums(2, 2) 的資料列設為 500000。

GoogleSQL 

public static async Task UpdateDataWithMutations(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    (long SingerId, long AlbumId, long MarketingBudget)[] albums = [
        (1L, 1L, 100000L),
        (2L, 2L, 500000L),
    ];
    // Use a batch to update two rows in one round-trip.
    var batch = connection.CreateBatch();
    foreach (var album in albums)
    {
        // This creates a command that will use a mutation to update the row.
        var command = batch.CreateUpdateCommand("Albums");
        command.AddParameter("SingerId", album.SingerId);
        command.AddParameter("AlbumId", album.AlbumId);
        command.AddParameter("MarketingBudget", album.MarketingBudget);
        batch.BatchCommands.Add(command);
    }
    var affected = await batch.ExecuteNonQueryAsync();
    Console.WriteLine($"Updated {affected} albums.");
}

PostgreSQL

public static async Task UpdateDataWithMutations(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    (long SingerId, long AlbumId, long MarketingBudget)[] albums = [
        (1L, 1L, 100000L),
        (2L, 2L, 500000L),
    ];
    // Use a batch to update two rows in one round-trip.
    var batch = connection.CreateBatch();
    foreach (var album in albums)
    {
        // This creates a command that will use a mutation to update the row.
        var command = batch.CreateUpdateCommand("albums");
        command.AddParameter("singer_id", album.SingerId);
        command.AddParameter("album_id", album.AlbumId);
        command.AddParameter("marketing_budget", album.MarketingBudget);
        batch.BatchCommands.Add(command);
    }
    var affected = await batch.ExecuteNonQueryAsync();
    Console.WriteLine($"Updated {affected} albums.");
}

使用下列指令執行範例：

GoogleSQL 

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

PostgreSQL

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

結果顯示：

Updated 2 albums

您也可以執行 SQL 查詢，藉此擷取剛寫入的值。

以下範例使用 ExecuteReaderAsync 方法執行查詢：

GoogleSQL 

public static async Task QueryNewColumn(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "SELECT SingerId, AlbumId, MarketingBudget " +
                          "FROM Albums " +
                          "ORDER BY SingerId, AlbumId";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["SingerId"]} {reader["AlbumId"]} {reader["MarketingBudget"]}");
    }
}

PostgreSQL

public static async Task QueryNewColumn(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "select singer_id, album_id, marketing_budget " +
                          "from albums " +
                          "order by singer_id, album_id";
    await using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader["singer_id"]} {reader["album_id"]} {reader["marketing_budget"]}");
    }
}

如要執行這項查詢，請執行下列指令：

GoogleSQL 

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

PostgreSQL

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

如下所示：

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

更新資料

您可以在讀寫交易中使用 DML 來更新資料。

呼叫 connection.BeginTransactionAsync()，在 ADO.NET 中執行讀寫交易。

GoogleSQL 

public static async Task WriteDataWithTransaction(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Transfer marketing budget from one album to another. We do it in a
    // transaction to ensure that the transfer is atomic.
    await using var transaction = await connection.BeginTransactionAsync();

    // The Spanner ADO.NET driver supports both positional and named
    // query parameters. This query uses named query parameters.
    const string selectSql =
        "SELECT MarketingBudget " +
        "FROM Albums " +
        "WHERE SingerId = @singerId and AlbumId = @albumId";
    // Get the marketing_budget of singer 2 / album 2.
    await using var command = connection.CreateCommand();
    command.CommandText = selectSql;
    command.Transaction = transaction;
    command.Parameters.AddWithValue("singerId", 2);
    command.Parameters.AddWithValue("albumId", 2);
    var budget2 = (long) (await command.ExecuteScalarAsync() ?? 0L);

    const long transfer = 20000L;
    // The transaction will only be committed if this condition still holds
    // at the time of commit. Otherwise, the transaction will be aborted.
    if (budget2 >= transfer)
    {
        // Get the marketing_budget of singer 1 / album 1.
        command.Parameters["singerId"].Value = 1;
        command.Parameters["albumId"].Value = 1;
        var budget1 = (long) (await command.ExecuteScalarAsync() ?? 0L);

        // Transfer part of the marketing budget of Album 2 to Album 1.
        budget1 += transfer;
        budget2 -= transfer;
        const string updateSql =
            "UPDATE Albums " +
            "SET MarketingBudget = @budget " +
            "WHERE SingerId = @singerId and AlbumId = @albumId";
        // Create a DML batch and execute it as part of the current transaction.
        var batch = connection.CreateBatch();
        batch.Transaction = transaction;

        // Update the marketing budgets of both Album 1 and Album 2 in a batch.
        (long SingerId, long AlbumId, long MarketingBudget)[] budgets = [
            new (1L, 1L, budget1),
            new (2L, 2L, budget2),
        ];
        foreach (var budget in budgets)
        {
            var batchCommand = batch.CreateBatchCommand();
            batchCommand.CommandText = updateSql;
            var singerIdParameter = batchCommand.CreateParameter();
            singerIdParameter.ParameterName = "singerId";
            singerIdParameter.Value = budget.SingerId;
            batchCommand.Parameters.Add(singerIdParameter);
            var albumIdParameter = batchCommand.CreateParameter();
            albumIdParameter.ParameterName = "albumId";
            albumIdParameter.Value = budget.AlbumId;
            batchCommand.Parameters.Add(albumIdParameter);
            var marketingBudgetParameter = batchCommand.CreateParameter();
            marketingBudgetParameter.ParameterName = "budget";
            marketingBudgetParameter.Value = budget.MarketingBudget;
            batchCommand.Parameters.Add(marketingBudgetParameter);
            batch.BatchCommands.Add(batchCommand);
        }
        var affected = await batch.ExecuteNonQueryAsync();
        // The batch should update 2 rows.
        if (affected != 2)
        {
            await transaction.RollbackAsync();
            throw new InvalidOperationException($"Unexpected num affected: {affected}");
        }
    }
    // Commit the transaction.
    await transaction.CommitAsync();
    Console.WriteLine("Transferred marketing budget from Album 2 to Album 1");
}

PostgreSQL

public static async Task WriteDataWithTransaction(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Transfer marketing budget from one album to another. We do it in a
    // transaction to ensure that the transfer is atomic.
    await using var transaction = await connection.BeginTransactionAsync();

    // The Spanner ADO.NET driver supports both positional and named
    // query parameters. This query uses named query parameters.
    const string selectSql =
        "SELECT marketing_budget " +
        "FROM albums " +
        "WHERE singer_id = $1 and album_id = $2";
    // Get the marketing_budget of singer 2 / album 2.
    await using var command = connection.CreateCommand();
    command.CommandText = selectSql;
    command.Transaction = transaction;
    command.Parameters.AddWithValue("p1", 2);
    command.Parameters.AddWithValue("p2", 2);
    var budget2 = (long) (await command.ExecuteScalarAsync() ?? 0L);

    const long transfer = 20000L;
    // The transaction will only be committed if this condition still holds
    // at the time of commit. Otherwise, the transaction will be aborted.
    if (budget2 >= transfer)
    {
        // Get the marketing_budget of singer 1 / album 1.
        command.Parameters["p1"].Value = 1;
        command.Parameters["p2"].Value = 1;
        var budget1 = (long) (await command.ExecuteScalarAsync() ?? 0L);

        // Transfer part of the marketing budget of Album 2 to Album 1.
        budget1 += transfer;
        budget2 -= transfer;
        const string updateSql =
            "UPDATE albums " +
            "SET marketing_budget = $1 " +
            "WHERE singer_id = $2 and album_id = $3";
        // Create a DML batch and execute it as part of the current transaction.
        var batch = connection.CreateBatch();
        batch.Transaction = transaction;

        // Update the marketing budgets of both Album 1 and Album 2 in a batch.
        (long SingerId, long AlbumId, long MarketingBudget)[] budgets = [
            new (1L, 1L, budget1),
            new (2L, 2L, budget2),
        ];
        foreach (var budget in budgets)
        {
            var batchCommand = batch.CreateBatchCommand();
            batchCommand.CommandText = updateSql;
            var marketingBudgetParameter = batchCommand.CreateParameter();
            marketingBudgetParameter.ParameterName = "p1";
            marketingBudgetParameter.Value = budget.MarketingBudget;
            batchCommand.Parameters.Add(marketingBudgetParameter);
            var singerIdParameter = batchCommand.CreateParameter();
            singerIdParameter.ParameterName = "p2";
            singerIdParameter.Value = budget.SingerId;
            batchCommand.Parameters.Add(singerIdParameter);
            var albumIdParameter = batchCommand.CreateParameter();
            albumIdParameter.ParameterName = "p3";
            albumIdParameter.Value = budget.AlbumId;
            batchCommand.Parameters.Add(albumIdParameter);
            batch.BatchCommands.Add(batchCommand);
        }
        var affected = await batch.ExecuteNonQueryAsync();
        // The batch should update 2 rows.
        if (affected != 2)
        {
            await transaction.RollbackAsync();
            throw new InvalidOperationException($"Unexpected num affected: {affected}");
        }
    }
    // Commit the transaction.
    await transaction.CommitAsync();
    Console.WriteLine("Transferred marketing budget from Album 2 to Album 1");
}

使用下列指令執行範例：

GoogleSQL 

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

PostgreSQL

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

交易標記和要求標記

使用交易標記和要求標記，排解 Spanner 中的交易和查詢問題。您可以在 Transaction 物件上設定標記，以傳送交易標記，並在 DbCommand 物件上設定標記，以傳送要求標記至 Spanner。例如：

GoogleSQL 

public static async Task Tags(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    const long singerId = 1L;
    const long albumId = 1L;

    await using var transaction = await connection.BeginTransactionAsync();
    // Set a tag on the transaction before executing any statements.
    transaction.Tag = "example-tx-tag";

    await using var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.Tag = "query-marketing-budget";
    command.CommandText =
        "SELECT MarketingBudget " +
        "FROM Albums " +
        "WHERE SingerId=? and AlbumId=?";
    command.Parameters.Add(singerId);
    command.Parameters.Add(albumId);
    var budget = (long)(await command.ExecuteScalarAsync() ?? 0L);

    // Reduce the marketing budget by 10% if it is more than 1,000.
    if (budget > 1000)
    {
        budget -= budget / 10;
        await using var updateCommand = connection.CreateCommand();
        updateCommand.Transaction = transaction;
        updateCommand.Tag = "reduce-marketing-budget";
        updateCommand.CommandText =
            "UPDATE Albums SET MarketingBudget=@budget WHERE SingerId=@singerId AND AlbumId=@albumId";
        updateCommand.Parameters.AddWithValue("budget", budget);
        updateCommand.Parameters.AddWithValue("singerId", singerId);
        updateCommand.Parameters.AddWithValue("albumId", albumId);
        await updateCommand.ExecuteNonQueryAsync();
    }
    // Commit the transaction.
    await transaction.CommitAsync();
    Console.WriteLine("Reduced marketing budget");
}

PostgreSQL

public static async Task Tags(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    const long singerId = 1L;
    const long albumId = 1L;

    await using var transaction = await connection.BeginTransactionAsync();
    // Set a tag on the transaction before executing any statements.
    transaction.Tag = "example-tx-tag";

    await using var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.Tag = "query-marketing-budget";
    command.CommandText =
        "select marketing_budget " +
        "from albums " +
        "where singer_id=? and album_id=?";
    command.Parameters.Add(singerId);
    command.Parameters.Add(albumId);
    var budget = (long)(await command.ExecuteScalarAsync() ?? 0L);

    // Reduce the marketing budget by 10% if it is more than 1,000.
    if (budget > 1000)
    {
        budget -= budget / 10;
        await using var updateCommand = connection.CreateCommand();
        updateCommand.Transaction = transaction;
        updateCommand.Tag = "reduce-marketing-budget";
        updateCommand.CommandText =
            "update albums set marketing_budget=$1 where singer_id=$2 and album_id=$3";
        updateCommand.Parameters.Add(budget);
        updateCommand.Parameters.Add(singerId);
        updateCommand.Parameters.Add(albumId);
        await updateCommand.ExecuteNonQueryAsync();
    }
    // Commit the transaction.
    await transaction.CommitAsync();
    Console.WriteLine("Reduced marketing budget");
}

使用下列指令執行範例：

GoogleSQL 

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

PostgreSQL

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

使用唯讀交易擷取資料

假設您想要在相同時間戳記執行一次以上的讀取作業。唯讀交易會觀察交易修訂版本記錄的一致前置字串，因此應用程式一律會取得一致的資料。呼叫 connection.BeginReadOnlyTransactionAsync() 即可執行唯讀交易。

以下顯示如何執行查詢，並在同一個唯讀交易中執行讀取作業：

GoogleSQL 

public static async Task ReadOnlyTransaction(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Start a read-only transaction on this connection.
    await using var transaction = await connection.BeginReadOnlyTransactionAsync();

    await using var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.CommandText = "SELECT SingerId, AlbumId, AlbumTitle " +
                          "FROM Albums " +
                          "ORDER BY SingerId, AlbumId";
    await using (var reader = await command.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine(
                $"{reader["SingerId"]} {reader["AlbumId"]} {reader["AlbumTitle"]}");
        }
    }

    // Execute another query using the same read-only transaction.
    command.CommandText = "SELECT SingerId, AlbumId, AlbumTitle " +
                          "FROM Albums " +
                          "ORDER BY AlbumTitle";
    await using (var reader = await command.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine(
                $"{reader["SingerId"]} {reader["AlbumId"]} {reader["AlbumTitle"]}");
        }
    }

    // End the read-only transaction by calling Commit.
    await transaction.CommitAsync();
}

PostgreSQL

public static async Task ReadOnlyTransaction(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Start a read-only transaction on this connection.
    await using var transaction = await connection.BeginReadOnlyTransactionAsync();

    await using var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.CommandText = "SELECT SingerId, AlbumId, AlbumTitle " +
                          "FROM Albums " +
                          "ORDER BY SingerId, AlbumId";
    await using (var reader = await command.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine(
                $"{reader["SingerId"]} {reader["AlbumId"]} {reader["AlbumTitle"]}");
        }
    }

    // Execute another query using the same read-only transaction.
    command.CommandText = "SELECT SingerId, AlbumId, AlbumTitle " +
                          "FROM Albums " +
                          "ORDER BY AlbumTitle";
    await using (var reader = await command.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine(
                $"{reader["SingerId"]} {reader["AlbumId"]} {reader["AlbumTitle"]}");
        }
    }

    // End the read-only transaction by calling Commit.
    await transaction.CommitAsync();
}

使用下列指令執行範例：

GoogleSQL 

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

PostgreSQL

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

結果顯示：

    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

分區 DML

分區 data manipulation language (DML) 適用於下列類型的大量更新和刪除作業：

  • 定期清理和垃圾收集。
  • 使用預設值回填新資料欄。

GoogleSQL 

public static async Task PartitionedDml(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Enable Partitioned DML on this connection.
    await using var command = connection.CreateCommand();
    command.CommandText = "SET AUTOCOMMIT_DML_MODE='PARTITIONED_NON_ATOMIC'";
    await command.ExecuteNonQueryAsync();

    // Back-fill a default value for the MarketingBudget column.
    command.CommandText = "UPDATE Albums SET MarketingBudget=0 WHERE MarketingBudget IS NULL";
    var affected = await command.ExecuteNonQueryAsync();

    // Partitioned DML returns the minimum number of records that were affected.
    Console.WriteLine($"Updated at least {affected} albums");

    // Reset the value for AUTOCOMMIT_DML_MODE to its default.
    command.CommandText = "RESET AUTOCOMMIT_DML_MODE";
    await command.ExecuteNonQueryAsync();
}

PostgreSQL

public static async Task PartitionedDml(string connectionString)
{
    await using var connection = new SpannerConnection(connectionString);
    await connection.OpenAsync();

    // Enable Partitioned DML on this connection.
    await using var command = connection.CreateCommand();
    command.CommandText = "set autocommit_dml_mode='partitioned_non_atomic'";
    await command.ExecuteNonQueryAsync();

    // Back-fill a default value for the MarketingBudget column.
    command.CommandText = "update albums set marketing_budget=0 where marketing_budget is null";
    var affected = await command.ExecuteNonQueryAsync();

    // Partitioned DML returns the minimum number of records that were affected.
    Console.WriteLine($"Updated at least {affected} albums");

    // Reset the value for autocommit_dml_mode to its default.
    command.CommandText = "reset autocommit_dml_mode";
    await command.ExecuteNonQueryAsync();
}

使用下列指令執行範例：

GoogleSQL 

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

PostgreSQL

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

清除所用資源

為避免系統向您的 Cloud Billing 帳戶收取本教學課程所用資源的額外費用，請捨棄資料庫並刪除您建立的執行個體。

刪除資料庫

如果刪除執行個體，其中的所有資料庫都會自動刪除。這個步驟說明如何在不刪除執行個體的情況下刪除資料庫 (您仍須支付執行個體費用)。

使用指令列

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

使用 Google Cloud 控制台

  1. 前往 Google Cloud 控制台的「Spanner Instances」(Spanner 執行個體) 頁面。

    前往「Instances」(執行個體) 頁面

  2. 點選執行個體。

  3. 點選您要刪除的資料庫。

  4. 在「Database details」(資料庫詳細資料) 頁面，按一下 [Delete] (刪除)。

  5. 確認您要刪除資料庫，然後按一下 [Delete] (刪除)。

刪除執行個體

您刪除執行個體時，也會自動捨棄您在其中建立的所有資料庫。

使用指令列

gcloud spanner instances delete test-instance

使用 Google Cloud 控制台

  1. 前往 Google Cloud 控制台的「Spanner Instances」(Spanner 執行個體) 頁面。

    前往「Instances」(執行個體) 頁面

  2. 點選執行個體。

  3. 按一下 [Delete] (刪除)。

  4. 確認您要刪除執行個體，然後按一下 [Delete] (刪除)。

後續步驟