Class MySqlCommand

Represents a SQL statement to execute against a MySQL database. This class cannot be inherited.

Inheritance

System.Object

System.MarshalByRefObject

System.ComponentModel.Component

System.Data.Common.DbCommand

MySqlCommand

Implements

System.ComponentModel.IComponent

System.Data.IDbCommand

System.IAsyncDisposable

System.IDisposable

System.ICloneable

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data.dll

Version: 9.1.0

Syntax

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

Remarks

You can reset the CommandText property and reuse the MySqlCommand object. However, you must close the MySqlDataReader object before you can execute a new or previous command.

If an exception of type MySqlException is generated by the method executing MySqlCommand, the MySqlConnection instance remains open. It is the responsibility of the programmer to close the connection.

You can read more about it here.

note

Using the '@' symbol for paramters is now the preferred approach although the old pattern of using '?' is still supported. Please be aware that using '@' can cause conflicts when user variables are also used. For more information, see the documentation on the AllowUserVariables connection string option.

Constructors

MySqlCommand()

Initializes a new instance of the MySqlCommand class.

Declaration

public MySqlCommand()

Remarks

The base constructor initializes all fields to their default values.

MySqlCommand(String)

Initializes a new instance of the MySqlCommand class with the text of the query.

Declaration

public MySqlCommand(string cmdText)

Parameters

Type	Name	Description
System.String	cmdText	The text of the query.

MySqlCommand(String, MySqlConnection)

Initializes a new instance of the MySqlCommand class with the text of the query and a MySqlConnection.

Declaration

public MySqlCommand(string cmdText, MySqlConnection connection)

Parameters

Type	Name	Description
System.String	cmdText	The text of the query.
MySqlConnection	connection	A MySqlConnection that represents the connection to an instance of MySQL Server.

MySqlCommand(String, MySqlConnection, MySqlTransaction)

Initializes a new instance of the MySqlCommand class with the text of the query, a MySqlConnection, and the MySqlTransaction.

Declaration

public MySqlCommand(string cmdText, MySqlConnection connection, MySqlTransaction transaction)

Parameters

Type	Name	Description
System.String	cmdText	The text of the query.
MySqlConnection	connection	A MySqlConnection that represents the connection to an instance of MySQL Server.
MySqlTransaction	transaction	The MySqlTransaction in which the MySqlCommand executes.

Properties

Attributes

Gets the MySqlAttributeCollection object.

Declaration

public MySqlAttributeCollection Attributes { get; }

Property Value

Type	Description
MySqlAttributeCollection	The query attributes defined for the statement. The default is an empty collection.

Remarks

Connector/NET does not support unnamed query attributes. Every query attribute added to the collection must have an associated name. You can read more about it here.

note

Query Attributes can be used along with MySqlParameter. There are no restrictions in this regard.

CacheAge

Gets or sets the seconds for how long a TableDirect result should be cached.

Declaration

public int CacheAge { get; set; }

Property Value

Type	Description
System.Int32	Number of seconds.

CommandText

Gets or sets the SQL statement to execute at the data source.

Declaration

public override string CommandText { get; set; }

Property Value

Type	Description
System.String	The SQL statement or stored procedure to execute. The default is an empty string.

Overrides

System.Data.Common.DbCommand.CommandText

Remarks

You can read more about it here.

CommandTimeout

Gets or sets the wait time before terminating the attempt to execute a command and generating an error.

Declaration

public override int CommandTimeout { get; set; }

Property Value

Type	Description
System.Int32	The time (in seconds) to wait for the command to execute. The default is 30 seconds.

Overrides

System.Data.Common.DbCommand.CommandTimeout

Remarks

CommandTimeout is dependent on the ability of MySQL to cancel an executing query.

CommandType

Gets or sets a value indicating how the CommandText property is to be interpreted.

Declaration

public override CommandType CommandType { get; set; }

Property Value

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

Overrides

System.Data.Common.DbCommand.CommandType

Remarks

You can read more about it here.

Connection

Gets or sets the MySqlConnection object used by this instance of the MySqlCommand.

Declaration

public MySqlConnection Connection { get; set; }

Property Value

Type	Description
MySqlConnection	The connection to a data source. The default value is a null reference.

DbConnection

Gets or sets the DbConnection used by this System.Data.Common.DbCommand.

Declaration

protected override DbConnection DbConnection { get; set; }

Property Value

Type	Description
System.Data.Common.DbConnection	The connection.

Overrides

System.Data.Common.DbCommand.DbConnection

DbParameterCollection

Gets the collection of MySqlParameter objects.

Declaration

protected override DbParameterCollection DbParameterCollection { get; }

Property Value

Type	Description
System.Data.Common.DbParameterCollection	The collection.

Overrides

