Class SpannerCommand (3.7.0)

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

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

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
NameDescription
commandTextBuilderSpannerCommandTextBuilder

The Google.Cloud.Spanner.Data.SpannerCommand.SpannerCommandTextBuilder that configures the text of this command. Must not be null.

connectionSpannerConnection

The SpannerConnection that is associated with this SpannerCommand. Must not be null.

transactionSpannerTransaction

An optional SpannerTransaction created through BeginTransactionAsync(CancellationToken). May be null.

parametersSpannerParameterCollection

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
NameDescription
connectionSpannerConnection

The SpannerConnection that is associated with this SpannerCommand. Must not be null.

transactionSpannerTransaction

The SpannerTransaction used when creating the CommandPartition.

commandPartitionCommandPartition

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
NameDescription
commandTextString

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"

connectionSpannerConnection

The SpannerConnection that is associated with this SpannerCommand. Must not be null.

transactionSpannerTransaction

An optional SpannerTransaction created through . May be null.

parametersSpannerParameterCollection

An optional collection of SpannerParameter that is used in the command. May be null.

Properties

CommandText

public override string CommandText { get; set; }
Property Value
TypeDescription
String
Overrides

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
TypeDescription
Int32
Overrides 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; }
Property Value
TypeDescription
CommandType
Overrides

DbConnection

protected override DbConnection DbConnection { get; set; }
Property Value
TypeDescription
DbConnection
Overrides

DbParameterCollection

protected override DbParameterCollection DbParameterCollection { get; }
Property Value
TypeDescription
DbParameterCollection
Overrides

DbTransaction

protected override DbTransaction DbTransaction { get; set; }
Property Value
TypeDescription
DbTransaction
Overrides

DesignTimeVisible

public override bool DesignTimeVisible { get; set; }
Property Value
TypeDescription
Boolean
Overrides

Parameters

public SpannerParameterCollection Parameters { get; }

The parameters of the SQL statement or command.

Property Value
TypeDescription
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
TypeDescription
CommandPartition

Priority

public Priority Priority { get; set; }

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

Property Value
TypeDescription
Priority

QueryOptions

public QueryOptions QueryOptions { get; set; }

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

Property Value
TypeDescription
QueryOptions

SpannerConnection

public SpannerConnection SpannerConnection { get; set; }

The connection to the data source.

Property Value
TypeDescription
SpannerConnection

Tag

public string Tag { get; set; }

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

Property Value
TypeDescription
String

UpdatedRowSource

public override UpdateRowSource UpdatedRowSource { get; set; }
Property Value
TypeDescription
UpdateRowSource
Overrides

Methods

Cancel()

public override void Cancel()
Overrides

Clone()

public object Clone()

Returns a copy of this SpannerCommand.

Returns
TypeDescription
Object

a copy of this SpannerCommand.

CreateDbParameter()

protected override DbParameter CreateDbParameter()
Returns
TypeDescription
DbParameter
Overrides

Dispose(Boolean)

protected override void Dispose(bool disposing)
Parameter
NameDescription
disposingBoolean
Overrides

ExecuteDbDataReader(CommandBehavior)

protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
Parameter
NameDescription
behaviorCommandBehavior
Returns
TypeDescription
DbDataReader
Overrides

ExecuteDbDataReaderAsync(CommandBehavior, CancellationToken)

protected override Task<DbDataReader> ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
Parameters
NameDescription
behaviorCommandBehavior
cancellationTokenCancellationToken
Returns
TypeDescription
Task<DbDataReader>
Overrides

ExecuteNonQuery()

public override int ExecuteNonQuery()
Returns
TypeDescription
Int32
Overrides

ExecuteNonQueryAsync(CancellationToken)

public override Task<int> ExecuteNonQueryAsync(CancellationToken cancellationToken)
Parameter
NameDescription
cancellationTokenCancellationToken
Returns
TypeDescription
Task<Int32>
Overrides

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
TypeDescription
Int64

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

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

Parameter
NameDescription
cancellationTokenCancellationToken

An optional token for canceling the call.

Returns
TypeDescription
Task<Int64>

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

public Task<SpannerDataReader> ExecuteReaderAsync()

Sends the command to Cloud Spanner and builds a SpannerDataReader.

Returns
TypeDescription
Task<SpannerDataReader>

An asynchronous Task that produces a SpannerDataReader.

ExecuteReaderAsync(TimestampBound, CancellationToken)

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

Executes the command against the SpannerConnection.

Parameters
NameDescription
singleUseReadSettingsTimestampBound

The settings to use for the implicit single-use read-only transaction.

cancellationTokenCancellationToken

A cancellation token for the operation.

Returns
TypeDescription
Task<DbDataReader>

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
NameDescription
behaviorCommandBehavior

Options for statement execution and data retrieval.

Returns
TypeDescription
Task<SpannerDataReader>

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
NameDescription
behaviorCommandBehavior

Options for statement execution and data retrieval.

cancellationTokenCancellationToken

An optional token for canceling the call.

Returns
TypeDescription
Task<SpannerDataReader>

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
NameDescription
cancellationTokenCancellationToken

An optional token for canceling the call.

Returns
TypeDescription
Task<SpannerDataReader>

An asynchronous Task that produces a SpannerDataReader.

ExecuteScalar()

public override object ExecuteScalar()
Returns
TypeDescription
Object
Overrides

ExecuteScalarAsync(CancellationToken)

public override Task<object> ExecuteScalarAsync(CancellationToken cancellationToken)
Parameter
NameDescription
cancellationTokenCancellationToken
Returns
TypeDescription
Task<Object>
Overrides

ExecuteScalarAsync<T>(CancellationToken)

public Task<T> ExecuteScalarAsync<T>(CancellationToken cancellationToken = default(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.

Parameter
NameDescription
cancellationTokenCancellationToken

An optional token for canceling the call.

Returns
TypeDescription
Task<T>

The first column of the first row resulting from execution of the query.

Type Parameter
NameDescription
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(Nullable<Int64>, Nullable<Int64>, CancellationToken)

public Task<IReadOnlyList<CommandPartition>> GetReaderPartitionsAsync(long? partitionSizeBytes = default(long? ), long? maxPartitions = default(long? ), CancellationToken cancellationToken = default(CancellationToken))

Creates a set of CommandPartition objects that are used to execute a query 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
NameDescription
partitionSizeBytesNullable<Int64>

The desired data size for each partition generated. The default for this option is currently 1 GiB. This is only a hint. The actual size of each partition may be smaller or larger than this size request.

maxPartitionsNullable<Int64>

The desired maximum number of partitions to return. For example, this may be set to the number of workers available. The default for this option is currently 10,000. The maximum value is currently 200,000. This is only a hint. The actual number of partitions returned may be smaller or larger than this maximum count request.

cancellationTokenCancellationToken

An optional token for canceling the call.

Returns
TypeDescription
Task<IReadOnlyList<CommandPartition>>

The list of partitions that can be used to create SpannerCommand objects.

Prepare()

public override void Prepare()
Overrides