23.2. Connector/NET

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.

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.

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.

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.

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 MySqlConnectionConnection, 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 MySqlConnectionConnection 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;
 }  
 

Returns true if the statement is prepared.

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.

Initializes a new instance of the MySqlCommandBuilder class.

Initializes a new instance of the MySqlCommandBuilder class and sets the last one wins property.

Parameters: False to generate change protection code. True otherwise.

The lastOneWins parameter indicates whether SQL code should be included with the generated DELETE and UPDATE commands that checks the underlying data for changes. If lastOneWins is true then this code is not included and data records could be overwritten in a multi-user or multi-threaded environments. Setting lastOneWins to false will include this check which will cause a concurrency exception to be thrown if the underlying data record has changed without our knowledge.

Initializes a new instance of the MySqlCommandBuilder class with the associated MySqlDataAdapter object.

Parameters: The MySqlDataAdapter to use.

The MySqlCommandBuilder registers itself as a listener for MySqlDataAdapter.RowUpdating events that are generated by the MySqlDataAdapter specified in this property.

When you create a new instance MySqlCommandBuilder, any existing MySqlCommandBuilder associated with this MySqlDataAdapter is released.

Initializes a new instance of the MySqlCommandBuilder class with the associated MySqlDataAdapter object.

Parameters: The MySqlDataAdapter to use.

Parameters: False to generate change protection code. True otherwise.

The MySqlCommandBuilder registers itself as a listener for MySqlDataAdapter.RowUpdating events that are generated by the MySqlDataAdapter specified in this property.

When you create a new instance MySqlCommandBuilder, any existing MySqlCommandBuilder associated with this MySqlDataAdapter is released.

The lastOneWins parameter indicates whether SQL code should be included with the generated DELETE and UPDATE commands that checks the underlying data for changes. If lastOneWins is true then this code is not included and data records could be overwritten in a multi-user or multi-threaded environments. Setting lastOneWins to false will include this check which will cause a concurrency exception to be thrown if the underlying data record has changed without our knowledge.

Gets or sets a MySqlDataAdapter object for which SQL statements are automatically generated.

Value: A MySqlDataAdapter object.

The MySqlCommandBuilder registers itself as a listener for MySqlDataAdapter.RowUpdating events that are generated by the MySqlDataAdapter specified in this property.

When you create a new instance MySqlCommandBuilder, any existing MySqlCommandBuilder associated with this MySqlDataAdapter is released.

Gets or sets the beginning character or characters to use when specifying MySQL database objects (for example, tables or columns) whose names contain characters such as spaces or reserved tokens.

Value: The beginning character or characters to use. The default value is `.

Database objects in MySQL can contain special characters such as spaces that would make normal SQL strings impossible to correctly parse. Use of the QuotePrefix and the QuoteSuffix properties allows the MySqlCommandBuilder to build SQL commands that handle this situation.

Gets or sets the beginning character or characters to use when specifying MySQL database objects (for example, tables or columns) whose names contain characters such as spaces or reserved tokens.

Value: The beginning character or characters to use. The default value is `.

Database objects in MySQL can contain special characters such as spaces that would make normal SQL strings impossible to correctly parse. Use of the QuotePrefix and the QuoteSuffix properties allows the MySqlCommandBuilder to build SQL commands that handle this situation.

Gets the automatically generated MySqlCommand object required to perform deletions on the database.

Returns: The MySqlCommand object generated to handle delete operations.

An application can use the GetDeleteCommand method for informational or troubleshooting purposes because it returns the MySqlCommand object to be executed.

You can also use GetDeleteCommand as the basis of a modified command. For example, you might call GetDeleteCommand and modify the MySqlCommand.CommandTimeout value, and then explicitly set that on the MySqlDataAdapter.

After the SQL statement is first generated, the application must explicitly call RefreshSchema if it changes the statement in any way. Otherwise, the GetDeleteCommand will be still be using information from the previous statement, which might not be correct. The SQL statements are first generated either when the application calls System.Data.Common.DataAdapter.Update or GetDeleteCommand.

Gets the automatically generated MySqlCommand object required to perform insertions on the database.

Returns: The MySqlCommand object generated to handle insert operations.

An application can use the GetInsertCommand method for informational or troubleshooting purposes because it returns the MySqlCommand object to be executed.

You can also use the GetInsertCommand as the basis of a modified command. For example, you might call GetInsertCommand and modify the MySqlCommand.CommandTimeout value, and then explicitly set that on the MySqlDataAdapter.

After the SQL statement is first generated, the application must explicitly call RefreshSchema if it changes the statement in any way. Otherwise, the GetInsertCommand will be still be using information from the previous statement, which might not be correct. The SQL statements are first generated either when the application calls System.Data.Common.DataAdapter.Update or GetInsertCommand.

Gets the automatically generated MySqlCommand object required to perform updates on the database.

Returns: The MySqlCommand object generated to handle update operations.

An application can use the GetUpdateCommand method for informational or troubleshooting purposes because it returns the MySqlCommand object to be executed.

You can also use GetUpdateCommand as the basis of a modified command. For example, you might call GetUpdateCommand and modify the MySqlCommand.CommandTimeout value, and then explicitly set that on the MySqlDataAdapter.

After the SQL statement is first generated, the application must explicitly call RefreshSchema if it changes the statement in any way. Otherwise, the GetUpdateCommand will be still be using information from the previous statement, which might not be correct. The SQL statements are first generated either when the application calls System.Data.Common.DataAdapter.Update or GetUpdateCommand.

Refreshes the database schema information used to generate INSERT, UPDATE, or DELETE statements.

An application should call RefreshSchema whenever the SELECT statement associated with the MySqlCommandBuilder changes.

An application should call RefreshSchema whenever the MySqlDataAdapter.SelectCommand value of the MySqlDataAdapter changes.

Initializes a new instance of the MySqlConnection class.

When a new instance of MySqlConnection is created, the read/write properties are set to the following initial values unless they are specifically set using their associated keywords in the ConnectionString property.

You can change the value for these properties only by using the ConnectionString property.

Examples

Overload methods for MySqlConnection

Initializes a new instance of the MySqlConnection class.

Initializes a new instance of the MySqlConnection class when given a string containing the connection string.

When a new instance of MySqlConnection is created, the read/write properties are set to the following initial values unless they are specifically set using their associated keywords in the ConnectionString property.

You can change the value for these properties only by using the ConnectionString property.

Examples

Parameters: The connection properties used to open the MySQL database.

Opens a database connection with the property settings specified by the ConnectionString.

Exception: Cannot open a connection without specifying a data source or server.

Exception: A connection-level error occurred while opening the connection.

The MySqlConnection draws an open connection from the connection pool if one is available. Otherwise, it establishes a new connection to an instance of MySQL.

Examples

The following example creates a MySqlConnection, opens it, displays some of its properties, then closes the connection.

Visual Basic example:

