Class SpannerCommand

Represents a SQL query or command to execute against a Spanner database. If the command is a SQL query, then CommandText contains the entire SQL statement. Use ExecuteReaderAsync() to obtain results.

If the command is an update, insert or delete command, then CommandText is simply "[operation] [spanner_table]" such as "UPDATE MYTABLE" with the parameter collection containing SpannerParameter instances whose name matches a column in the target table. Use ExecuteNonQueryAsync(CancellationToken) to execute the command.

The command may also be a DDL statement such as CREATE TABLE. Use ExecuteNonQueryAsync(CancellationToken) to execute a DDL statement.

Inheritance

object

MarshalByRefObject

Component

DbCommand

SpannerCommand

Implements

IComponent

IDbCommand

IDisposable

IAsyncDisposable

ICloneable

Inherited Members

DbCommand.CreateParameter()

DbCommand.ExecuteNonQueryAsync()

DbCommand.ExecuteReader()

DbCommand.ExecuteReader(CommandBehavior)

DbCommand.ExecuteScalarAsync()

DbCommand.PrepareAsync(CancellationToken)

DbCommand.DisposeAsync()

DbCommand.Connection

DbCommand.Transaction

Component.Dispose()

Component.ToString()

Component.Container

Component.Site

Component.Disposed

MarshalByRefObject.GetLifetimeService()

MarshalByRefObject.InitializeLifetimeService()

object.GetHashCode()

object.GetType()

Namespace: Google.Cloud.Spanner.Data

Assembly: Google.Cloud.Spanner.Data.dll

Syntax

public sealed class SpannerCommand : DbCommand, IComponent, IDbCommand, IDisposable, IAsyncDisposable, ICloneable

Constructors

SpannerCommand()

Initializes a new instance of SpannerCommand, using a default command timeout.

Declaration

public SpannerCommand()

SpannerCommand(SpannerCommandTextBuilder, SpannerConnection, SpannerTransaction, SpannerParameterCollection)

Initializes a new instance of SpannerCommand.

Declaration

public SpannerCommand(SpannerCommandTextBuilder commandTextBuilder, SpannerConnection connection, SpannerTransaction transaction = null, SpannerParameterCollection parameters = null)

Parameters

Type	Name	Description
SpannerCommandTextBuilder	commandTextBuilder	The SpannerCommandTextBuilder that configures the text of this command. Must not be null.
SpannerConnection	connection	The SpannerConnection that is associated with this SpannerCommand. Must not be null.
SpannerTransaction	transaction	An optional SpannerTransaction created through BeginTransactionAsync(SpannerTransactionCreationOptions, CancellationToken). May be null.
SpannerParameterCollection	parameters	An optional collection of SpannerParameter that is used in the command. May be null.

Remarks

The initial command timeout is taken from the options associated with connection.

SpannerCommand(SpannerConnection, SpannerTransaction, CommandPartition)

Initializes a new instance of SpannerCommand.

Declaration

public SpannerCommand(SpannerConnection connection, SpannerTransaction transaction, CommandPartition commandPartition)

Parameters

Type	Name	Description
SpannerConnection	connection	The SpannerConnection that is associated with this SpannerCommand. Must not be null.
SpannerTransaction	transaction	The SpannerTransaction used when creating the CommandPartition.
CommandPartition	commandPartition	The partition which this command is restricted to. See BeginReadOnlyTransaction(TransactionId)

SpannerCommand(string, SpannerConnection, SpannerTransaction, SpannerParameterCollection)

Initializes a new instance of SpannerCommand

Declaration

public SpannerCommand(string commandText, SpannerConnection connection, SpannerTransaction transaction = null, SpannerParameterCollection parameters = null)

Parameters

Type	Name	Description
string	commandText	If this command is a SQL Query, then commandText is the SQL statement. If its an update, insert or delete command, then this text is "[operation] [table]" such as "UPDATE MYTABLE"
SpannerConnection	connection	The SpannerConnection that is associated with this SpannerCommand. Must not be null.
SpannerTransaction	transaction	An optional SpannerTransaction created through SpannerConnection.BeginTransactionAsync. May be null.
SpannerParameterCollection	parameters	An optional collection of SpannerParameter that is used in the command. May be null.

