MySqlCommandBuilder Class

In This Topic

Automatically generates single-table commands used to reconcile changes made to a System.Data.DataSet with the MySQL.

Syntax

Visual Basic
C#


'Declaration Public Class MySqlCommandBuilder Inherits Devart.Common.DbCommandBuilder Implements System.ComponentModel.IComponent, System.IDisposable


public class MySqlCommandBuilder : Devart.Common.DbCommandBuilder, System.ComponentModel.IComponent, System.IDisposable

Remarks

The MySqlDataAdapter does not automatically generate the SQL statements required to reconcile changes made to a System.Data.DataSet associated with MySQL. However, you can create a MySqlCommandBuilder object that generates SQL statements for single-table updates. After assigning the MySqlDataAdapter to the MySqlCommandBuilder, it begins to generate any additional SQL statements that you do not set.

The relationship between a MySqlDataAdapter and its corresponding MySqlCommandBuilder is always one-to-one. To create this correspondence, you set the DataAdapter property of the MySqlCommandBuilder object. This causes the MySqlCommandBuilder to register itself as a listener, which produces the output of MySqlDataAdapter.RowUpdating events that affect the System.Data.DataSet.

To generate INSERT, UPDATE, or DELETE statements, the MySqlCommandBuilder uses the MySqlDataAdapter.SelectCommand property to retrieve a required set of metadata. If you change the value of MySqlDataAdapter.SelectCommand after the metadata has been retrieved (for example, after the first update), you then should call the Devart.Common.DbCommandBuilderBase.RefreshSchema method to update the metadata.

The MySqlCommandBuilder also uses the Connection, CommandTimeout, and System.Data.Common.DbCommand.Transaction properties referenced by the MySqlDataAdapter.SelectCommand. The user should call Devart.Common.DbCommandBuilderBase.RefreshSchema if any of these properties are modified, or value of the MySqlDataAdapter.SelectCommand property itself is changed. Otherwise the MySqlDataAdapter.InsertCommand, MySqlDataAdapter.UpdateCommand, and MySqlDataAdapter.DeleteCommand properties retain their previous values.

If you assign null to corresponding property of MySqlDataAdapter, the MySqlCommandBuilder will be disassociated from the MySqlDataAdapter, and the generated commands will no longer be used.

Note: This class is not available in .NET Standard 1.3 compatible assembly. It is available only in the assembly for full .NET Framework and .NET Standard 2.0 compatible assembly.

Example

The following example uses MySqlCommand, along with MySqlDataAdapter and MySqlConnection, to select rows from MySQL. The example is passed an initialized System.Data.DataSet, a connection string, a query string that is SQL SELECT statement, and a string that is the name of the MySQL table. The example then creates a MySqlCommandBuilder.

csharp


public DataSet SelectMySqlSrvRows(DataSet myDataSet,string myConnection,string mySelectQuery,string myTableName) { MySqlConnection myConn = new MySqlConnection(myConnection); MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(); myDataAdapter.SelectCommand = new MySqlCommand(mySelectQuery, myConn); MySqlCommandBuilder myCommandBuilder = new MySqlCommandBuilder(myDataAdapter); myConn.Open(); DataSet myDataSet = new DataSet(); myDataAdapter.Fill(myDataSet, "Departments"); //code to modify data in dataset here //Without the MySqlCommandBuilder this line would fail myDataAdapter.Update(myDataSet, "Departments"); myConn.Close(); return myDataSet; }

public DataSet SelectMySqlSrvRows(DataSet myDataSet,string myConnection,string mySelectQuery,string myTableName)
{
  MySqlConnection myConn = new MySqlConnection(myConnection);
  MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
  myDataAdapter.SelectCommand = new MySqlCommand(mySelectQuery, myConn);
  MySqlCommandBuilder myCommandBuilder = new MySqlCommandBuilder(myDataAdapter);

  myConn.Open();

  DataSet myDataSet = new DataSet();
  myDataAdapter.Fill(myDataSet, "Departments");

  //code to modify data in dataset here

  //Without the MySqlCommandBuilder this line would fail
  myDataAdapter.Update(myDataSet, "Departments");

  myConn.Close();

  return myDataSet;
}

Visual Basic


Public Function SelectMySqlSrvRows(myDataSet As DataSet, myConnection As String, mySelectQuery As String, myTableName As String) As DataSet Dim myConn As New MySqlConnection(myConnection) Dim myDataAdapter As New MySqlDataAdapter() myDataAdapter.SelectCommand = New MySqlCommand(mySelectQuery, myConn) Dim myCommandBuilder As MySqlCommandBuilder = New MySqlCommandBuilder(myDataAdapter) myConn.Open() Dim myDataSet As DataSet = New DataSet myDataAdapter.Fill(myDataSet, "Departments") ' Code to modify data in DataSet here ' Without the MySqlCommandBuilder this line would fail. myDataAdapter.Update(myDataSet, "Departments") myConn.Close() SelectMySqlSrvRows = myDataSet End Function

Public Function SelectMySqlSrvRows(myDataSet As DataSet, myConnection As String, mySelectQuery As String, myTableName As String) As DataSet
  Dim myConn As New MySqlConnection(myConnection)
  Dim myDataAdapter As New MySqlDataAdapter()
  myDataAdapter.SelectCommand = New MySqlCommand(mySelectQuery, myConn)
  Dim myCommandBuilder As MySqlCommandBuilder = New MySqlCommandBuilder(myDataAdapter)

  myConn.Open()

  Dim myDataSet As DataSet = New DataSet
  myDataAdapter.Fill(myDataSet, "Departments")

  ' Code to modify data in DataSet here

  ' Without the MySqlCommandBuilder this line would fail.
  myDataAdapter.Update(myDataSet, "Departments")

  myConn.Close()

  SelectMySqlSrvRows = myDataSet
End Function

Inheritance Hierarchy

System.Object
   System.MarshalByRefObject
      System.ComponentModel.Component
         System.Data.Common.DbCommandBuilder
            Devart.Common.DbCommandBuilderBase
               Devart.Common.DbCommandBuilder
                  Devart.Data.MySql.MySqlCommandBuilder

Reference

MySqlCommandBuilder Members
Devart.Data.MySql Namespace
MySqlDataAdapter Class
MySqlConnection Class
MySqlCommand Class

Send Feedback