Public Sub CreateMySqlConnection(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    MessageBox.Show("ServerVersion: " + myConnection.ServerVersion _
    + ControlChars.Cr + "State: " + myConnection.State.ToString())
    myConnection.Close()
End Sub
    

C# example:

public void CreateMySqlConnection(string myConnString) 
{
  MySqlConnection myConnection = new MySqlConnection(myConnString);
  myConnection.Open();
  MessageBox.Show("ServerVersion: " + myConnection.ServerVersion + 
          "\nState: " + myConnection.State.ToString());
  myConnection.Close();
}
    

Gets the name of the current database or the database to be used after a connection is opened.

Returns: The name of the current database or the name of the database to be used after a connection is opened. The default value is an empty string.

The Database property does not update dynamically. If you change the current database using a SQL statement, then this property may reflect the wrong value. If you change the current database using the ChangeDatabase method, this property is updated to reflect the new database.

Examples

The following example creates a MySqlConnection and displays some of its read-only properties.

Visual Basic example:

Public Sub CreateMySqlConnection()
  Dim myConnString As String = _
    "Persist Security Info=False;database=test;server=localhost;user id=joeuser;pwd=pass"
  Dim myConnection As New MySqlConnection( myConnString )
  myConnection.Open()
  MessageBox.Show( "Server Version: " + myConnection.ServerVersion _
    + ControlChars.NewLine + "Database: " + myConnection.Database )
  myConnection.ChangeDatabase( "test2" )
  MessageBox.Show( "ServerVersion: " + myConnection.ServerVersion _ 
    + ControlChars.NewLine + "Database: " + myConnection.Database )
  myConnection.Close()
End Sub    
    

C# example:

public void CreateMySqlConnection()
{
  string myConnString = 
    "Persist Security Info=False;database=test;server=localhost;user id=joeuser;pwd=pass";
  MySqlConnection myConnection = new MySqlConnection( myConnString );
  myConnection.Open();
  MessageBox.Show( "Server Version: " + myConnection.ServerVersion 
    + "\nDatabase: " + myConnection.Database );
  myConnection.ChangeDatabase( "test2" );
  MessageBox.Show( "ServerVersion: " + myConnection.ServerVersion  
    + "\nDatabase: " + myConnection.Database );
  myConnection.Close();
}
    

Gets the current state of the connection.

Returns: A bitwise combination of the System.Data.ConnectionState values. The default is Closed.

The allowed state changes are:

Examples

The following example creates a MySqlConnection, opens it, displays some of its properties, then closes the connection.

Visual Basic example:

Public Sub CreateMySqlConnection(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    MessageBox.Show("ServerVersion: " + myConnection.ServerVersion _
    + ControlChars.Cr + "State: " + myConnection.State.ToString())
    myConnection.Close()
End Sub
    

C# example:

public void CreateMySqlConnection(string myConnString) 
{
  MySqlConnection myConnection = new MySqlConnection(myConnString);
  myConnection.Open();
  MessageBox.Show("ServerVersion: " + myConnection.ServerVersion + 
          "\nState: " + myConnection.State.ToString());
  myConnection.Close();
}
    

Gets a string containing the version of the MySQL server to which the client is connected.

Returns: The version of the instance of MySQL.

Exception: The connection is closed.

Examples

The following example creates a MySqlConnection, opens it, displays some of its properties, then closes the connection.

Visual Basic example:

Public Sub CreateMySqlConnection(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    MessageBox.Show("ServerVersion: " + myConnection.ServerVersion _
    + ControlChars.Cr + "State: " + myConnection.State.ToString())
    myConnection.Close()
End Sub
    

C# example:

public void CreateMySqlConnection(string myConnString) 
{
  MySqlConnection myConnection = new MySqlConnection(myConnString);
  myConnection.Open();
  MessageBox.Show("ServerVersion: " + myConnection.ServerVersion + 
          "\nState: " + myConnection.State.ToString());
  myConnection.Close();
}
    

Closes the connection to the database. This is the preferred method of closing any open connection.

The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled.

An application can call Close more than one time. No exception is generated.

Examples

The following example creates a MySqlConnection, opens it, displays some of its properties, then closes the connection.

Visual Basic example:

Public Sub CreateMySqlConnection(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    MessageBox.Show("ServerVersion: " + myConnection.ServerVersion _
    + ControlChars.Cr + "State: " + myConnection.State.ToString())
    myConnection.Close()
End Sub
    

C# example:

public void CreateMySqlConnection(string myConnString) 
{
  MySqlConnection myConnection = new MySqlConnection(myConnString);
  myConnection.Open();
  MessageBox.Show("ServerVersion: " + myConnection.ServerVersion + 
          "\nState: " + myConnection.State.ToString());
  myConnection.Close();
}
    

Creates and returns a MySqlCommand object associated with the MySqlConnection.

Returns: A MySqlCommand object.

Begins a database transaction.

Returns: An object representing the new transaction.

Exception: Parallel transactions are not supported.

This command is equivalent to the MySQL BEGIN TRANSACTION command.

You must explicitly commit or roll back the transaction using the MySqlTransaction.Commit or MySqlTransaction.Rollback method.

Note. If you do not specify an isolation level, the default isolation level is used. To specify an isolation level with the BeginTransaction method, use the overload that takes the iso parameter.

Examples

The following example creates a MySqlConnection and a MySqlTransaction. It also demonstrates how to use the BeginTransaction, a MySqlTransaction.Commit, and MySqlTransaction.Rollback methods.

Visual Basic example:

Public Sub RunTransaction(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    
    Dim myCommand As MySqlCommand = myConnection.CreateCommand()
    Dim myTrans As MySqlTransaction
    
    ' Start a local transaction
    myTrans = myConnection.BeginTransaction()
    ' Must assign both transaction object and connection
    ' to Command object for a pending local transaction
    myCommand.Connection = myConnection
    myCommand.Transaction = myTrans
    
    Try
      myCommand.CommandText = "Insert into Test (id, desc) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into Test (id, desc) VALUES (101, 'Description')"
      myCommand.ExecuteNonQuery()
      myTrans.Commit()
      Console.WriteLine("Both records are written to database.")
    Catch e As Exception
      Try
        myTrans.Rollback()
      Catch ex As MySqlException
        If Not myTrans.Connection Is Nothing Then
          Console.WriteLine("An exception of type " + ex.GetType().ToString() + _
                            " was encountered while attempting to roll back the transaction.")
        End If
      End Try
    
      Console.WriteLine("An exception of type " + e.GetType().ToString() + _
                      "was encountered while inserting the data.")
      Console.WriteLine("Neither record was written to database.")
    Finally
      myConnection.Close()
    End Try
End Sub
    

C# example:

public void RunTransaction(string myConnString) 
{
  MySqlConnection myConnection = new MySqlConnection(myConnString);
  myConnection.Open();
  MySqlCommand myCommand = myConnection.CreateCommand();
  MySqlTransaction myTrans;
  // Start a local transaction
  myTrans = myConnection.BeginTransaction();
  // Must assign both transaction object and connection
  // to Command object for a pending local transaction
  myCommand.Connection = myConnection;
  myCommand.Transaction = myTrans;
    try
    {
      myCommand.CommandText = "insert into Test (id, desc) VALUES (100, 'Description')";
      myCommand.ExecuteNonQuery();
      myCommand.CommandText = "insert into Test (id, desc) VALUES (101, 'Description')";
      myCommand.ExecuteNonQuery();
      myTrans.Commit();
      Console.WriteLine("Both records are written to database.");
    }
    catch(Exception e)
    {
      try
      {
        myTrans.Rollback();
      }
      catch (SqlException ex)
      {
        if (myTrans.Connection != null)
        {
          Console.WriteLine("An exception of type " + ex.GetType() +
                            " was encountered while attempting to roll back the transaction.");
        }
      }
    
      Console.WriteLine("An exception of type " + e.GetType() +
                        " was encountered while inserting the data.");
      Console.WriteLine("Neither record was written to database.");
    }
    finally 
    {
      myConnection.Close();
    }
}
    

Begins a database transaction with the specified isolation level.

Parameters: The isolation level under which the transaction should run.

Returns: An object representing the new transaction.

Exception: Parallel exceptions are not supported.

This command is equivalent to the MySQL BEGIN TRANSACTION command.

You must explicitly commit or roll back the transaction using the MySqlTransaction.Commit or MySqlTransaction.Rollback method.

Note. If you do not specify an isolation level, the default isolation level is used. To specify an isolation level with the BeginTransaction method, use the overload that takes the iso parameter.

Examples

The following example creates a MySqlConnection and a MySqlTransaction. It also demonstrates how to use the BeginTransaction, a MySqlTransaction.Commit, and MySqlTransaction.Rollback methods.

Visual Basic example:

Public Sub RunTransaction(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    
    Dim myCommand As MySqlCommand = myConnection.CreateCommand()
    Dim myTrans As MySqlTransaction
    
    ' Start a local transaction
    myTrans = myConnection.BeginTransaction()
    ' Must assign both transaction object and connection
    ' to Command object for a pending local transaction
    myCommand.Connection = myConnection
    myCommand.Transaction = myTrans
    
    Try
      myCommand.CommandText = "Insert into Test (id, desc) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into Test (id, desc) VALUES (101, 'Description')"
      myCommand.ExecuteNonQuery()
      myTrans.Commit()
      Console.WriteLine("Both records are written to database.")
    Catch e As Exception
      Try
        myTrans.Rollback()
      Catch ex As MySqlException
        If Not myTrans.Connection Is Nothing Then
          Console.WriteLine("An exception of type " + ex.GetType().ToString() + _
                            " was encountered while attempting to roll back the transaction.")
        End If
      End Try
    
      Console.WriteLine("An exception of type " + e.GetType().ToString() + _
                      "was encountered while inserting the data.")
      Console.WriteLine("Neither record was written to database.")
    Finally
      myConnection.Close()
    End Try
End Sub
    

C# example:

public void RunTransaction(string myConnString) 
{
  MySqlConnection myConnection = new MySqlConnection(myConnString);
  myConnection.Open();
  MySqlCommand myCommand = myConnection.CreateCommand();
  MySqlTransaction myTrans;
  // Start a local transaction
  myTrans = myConnection.BeginTransaction();
  // Must assign both transaction object and connection
  // to Command object for a pending local transaction
  myCommand.Connection = myConnection;
  myCommand.Transaction = myTrans;
    try
    {
      myCommand.CommandText = "insert into Test (id, desc) VALUES (100, 'Description')";
      myCommand.ExecuteNonQuery();
      myCommand.CommandText = "insert into Test (id, desc) VALUES (101, 'Description')";
      myCommand.ExecuteNonQuery();
      myTrans.Commit();
      Console.WriteLine("Both records are written to database.");
    }
    catch(Exception e)
    {
      try
      {
        myTrans.Rollback();
      }
      catch (SqlException ex)
      {
        if (myTrans.Connection != null)
        {
          Console.WriteLine("An exception of type " + ex.GetType() +
                            " was encountered while attempting to roll back the transaction.");
        }
      }
    
      Console.WriteLine("An exception of type " + e.GetType() +
                        " was encountered while inserting the data.");
      Console.WriteLine("Neither record was written to database.");
    }
    finally 
    {
      myConnection.Close();
    }
}
    

Changes the current database for an open MySqlConnection.

Parameters: The name of the database to use.

The value supplied in the database parameter must be a valid database name. The database parameter cannot contain a null value, an empty string, or a string with only blank characters.

When you are using connection pooling against MySQL, and you close the connection, it is returned to the connection pool. The next time the connection is retrieved from the pool, the reset connection request executes before the user performs any operations.

Exception: The database name is not valid.

Exception: The connection is not open.

Exception: Cannot change the database.

Examples

The following example creates a MySqlConnection and displays some of its read-only properties.

Visual Basic example:

Public Sub CreateMySqlConnection()
  Dim myConnString As String = _
    "Persist Security Info=False;database=test;server=localhost;user id=joeuser;pwd=pass"
  Dim myConnection As New MySqlConnection( myConnString )
  myConnection.Open()
  MessageBox.Show( "Server Version: " + myConnection.ServerVersion _
    + ControlChars.NewLine + "Database: " + myConnection.Database )
  myConnection.ChangeDatabase( "test2" )
  MessageBox.Show( "ServerVersion: " + myConnection.ServerVersion _ 
    + ControlChars.NewLine + "Database: " + myConnection.Database )
  myConnection.Close()
End Sub    
    

C# example:

public void CreateMySqlConnection()
{
  string myConnString = 
    "Persist Security Info=False;database=test;server=localhost;user id=joeuser;pwd=pass";
  MySqlConnection myConnection = new MySqlConnection( myConnString );
  myConnection.Open();
  MessageBox.Show( "Server Version: " + myConnection.ServerVersion 
    + "\nDatabase: " + myConnection.Database );
  myConnection.ChangeDatabase( "test2" );
  MessageBox.Show( "ServerVersion: " + myConnection.ServerVersion  
    + "\nDatabase: " + myConnection.Database );
  myConnection.Close();
}
    

Occurs when the state of the connection changes.

The StateChange event fires whenever the State changes from closed to opened, or from opened to closed. StateChange fires immediately after the MySqlConnection transitions.

If an event handler throws an exception from within the StateChange event, the exception propagates to the caller of the Open or Close method.

The StateChange event is not raised unless you explicitly call Close or Dispose.

The event handler receives an argument of type System.Data.StateChangeEventArgs containing data related to this event. The following StateChangeEventArgs properties provide information specific to this event.

Occurs when MySQL returns warnings as a result of executing a command or query.

Gets the time to wait while trying to establish a connection before terminating the attempt and generating an error.

Exception: The value set is less than 0.

A value of 0 indicates no limit, and should be avoided in a MySqlConnection.ConnectionString because an attempt to connect will wait indefinitely.

Examples

The following example creates a MySqlConnection and sets some of its properties in the connection string.

Visual Basic example:

Public Sub CreateSqlConnection()
  Dim myConnection As New MySqlConnection()
  myConnection.ConnectionString = "Persist Security Info=False;Username=user;Password=pass;database=test1;server=localhost;Connect Timeout=30"
  myConnection.Open()
End Sub

C# example:

public void CreateSqlConnection() 
{
  MySqlConnection myConnection = new MySqlConnection();
  myConnection.ConnectionString = "Persist Security Info=False;Username=user;Password=pass;database=test1;server=localhost;Connect Timeout=30";
  myConnection.Open();
}

Gets or sets the string used to connect to a MySQL Server database.

The ConnectionString returned may not be exactly like what was originally set but will be indentical in terms of keyword/value pairs. Security information will not be included unless the Persist Security Info value is set to true.

You can use the ConnectionString property to connect to a database. The following example illustrates a typical connection string.

"Persist Security Info=False;database=MyDB;server=MySqlServer;user id=myUser;Password=myPass"

The ConnectionString property can be set only when the connection is closed. Many of the connection string values have corresponding read-only properties. When the connection string is set, all of these properties are updated, except when an error is detected. In this case, none of the properties are updated. MySqlConnection properties return only those settings contained in the ConnectionString.

To connect to a local machine, specify "localhost" for the server. If you do not specify a server, localhost is assumed.

Resetting the ConnectionString on a closed connection resets all connection string values (and related properties) including the password. For example, if you set a connection string that includes "Database= MyDb", and then reset the connection string to "Data Source=myserver;User Id=myUser;Password=myPass", the MySqlConnection.Database property is no longer set to MyDb.

The connection string is parsed immediately after being set. If errors in syntax are found when parsing, a runtime exception, such as ArgumentException, is generated. Other errors can be found only when an attempt is made to open the connection.

The basic format of a connection string consists of a series of keyword/value pairs separated by semicolons. The equal sign (=) connects each keyword and its value. To include values that contain a semicolon, single-quote character, or double-quote character, the value must be enclosed in double quotes. If the value contains both a semicolon and a double-quote character, the value can be enclosed in single quotes. The single quote is also useful if the value begins with a double-quote character. Conversely, the double quote can be used if the value begins with a single quote. If the value contains both single-quote and double-quote characters, the quote character used to enclose the value must be doubled each time it occurs within the value.

To include preceding or trailing spaces in the string value, the value must be enclosed in either single quotes or double quotes. Any leading or trailing spaces around integer, Boolean, or enumerated values are ignored, even if enclosed in quotes. However, spaces within a string literal keyword or value are preserved. Using .NET Framework version 1.1, single or double quotes may be used within a connection string without using delimiters (for example, Data Source= my'Server or Data Source= my"Server), unless a quote character is the first or last character in the value.

To include an equal sign (=) in a keyword or value, it must be preceded by another equal sign. For example, in the hypothetical connection string

"key==word=value"

the keyword is "key=word" and the value is "value".

If a specific keyword in a keyword= value pair occurs multiple times in a connection string, the last occurrence listed is used in the value set.

Keywords are not case sensitive.

The following table lists the valid names for keyword values within the ConnectionString.

The following table lists the valid names for connection pooling values within the ConnectionString. For more information about connection pooling, see Connection Pooling for the MySql Data Provider.

When setting keyword or connection pooling values that require a Boolean value, you can use 'yes' instead of 'true', and 'no' instead of 'false'.

Note The MySql Data Provider uses the native socket protocol to communicate with MySQL. Therefore, it does not support the use of an ODBC data source name (DSN) when connecting to MySQL because it does not add an ODBC layer.

CAUTION In this release, the application should use caution when constructing a connection string based on user input (for example when retrieving user ID and password information from a dialog box, and appending it to the connection string). The application should ensure that a user cannot embed extra connection string parameters in these values (for example, entering a password as "validpassword;database=somedb" in an attempt to attach to a different database).

Examples

The following example creates a MySqlConnection and sets some of its properties

Visual Basic example:

  Public Sub CreateConnection()
    Dim myConnection As New MySqlConnection()
    myConnection.ConnectionString = "Persist Security Info=False;database=myDB;server=myHost;Connect Timeout=30;user id=myUser; pwd=myPass"
    myConnection.Open()
  End Sub 'CreateConnection
  

C# example:

  public void CreateConnection() 
  {
    MySqlConnection myConnection = new MySqlConnection();
    myConnection.ConnectionString = "Persist Security Info=False;database=myDB;server=myHost;Connect Timeout=30;user id=myUser; pwd=myPass";
    myConnection.Open();
  }
  

Examples

The following example creates a MySqlConnection in Unix environment with Mono installed. MySQL socket filename used in this example is "/var/lib/mysql/mysql.sock". The actual filename depends on your MySQL configuration.

Visual Basic example:

  Public Sub CreateConnection()
    Dim myConnection As New MySqlConnection()
    myConnection.ConnectionString = "database=myDB;server=/var/lib/mysql/mysql.sock;user id=myUser; pwd=myPass"
    myConnection.Open()
  End Sub 'CreateConnection
  

C# example:

  public void CreateConnection() 
  {
    MySqlConnection myConnection = new MySqlConnection();
    myConnection.ConnectionString = "database=myDB;server=/var/lib/mysql/mysql.sock;user id=myUser; pwd=myPass";
    myConnection.Open();
  }
  

Overload methods for MySqlDataAdapter

Initializes a new instance of the MySqlDataAdapter class.

When an instance of MySqlDataAdapter is created, the following read/write properties are set to the following initial values.

You can change the value of any of these properties through a separate call to the property.

Examples

The following example creates a MySqlDataAdapter and sets some of its properties.

Visual Basic example:

 
Public Sub CreateSqlDataAdapter()
    Dim conn As MySqlConnection = New MySqlConnection("Data Source=localhost;" & _
    "database=test")
    Dim da As MySqlDataAdapter = New MySqlDataAdapter
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey    
   
    da.SelectCommand = New MySqlCommand("SELECT id, name FROM mytable", conn)
    da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _
                                            "VALUES (?id, ?name)", conn)
    da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=?id, name=?name " & _
                                            "WHERE id=?oldId", conn)
    da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=?id", conn)
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
End Sub

C# example:

public static void CreateSqlDataAdapter() 
{
    MySqlConnection conn = new MySqlConnection("Data Source=localhost;database=test");
    MySqlDataAdapter da = new MySqlDataAdapter();
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   
    da.SelectCommand = new MySqlCommand("SELECT id, name FROM mytable", conn);
    da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " +
                                            "VALUES (?id, ?name)", conn);
    da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=?id, name=?name " +
                                            "WHERE id=?oldId", conn);
    da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=?id", conn);
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
}  

Initializes a new instance of the MySqlDataAdapter class with the specified MySqlCommand as the SelectCommand property.

Parameters: MySqlCommand that is a SQL SELECT statement or stored procedure and is set as the SelectCommand property of the MySqlDataAdapter.

When an instance of MySqlDataAdapter is created, the following read/write properties are set to the following initial values.

You can change the value of any of these properties through a separate call to the property.

When SelectCommand (or any of the other command properties) is assigned to a previously created MySqlCommand, the MySqlCommand is not cloned. The SelectCommand maintains a reference to the previously created MySqlCommand object.

Examples

The following example creates a MySqlDataAdapter and sets some of its properties.

Visual Basic example:

 
Public Sub CreateSqlDataAdapter()
    Dim conn As MySqlConnection = New MySqlConnection("Data Source=localhost;" & _
    "database=test")
  Dim cmd as new MySqlCommand("SELECT id, name FROM mytable", conn)
    Dim da As MySqlDataAdapter = New MySqlDataAdapter(cmd)
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey    
   
    da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _
                                            "VALUES (?id, ?name)", conn)
    da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=?id, name=?name " & _
                                            "WHERE id=?oldId", conn)
    da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=?id", conn)
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
End Sub

C# example:

public static void CreateSqlDataAdapter() 
{
    MySqlConnection conn = new MySqlConnection("Data Source=localhost;database=test");
    MySqlCommand cmd = new MySqlCommand("SELECT id, name FROM mytable", conn);
    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   
    da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " +
                                            "VALUES (?id, ?name)", conn);
    da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=?id, name=?name " +
                                            "WHERE id=?oldId", conn);
    da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=?id", conn);
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
}  

