Introduzione a Spanner in ADO.NET

Obiettivi

Questo tutorial ti guida attraverso i seguenti passaggi utilizzando il driver Spanner ADO.NET:

• Crea un'istanza e un database Spanner.
• Scrivi, leggi ed esegui query SQL sui dati nel database.
• Aggiorna lo schema del database.
• Aggiorna i dati utilizzando una transazione di lettura/scrittura.
• Aggiungi un indice secondario al database.
• Utilizza l'indice per leggere ed eseguire query SQL sui dati.
• Recupera i dati utilizzando una transazione di sola lettura.

Costi

Questo tutorial utilizza Spanner, che è un componente fatturabile di Google Cloud. Per informazioni sul costo dell'utilizzo di Spanner, consulta Prezzi.

Prima di iniziare

Completa i passaggi descritti in Configurazione, che riguardano la creazione e l'impostazione di un progetto Google Cloud predefinito, l'abilitazione della fatturazione, l'abilitazione dell'API Cloud Spanner e la configurazione di OAuth 2.0 per ottenere le credenziali di autenticazione da utilizzare con l'API Cloud Spanner.

In particolare, assicurati di eseguire gcloud auth application-default login per configurare l'ambiente di sviluppo locale con le credenziali di autenticazione.

Nota: se non prevedi di conservare le risorse che crei in questo tutorial, valuta la possibilità di creare un nuovo progetto Google Cloud invece di selezionarne uno esistente. Al termine del tutorial, puoi eliminare il progetto e tutte le risorse associate.

Prepara l'ambiente ADO.NET locale

1. Scarica e installa .NET sul computer di sviluppo se non è già installato.

2. Clona il repository di esempio sulla tua macchina locale:

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

3. Passa alla directory che contiene il codice campione del driver ADO.NET di Spanner:

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

Crea un'istanza

Quando utilizzi Spanner per la prima volta, devi creare un'istanza, ovvero un'allocazione di risorse utilizzate dai database Spanner. Quando crei un'istanza, scegli una configurazione dell'istanza, che determina dove vengono archiviati i dati, nonché il numero di nodi da utilizzare, che determina la quantità di risorse di gestione e archiviazione nell'istanza.

Consulta Crea un'istanza per scoprire come creare un'istanza Spanner utilizzando uno dei seguenti metodi. Puoi denominare la tua istanza test-instance per utilizzarla con altri argomenti di questo documento che fanno riferimento a un'istanza denominata test-instance.

• Google Cloud CLI
• La console Google Cloud
• Una libreria client (C++, C#, Go, Java, Node.js, PHP, Python o Ruby)

Esaminare i file di esempio

Il repository di esempi contiene un esempio che mostra come utilizzare Spanner con ADO.NET.

Dai un'occhiata al file SampleRunner.cs, che mostra come utilizzare Spanner. Il codice mostra come creare e utilizzare un nuovo database. I dati utilizzano lo schema di esempio mostrato nella pagina Schema e modello dei dati.

Crea un database

GoogleSQL

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

PostgreSQL

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

Dovresti vedere:

Creating database...done.

Crea tabelle

Il seguente codice crea due tabelle nel database.

Nota: gli esempi di codice successivi utilizzano queste due tabelle. Se non esegui questo codice, crea le tabelle utilizzando la console Google Cloud o gcloud CLI. Per saperne di più, consulta lo schema di esempio.

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

Esegui l'esempio con questo comando:

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

Il passaggio successivo consiste nello scrivere i dati nel database.

Crea una connessione

Prima di poter eseguire letture o scritture, devi creare una connessione per interagire con Spanner. Il nome del database e altre proprietà di connessione sono specificati nella stringa di connessione 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)}");
    }
}

Scrivere dati con DML

Puoi inserire dati utilizzando Data Manipulation Language (DML) in una transazione di lettura/scrittura.

Utilizzi il metodo DbCommand#ExecuteNonQuery per eseguire un'istruzione 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.");
}

Esegui l'esempio con questo comando:

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

Il risultato dovrebbe mostrare:

4 records inserted.

Nota: esistono limiti alle dimensioni del commit. Per saperne di più, consulta la sezione Limite CRUD.

