Documentation Home
Connectors and APIs Manual
Download this Manual
PDF (US Ltr) - 4.5Mb
PDF (A4) - 4.5Mb


4.5.2 Using MySqlCommand

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:

CommandText and CommandType 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]...);
}
...

Parameters Property

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:

Attributes Property

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

CommandTimeout Property

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.