Properties

CommandText

Gets or sets the text command to run against the data source.

Declaration

public override string CommandText { get; set; }

Property Value

Type	Description
string	The text command to execute. The default value is an empty string ("").

Overrides

DbCommand.CommandText

CommandTimeout

Gets or sets the wait time before terminating the attempt to execute a command and generating an error. Defaults to the timeout from the connection string.

Declaration

public override int CommandTimeout { get; set; }

Property Value

Type	Description
int

Overrides

DbCommand.CommandTimeout

Remarks

A value of '0' normally indicates that no timeout should be used (it waits an infinite amount of time). However, if you specify AllowImmediateTimeouts=true in the connection string, '0' will cause a timeout that expires immediately. This is normally used only for testing purposes.

CommandType

Indicates or specifies how the CommandText property is interpreted.

Declaration

public override CommandType CommandType { get; set; }

Property Value

Type	Description
CommandType	One of the CommandType values. The default is Text.

Overrides

DbCommand.CommandType

DbConnection

Gets or sets the DbConnection used by this DbCommand.

Declaration

protected override DbConnection DbConnection { get; set; }

Property Value

Type	Description
DbConnection	The connection to the data source.

Overrides

DbCommand.DbConnection

DbParameterCollection

Gets the collection of DbParameter objects.

Declaration

protected override DbParameterCollection DbParameterCollection { get; }

Property Value

Type	Description
DbParameterCollection	The parameters of the SQL statement or stored procedure.

Overrides

DbCommand.DbParameterCollection

DbTransaction

Gets or sets the DbTransaction within which this DbCommand object executes.

Declaration

protected override DbTransaction DbTransaction { get; set; }

Property Value

Type	Description
DbTransaction	The transaction within which a Command object of a .NET Framework data provider executes. The default value is a null reference (Nothing in Visual Basic).

Overrides

DbCommand.DbTransaction

DesignTimeVisible

Gets or sets a value indicating whether the command object should be visible in a customized interface control.

Declaration

public override bool DesignTimeVisible { get; set; }

Property Value

Type	Description
bool	true, if the command object should be visible in a control; otherwise false. The default is true.

Overrides

DbCommand.DesignTimeVisible

DirectedReadOptions

Specifies which replicas or regions should be used for non-transactional reads or queries. May be null.

Declaration

public DirectedReadOptions DirectedReadOptions { get; set; }

Property Value

Type	Description
DirectedReadOptions

Remarks

These options will only be applied to read and query commands, including partitioned reads and queries. They will be ignored for all other operations. Directed reads are only supported for read-only and single use transactions. If you set this value on a read or query operation that is then executed in a read-write or partioned DML transaction, the Spanner service will fail.

EphemeralTransactionCreationOptions

Options to be used for creating the ephemeral transaction under which this command will be executed if no explicit or ambient transaction is set. These options will be ignored if an explicit transaction is set on the command via Transaction or an ambient transaction has been started via OpenAsync(SpannerTransactionCreationOptions, SpannerTransactionOptions, CancellationToken) and similar methods. May be null, in which case appropriate defaults will be used when needed.

Declaration

public SpannerTransactionCreationOptions EphemeralTransactionCreationOptions { get; set; }

Property Value

Type	Description
SpannerTransactionCreationOptions

KeySet

The keys of the rows that should be read from the target table if the command is Read, or null otherwise.

Declaration

public KeySet KeySet { get; }

Property Value

Type	Description
KeySet

MaxCommitDelay

The maximum amount of time the commit of the implicit transaction associated with this command, if any, may be delayed server side for batching with other commits. The bigger the delay, the better the throughput (QPS), but at the expense of commit latency. If set to Zero, commit batching is disabled. May be null, in which case commits will continue to be batched as they had been before this configuration option was made available to Spanner API consumers. May be set to any value between Zero and 500ms.

Declaration