Initializes a new instance of the MySqlDataAdapter class with a SelectCommand and a MySqlConnection object.

Parameters: A String that is a SQL SELECT statement or stored procedure to be used by the SelectCommand property of the MySqlDataAdapter.

Parameters: A MySqlConnection that represents the connection.

This implementation of the MySqlDataAdapter opens and closes a MySqlConnection if it is not already open. This can be useful in a an application that must call the DbDataAdapter.Fill method for two or more MySqlDataAdapter objects. If the MySqlConnection is already open, you must explicitly call MySqlConnection.Close or MySqlConnection.Dispose to close it.

When an instance of MySqlDataAdapter is created, the following read/write properties are set to the following initial values.

You can change the value of any of these properties through a separate call to the property.

Examples

The following example creates a MySqlDataAdapter and sets some of its properties.

Visual Basic example:

 
Public Sub CreateSqlDataAdapter()
    Dim conn As MySqlConnection = New MySqlConnection("Data Source=localhost;" & _
    "database=test")
    Dim da As MySqlDataAdapter = New MySqlDataAdapter("SELECT id, name FROM mytable", conn)
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey    
   
    da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _
                                            "VALUES (?id, ?name)", conn)
    da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=?id, name=?name " & _
                                            "WHERE id=?oldId", conn)
    da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=?id", conn)
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
End Sub

