PDF (US Ltr)
- 4.5Mb
PDF (A4)
- 4.5Mb
The MySqlCommand class represents a SQL statement to execute against a MySQL database. Class methods enable you to perform the following database operations:
Query a database
Insert, update, and delete data
Return a single value
Command-based database operations can run within a transaction, if
needed. For a short tutorial demonstrating how and when to use the
ExecuteReader,
ExecuteNonQuery, and
ExecuteScalar methods, see
Section 4.6.1.2, “The MySqlCommand Object”.
An instance of MySqlCommand can be organized to
execute as a prepared statement for faster excecution and reuse,
or as a stored procedure. A flexible set of class properties
permits you to package MySQL commands in several forms. The
remainder of this section describes following
MySqlCommand properties:
The MySqlCommand class provides the
CommandText and
CommandType properties that you may combine
to create the type of SQL statements needed for your project.
The CommandText property is interpreted
differently, depending on how you set the
CommandType property. The following
CommandType types are permitted:
Text- An SQL text command (default).StoredProcedure- Name of a stored procedure.TableDirect- Name of a table.
The default CommandType type,
Text, is used for executing queries and other
SQL commands. See
Section 4.6.1.2, “The MySqlCommand Object” for usage
examples.
If CommandType is set to
StoredProcedure, set
CommandText to the name of the stored
procedure to access. For use-case examples of the
CommandType property with type
StoredProcedure, see
Section 4.5.5, “Creating and Calling Stored Procedures”.
If CommandType is set to
TableDirect, all rows and columns of the
named table are returned when you call one of the execute
methods. In effect, this command performs a SELECT
* on the table specified. The
CommandText property is set to the name of
the table to query. This usage is illustrated by the following
code snippet:
...
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "mytable";
cmd.Connection = someConnection;
cmd.CommandType = CommandType.TableDirect;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLn(reader[0], reader[1]...);
}
...
The Parameters property gives you control
over the data you use to build a SQL query. Defining a parameter
is the preferred practice to reduce the risk of acquiring
unwanted or malicous input. For usage information and examples,
see:
As of Connector/NET 8.0.26, an instance of
MySqlCommand can be organized to execute
simple Transact-SQL statements or stored procedures, both can be
used in a prepared statement for faster execution and reuse. The
query_attributes component must be installed
on the server (see
Prerequisites for Using Query Attributes) before
attributes can be searched for and used on the server side.
Query-attributes support varies by server version:
Prior to MySQL Server 8.0.23: no support for query attributes.
MySQL Server 8.0.23 to 8.0.24: support for query attributes in regular statements only.
MySQL Server 8.0.25 and higher: support for query attributes in both regular and prepared statements.
If you send query attribute metadata to a server that does not support query attributes, the attempt is logged by the connector but no error is emitted.
Like parameters, attributes must be named. Unlike a parameter, an attribute represents an object from the underlying query, such as a field or table. Connector/NET does not check or enforce whether your attribute names are unique. Parameters and attributes can be combined together in commands without restrictions.
You can declare an attritue name and value directly by using the
SetAttribute method to create an instance of
MySqlAttribute that is exposed in a
collection through the
MySqlAttributeCollection object within
MySqlCommand. For example, to declare a
single attribute named qa1, use the following
C# syntax:
myCommand.Attributes.SetAttribute("qa1", "qaValue");
Alternatively, you can declare a variable of type
MySqlAttribute to hold your attribute name
and value. Both forms persist the attribute after the query is
executed, until the Clear method is called on
the MySqlAttributeCollection object. The next
snippet declares two attributes named qa1 and
qa2 as variables
mySqlAttribute1 and
mySqlAttribute2.
MySqlCommand myCommand = new MySqlCommand();
myCommand.Connection = myConnection;
MySqlAttribute mySqlAttribute1 = new MySqlAttribute("qa1", "qaValue");
MySqlAttribute mySqlAttribute2 = new MySqlAttribute("qa2", 2);
myCommand.Attributes.SetAttribute(mySqlAttribute1);
myCommand.Attributes.SetAttribute(mySqlAttribute2);
With attribute names and values defined, a statement specifying
attributes can be sent to the server. The following
SELECT statement includes the
mysql_query_attribute_string() loadable
function that is used to retrieve the two attributes decared
previously and then prints the results. For more readable and
convenient syntax, the $ symbol is used in
this example to identify string literals as interpolated
strings.
myCommand.CommandText = $"SELECT mysql_query_attribute_string('{mySqlAttribute1.AttributeName}') AS attr1," +
$"mysql_query_attribute_string('{mySqlAttribute2.AttributeName}') AS attr2";
using (var reader = myCommand.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine($"Attribute1 Value: {reader.GetString(0)}");
Console.WriteLine($"Attribute2 Value: {reader.GetString(1)}");
}
}
/* Output:
Attribute1 Value: qaValue
Attribute2 Value: 2
*/The following code block shows the same process for setting attributes and retrieving the results using Visual Basic syntax.
Public Sub CreateMySqlCommandWithQueryAttributes(ByVal myConnection As MySqlConnection)
Dim myCommand As MySqlCommand = New MySqlCommand()
myCommand.Connection = myConnection
Dim mySqlAttribute1 As MySqlAttribute = New MySqlAttribute("qa1", "qaValue")
Dim mySqlAttribute2 As MySqlAttribute = New MySqlAttribute("qa2", 2)
myCommand.Attributes.SetAttribute(mySqlAttribute1)
myCommand.Attributes.SetAttribute(mySqlAttribute2)
myCommand.CommandText = $"SELECT mysql_query_attribute_string('{mySqlAttribute1.AttributeName}') AS attr1," &
$"mysql_query_attribute_string('{mySqlAttribute2.AttributeName}') AS attr2"
Using reader = myCommand.ExecuteReader()
While reader.Read()
Console.WriteLine($"Attribute1 Value: {reader.GetString(0)}")
Console.WriteLine($"Attribute2 Value: {reader.GetString(1)}")
End While
End Using
End Sub
Commands can have a timeout associated with them. This feature
is useful as you may not want a situation were a command takes
up an excessive amount of time. A timeout can be set using the
CommandTimeout property. The following code
snippet sets a timeout of one minute:
MySqlCommand cmd = new MySqlCommand();
cmd.CommandTimeout = 60;
The default value is 30 seconds. Avoid a value of 0, which
indicates an indefinite wait. To change the default command
timeout, use the connection string option Default
Command Timeout.
Connector/NET supports timeouts that are aligned with how Microsoft
handles SqlCommand.CommandTimeout. This
property is the cumulative timeout for all network reads and
writes during command execution or processing of the results. A
timeout can still occur in the
MySqlReader.Read method after the first row
is returned, and does not include user processing time, only IO
operations.
Further details on this can be found in the relevant Microsoft documentation.