System.Data.Common.DbCommand.DbParameterCollection

DbTransaction

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

Declaration

protected override DbTransaction DbTransaction { get; set; }

Property Value

Type	Description
System.Data.Common.DbTransaction	The transaction.

Overrides

System.Data.Common.DbCommand.DbTransaction

DesignTimeVisible

Gets or sets a value indicating whether the command object should be visible in a Windows Form Designer control.

Declaration

[Browsable(false)]
public override bool DesignTimeVisible { get; set; }

Property Value

Type	Description
System.Boolean	True if it should be visible; otherwise, false.

Overrides

System.Data.Common.DbCommand.DesignTimeVisible

EnableCaching

Gets or sets a System.Boolean value that indicates whether caching is enabled.

Declaration

public bool EnableCaching { get; set; }

Property Value

Type	Description
System.Boolean	True if it is enabled; otherwise, false.

IsPrepared

Gets a boolean value that indicates whether the Prepare() method has been called.

Declaration

[Browsable(false)]
public bool IsPrepared { get; }

Property Value

Type	Description
System.Boolean	True if it is Prepared; otherwise, false.

LastInsertedId

Provides the ID of the last inserted row.

Declaration

[Browsable(false)]
public long LastInsertedId { get; }

Property Value

Type	Description
System.Int64	ID of the last inserted row. -1 if none exists.

Remarks

An important point to remember is that this property can be used in batch SQL scenarios but it's important to remember that it will only reflect the insert ID from the last insert statement in the batch. This property can also be used when the batch includes select statements and ExecuteReader is used. This property can be consulted during result set processing.

Parameters

Gets the MySqlParameterCollection object.

Declaration

public MySqlParameterCollection Parameters { get; }

Property Value

Type	Description
MySqlParameterCollection	The parameters of the SQL statement or stored procedure. The default is an empty collection.

Remarks

Connector/NET does not support unnamed parameters. Every parameter added to the collection must have an associated name. You can read more about it here.

note

Parameters can be used along with MySqlAttribute. There are no restrictions in this regard.

Transaction

Gets or sets the instance of MySqlTransaction within which MySqlCommand executes.

Declaration

[Browsable(false)]
public MySqlTransaction Transaction { get; set; }

Property Value

Type	Description
MySqlTransaction	The MySqlTransaction. The default value is a null reference (Nothing in Visual Basic).

Remarks

You cannot set the Transaction property if it is already set to a specific value, and the command is in the process of executing. If you set the transaction to use a MySqlTransaction object that is not connected to the same MySqlConnection as the MySqlCommand object, an exception will be thrown the next time you attempt to execute a statement.

UpdatedRowSource

Gets or sets how command results are applied to the System.Data.DataRow when used by the System.Data.Common.DbDataAdapter.Update(System.Data.DataRow[]) method of the System.Data.Common.DbDataAdapter.

Declaration

public override UpdateRowSource UpdatedRowSource { get; set; }

Property Value

Type	Description
System.Data.UpdateRowSource	One of the System.Data.UpdateRowSource values.

Overrides

System.Data.Common.DbCommand.UpdatedRowSource

Remarks

The default System.Data.UpdateRowSource value is Both unless the command is automatically generated (as in the case of the MySqlCommandBuilder), in which case the default is None.

Methods

BeginExecuteNonQuery()

Initiates the asynchronous execution of the SQL statement or stored procedure that is described by this MySqlCommand.

Declaration

[Obsolete]
public IAsyncResult BeginExecuteNonQuery()

Returns

Type	Description
System.IAsyncResult	An System.IAsyncResult that can be used to poll or wait for results, or both; this value is also needed when invoking EndExecuteNonQuery(IAsyncResult), which returns the number of affected rows.

BeginExecuteNonQuery(AsyncCallback, Object)

Initiates the asynchronous execution of the SQL statement or stored procedure that is described by this MySqlCommand.

Declaration

[Obsolete]
public IAsyncResult BeginExecuteNonQuery(AsyncCallback callback, object stateObject)

Parameters

Type	Name	Description
System.AsyncCallback	callback	An System.AsyncCallback delegate that is invoked when the command's execution has completed. Pass a null reference to indicate that no callback is required.
System.Object	stateObject	A user-defined state object that is passed to the callback procedure. Retrieve this object from within the callback procedure using the System.IAsyncResult.AsyncState property.

Returns

Type	Description
System.IAsyncResult	An System.IAsyncResult that can be used to poll or wait for results, or both; this value is also needed when invoking EndExecuteNonQuery(IAsyncResult), which returns the number of affected rows.

BeginExecuteReader()

Initiates the asynchronous execution of the SQL statement or stored procedure that is described by this MySqlCommand, and retrieves one or more result sets from the server.

