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 theExecuteReader,ExecuteNonQuery, andExecuteScalar methods, seeSection 6.1.2, “The MySqlCommand Object”.
An instance ofMySqlCommand 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 followingMySqlCommand properties:
TheMySqlCommand class provides theCommandText andCommandType properties that you may combine to create the type of SQL statements needed for your project. TheCommandText property is interpreted differently, depending on how you set theCommandType property. The followingCommandType types are permitted:
Text- An SQL text command (default).StoredProcedure- Name of a stored procedure.TableDirect- Name of a table.
The defaultCommandType type,Text, is used for executing queries and other SQL commands. SeeSection 6.1.2, “The MySqlCommand Object” for usage examples.
IfCommandType is set toStoredProcedure, setCommandText to the name of the stored procedure to access. For use-case examples of theCommandType property with typeStoredProcedure, seeSection 5.5, “Creating and Calling Stored Procedures”.
IfCommandType is set toTableDirect, all rows and columns of the named table are returned when you call one of the execute methods. In effect, this command performs aSELECT * on the table specified. TheCommandText 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]...);}... TheParameters 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 ofMySqlCommand 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. Thequery_attributes component must be installed on the server (seePrerequisites 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 theSetAttribute method to create an instance ofMySqlAttribute that is exposed in a collection through theMySqlAttributeCollection object withinMySqlCommand. For example, to declare a single attribute namedqa1, use the following C# syntax:
myCommand.Attributes.SetAttribute("qa1", "qaValue"); Alternatively, you can declare a variable of typeMySqlAttribute to hold your attribute name and value. Both forms persist the attribute after the query is executed, until theClear method is called on theMySqlAttributeCollection object. The next snippet declares two attributes namedqa1 andqa2 as variablesmySqlAttribute1 andmySqlAttribute2.
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 followingSELECT statement includes themysql_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 UsingEnd 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 theCommandTimeout 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 optionDefault Command Timeout.
Connector/NET supports timeouts that are aligned with how Microsoft handlesSqlCommand.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 theMySqlReader.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 relevantMicrosoft documentation.