C# example:

public static void CreateSqlDataAdapter() 
{
    MySqlConnection conn = new MySqlConnection("Data Source=localhost;database=test");
    MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, name FROM mytable", conn);
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   
    da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " +
                                            "VALUES (?id, ?name)", conn);
    da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=?id, name=?name " +
                                            "WHERE id=?oldId", conn);
    da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=?id", conn);
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
}  

Initializes a new instance of the MySqlDataAdapter class with a SelectCommand and a connection string.

Parameters: A string that is a SQL SELECT statement or stored procedure to be used by the SelectCommand property of the MySqlDataAdapter.

Parameters: The connection string

When an instance of MySqlDataAdapter is created, the following read/write properties are set to the following initial values.

You can change the value of any of these properties through a separate call to the property.

Examples

The following example creates a MySqlDataAdapter and sets some of its properties.

Visual Basic example:

 
Public Sub CreateSqlDataAdapter()
    Dim da As MySqlDataAdapter = New MySqlDataAdapter("SELECT id, name FROM mytable", "Data Source=localhost;database=test")
    Dim conn As MySqlConnection = da.SelectCommand.Connection
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey    
   
    da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _
                                            "VALUES (?id, ?name)", conn)
    da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=?id, name=?name " & _
                                            "WHERE id=?oldId", conn)
    da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=?id", conn)
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
End Sub

C# example:

public static void CreateSqlDataAdapter() 
{
    MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, name FROM mytable", "Data Source=localhost;database=test");
    MySqlConnection conn = da.SelectCommand.Connection;
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   
    da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " +
                                            "VALUES (?id, ?name)", conn);
    da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=?id, name=?name " +
                                            "WHERE id=?oldId", conn);
    da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=?id", conn);
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
}  

Gets or sets a SQL statement or stored procedure used to delete records from the data set.

Value: A MySqlCommand used during System.Data.Common.DataAdapter.Update to delete records in the database that correspond to deleted rows in the DataSet.

During System.Data.Common.DataAdapter.Update, if this property is not set and primary key information is present in the DataSet, the DeleteCommand can be generated automatically if you set the SelectCommand property and use the MySqlCommandBuilder. Then, any additional commands that you do not set are generated by the MySqlCommandBuilder. This generation logic requires key column information to be present in the DataSet.

When DeleteCommand is assigned to a previously created MySqlCommand, the MySqlCommand is not cloned. The DeleteCommand maintains a reference to the previously created MySqlCommand object.

Examples

The following example creates a MySqlDataAdapter and sets the SelectCommand and DeleteCommand properties. It assumes you have already created a MySqlConnection object.

Visual Basic example:

Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter 
  
  Dim da As MySqlDataAdapter = New MySqlDataAdapter()
  Dim cmd As MySqlCommand
  Dim parm As MySqlParameter
  ' Create the SelectCommand.
  cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn)
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15)
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15)
  da.SelectCommand = cmd
  ' Create the DeleteCommand.
  cmd = New MySqlCommand("DELETE FROM mytable WHERE id=?id", conn)
  parm = cmd.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
  parm.SourceVersion = DataRowVersion.Original
  da.DeleteCommand = cmd
  Return da