public TimeSpan? MaxCommitDelay { get; set; }

Property Value

Type	Description
TimeSpan?

Remarks

When a DML or mutation command is executed with no explicit or ambient transaction, an implicit transaction is created and the command is executed within it. This value will be applied to the commit operation of such transaction, if there is any. Otherwise, this value will be ignored.

Parameters

The parameters of the SQL statement or command.

Declaration

public SpannerParameterCollection Parameters { get; }

Property Value

Type	Description
SpannerParameterCollection

Partition

The optional partition which this command is restricted to. If set, the command runs only on the data associated with the given partition. Setting this property overrides any values in CommandText and Parameters

Declaration

public CommandPartition Partition { get; set; }

Property Value

Type	Description
CommandPartition

Priority

The RPC priority to use for this command. The default priority is Unspecified.

Declaration

public Priority Priority { get; set; }

Property Value

Type	Description
Priority

QueryOptions

Query options to use when running SQL and streaming SQL commands.

Declaration

public QueryOptions QueryOptions { get; set; }

Property Value

Type	Description
QueryOptions

SpannerConnection

The connection to the data source.

Declaration

public SpannerConnection SpannerConnection { get; set; }

Property Value

Type	Description
SpannerConnection

Tag

The statement tag to send to Cloud Spanner for this command.

Declaration

public string Tag { get; set; }

Property Value

Type	Description
string

UpdatedRowSource

Gets or sets how command results are applied to the DataRow when used by the Update method of a DbDataAdapter.

Declaration

public override UpdateRowSource UpdatedRowSource { get; set; }

Property Value

Type	Description
UpdateRowSource	One of the UpdateRowSource values. The default is Both unless the command is automatically generated. Then the default is None.

Overrides

DbCommand.UpdatedRowSource

Methods

Cancel()

Attempts to cancels the execution of a DbCommand.

Declaration

public override void Cancel()

Overrides

DbCommand.Cancel()

Clone()

Returns a copy of this SpannerCommand.

Declaration

public object Clone()

Returns

Type	Description
object	a copy of this SpannerCommand.

Implements

ICloneable.Clone()

CreateDbParameter()

Creates a new instance of a DbParameter object.

Declaration

protected override DbParameter CreateDbParameter()

Returns

Type	Description
DbParameter	A DbParameter object.

Overrides

DbCommand.CreateDbParameter()

Dispose(bool)

Releases the unmanaged resources used by the Component and optionally releases the managed resources.

Declaration

protected override void Dispose(bool disposing)

Parameters

Type	Name	Description
bool	disposing	true to release both managed and unmanaged resources; false to release only unmanaged resources.

Overrides

Component.Dispose(bool)

ExecuteDbDataReader(CommandBehavior)

Executes the command text against the connection.

Declaration

protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)

Parameters

Type	Name	Description
CommandBehavior	behavior	An instance of CommandBehavior.

Returns

Type	Description
DbDataReader	A task representing the operation.

Overrides

DbCommand.ExecuteDbDataReader(CommandBehavior)

Exceptions

Type	Condition
DbException	An error occurred while executing the command text.
ArgumentException	An invalid CommandBehavior value.

ExecuteDbDataReaderAsync(CommandBehavior, CancellationToken)

Providers should implement this method to provide a non-default implementation for System.Data.Common.DbCommand.ExecuteReader* overloads.
The default implementation invokes the synchronous ExecuteReader() method and returns a completed task, blocking the calling thread. The default implementation will return a cancelled task if passed an already cancelled cancellation token. Exceptions thrown by ExecuteReader will be communicated via the returned Task Exception property.
This method accepts a cancellation token that can be used to request the operation to be cancelled early. Implementations may ignore this request.

Declaration

protected override Task<DbDataReader> ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

Parameters

Type	Name	Description
CommandBehavior	behavior	Options for statement execution and data retrieval.
CancellationToken	cancellationToken	The token to monitor for cancellation requests.

Returns

Type	Description
Task<DbDataReader>	A task representing the asynchronous operation.

Overrides