Declaration

[Obsolete]
public IAsyncResult BeginExecuteReader()

Returns

Type	Description
System.IAsyncResult	An System.IAsyncResult that can be used to poll, wait for results, or both; this value is also needed when invoking EndExecuteReader, which returns a MySqlDataReader instance that can be used to retrieve the returned rows.

BeginExecuteReader(CommandBehavior)

Initiates the asynchronous execution of the SQL statement or stored procedure that is described by this MySqlCommand using one of the CommandBehavior values.

Declaration

[Obsolete]
public IAsyncResult BeginExecuteReader(CommandBehavior behavior)

Parameters

Type	Name	Description
System.Data.CommandBehavior	behavior	One of the System.Data.CommandBehavior values, indicating options for statement execution and data retrieval.

Returns

Type	Description
System.IAsyncResult	An System.IAsyncResult that can be used to poll, wait for results, or both; this value is also needed when invoking EndExecuteReader, which returns a MySqlDataReader instance that can be used to retrieve the returned rows.

Cancel()

Attempts to cancel the execution of a currently active command

Declaration

public override void Cancel()

Overrides

System.Data.Common.DbCommand.Cancel()

Clone()

Creates a clone of this MySqlCommand object. CommandText, Connection, and Transaction properties are included as well as the entire parameter and the arribute list.

Declaration

public object Clone()

Returns

Type	Description
System.Object	The cloned MySqlCommand object.

CreateDbParameter()

Declaration

protected override DbParameter CreateDbParameter()

Returns

Type	Description
System.Data.Common.DbParameter

Overrides

System.Data.Common.DbCommand.CreateDbParameter()

CreateParameter()

Creates a new instance of a MySqlParameter object.

Declaration

public MySqlParameter CreateParameter()

Returns

Type	Description
MySqlParameter	A MySqlParameter object.

Remarks

This method is a strongly-typed version of System.Data.IDbCommand.CreateParameter.

Dispose()

Releases the resources used by the MySqlCommand

Declaration

public void Dispose()

Dispose(Boolean)

Releases the resources used by the MySqlCommand

Declaration

protected override void Dispose(bool disposing)

Parameters

Type	Name	Description
System.Boolean	disposing

Overrides

System.ComponentModel.Component.Dispose(System.Boolean)

DisposeAsync()

Declaration

public override ValueTask DisposeAsync()

Returns

Type	Description
System.Threading.Tasks.ValueTask

Overrides

System.Data.Common.DbCommand.DisposeAsync()

EndExecuteNonQuery(IAsyncResult)

Finishes asynchronous execution of a SQL statement.

Declaration

[Obsolete]
public int EndExecuteNonQuery(IAsyncResult asyncResult)

Parameters

Type	Name	Description
System.IAsyncResult	asyncResult	The System.IAsyncResult returned by the call to BeginExecuteNonQuery().

Returns

Type	Description
System.Int32

EndExecuteReader(IAsyncResult)

Finishes asynchronous execution of a SQL statement, returning the requested MySqlDataReader.

Declaration

[Obsolete]
public MySqlDataReader EndExecuteReader(IAsyncResult result)

Parameters

Type	Name	Description
System.IAsyncResult	result	The System.IAsyncResult returned by the call to BeginExecuteReader().

Returns

Type	Description
MySqlDataReader	A MySqlDataReader object that can be used to retrieve the requested rows.

ExecuteDbDataReader(CommandBehavior)

Declaration

protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)

Parameters

Type	Name	Description
System.Data.CommandBehavior	behavior

Returns

Type	Description
System.Data.Common.DbDataReader

Overrides

System.Data.Common.DbCommand.ExecuteDbDataReader(System.Data.CommandBehavior)

ExecuteDbDataReaderAsync(CommandBehavior, CancellationToken)

Declaration

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

Parameters

Type	Name	Description
System.Data.CommandBehavior	behavior
System.Threading.CancellationToken	cancellationToken

Returns

Type	Description
System.Threading.Tasks.Task<System.Data.Common.DbDataReader>

Overrides

System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(System.Data.CommandBehavior, System.Threading.CancellationToken)

ExecuteNonQuery()

Executes a SQL statement against the connection and returns the number of rows affected.

Declaration

public override int ExecuteNonQuery()

Returns

Type	Description
System.Int32	Number of rows affected

Overrides

System.Data.Common.DbCommand.ExecuteNonQuery()

Remarks

You can use ExecuteNonQuery() to perform any type of database operation, however any resultsets returned will not be available. Any output parameters used in calling a stored procedure will be populated with data and can be retrieved after execution is complete. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

ExecuteNonQueryAsync(CancellationToken)

Asynchronous version of ExecuteNonQuery().

Declaration