End Function

C# example:

public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn)
{
  MySqlDataAdapter da = new MySqlDataAdapter();
  MySqlCommand cmd;
  MySqlParameter parm;
  // Create the SelectCommand.
  cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15);
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15);
  da.SelectCommand = cmd;
  // Create the DeleteCommand.
  cmd = new MySqlCommand("DELETE FROM mytable WHERE id=?id", conn);
  parm = cmd.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
  parm.SourceVersion = DataRowVersion.Original;
  da.DeleteCommand = cmd;
  return da;
}

Gets or sets a SQL statement or stored procedure used to insert records into the data set.

Value: A MySqlCommand used during System.Data.Common.DataAdapter.Update to insert records into the database that correspond to new rows in the DataSet.

During System.Data.Common.DataAdapter.Update, if this property is not set and primary key information is present in the DataSet, the InsertCommand can be generated automatically if you set the SelectCommand property and use the MySqlCommandBuilder. Then, any additional commands that you do not set are generated by the MySqlCommandBuilder. This generation logic requires key column information to be present in the DataSet.

When InsertCommand is assigned to a previously created MySqlCommand, the MySqlCommand is not cloned. The InsertCommand maintains a reference to the previously created MySqlCommand object.

Note.  If execution of this command returns rows, these rows may be added to the DataSet depending on how you set the MySqlCommand.UpdatedRowSource property of the MySqlCommand object.

Examples

The following example creates a MySqlDataAdapter and sets the SelectCommand and InsertCommand properties. It assumes you have already created a MySqlConnection object.

Visual Basic example:

Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter 
  
  Dim da As MySqlDataAdapter = New MySqlDataAdapter()
  Dim cmd As MySqlCommand
  Dim parm As MySqlParameter
  ' Create the SelectCommand.
  cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn)
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15)
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15)
  da.SelectCommand = cmd
  ' Create the InsertCommand.
  cmd = New MySqlCommand("INSERT INTO mytable (id,name) VALUES (?id, ?name)", conn)
  cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15, "id" )
  cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15, "name" )  
  da.InsertCommand = cmd
  
  Return da
End Function

C# example:

public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn)
{
  MySqlDataAdapter da = new MySqlDataAdapter();
  MySqlCommand cmd;
  MySqlParameter parm;
  // Create the SelectCommand.
  cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15);
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15);
  da.SelectCommand = cmd;
  // Create the InsertCommand.
  cmd = new MySqlCommand("INSERT INTO mytable (id,name) VALUES (?id,?name)", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15, "id" );
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15, "name" );
  
  da.InsertCommand = cmd;  
  return da;
}

Gets or sets a SQL statement or stored procedure used to updated records in the data source.

Value: A MySqlCommand used during System.Data.Common.DataAdapter.Update to update records in the database with data from the DataSet.

During System.Data.Common.DataAdapter.Update, if this property is not set and primary key information is present in the DataSet, the UpdateCommand can be generated automatically if you set the SelectCommand property and use the MySqlCommandBuilder. Then, any additional commands that you do not set are generated by the MySqlCommandBuilder. This generation logic requires key column information to be present in the DataSet.

When UpdateCommand is assigned to a previously created MySqlCommand, the MySqlCommand is not cloned. The UpdateCommand maintains a reference to the previously created MySqlCommand object.

Note.  If execution of this command returns rows, these rows may be merged with the DataSet depending on how you set the MySqlCommand.UpdatedRowSource property of the MySqlCommand object.

Examples

The following example creates a MySqlDataAdapter and sets the SelectCommand and UpdateCommand properties. It assumes you have already created a MySqlConnection object.

Visual Basic example:

Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter 
  
  Dim da As MySqlDataAdapter = New MySqlDataAdapter()
  Dim cmd As MySqlCommand
  Dim parm As MySqlParameter
  ' Create the SelectCommand.
  cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn)
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15)
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15)
  da.SelectCommand = cmd
  ' Create the UpdateCommand.
  cmd = New MySqlCommand("UPDATE mytable SET id=?id, name=?name WHERE id=?oldId", conn)
  cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15, "id" )
  cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15, "name" )  
  
  parm = cmd.Parameters.Add("?oldId", MySqlDbType.VarChar, 15, "id")
  parm.SourceVersion = DataRowVersion.Original
  
  da.UpdateCommand = cmd
  
  Return da
End Function

C# example:

public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn)
{
  MySqlDataAdapter da = new MySqlDataAdapter();
  MySqlCommand cmd;
  MySqlParameter parm;
  // Create the SelectCommand.
  cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15);
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15);
  da.SelectCommand = cmd;
  // Create the UpdateCommand.
  cmd = new MySqlCommand("UPDATE mytable SET id=?id, name=?name WHERE id=?oldId", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15, "id" );
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15, "name" );
  
  parm = cmd.Parameters.Add( "?oldId", MySqlDbType.VarChar, 15, "id" );
  parm.SourceVersion = DataRowVersion.Original;
  
  da.UpdateCommand = cmd;  
  return da;
}

Gets or sets a SQL statement or stored procedure used to select records in the data source.

Value: A MySqlCommand used during System.Data.Common.DbDataAdapter.Fill to select records from the database for placement in the DataSet.

When SelectCommand is assigned to a previously created MySqlCommand, the MySqlCommand is not cloned. The SelectCommand maintains a reference to the previously created MySqlCommand object.

If the SelectCommand does not return any rows, no tables are added to the DataSet, and no exception is raised.

Examples

The following example creates a MySqlDataAdapter and sets the SelectCommand and InsertCommand properties. It assumes you have already created a MySqlConnection object.

Visual Basic example:

Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter 
  
  Dim da As MySqlDataAdapter = New MySqlDataAdapter()
  Dim cmd As MySqlCommand
  Dim parm As MySqlParameter
  ' Create the SelectCommand.
  cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn)
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15)
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15)
  da.SelectCommand = cmd
  ' Create the InsertCommand.
  cmd = New MySqlCommand("INSERT INTO mytable (id,name) VALUES (?id, ?name)", conn)
  cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15, "id" )
  cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15, "name" )  
  da.InsertCommand = cmd
  
  Return da
End Function

C# example:

public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn)
{
  MySqlDataAdapter da = new MySqlDataAdapter();
  MySqlCommand cmd;
  MySqlParameter parm;
  // Create the SelectCommand.
  cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15);
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15);
  da.SelectCommand = cmd;
  // Create the InsertCommand.
  cmd = new MySqlCommand("INSERT INTO mytable (id,name) VALUES (?id,?name)", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15, "id" );
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15, "name" );
  
  da.InsertCommand = cmd;  
  return da;
}

GetBytes returns the number of available bytes in the field. In most cases this is the exact length of the field. However, the number returned may be less than the true length of the field if GetBytes has already been used to obtain bytes from the field. This may be the case, for example, if the MySqlDataReader is reading a large data structure into a buffer. For more information, see the SequentialAccess setting for MySqlCommand.CommandBehavior.

If you pass a buffer that is a null reference (Nothing in Visual Basic), GetBytes returns the length of the field in bytes.

No conversions are performed; therefore the data retrieved must already be a byte array.

Gets the value of the specified column as a TimeSpan object.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a DateTime object.

Note.  MySql allows date columns to contain the value '0000-00-00' and datetime columns to contain the value '0000-00-00 00:00:00'. The DateTime structure cannot contain or represent these values. To read a datetime value from a column that might contain zero values, use GetMySqlDateTime. The behavior of reading a zero datetime column using this method is defined by the ZeroDateTimeBehavior connection string option. For more information on this option, please refer to MySqlConnection.ConnectionString.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a MySql.Data.Types.MySqlDateTime object.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a String object.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a Decimal object.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a double-precision floating point number.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a single-precision floating point number.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a globally-unique identifier (GUID).

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a 16-bit signed integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a 32-bit signed integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a 64-bit signed integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a 16-bit unsigned integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a 32-bit unsigned integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Gets the value of the specified column as a 64-bit unsigned integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

Rolls back a transaction from a pending state.

The Rollback method is equivalent to the MySQL statement ROLLBACK. The transaction can only be rolled back from a pending state (after BeginTransaction has been called, but before Commit is called).