DbCommand.ExecuteDbDataReaderAsync(CommandBehavior, CancellationToken)

Exceptions

Type	Condition
DbException	An error occurred while executing the command text.
ArgumentException	An invalid CommandBehavior value.

ExecuteNonQuery()

Executes a SQL statement against a connection object.

Declaration

public override int ExecuteNonQuery()

Returns

Type	Description
int	The number of rows affected.

Overrides

DbCommand.ExecuteNonQuery()

ExecuteNonQueryAsync(CancellationToken)

This is the asynchronous version of ExecuteNonQuery(). Providers should override with an appropriate implementation. The cancellation token may optionally be ignored.
The default implementation invokes the synchronous ExecuteNonQuery() method and returns a completed task, blocking the calling thread. The default implementation will return a cancelled task if passed an already cancelled cancellation token. Exceptions thrown by ExecuteNonQuery() will be communicated via the returned Task Exception property.
Do not invoke other methods and properties of the DbCommand object until the returned Task is complete.

Declaration

public override Task<int> ExecuteNonQueryAsync(CancellationToken cancellationToken)

Parameters

Type	Name	Description
CancellationToken	cancellationToken	The token to monitor for cancellation requests.

Returns

Type	Description
Task<int>	A task representing the asynchronous operation.

Overrides

DbCommand.ExecuteNonQueryAsync(CancellationToken)

Exceptions

Type	Condition
DbException	An error occurred while executing the command text.

ExecutePartitionedUpdate()

Executes this command as a partitioned update. The command must be a generalized DML command; CreateDmlCommand(string, SpannerParameterCollection) for details.

Declaration

public long ExecutePartitionedUpdate()

Returns

Type	Description
long	A lower bound for the number of rows affected.

Remarks

The command is executed in parallel across multiple partitions, and automatically committed as it executes. This operation is not atomic: if it is cancelled part way through, the data that has already been updated will remain updated. Additionally, it is performed "at least once" in each partition; if the statement is non-idempotent (for example, incrementing a column) then the update may be performed more than once on a given row. This command must not be part of any other transaction.

ExecutePartitionedUpdateAsync(CancellationToken)

Executes this command as a partitioned update. The command must be a generalized DML command; CreateDmlCommand(string, SpannerParameterCollection) for details.

Declaration