Scrivere dati con modifiche

Puoi anche inserire i dati utilizzando le mutazioni.

Puoi inserire i dati utilizzando il metodo batch.CreateInsertCommand(), che crea un nuovo SpannerBatchCommand per inserire righe in una tabella. Il metodo SpannerBatchCommand.ExecuteNonQueryAsync() aggiunge nuove righe alla tabella.

Il seguente codice mostra come scrivere dati utilizzando le mutazioni:

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

Esegui l'esempio seguente utilizzando l'argomento 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

Nota: esistono limiti alle dimensioni del commit. Per saperne di più, consulta la sezione Limite CRUD.

Eseguire query sui dati utilizzando SQL

Spanner supporta un'interfaccia SQL per la lettura dei dati, a cui puoi accedere dalla riga di comando utilizzando Google Cloud CLI o in modo programmatico utilizzando il driver Spanner ADO.NET.

Nella riga di comando

Esegui la seguente istruzione SQL per leggere i valori di tutte le colonne della tabella Albums:

GoogleSQL

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

PostgreSQL

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

Nota :per il riferimento GoogleSQL, consulta Sintassi delle query in GoogleSQL e per il riferimento PostgreSQL, consulta Struttura lessicale e sintassi di PostgreSQL.

Il risultato mostra:

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

Utilizzare il driver ADO.NET di Spanner

Oltre a eseguire un'istruzione SQL dalla riga di comando, puoi emettere la stessa istruzione SQL in modo programmatico utilizzando il driver Spanner ADO.NET.

Per eseguire una query SQL vengono utilizzati i seguenti metodi:

• Il metodo ExecuteReader nella classe DbCommand: utilizzalo per eseguire un'istruzione SQL che restituisce righe, ad esempio una query o un'istruzione DML con una clausola THEN RETURN.
• La classe DbDataReader: utilizzala per accedere ai dati restituiti da un'istruzione SQL.

L'esempio seguente utilizza il metodo 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"]}");
    }
}

Esegui l'esempio con questo comando:

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

Il risultato dovrebbe mostrare:

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

Query che utilizza un parametro SQL

Se la tua applicazione ha una query eseguita di frequente, puoi migliorarne le prestazioni parametrizzandola. La query parametrica risultante può essere memorizzata nella cache e riutilizzata, il che riduce i costi di compilazione. Per saperne di più, consulta Utilizzare parametri di ricerca per velocizzare le query eseguite di frequente.

Ecco un esempio di utilizzo di un parametro nella clausola WHERE per eseguire query sui record contenenti un valore specifico per LastName.

Il driver Spanner ADO.NET supporta parametri di ricerca posizionali e denominati. Un ? in un'istruzione SQL indica un parametro di query posizionale. Aggiungi i valori dei parametri di ricerca a Parameters di DbCommand. Ad esempio:

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

Esegui l'esempio con questo comando:

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

Il risultato mostra:

12 Melissa Garcia

Aggiorna lo schema del database

Supponiamo che tu debba aggiungere una nuova colonna denominata MarketingBudget alla tabella Albums. L'aggiunta di una nuova colonna a una tabella esistente richiede un aggiornamento dello schema del database. Spanner supporta gli aggiornamenti dello schema di un database mentre il database continua a gestire il traffico. Gli aggiornamenti dello schema non richiedono di mettere offline il database e non bloccano intere tabelle o colonne. Puoi continuare a scrivere dati nel database durante l'aggiornamento dello schema. Scopri di più sugli aggiornamenti dello schema e sul rendimento delle modifiche dello schema supportati in Esegui aggiornamenti dello schema.

Aggiungere una colonna

Puoi aggiungere una colonna dalla riga di comando utilizzando Google Cloud CLI o in modo programmatico utilizzando il driver Spanner ADO.NET.

Nella riga di comando

Utilizza il seguente comando ALTER TABLE per aggiungere la nuova colonna alla tabella:

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'

Dovresti vedere:

Schema updating...done.

Utilizzare il driver ADO.NET di Spanner

Utilizza il metodo ExecuteNonQueryAsync per modificare lo schema:

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

Esegui l'esempio con questo comando:

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