Examples

The following example creates MySqlConnection and a MySqlTransaction. It also demonstrates how to use the MySqlConnection.BeginTransaction, Commit, and Rollback methods.

Visual Basic example:

Public Sub RunSqlTransaction(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    
    Dim myCommand As MySqlCommand = myConnection.CreateCommand()
    Dim myTrans As MySqlTransaction
    
    ' Start a local transaction
    myTrans = myConnection.BeginTransaction()
    
    ' Must assign both transaction object and connection
    ' to Command object for a pending local transaction
    myCommand.Connection = myConnection
    myCommand.Transaction = myTrans
    
    Try
      myCommand.CommandText = "Insert into mytable (id, desc) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into mytable (id, desc) VALUES (101, 'Description')"
      myCommand.ExecuteNonQuery()
      myTrans.Commit()
      Console.WriteLine("Success.")
    Catch e As Exception
      Try
        myTrans.Rollback()
      Catch ex As MySqlException
        If Not myTrans.Connection Is Nothing Then
          Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                            " was encountered while attempting to roll back the transaction.")
        End If
      End Try
    
      Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                      "was encountered while inserting the data.")
      Console.WriteLine("Neither record was written to database.")
    Finally
      myConnection.Close()
    End Try
End Sub

C# example:

public void RunSqlTransaction(string myConnString) 
 {
    MySqlConnection myConnection = new MySqlConnection(myConnString);
    myConnection.Open();
    MySqlCommand myCommand = myConnection.CreateCommand();
    MySqlTransaction myTrans;
    // Start a local transaction
    myTrans = myConnection.BeginTransaction();
    // Must assign both transaction object and connection
    // to Command object for a pending local transaction
    myCommand.Connection = myConnection;
    myCommand.Transaction = myTrans;
    try
    {
      myCommand.CommandText = "Insert into mytable (id, desc) VALUES (100, 'Description')";
      myCommand.ExecuteNonQuery();
      myCommand.CommandText = "Insert into mytable (id, desc) VALUES (101, 'Description')";
      myCommand.ExecuteNonQuery();
      myTrans.Commit();
      Console.WriteLine("Both records are written to database.");
    }
    catch(Exception e)
    {
      try
      {
        myTrans.Rollback();
      }
      catch (MySqlException ex)
      {
        if (myTrans.Connection != null)
        {
          Console.WriteLine("An exception of type " + ex.GetType() +
                            " was encountered while attempting to roll back the transaction.");
        }
      }
    
      Console.WriteLine("An exception of type " + e.GetType() +
                        " was encountered while inserting the data.");
      Console.WriteLine("Neither record was written to database.");
    }
    finally 
    {
      myConnection.Close();
    }
}  

Commits the database transaction.

The Commit method is equivalent to the MySQL SQL statement COMMIT.

Examples

The following example creates MySqlConnection and a MySqlTransaction. It also demonstrates how to use the MySqlConnection.BeginTransaction, Commit, and Rollback methods.

Visual Basic example:

Public Sub RunSqlTransaction(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    
    Dim myCommand As MySqlCommand = myConnection.CreateCommand()
    Dim myTrans As MySqlTransaction
    
    ' Start a local transaction
    myTrans = myConnection.BeginTransaction()
    
    ' Must assign both transaction object and connection
    ' to Command object for a pending local transaction
    myCommand.Connection = myConnection
    myCommand.Transaction = myTrans
    
    Try
      myCommand.CommandText = "Insert into mytable (id, desc) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into mytable (id, desc) VALUES (101, 'Description')"
      myCommand.ExecuteNonQuery()
      myTrans.Commit()
      Console.WriteLine("Success.")
    Catch e As Exception
      Try
        myTrans.Rollback()
      Catch ex As MySqlException
        If Not myTrans.Connection Is Nothing Then
          Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                            " was encountered while attempting to roll back the transaction.")
        End If
      End Try
    
      Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                      "was encountered while inserting the data.")
      Console.WriteLine("Neither record was written to database.")
    Finally
      myConnection.Close()
    End Try
End Sub

C# example:

public void RunSqlTransaction(string myConnString) 
 {
    MySqlConnection myConnection = new MySqlConnection(myConnString);
    myConnection.Open();
    MySqlCommand myCommand = myConnection.CreateCommand();
    MySqlTransaction myTrans;
    // Start a local transaction
    myTrans = myConnection.BeginTransaction();
    // Must assign both transaction object and connection
    // to Command object for a pending local transaction
    myCommand.Connection = myConnection;
    myCommand.Transaction = myTrans;
    try
    {
      myCommand.CommandText = "Insert into mytable (id, desc) VALUES (100, 'Description')";
      myCommand.ExecuteNonQuery();
      myCommand.CommandText = "Insert into mytable (id, desc) VALUES (101, 'Description')";
      myCommand.ExecuteNonQuery();
      myTrans.Commit();
      Console.WriteLine("Both records are written to database.");
    }
    catch(Exception e)
    {
      try
      {
        myTrans.Rollback();
      }
      catch (MySqlException ex)
      {
        if (myTrans.Connection != null)
        {
          Console.WriteLine("An exception of type " + ex.GetType() +
                            " was encountered while attempting to roll back the transaction.");
        }
      }
    
      Console.WriteLine("An exception of type " + e.GetType() +
                        " was encountered while inserting the data.");
      Console.WriteLine("Neither record was written to database.");
    }
    finally 
    {
      myConnection.Close();
    }
}  

See Also

MySql.Data.MySqlClient Namespace

For a list of all members of this type, see MySqlCommand Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlCommand_
  Inherits Component_
  Implements IDbCommand, ICloneable

Syntax: C#

public sealed class MySqlCommand : Component, IDbCommand, ICloneable

Thread Safety

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlCommand Members , MySql.Data.MySqlClient Namespace

Overloads Public Sub New()

public MySqlCommand();

Overloads Public Sub New( _
   ByVal cmdText As String _
)

public MySqlCommand(
stringcmdText
);

Overloads Public Sub New( _
   ByVal cmdText As String, _
   ByVal connection As MySqlConnection _
)

public MySqlCommand(
stringcmdText,
MySqlConnectionconnection
);

NotInheritable Public Class MySqlConnection_
  Inherits Component_
  Implements IDbConnection, ICloneable

public sealed class MySqlConnection : Component, IDbConnection, ICloneable

Overloads Public Sub New()

public MySqlConnection();

Overloads Public Sub New( _
   ByVal connectionString As String _
)

public MySqlConnection(
stringconnectionString
);

NotOverridable Public Property ConnectionString As String _
_
  Implements IDbConnection.ConnectionString

public string ConnectionString {get; set;}

NotOverridable Public ReadOnly Property ConnectionTimeout As Integer _
_
  Implements IDbConnection.ConnectionTimeout

public int ConnectionTimeout {get;}

NotOverridable Public ReadOnly Property Database As String _
_
  Implements IDbConnection.Database

public string Database {get;}

Public ReadOnly Property DataSource As String

public string DataSource {get;}

Public ReadOnly Property ServerThread As Integer

public int ServerThread {get;}

Public ReadOnly Property ServerVersion As String

public string ServerVersion {get;}

NotOverridable Public ReadOnly Property State As ConnectionState _
_
  Implements IDbConnection.State

public System.Data.ConnectionState State {get;}

Public ReadOnly Property UseCompression As Boolean

public bool UseCompression {get;}

Overloads Public Function BeginTransaction() As MySqlTransaction

public MySqlTransaction BeginTransaction();

NotInheritable Public Class MySqlTransaction_
  Implements IDbTransaction, IDisposable

public sealed class MySqlTransaction : IDbTransaction, IDisposable

Public ReadOnly Property Connection As MySqlConnection

public MySqlConnection Connection {get;}

NotOverridable Public ReadOnly Property IsolationLevel As IsolationLevel _
_
  Implements IDbTransaction.IsolationLevel

public System.Data.IsolationLevel IsolationLevel {get;}

NotOverridable Public Sub Commit() _
_
  Implements IDbTransaction.Commit

public void Commit();

NotOverridable Public Sub Rollback() _
_
  Implements IDbTransaction.Rollback

public void Rollback();

Overloads Public Function BeginTransaction( _
   ByVal iso As IsolationLevel _
) As MySqlTransaction

public MySqlTransaction BeginTransaction(
IsolationLeveliso
);