public override Task<int> ExecuteNonQueryAsync(CancellationToken cancellationToken)

Parameters

Type	Name	Description
System.Threading.CancellationToken	cancellationToken	The cancellation token.

Returns

Type	Description
System.Threading.Tasks.Task<System.Int32>	A task representing the asynchronous operation.

Overrides

System.Data.Common.DbCommand.ExecuteNonQueryAsync(System.Threading.CancellationToken)

ExecuteReader()

Sends the CommandText value to MySqlConnection and builds a MySqlDataReader object.

Declaration

public MySqlDataReader ExecuteReader()

Returns

Type	Description
MySqlDataReader	A MySqlDataReader object.

Remarks

When the CommandType property is set to StoredProcedure, the CommandText property should be set to the name of the stored procedure. The command executes this stored procedure when you call ExecuteReader.

While MySqlDataReader is in use, the associated instance of MySqlConnection is busy serving it and no other operations can be performed on MySqlConnection, other than closing it. This is the case until the Close() method of MySqlDataReader is called.

ExecuteReader(CommandBehavior)

Sends the CommandText to the Connection, and builds a MySqlDataReader using one of the System.Data.CommandBehavior values.

Declaration

public MySqlDataReader ExecuteReader(CommandBehavior behavior)

Parameters

Type	Name	Description
System.Data.CommandBehavior	behavior	One of the System.Data.CommandBehavior values.

Returns

Type	Description
MySqlDataReader	A MySqlDataReader object.

Remarks

When the CommandType property is set to StoredProcedure, the CommandText property should be set to the name of the stored procedure. The command executes this stored procedure when you call ExecuteReader.

If the MySqlDataReader object is created with CommandBehavior set to CloseConnection, closing the MySqlDataReader instance closes the connection automatically.

note

When calling ExecuteReader with the SingleRow behavior, you should be aware that using a limit clause in your SQL will cause all rows (up to the limit given) to be retrieved by the client. The Read() method will still return false after the first row but pulling all rows of data into the client will have a performance impact. If the limit clause is not necessary, it should be avoided.

ExecuteReaderAsync(CommandBehavior)

Asynchronous version of ExecuteReader(CommandBehavior).

Declaration

public Task<MySqlDataReader> ExecuteReaderAsync(CommandBehavior behavior)

Parameters

Type	Name	Description
System.Data.CommandBehavior	behavior	One of the System.Data.CommandBehavior values.

Returns

Type	Description
System.Threading.Tasks.Task<MySqlDataReader>	A task representing the asynchronous operation.

ExecuteReaderAsync(CommandBehavior, CancellationToken)

Asynchronous version of ExecuteReader(CommandBehavior) with a cancellation token.

Declaration

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

Parameters

Type	Name	Description
System.Data.CommandBehavior	behavior	One of the System.Data.CommandBehavior values.
System.Threading.CancellationToken	cancellationToken	The cancellation token.

Returns

Type	Description
System.Threading.Tasks.Task<MySqlDataReader>	A task representing the asynchronous operation.

ExecuteScalar()

Executes the query, and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.

Declaration

public override object ExecuteScalar()

Returns

Type	Description
System.Object	The first column of the first row in the result set, or a null reference if the result set is empty

Overrides

System.Data.Common.DbCommand.ExecuteScalar()

Remarks

Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader() method, and then performing the operations necessary to generate the single value using the data returned by a MySqlDataReader

ExecuteScalarAsync(CancellationToken)

Asynchronous version of ExecuteScalar().

Declaration

public override Task<object> ExecuteScalarAsync(CancellationToken cancellationToken)

Parameters

Type	Name	Description
System.Threading.CancellationToken	cancellationToken	The cancellation token.

Returns

Type	Description
System.Threading.Tasks.Task<System.Object>	A task representing the asynchronous operation.

Overrides

System.Data.Common.DbCommand.ExecuteScalarAsync(System.Threading.CancellationToken)

Finalize()

Declaration

protected void Finalize()

Prepare()

Creates a prepared version of the command on an instance of MySQL Server.

Declaration

public override void Prepare()

Overrides

System.Data.Common.DbCommand.Prepare()

PrepareAsync(CancellationToken)

Asynchronously creates a prepared version of the command on an instance of MySQL Server.

Declaration

public override Task PrepareAsync(CancellationToken cancellationToken = default(CancellationToken))

Parameters

Type	Name	Description
System.Threading.CancellationToken	cancellationToken

Returns

Type	Description
System.Threading.Tasks.Task

Overrides

System.Data.Common.DbCommand.PrepareAsync(System.Threading.CancellationToken)

Implements

System.ComponentModel.IComponent

System.Data.IDbCommand

System.IAsyncDisposable

System.IDisposable

System.ICloneable