Il risultato mostra:

Added MarketingBudget column.

Esegui un batch DDL

Ti consigliamo di eseguire più modifiche allo schema in un unico batch. Utilizza il metodo ADO.NET CreateBatch per creare un batch. L'esempio seguente crea due tabelle in un batch:

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

Esegui l'esempio con questo comando:

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

Il risultato mostra:

Added Venues and Concerts tables.

Scrivi i dati nella nuova colonna

Il seguente codice scrive i dati nella nuova colonna. Imposta MarketingBudget su 100000 per la riga con chiave Albums(1, 1) e su 500000 per la riga con chiave Albums(2, 2).

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

Esegui l'esempio con questo comando:

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

Il risultato mostra:

Updated 2 albums

Puoi anche eseguire una query SQL per recuperare i valori che hai appena scritto.

L'esempio seguente utilizza il metodo ExecuteReaderAsync per eseguire una query:

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

Per eseguire questa query, esegui questo comando:

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

Dovresti vedere:

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

Aggiorna dati

Puoi aggiornare i dati utilizzando DML in una transazione di lettura/scrittura.

Chiama connection.BeginTransactionAsync() per eseguire transazioni di lettura/scrittura in 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");
}

Esegui l'esempio con questo comando:

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

Tag transazione e tag richiesta

Utilizza i tag di transazione e i tag di richiesta per risolvere i problemi relativi a transazioni e query in Spanner. Puoi impostare tag sugli oggetti Transaction per inviare tag di transazione e sugli oggetti DbCommand per inviare tag di richiesta a Spanner. Ad esempio:

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

Esegui l'esempio con questo comando:

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

Recuperare i dati utilizzando le transazioni di sola lettura

Supponiamo che tu voglia eseguire più di una lettura con lo stesso timestamp. Le transazioni di sola lettura osservano un prefisso coerente della cronologia dei commit delle transazioni, in modo che la tua applicazione riceva sempre dati coerenti. Chiama connection.BeginReadOnlyTransactionAsync() per eseguire una transazione di sola lettura.

Di seguito viene mostrato come eseguire una query ed eseguire una lettura nella stessa transazione di sola lettura:

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

Esegui l'esempio con questo comando:

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

Il risultato mostra:

    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 partizionato

Il Data Manipulation Language (DML) partizionato è progettato per i seguenti tipi di aggiornamenti ed eliminazioni collettivi:

• Pulizia periodica e garbage collection.
• Riempimento delle nuove colonne con valori predefiniti.

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

Esegui l'esempio con questo comando:

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

Esegui la pulizia

Per evitare che al tuo account di fatturazione Cloud vengano addebitati costi aggiuntivi per le risorse utilizzate in questo tutorial, elimina il database e l'istanza che hai creato.

Elimina il database

Se elimini un'istanza, tutti i database al suo interno vengono eliminati automaticamente. Questo passaggio mostra come eliminare un database senza eliminare un'istanza (continuerai a sostenere i costi per l'istanza).

Nella riga di comando

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

Utilizzo della console Google Cloud

1. Vai alla pagina Istanze Spanner nella console Google Cloud .

   Vai alla pagina Istanze

2. Fai clic sull'istanza.

3. Fai clic sul database che vuoi eliminare.

4. Nella pagina Dettagli database, fai clic su Elimina.

5. Conferma di voler eliminare il database e fai clic su Elimina.

Elimina l'istanza

L'eliminazione di un'istanza elimina automaticamente tutti i database creati in quell'istanza.

Nella riga di comando

gcloud spanner instances delete test-instance

Utilizzo della console Google Cloud

1. Vai alla pagina Istanze Spanner nella console Google Cloud .

   Vai alla pagina Istanze

2. Fai clic sull'istanza.

3. Fai clic su Elimina.

4. Conferma di voler eliminare l'istanza e fai clic su Elimina.

Passaggi successivi

• Scopri come accedere a Spanner con un'istanza di macchina virtuale.

• Scopri di più sulle credenziali di autorizzazione e autenticazione in Autenticarsi ai servizi cloud utilizzando le librerie client.

• Scopri di più sulle best practice per la progettazione dello schema di Spanner.