public Task<long> ExecutePartitionedUpdateAsync(CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
CancellationToken	cancellationToken	An optional token for canceling the call.

Returns

Type	Description
Task<long>	A task whose result is a lower bound for the number of rows affected.

Remarks

The command is executed in parallel across multiple partitions, and automatically committed as it executes. This operation is not atomic: if it is cancelled part way through, the data that has already been updated will remain updated. Additionally, it is performed "at least once" in each partition; if the statement is non-idempotent (for example, incrementing a column) then the update may be performed more than once on a given row. This command must not be part of any other transaction.

ExecuteReaderAsync()

Sends the command to Cloud Spanner and builds a SpannerDataReader.

Declaration

public Task<SpannerDataReader> ExecuteReaderAsync()

Returns

Type	Description
Task<SpannerDataReader>	An asynchronous Task that produces a SpannerDataReader.

ExecuteReaderAsync(TimestampBound, CancellationToken)

Executes the command against the SpannerConnection.

Declaration

[Obsolete("Please set EphemeralTransactionCreationOptions to SpannerTransactionCreationOptions.ForTimestampBoundReadOnly(singleUseReadSettings) and call any of the other ExecuteReaderAsync overloads.")]
public Task<DbDataReader> ExecuteReaderAsync(TimestampBound singleUseReadSettings, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
TimestampBound	singleUseReadSettings	The settings to use for the implicit single-use read-only transaction.
CancellationToken	cancellationToken	A cancellation token for the operation.

Returns

Type	Description
Task<DbDataReader>	A SpannerDataReader.

Remarks

This method is thread safe.

ExecuteReaderAsync(CommandBehavior)

Sends the command to Cloud Spanner and builds a SpannerDataReader.

Declaration

public Task<SpannerDataReader> ExecuteReaderAsync(CommandBehavior behavior)

Parameters

Type	Name	Description
CommandBehavior	behavior	Options for statement execution and data retrieval.

Returns

Type	Description
Task<SpannerDataReader>	An asynchronous Task that produces a SpannerDataReader.

ExecuteReaderAsync(CommandBehavior, CancellationToken)

Sends the command to Cloud Spanner and builds a SpannerDataReader.

Declaration

public Task<SpannerDataReader> ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

Parameters

Type	Name	Description
CommandBehavior	behavior	Options for statement execution and data retrieval.
CancellationToken	cancellationToken	An optional token for canceling the call.

Returns

Type	Description
Task<SpannerDataReader>	An asynchronous Task that produces a SpannerDataReader.

ExecuteReaderAsync(CancellationToken)

Sends the command to Cloud Spanner and builds a SpannerDataReader.

Declaration

public Task<SpannerDataReader> ExecuteReaderAsync(CancellationToken cancellationToken)

Parameters

Type	Name	Description
CancellationToken	cancellationToken	An optional token for canceling the call.

Returns

Type	Description
Task<SpannerDataReader>	An asynchronous Task that produces a SpannerDataReader.

ExecuteScalar()

Executes the query and returns the first column of the first row in the result set returned by the query. All other columns and rows are ignored.

Declaration

public override object ExecuteScalar()

Returns

Type	Description
object	The first column of the first row in the result set.

Overrides

DbCommand.ExecuteScalar()

ExecuteScalarAsync(CancellationToken)

This is the asynchronous version of ExecuteScalar(). Providers should override with an appropriate implementation. The cancellation token may optionally be ignored.
The default implementation invokes the synchronous ExecuteScalar() method and returns a completed task, blocking the calling thread. The default implementation will return a cancelled task if passed an already cancelled cancellation token. Exceptions thrown by ExecuteScalar will be communicated via the returned Task Exception property.
Do not invoke other methods and properties of the DbCommand object until the returned Task is complete.

Declaration

public override Task<object> ExecuteScalarAsync(CancellationToken cancellationToken)

Parameters

Type	Name	Description
CancellationToken	cancellationToken	The token to monitor for cancellation requests.

Returns

Type	Description
Task<object>	A task representing the asynchronous operation.

Overrides

DbCommand.ExecuteScalarAsync(CancellationToken)

Exceptions

Type	Condition
DbException	An error occurred while executing the command text.

ExecuteScalarAsync<T>(CancellationToken)

Executes the query and returns the first column of the first row in the result set returned by the query. All other columns and rows are ignored. The return value is converted to type T if possible. This method is thread safe.

Declaration

public Task<T> ExecuteScalarAsync<T>(CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
CancellationToken	cancellationToken	An optional token for canceling the call.

Returns

Type	Description
Task<T>	The first column of the first row resulting from execution of the query.

Type Parameters

Name

Description

T

The expected return type. If possible the return type will be converted to this type. If conversion is requested between incompatible types, an InvalidOperationException will be thrown. If the conversion fails due to the contents returned (for example a string representing a boolean does not have either 'true' or 'false') then a FormatException exception will be thrown as documented by the Convert class.

GetReaderPartitionsAsync(PartitionOptions, CancellationToken)

Creates a set of CommandPartition objects that are used to execute a query or read operation in parallel. Each of the returned command partitions are used by CreateCommandWithPartition(CommandPartition, SpannerTransaction) to create a new SpannerCommand that returns a subset of data.

Declaration

public Task<IReadOnlyList<CommandPartition>> GetReaderPartitionsAsync(PartitionOptions options, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
PartitionOptions	options	The PartitionOptions used to create and read partitions.
CancellationToken	cancellationToken	An optional token for canceling the call.

Returns

Type	Description
Task<IReadOnlyList<CommandPartition>>	The list of partitions that can be used to create SpannerCommand objects.

Prepare()

Creates a prepared (or compiled) version of the command on the data source.

Declaration

public override void Prepare()

Overrides

DbCommand.Prepare()