NotOverridable Public Sub ChangeDatabase( _
   ByVal databaseName As String _
) _
_
  Implements IDbConnection.ChangeDatabase

public void ChangeDatabase(
stringdatabaseName
);

NotOverridable Public Sub Close() _
_
  Implements IDbConnection.Close

public void Close();

Public Function CreateCommand() As MySqlCommand

public MySqlCommand CreateCommand();

NotOverridable Public Sub Open() _
_
  Implements IDbConnection.Open

public void Open();

Public Function Ping() As Boolean

public bool Ping();

Public Event InfoMessage As MySqlInfoMessageEventHandler

public event MySqlInfoMessageEventHandler InfoMessage;

Public Delegate Sub MySqlInfoMessageEventHandler( _
   ByVal sender As Object, _
   ByVal args As MySqlInfoMessageEventArgs _
)

public delegate void MySqlInfoMessageEventHandler(
objectsender,
MySqlInfoMessageEventArgsargs
);

Public Class MySqlInfoMessageEventArgs_
  Inherits EventArgs

public class MySqlInfoMessageEventArgs : EventArgs

Public Sub New()

public MySqlInfoMessageEventArgs();

Public errors As MySqlError()

public MySqlError[] errors;

Public Class MySqlError

public class MySqlError

Public Sub New( _
   ByVal level As String, _
   ByVal code As Integer, _
   ByVal message As String _
)

public MySqlError(
stringlevel,
intcode,
stringmessage
);

Public ReadOnly Property Code As Integer

public int Code {get;}

Public ReadOnly Property Level As String

public string Level {get;}

Public ReadOnly Property Message As String

public string Message {get;}

Public Event StateChange As StateChangeEventHandler

public event StateChangeEventHandler StateChange;

Overloads Public Sub New( _
   ByVal cmdText As String, _
   ByVal connection As MySqlConnection, _
   ByVal transaction As MySqlTransaction _
)

public MySqlCommand(
stringcmdText,
MySqlConnectionconnection,
MySqlTransactiontransaction
);

NotOverridable Public Property CommandText As String _
_
  Implements IDbCommand.CommandText

public string CommandText {get; set;}

NotOverridable Public Property CommandTimeout As Integer _
_
  Implements IDbCommand.CommandTimeout

public int CommandTimeout {get; set;}

NotOverridable Public Property CommandType As CommandType _
_
  Implements IDbCommand.CommandType

public System.Data.CommandType CommandType {get; set;}

Public Property Connection As MySqlConnection

public MySqlConnection Connection {get; set;}

Public ReadOnly Property IsPrepared As Boolean

public bool IsPrepared {get;}

Public ReadOnly Property Parameters As MySqlParameterCollection

public MySqlParameterCollection Parameters {get;}

NotInheritable Public Class MySqlParameterCollection_
  Inherits MarshalByRefObject_
  Implements IDataParameterCollection, IList, ICollection, IEnumerable

public sealed class MySqlParameterCollection : MarshalByRefObject, IDataParameterCollection, IList, ICollection, IEnumerable

Public Sub New()

public MySqlParameterCollection();

NotOverridable Public ReadOnly Property Count As Integer _
_
  Implements ICollection.Count

public int Count {get;}

NotInheritable Public Class MySqlParameter_
  Inherits MarshalByRefObject_
  Implements IDataParameter, IDbDataParameter, ICloneable

public sealed class MySqlParameter : MarshalByRefObject, IDataParameter, IDbDataParameter, ICloneable

Overloads Public Sub New()

public MySqlParameter();

Overloads Public Sub New( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType _
)

public MySqlParameter(
stringparameterName,
MySqlDbTypedbType
);

Public Enum MySqlDbType

public enum MySqlDbType

Overloads Public Sub New( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType, _
   ByVal size As Integer _
)

public MySqlParameter(
stringparameterName,
MySqlDbTypedbType,
intsize
);

Overloads Public Sub New( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType, _
   ByVal size As Integer, _
   ByVal direction As ParameterDirection, _
   ByVal isNullable As Boolean, _
   ByVal precision As Byte, _
   ByVal scale As Byte, _
   ByVal sourceColumn As String, _
   ByVal sourceVersion As DataRowVersion, _
   ByVal value As Object _
)

public MySqlParameter(
stringparameterName,
MySqlDbTypedbType,
intsize,
ParameterDirectiondirection,
boolisNullable,
byteprecision,
bytescale,
stringsourceColumn,
DataRowVersionsourceVersion,
objectvalue
);

NotOverridable Public Property Value As Object _
_
  Implements IDataParameter.Value

public object Value {get; set;}

Overloads Public Sub New( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType, _
   ByVal size As Integer, _
   ByVal sourceColumn As String _
)

public MySqlParameter(
stringparameterName,
MySqlDbTypedbType,
intsize,
stringsourceColumn
);

Overloads Public Sub New( _
   ByVal parameterName As String, _
   ByVal value As Object _
)

public MySqlParameter(
stringparameterName,
objectvalue
);

NotOverridable Public Property DbType As DbType _
_
  Implements IDataParameter.DbType

public System.Data.DbType DbType {get; set;}

NotOverridable Public Property Direction As ParameterDirection _
_
  Implements IDataParameter.Direction

public System.Data.ParameterDirection Direction {get; set;}

NotOverridable Public Property IsNullable As Boolean _
_
  Implements IDataParameter.IsNullable

public bool IsNullable {get; set;}

Public Property IsUnsigned As Boolean

public bool IsUnsigned {get; set;}

Public Property MySqlDbType As MySqlDbType

public MySqlDbType MySqlDbType {get; set;}

NotOverridable Public Property ParameterName As String _
_
  Implements IDataParameter.ParameterName

public string ParameterName {get; set;}

NotOverridable Public Property Precision As Byte _
_
  Implements IDbDataParameter.Precision

public byte Precision {get; set;}

NotOverridable Public Property Scale As Byte _
_
  Implements IDbDataParameter.Scale

public byte Scale {get; set;}

NotOverridable Public Property Size As Integer _
_
  Implements IDbDataParameter.Size

public int Size {get; set;}

NotOverridable Public Property SourceColumn As String _
_
  Implements IDataParameter.SourceColumn

public string SourceColumn {get; set;}

NotOverridable Public Property SourceVersion As DataRowVersion _
_
  Implements IDataParameter.SourceVersion

public System.Data.DataRowVersion SourceVersion {get; set;}

Overrides Public Function ToString() As String

public override string ToString();

Overloads Public Default Property Item( _
   ByVal index As Integer _
) As MySqlParameter

public MySqlParameter this[
intindex
] {get; set;}

Overloads Public Default Property Item( _
   ByVal name As String _
) As MySqlParameter

public MySqlParameter this[
stringname
] {get; set;}

Overloads Public Function Add( _
   ByVal value As MySqlParameter _
) As MySqlParameter

public MySqlParameter Add(
MySqlParametervalue
);

NotOverridable Overloads Public Function Add( _
   ByVal value As Object _
) As Integer _
_
  Implements IList.Add

public int Add(
objectvalue
);

Overloads Public Function Add( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType _
) As MySqlParameter

public MySqlParameter Add(
stringparameterName,
MySqlDbTypedbType
);

Overloads Public Function Add( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType, _
   ByVal size As Integer _
) As MySqlParameter

public MySqlParameter Add(
stringparameterName,
MySqlDbTypedbType,
intsize
);

Overloads Public Function Add( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType, _
   ByVal size As Integer, _
   ByVal sourceColumn As String _
) As MySqlParameter

public MySqlParameter Add(
stringparameterName,
MySqlDbTypedbType,
intsize,
stringsourceColumn
);

Overloads Public Function Add( _
   ByVal parameterName As String, _
   ByVal value As Object _
) As MySqlParameter

public MySqlParameter Add(
stringparameterName,
objectvalue
);

NotOverridable Public Sub Clear() _
_
  Implements IList.Clear

public void Clear();

NotOverridable Overloads Public Function Contains( _
   ByVal value As Object _
) As Boolean _
_
  Implements IList.Contains

public bool Contains(
objectvalue
);

NotOverridable Overloads Public Function Contains( _
   ByVal name As String _
) As Boolean _
_
  Implements IDataParameterCollection.Contains

public bool Contains(
stringname
);

NotOverridable Public Sub CopyTo( _
   ByVal array As Array, _
   ByVal index As Integer _
) _
_
  Implements ICollection.CopyTo

