Google.Cloud.Spanner.Data - Class SpannerCommand (5.0.0-beta03)

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

Reference documentation and code samples for the Google.Cloud.Spanner.Data 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

Constructors

SpannerCommand()

public SpannerCommand()

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

SpannerCommand(SpannerCommandTextBuilder, SpannerConnection, SpannerTransaction, SpannerParameterCollection)

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

Initializes a new instance of SpannerCommand.

Parameters
Name	Description
`commandTextBuilder`	`SpannerCommandTextBuilder` The SpannerCommandTextBuilder that configures the text of this command. Must not be null.
`connection`	`SpannerConnection` The SpannerConnection that is associated with this SpannerCommand. Must not be null.
`transaction`	`SpannerTransaction` An optional SpannerTransaction created through BeginTransactionAsync(CancellationToken). May be null.
`parameters`	`SpannerParameterCollection` 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)

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

Initializes a new instance of SpannerCommand.

Parameters
Name	Description
`connection`	`SpannerConnection` The SpannerConnection that is associated with this SpannerCommand. Must not be null.
`transaction`	`SpannerTransaction` 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)

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

Initializes a new instance of SpannerCommand

Parameters
Name	Description
`commandText`	`string` 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"
`connection`	`SpannerConnection` The SpannerConnection that is associated with this SpannerCommand. Must not be null.
`transaction`	`SpannerTransaction` An optional SpannerTransaction created through . May be null.
`parameters`	`SpannerParameterCollection` An optional collection of SpannerParameter that is used in the command. May be null.

Properties

CommandText

public override string CommandText { get; set; }

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

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

Overrides

DbCommand.CommandText

CommandTimeout

public override int CommandTimeout { get; set; }

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.

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

public override CommandType CommandType { get; set; }

Indicates or specifies how the CommandText property is interpreted.

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

Overrides

DbCommand.CommandType

DbConnection

protected override DbConnection DbConnection { get; set; }

Gets or sets the DbConnection used by this DbCommand.

Property Value
Type	Description
`DbConnection`	The connection to the data source.

Overrides

DbCommand.DbConnection

DbParameterCollection

protected override DbParameterCollection DbParameterCollection { get; }

Gets the collection of DbParameter objects.

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

Overrides

DbCommand.DbParameterCollection

DbTransaction

protected override DbTransaction DbTransaction { get; set; }

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

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

public override bool DesignTimeVisible { get; set; }

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

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

public DirectedReadOptions DirectedReadOptions { get; set; }

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

