[+/-]
Represents a SQL statement to execute against a MySQL database. This class cannot be inherited.
MySqlCommand
features the following methods
for executing commands at a MySQL database:
Item | Description |
ExecuteReader | Executes commands that return rows. |
ExecuteNonQuery | Executes commands such as SQL INSERT, DELETE, and UPDATE statements. |
ExecuteScalar | Retrieves a single value (for example, an aggregate value) from a database. |
You can reset the CommandText
property and
reuse the MySqlCommand
object. However, you
must close the
MySqlDataReader
before you can execute a new or previous command.
If a
MySqlException
is generated by the method executing a
MySqlCommand
, the
MySqlConnection
remains open. It is the responsibility of the programmer to
close the connection.
Note. Prior versions of the provider used the '@' symbol to mark parameters in SQL. This is incompatible with MySQL user variables, so the provider now uses the '?' symbol to locate parameters in SQL. To support older code, you can set 'old syntax=yes' on your connection string. If you do this, please be aware that an exception will not be throw if you fail to define a parameter that you intended to use in your SQL.
Examples
The following example creates a
MySqlCommand
and a MySqlConnection
. The
MySqlConnection
is opened and set as the
Connection
for the MySqlCommand
. The example then calls
ExecuteNonQuery,
and closes the connection. To accomplish this, the
ExecuteNonQuery
is passed a connection string
and a query string that is a SQL INSERT statement.
Visual Basic example:
Public Sub InsertRow(myConnectionString As String) " If the connection string is null, use a default. If myConnectionString = "" Then myConnectionString = "Database=Test;Data Source=localhost;User Id=username;Password=pass" End If Dim myConnection As New MySqlConnection(myConnectionString) Dim myInsertQuery As String = "INSERT INTO Orders (id, customerId, amount) Values(1001, 23, 30.66)" Dim myCommand As New MySqlCommand(myInsertQuery) myCommand.Connection = myConnection myConnection.Open() myCommand.ExecuteNonQuery() myCommand.Connection.Close() End Sub
C# example:
public void InsertRow(string myConnectionString) { // If the connection string is null, use a default. if(myConnectionString == "") { myConnectionString = "Database=Test;Data Source=localhost;User Id=username;Password=pass"; } MySqlConnection myConnection = new MySqlConnection(myConnectionString); string myInsertQuery = "INSERT INTO Orders (id, customerId, amount) Values(1001, 23, 30.66)"; MySqlCommand myCommand = new MySqlCommand(myInsertQuery); myCommand.Connection = myConnection; myConnection.Open(); myCommand.ExecuteNonQuery(); myCommand.Connection.Close(); }
Overload methods for MySqlCommand
Initializes a new instance of the MySqlCommand class.
Examples
The following example creates a MySqlCommand and sets some of its properties.
Note. This example shows how to use one of the overloaded versions of the MySqlCommand constructor. For other examples that might be available, see the individual overload topics.
Visual Basic example:
Public Sub CreateMySqlCommand() Dim myConnection As New MySqlConnection _ ("Persist Security Info=False;database=test;server=myServer") myConnection.Open() Dim myTrans As MySqlTransaction = myConnection.BeginTransaction() Dim mySelectQuery As String = "SELECT * FROM MyTable" Dim myCommand As New MySqlCommand(mySelectQuery, myConnection, myTrans) myCommand.CommandTimeout = 20 End Sub
C# example:
public void CreateMySqlCommand() { MySqlConnection myConnection = new MySqlConnection("Persist Security Info=False; database=test;server=myServer"); myConnection.Open(); MySqlTransaction myTrans = myConnection.BeginTransaction(); string mySelectQuery = "SELECT * FROM myTable"; MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection,myTrans); myCommand.CommandTimeout = 20; }
C++ example:
public: void CreateMySqlCommand() { MySqlConnection* myConnection = new MySqlConnection(S"Persist Security Info=False; database=test;server=myServer"); myConnection->Open(); MySqlTransaction* myTrans = myConnection->BeginTransaction(); String* mySelectQuery = S"SELECT * FROM myTable"; MySqlCommand* myCommand = new MySqlCommand(mySelectQuery, myConnection, myTrans); myCommand->CommandTimeout = 20; };
Initializes a new instance of the MySqlCommand class.
The base constructor initializes all fields to their default
values. The following table shows initial property values for
an instance of MySqlCommand
.
Properties | Initial Value |
CommandText |
empty string ("") |
CommandTimeout |
0 |
CommandType |
CommandType.Text |
Connection |
Null |
You can change the value for any of these properties through a separate call to the property.
Examples
The following example creates a
MySqlCommand
and sets some of its
properties.
Visual Basic example:
Public Sub CreateMySqlCommand() Dim myCommand As New MySqlCommand() myCommand.CommandType = CommandType.Text End Sub
C# example:
public void CreateMySqlCommand() { MySqlCommand myCommand = new MySqlCommand(); myCommand.CommandType = CommandType.Text; }
Initializes a new instance of the
MySqlCommand
class with the text of the
query.
Parameters: The text of the query.
When an instance of MySqlCommand
is
created, the following read/write properties are set to
initial values.
Properties | Initial Value |
CommandText |
cmdText |
CommandTimeout |
0 |
CommandType |
CommandType.Text |
Connection |
Null |
You can change the value for any of these properties through a separate call to the property.
Examples
The following example creates a
MySqlCommand
and sets some of its
properties.
Visual Basic example:
Public Sub CreateMySqlCommand() Dim sql as String = "SELECT * FROM mytable" Dim myCommand As New MySqlCommand(sql) myCommand.CommandType = CommandType.Text End Sub
C# example:
public void CreateMySqlCommand() { string sql = "SELECT * FROM mytable"; MySqlCommand myCommand = new MySqlCommand(sql); myCommand.CommandType = CommandType.Text; }
Initializes a new instance of the
MySqlCommand
class with the text of the
query and a MySqlConnection
.
Parameters: The text of the query.
Parameters: A
MySqlConnection
that represents the
connection to an instance of SQL Server.
When an instance of MySqlCommand
is
created, the following read/write properties are set to
initial values.
Properties | Initial Value |
CommandText |
cmdText |
CommandTimeout |
0 |
CommandType |
CommandType.Text |
Connection |
connection |
You can change the value for any of these properties through a separate call to the property.
Examples
The following example creates a
MySqlCommand
and sets some of its
properties.
Visual Basic example:
Public Sub CreateMySqlCommand() Dim conn as new MySqlConnection("server=myServer") Dim sql as String = "SELECT * FROM mytable" Dim myCommand As New MySqlCommand(sql, conn) myCommand.CommandType = CommandType.Text End Sub
C# example:
public void CreateMySqlCommand() { MySqlConnection conn = new MySqlConnection("server=myserver") string sql = "SELECT * FROM mytable"; MySqlCommand myCommand = new MySqlCommand(sql, conn); myCommand.CommandType = CommandType.Text; }
Initializes a new instance of the
MySqlCommand
class with the text of the
query, a MySqlConnection
, and the
MySqlTransaction
.
Parameters: The text of the query.
Parameters: A
MySqlConnection
that represents the
connection to an instance of SQL Server.
Parameters: The
MySqlTransaction
in which the
MySqlCommand
executes.
When an instance of MySqlCommand
is
created, the following read/write properties are set to
initial values.
Properties | Initial Value |
CommandText |
cmdText |
CommandTimeout |
0 |
CommandType |
CommandType.Text |
Connection |
connection |
You can change the value for any of these properties through a separate call to the property.
Examples
The following example creates a
MySqlCommand
and sets some of its
properties.
Visual Basic example:
Public Sub CreateMySqlCommand() Dim conn as new MySqlConnection("server=myServer") conn.Open(); Dim txn as MySqlTransaction = conn.BeginTransaction() Dim sql as String = "SELECT * FROM mytable" Dim myCommand As New MySqlCommand(sql, conn, txn) myCommand.CommandType = CommandType.Text End Sub
C# example:
public void CreateMySqlCommand() { MySqlConnection conn = new MySqlConnection("server=myserver") conn.Open(); MySqlTransaction txn = conn.BeginTransaction(); string sql = "SELECT * FROM mytable"; MySqlCommand myCommand = new MySqlCommand(sql, conn, txn); myCommand.CommandType = CommandType.Text; }
Executes a SQL statement against the connection and returns the number of rows affected.
Returns: Number of rows affected
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.
Examples
The following example creates a MySqlCommand and then executes it using ExecuteNonQuery. The example is passed a string that is a SQL statement (such as UPDATE, INSERT, or DELETE) and a string to use to connect to the data source.
Visual Basic example:
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As MySqlConnection) Dim myCommand As New MySqlCommand(myExecuteQuery, myConnection) myCommand.Connection.Open() myCommand.ExecuteNonQuery() myConnection.Close() End Sub
C# example:
public void CreateMySqlCommand(string myExecuteQuery, MySqlConnection myConnection) { MySqlCommand myCommand = new MySqlCommand(myExecuteQuery, myConnection); myCommand.Connection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); }
Sends the CommandText
to the
MySqlConnection
Connection, and builds a
MySqlDataReader
using one of the
CommandBehavior
values.
Parameters: One of the
CommandBehavior
values.
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
.
The MySqlDataReader
supports a special mode
that enables large binary values to be read efficiently. For
more information, see the SequentialAccess
setting for CommandBehavior
.
While the MySqlDataReader
is in use, the
associated MySqlConnection
is busy serving
the MySqlDataReader
. While in this state,
no other operations can be performed on the
MySqlConnection
other than closing it. This
is the case until the MySqlDataReader.Close
method of the MySqlDataReader
is called. If
the MySqlDataReader
is created with
CommandBehavior
set to
CloseConnection
, closing the
MySqlDataReader
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
MySqlDataReader.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.
Returns: A
MySqlDataReader
object.
Sends the CommandText
to the
MySqlConnection
Connection and builds a
MySqlDataReader
.
Returns: A
MySqlDataReader
object.
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 the MySqlDataReader
is in use, the
associated MySqlConnection
is busy serving
the MySqlDataReader
. While in this state,
no other operations can be performed on the
MySqlConnection
other than closing it. This
is the case until the MySqlDataReader.Close
method of the MySqlDataReader
is called.
Examples
The following example creates a
MySqlCommand
, then executes it by passing a
string that is a SQL SELECT
statement, and
a string to use to connect to the data source.
Visual Basic example:
Public Sub CreateMySqlDataReader(mySelectQuery As String, myConnection As MySqlConnection) Dim myCommand As New MySqlCommand(mySelectQuery, myConnection) myConnection.Open() Dim myReader As MySqlDataReader myReader = myCommand.ExecuteReader() Try While myReader.Read() Console.WriteLine(myReader.GetString(0)) End While Finally myReader.Close myConnection.Close End Try End Sub
C# example:
public void CreateMySqlDataReader(string mySelectQuery, MySqlConnection myConnection) { MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection); myConnection.Open(); MySqlDataReader myReader; myReader = myCommand.ExecuteReader(); try { while(myReader.Read()) { Console.WriteLine(myReader.GetString(0)); } } finally { myReader.Close(); myConnection.Close(); } }
Creates a prepared version of the command on an instance of MySQL Server.
Prepared statements are only supported on MySQL version 4.1 and higher. Calling prepare while connected to earlier versions of MySQL will succeed but will execute the statement in the same way as unprepared.
Examples
The following example demonstrates the use of the
Prepare
method.
Visual Basic example:
public sub PrepareExample() Dim cmd as New MySqlCommand("INSERT INTO mytable VALUES (?val)", myConnection) cmd.Parameters.Add( "?val", 10 ) cmd.Prepare() cmd.ExecuteNonQuery() cmd.Parameters(0).Value = 20 cmd.ExecuteNonQuery() end sub
C# example:
private void PrepareExample() { MySqlCommand cmd = new MySqlCommand("INSERT INTO mytable VALUES (?val)", myConnection); cmd.Parameters.Add( "?val", 10 ); cmd.Prepare(); cmd.ExecuteNonQuery(); cmd.Parameters[0].Value = 20; cmd.ExecuteNonQuery(); }
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.
Returns: The first column of the first row in the result set, or a null reference if the result set is empty
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
A typical ExecuteScalar
query can be
formatted as in the following C# example:
C# example:
cmd.CommandText = "select count(*) from region"; Int32 count = (int32) cmd.ExecuteScalar();
Examples
The following example creates a
MySqlCommand
and then executes it using
ExecuteScalar
. The example is passed a
string that is a SQL statement that returns an aggregate
result, and a string to use to connect to the data source.
Visual Basic example:
Public Sub CreateMySqlCommand(myScalarQuery As String, myConnection As MySqlConnection) Dim myCommand As New MySqlCommand(myScalarQuery, myConnection) myCommand.Connection.Open() myCommand.ExecuteScalar() myConnection.Close() End Sub
C# example:
public void CreateMySqlCommand(string myScalarQuery, MySqlConnection myConnection) { MySqlCommand myCommand = new MySqlCommand(myScalarQuery, myConnection); myCommand.Connection.Open(); myCommand.ExecuteScalar(); myConnection.Close(); }
C++ example:
public: void CreateMySqlCommand(String* myScalarQuery, MySqlConnection* myConnection) { MySqlCommand* myCommand = new MySqlCommand(myScalarQuery, myConnection); myCommand->Connection->Open(); myCommand->ExecuteScalar(); myConnection->Close(); }
Gets or sets the SQL statement to execute at the data source.
Value: The SQL statement or stored procedure to execute. The default is an empty string.
When the CommandType
property is set to
StoredProcedure
, the
CommandText
property should be set to the
name of the stored procedure. The user may be required to use
escape character syntax if the stored procedure name contains
any special characters. The command executes this stored
procedure when you call one of the Execute methods.
Examples
The following example creates a
MySqlCommand
and sets some of its
properties.
Visual Basic example:
Public Sub CreateMySqlCommand() Dim myCommand As New MySqlCommand() myCommand.CommandText = "SELECT * FROM Mytable ORDER BY id" myCommand.CommandType = CommandType.Text End Sub
C# example:
public void CreateMySqlCommand() { MySqlCommand myCommand = new MySqlCommand(); myCommand.CommandText = "SELECT * FROM mytable ORDER BY id"; myCommand.CommandType = CommandType.Text; }
Gets or sets the wait time before terminating the attempt to execute a command and generating an error.
Value: The time (in seconds) to wait for the command to execute. The default is 0 seconds.
MySQL currently does not support any method of canceling a pending or executing operation. All commands issues against a MySQL server will execute until completion or exception occurs.
Gets or sets a value indicating how the
CommandText
property is to be interpreted.
Value: One of the
System.Data.CommandType
values. The default
is Text
.
When you set the CommandType
property to
StoredProcedure
, you should set the
CommandText
property to the name of the
stored procedure. The command executes this stored procedure
when you call one of the Execute methods.
Examples
The following example creates a
MySqlCommand
and sets some of its
properties.
Visual Basic example:
Public Sub CreateMySqlCommand() Dim myCommand As New MySqlCommand() myCommand.CommandType = CommandType.Text End Sub
C# example:
public void CreateMySqlCommand() { MySqlCommand myCommand = new MySqlCommand(); myCommand.CommandType = CommandType.Text; }
Gets or sets the MySqlConnection
used by
this instance of the MySqlCommand
.
Value: The connection to a
data source. The default value is a null reference
(Nothing
in Visual Basic).
If you set Connection
while a transaction
is in progress and the Transaction
property
is not null, an InvalidOperationException
is generated. If the Transaction
property
is not null and the transaction has already been committed or
rolled back, Transaction
is set to null.
Examples
The following example creates a
MySqlCommand
and sets some of its
properties.
Visual Basic example:
Public Sub CreateMySqlCommand() Dim mySelectQuery As String = "SELECT * FROM mytable ORDER BY id" Dim myConnectString As String = "Persist Security Info=False;database=test;server=myServer" Dim myCommand As New MySqlCommand(mySelectQuery) myCommand.Connection = New MySqlConnection(myConnectString) myCommand.CommandType = CommandType.Text End Sub
C# example:
public void CreateMySqlCommand() { string mySelectQuery = "SELECT * FROM mytable ORDER BY id"; string myConnectString = "Persist Security Info=False;database=test;server=myServer"; MySqlCommand myCommand = new MySqlCommand(mySelectQuery); myCommand.Connection = new MySqlConnection(myConnectString); myCommand.CommandType = CommandType.Text; }
Get the MySqlParameterCollection
Value: The parameters of the SQL statement or stored procedure. The default is an empty collection.
Connector/Net does not support unnamed parameters. Every parameter added to the collection must have an associated name.
Examples
The following example creates a
MySqlCommand
and displays its parameters.
To accomplish this, the method is passed a
MySqlConnection
, a query string that is a
SQL SELECT
statement, and an array of
MySqlParameter
objects.
Visual Basic example:
Public Sub CreateMySqlCommand(myConnection As MySqlConnection, _ mySelectQuery As String, myParamArray() As MySqlParameter) Dim myCommand As New MySqlCommand(mySelectQuery, myConnection) myCommand.CommandText = "SELECT id, name FROM mytable WHERE age=?age" myCommand.UpdatedRowSource = UpdateRowSource.Both myCommand.Parameters.Add(myParamArray) Dim j As Integer For j = 0 To myCommand.Parameters.Count - 1 myCommand.Parameters.Add(myParamArray(j)) Next j Dim myMessage As String = "" Dim i As Integer For i = 0 To myCommand.Parameters.Count - 1 myMessage += myCommand.Parameters(i).ToString() & ControlChars.Cr Next i Console.WriteLine(myMessage) End Sub
C# example:
public void CreateMySqlCommand(MySqlConnection myConnection, string mySelectQuery, MySqlParameter[] myParamArray) { MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection); myCommand.CommandText = "SELECT id, name FROM mytable WHERE age=?age"; myCommand.Parameters.Add(myParamArray); for (int j=0; j<myParamArray.Length; j++) { myCommand.Parameters.Add(myParamArray[j]) ; } string myMessage = ""; for (int i = 0; i < myCommand.Parameters.Count; i++) { myMessage += myCommand.Parameters[i].ToString() + "\n"; } MessageBox.Show(myMessage); }
Gets or sets the MySqlTransaction
within
which the MySqlCommand
executes.
Value: The
MySqlTransaction
. The default value is a
null reference (Nothing
in Visual Basic).
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 property
to 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.
Gets or sets how command results are applied to the
DataRow
when used by the
System.Data.Common.DbDataAdapter.Update
method of the
System.Data.Common.DbDataAdapter
.
Value: One of the
UpdateRowSource
values.
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
.
Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.