public void CopyTo(
Arrayarray,
intindex
);

NotOverridable Overloads Public Function IndexOf( _
   ByVal value As Object _
) As Integer _
_
  Implements IList.IndexOf

public int IndexOf(
objectvalue
);

NotOverridable Overloads Public Function IndexOf( _
   ByVal parameterName As String _
) As Integer _
_
  Implements IDataParameterCollection.IndexOf

public int IndexOf(
stringparameterName
);

NotOverridable Public Sub Insert( _
   ByVal index As Integer, _
   ByVal value As Object _
) _
_
  Implements IList.Insert

public void Insert(
intindex,
objectvalue
);

NotOverridable Public Sub Remove( _
   ByVal value As Object _
) _
_
  Implements IList.Remove

public void Remove(
objectvalue
);

NotOverridable Overloads Public Sub RemoveAt( _
   ByVal index As Integer _
) _
_
  Implements IList.RemoveAt

public void RemoveAt(
intindex
);

NotOverridable Overloads Public Sub RemoveAt( _
   ByVal name As String _
) _
_
  Implements IDataParameterCollection.RemoveAt

public void RemoveAt(
stringname
);

Public Property Transaction As MySqlTransaction

public MySqlTransaction Transaction {get; set;}

NotOverridable Public Property UpdatedRowSource As UpdateRowSource _
_
  Implements IDbCommand.UpdatedRowSource

public System.Data.UpdateRowSource UpdatedRowSource {get; set;}

NotOverridable Public Sub Cancel() _
_
  Implements IDbCommand.Cancel

public void Cancel();

Public Function CreateParameter() As MySqlParameter

public MySqlParameter CreateParameter();

NotOverridable Public Function ExecuteNonQuery() As Integer _
_
  Implements IDbCommand.ExecuteNonQuery

public int ExecuteNonQuery();

Overloads Public Function ExecuteReader() As MySqlDataReader

public MySqlDataReader ExecuteReader();

NotInheritable Public Class MySqlDataReader_
  Inherits MarshalByRefObject_
  Implements IEnumerable, IDataReader, IDisposable, IDataRecord

public sealed class MySqlDataReader : MarshalByRefObject, IEnumerable, IDataReader, IDisposable, IDataRecord

NotOverridable Public ReadOnly Property Depth As Integer _
_
  Implements IDataReader.Depth

public int Depth {get;}

NotOverridable Public ReadOnly Property FieldCount As Integer _
_
  Implements IDataRecord.FieldCount

public int FieldCount {get;}

Public ReadOnly Property HasRows As Boolean

public bool HasRows {get;}

NotOverridable Public ReadOnly Property IsClosed As Boolean _
_
  Implements IDataReader.IsClosed

public bool IsClosed {get;}

NotOverridable Overloads Public Default ReadOnly Property Item( _
   ByVal i As Integer _
) _
_
  Implements IDataRecord.Item As Object _
_
  Implements IDataRecord.Item

public object this[
inti
] {get;}

NotOverridable Overloads Public Default ReadOnly Property Item( _
   ByVal name As String _
) _
_
  Implements IDataRecord.Item As Object _
_
  Implements IDataRecord.Item

public object this[
stringname
] {get;}

NotOverridable Public ReadOnly Property RecordsAffected As Integer _
_
  Implements IDataReader.RecordsAffected

public int RecordsAffected {get;}

NotOverridable Public Sub Close() _
_
  Implements IDataReader.Close

public void Close();

NotOverridable Public Function GetBoolean( _
   ByVal i As Integer _
) As Boolean _
_
  Implements IDataRecord.GetBoolean

public bool GetBoolean(
inti
);

NotOverridable Public Function GetByte( _
   ByVal i As Integer _
) As Byte _
_
  Implements IDataRecord.GetByte

public byte GetByte(
inti
);

NotOverridable Public Function GetBytes( _
   ByVal i As Integer, _
   ByVal dataIndex As Long, _
   ByVal buffer As Byte(), _
   ByVal bufferIndex As Integer, _
   ByVal length As Integer _
) As Long _
_
  Implements IDataRecord.GetBytes

public long GetBytes(
inti,
longdataIndex,
byte[]buffer,
intbufferIndex,
intlength
);

NotOverridable Public Function GetChar( _
   ByVal i As Integer _
) As Char _
_
  Implements IDataRecord.GetChar

public char GetChar(
inti
);

NotOverridable Public Function GetChars( _
   ByVal i As Integer, _
   ByVal fieldOffset As Long, _
   ByVal buffer As Char(), _
   ByVal bufferoffset As Integer, _
   ByVal length As Integer _
) As Long _
_
  Implements IDataRecord.GetChars

public long GetChars(
inti,
longfieldOffset,
char[]buffer,
intbufferoffset,
intlength
);

NotOverridable Public Function GetDataTypeName( _
   ByVal i As Integer _
) As String _
_
  Implements IDataRecord.GetDataTypeName

public string GetDataTypeName(
inti
);

NotOverridable Public Function GetDateTime( _
   ByVal index As Integer _
) As Date _
_
  Implements IDataRecord.GetDateTime

public DateTime GetDateTime(
intindex
);

NotOverridable Public Function GetDecimal( _
   ByVal index As Integer _
) As Decimal _
_
  Implements IDataRecord.GetDecimal

public decimal GetDecimal(
intindex
);

NotOverridable Public Function GetDouble( _
   ByVal index As Integer _
) As Double _
_
  Implements IDataRecord.GetDouble

public double GetDouble(
intindex
);

NotOverridable Public Function GetFieldType( _
   ByVal i As Integer _
) As Type _
_
  Implements IDataRecord.GetFieldType

public Type GetFieldType(
inti
);

NotOverridable Public Function GetFloat( _
   ByVal index As Integer _
) As Single _
_
  Implements IDataRecord.GetFloat

public float GetFloat(
intindex
);

NotOverridable Public Function GetGuid( _
   ByVal index As Integer _
) As Guid _
_
  Implements IDataRecord.GetGuid

public Guid GetGuid(
intindex
);

NotOverridable Public Function GetInt16( _
   ByVal index As Integer _
) As Short _
_
  Implements IDataRecord.GetInt16

public short GetInt16(
intindex
);

NotOverridable Public Function GetInt32( _
   ByVal index As Integer _
) As Integer _
_
  Implements IDataRecord.GetInt32

public int GetInt32(
intindex
);

NotOverridable Public Function GetInt64( _
   ByVal index As Integer _
) As Long _
_
  Implements IDataRecord.GetInt64

public long GetInt64(
intindex
);

Public Function GetMySqlDateTime( _
   ByVal index As Integer _
) As MySqlDateTime

public MySqlDateTime GetMySqlDateTime(
intindex
);

NotOverridable Public Function GetName( _
   ByVal i As Integer _
) As String _
_
  Implements IDataRecord.GetName

public string GetName(
inti
);

NotOverridable Public Function GetOrdinal( _
   ByVal name As String _
) As Integer _
_
  Implements IDataRecord.GetOrdinal

public int GetOrdinal(
stringname
);

NotOverridable Public Function GetSchemaTable() As DataTable _
_
  Implements IDataReader.GetSchemaTable

public DataTable GetSchemaTable();

NotOverridable Public Function GetString( _
   ByVal index As Integer _
) As String _
_
  Implements IDataRecord.GetString

public string GetString(
intindex
);

Public Function GetTimeSpan( _
   ByVal index As Integer _
) As TimeSpan

public TimeSpan GetTimeSpan(
intindex
);

Public Function GetUInt16( _
   ByVal index As Integer _
) As UInt16

public ushort GetUInt16(
intindex
);

Public Function GetUInt32( _
   ByVal index As Integer _
) As UInt32

public uint GetUInt32(
intindex
);

Public Function GetUInt64( _
   ByVal index As Integer _
) As UInt64

public ulong GetUInt64(
intindex
);

NotOverridable Public Function GetValue( _
   ByVal i As Integer _
) As Object _
_
  Implements IDataRecord.GetValue

public object GetValue(
inti
);

NotOverridable Public Function GetValues( _
   ByVal values As Object() _
) As Integer _
_
  Implements IDataRecord.GetValues

public int GetValues(
object[]values
);

NotOverridable Public Function IsDBNull( _
   ByVal i As Integer _
) As Boolean _
_
  Implements IDataRecord.IsDBNull