Property Value
Type	Description
`Google.Cloud.Spanner.V1.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.

KeySet

public KeySet KeySet { get; }

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

Property Value
Type	Description
`KeySet`

MaxCommitDelay

public TimeSpan? MaxCommitDelay { get; set; }

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.

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

public SpannerParameterCollection Parameters { get; }

The parameters of the SQL statement or command.

Property Value
Type	Description
`SpannerParameterCollection`

Partition

public CommandPartition Partition { get; set; }

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

Property Value
Type	Description
`CommandPartition`

Priority

public Priority Priority { get; set; }

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

Property Value
Type	Description
`Priority`

QueryOptions

public QueryOptions QueryOptions { get; set; }

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

Property Value
Type	Description
`QueryOptions`

SpannerConnection

public SpannerConnection SpannerConnection { get; set; }

The connection to the data source.

Property Value
Type	Description
`SpannerConnection`

Tag

public string Tag { get; set; }

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

Property Value
Type	Description
`string`

UpdatedRowSource

public override UpdateRowSource UpdatedRowSource { get; set; }

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

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

public override void Cancel()

Attempts to cancels the execution of a DbCommand.

Overrides

DbCommand.Cancel()

Clone()

public object Clone()

Returns a copy of this SpannerCommand.

Returns
Type	Description
`object`	a copy of this SpannerCommand.

CreateDbParameter()

protected override DbParameter CreateDbParameter()

Creates a new instance of a DbParameter object.

Returns
Type	Description
`DbParameter`	A DbParameter object.

Overrides

DbCommand.CreateDbParameter()

Dispose(bool)

protected override void Dispose(bool disposing)

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

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

Overrides

Component.Dispose(bool)

ExecuteDbDataReader(CommandBehavior)

protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)

Executes the command text against the connection.

Parameter
Name	Description
`behavior`	`CommandBehavior` An instance of CommandBehavior.

Returns
Type	Description
`DbDataReader`	A task representing the operation.

Overrides

DbCommand.ExecuteDbDataReader(CommandBehavior)

Exceptions
Type	Description
`DbException`	An error occurred while executing the command text.
`ArgumentException`	An invalid CommandBehavior value.

ExecuteDbDataReaderAsync(CommandBehavior, CancellationToken)

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

Providers should implement this method to provide a non-default implementation for 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.

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

Returns
Type	Description
`TaskDbDataReader`	A task representing the asynchronous operation.

Overrides

DbCommand.ExecuteDbDataReaderAsync(CommandBehavior, CancellationToken)

Exceptions
Type	Description
`DbException`	An error occurred while executing the command text.
`ArgumentException`	An invalid CommandBehavior value.

ExecuteNonQuery()

public override int ExecuteNonQuery()

Executes a SQL statement against a connection object.

Returns
Type	Description
`int`	The number of rows affected.

Overrides

DbCommand.ExecuteNonQuery()

ExecuteNonQueryAsync(CancellationToken)

public override Task<int> ExecuteNonQueryAsync(CancellationToken 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.

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

Returns
Type	Description
`Taskint`	A task representing the asynchronous operation.

Overrides

DbCommand.ExecuteNonQueryAsync(CancellationToken)

Exceptions
Type	Description
`DbException`	An error occurred while executing the command text.

ExecutePartitionedUpdate()

public long ExecutePartitionedUpdate()

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

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)

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

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

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

Returns
Type	Description
`Tasklong`	A task whose result is a lower bound for the number of rows affected.

Remarks

ExecuteReaderAsync()

public Task<SpannerDataReader> ExecuteReaderAsync()

Sends the command to Cloud Spanner and builds a SpannerDataReader.

Returns
Type	Description
`TaskSpannerDataReader`	An asynchronous Task that produces a SpannerDataReader.

ExecuteReaderAsync(TimestampBound, CancellationToken)

public Task<DbDataReader> ExecuteReaderAsync(TimestampBound singleUseReadSettings, CancellationToken cancellationToken = default)

Executes the command against the SpannerConnection.

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

Returns
Type	Description
`TaskDbDataReader`	A SpannerDataReader.

Remarks

This method is thread safe.

ExecuteReaderAsync(CommandBehavior)

public Task<SpannerDataReader> ExecuteReaderAsync(CommandBehavior behavior)

Sends the command to Cloud Spanner and builds a SpannerDataReader.

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

Returns
Type	Description
`TaskSpannerDataReader`	An asynchronous Task that produces a SpannerDataReader.

ExecuteReaderAsync(CommandBehavior, CancellationToken)

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

Sends the command to Cloud Spanner and builds a SpannerDataReader.

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

Returns
Type	Description
`TaskSpannerDataReader`	An asynchronous Task that produces a SpannerDataReader.

ExecuteReaderAsync(CancellationToken)

public Task<SpannerDataReader> ExecuteReaderAsync(CancellationToken cancellationToken)

Sends the command to Cloud Spanner and builds a SpannerDataReader.

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

Returns
Type	Description
`TaskSpannerDataReader`	An asynchronous Task that produces a SpannerDataReader.

ExecuteScalar()

public override object 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.

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

Overrides

DbCommand.ExecuteScalar()

ExecuteScalarAsync(CancellationToken)

public override Task<object> ExecuteScalarAsync(CancellationToken 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.

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

Returns
Type	Description
`Taskobject`	A task representing the asynchronous operation.

Overrides

DbCommand.ExecuteScalarAsync(CancellationToken)

Exceptions
Type	Description
`DbException`	An error occurred while executing the command text.

ExecuteScalarAsync<T>(CancellationToken)

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

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.

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

Returns
Type	Description
`Task`	The first column of the first row resulting from execution of the query.

Type Parameter

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)

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

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.

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

Returns
Type	Description
`TaskIReadOnlyListCommandPartition`	The list of partitions that can be used to create SpannerCommand objects.

Prepare()

public override void Prepare()

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

Overrides

DbCommand.Prepare()