Search Results for

    Show / Hide Table of Contents

    Class MySqlCommand

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

    Inheritance
    Object
    MarshalByRefObject
    Component
    DbCommand
    MySqlCommand
    Implements
    IComponent
    IDbCommand
    IAsyncDisposable
    IDisposable
    ICloneable
    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

    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.

    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
    In This Article
    Back to top Copyright © 2021, 2023, Oracle and/or its affiliates.