Class MySqlCommand

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

Inheritance

Object

MarshalByRefObject

Component

DbCommand

MySqlCommand

Implements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data.dll

Version: 8.0.32

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
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
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
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
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
String	The SQL statement or stored procedure to execute. The default is an empty string.

Overrides

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
Int32	The time (in seconds) to wait for the command to execute. The default is 30 seconds.

Overrides

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
CommandType	One of the CommandType values. The default is Text.

Overrides

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 DbCommand.

Declaration

protected override DbConnection DbConnection { get; set; }

Property Value

Type	Description
DbConnection	The connection.

Overrides

DbCommand.DbConnection

DbParameterCollection

Gets the collection of MySqlParameter objects.

Declaration

protected override DbParameterCollection DbParameterCollection { get; }

Property Value

Type	Description
DbParameterCollection	The collection.

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.

Overrides

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
Boolean	True if it should be visible; otherwise, false.

Overrides

DbCommand.DesignTimeVisible

EnableCaching

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

Declaration

public bool EnableCaching { get; set; }

Property Value

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

IsPrepared

Gets a boolean value that indicates whether the MySql.Data.MySqlClient.MySqlCommand.Prepare(System.Int32) method has been called.

Declaration

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

Property Value

Type	Description
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
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 DataRow when used by the Update(DataRow[]) method of the DbDataAdapter.

Declaration

public override UpdateRowSource UpdatedRowSource { get; set; }

Property Value

Type	Description
UpdateRowSource	One of the UpdateRowSource values.

Overrides

DbCommand.UpdatedRowSource

Remarks

The default 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

public IAsyncResult BeginExecuteNonQuery()

Returns

Type	Description
IAsyncResult	An 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

public IAsyncResult BeginExecuteNonQuery(AsyncCallback callback, object stateObject)

Parameters

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

Returns

Type	Description
IAsyncResult	An 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

public IAsyncResult BeginExecuteReader()

Returns

Type	Description
IAsyncResult	An 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

public IAsyncResult BeginExecuteReader(CommandBehavior behavior)

Parameters

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

Returns

Type	Description
IAsyncResult	An 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

DbCommand.Cancel()

Remarks

Cancelling a currently active query only works with MySQL versions 5.0.0 and higher.

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
Object	The cloned MySqlCommand object.

CreateDbParameter()

Declaration

protected override DbParameter CreateDbParameter()

Returns

Type	Description
DbParameter

Overrides

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 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
Boolean	disposing

Overrides

Component.Dispose(Boolean)

EndExecuteNonQuery(IAsyncResult)

Finishes asynchronous execution of a SQL statement.

Declaration

public int EndExecuteNonQuery(IAsyncResult asyncResult)

Parameters

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

Returns

Type	Description
Int32

EndExecuteReader(IAsyncResult)

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

Declaration

public MySqlDataReader EndExecuteReader(IAsyncResult result)

Parameters

Type	Name	Description
IAsyncResult	result	The 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
CommandBehavior	behavior

Returns

Type	Description
DbDataReader

Overrides

DbCommand.ExecuteDbDataReader(CommandBehavior)

ExecuteNonQuery()

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

Declaration

public override int ExecuteNonQuery()

Returns

Type	Description
Int32	Number of rows affected

Overrides

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.

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 CommandBehavior values.

Declaration

public MySqlDataReader ExecuteReader(CommandBehavior behavior)

Parameters

Type	Name	Description
CommandBehavior	behavior	One of the CommandBehavior values.

Returns

Type	Description
MySqlDataReader	A MySqlDataReader object.

Remarks

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.

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
Object	The first column of the first row in the result set, or a null reference if the result set is empty

Overrides

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

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

DbCommand.Prepare()

Implements

System.ComponentModel.IComponent

System.Data.IDbCommand

System.IAsyncDisposable

System.IDisposable

System.ICloneable