26.2.3.2. The MySqlCommandBuilder Class
The MySqlDataAdapter does not automatically generate the SQL
statements required to reconcile changes made to a DataSet with
the associated instance of MySQL. However, you can create a
MySqlCommandBuilder object to automatically generate SQL
statements for single-table updates if you set the SelectCommand
property of the MySqlDataAdapter. Then, any additional SQL
statements that you do not set are generated by the
MySqlCommandBuilder.
The MySqlCommandBuilder registers itself as a listener for
OnRowUpdating events whenever you set the DataAdapter property.
You can only associate one MySqlDataAdapter or
MySqlCommandBuilder object with each other at one time.
To generate INSERT, UPDATE, or DELETE statements, the
MySqlCommandBuilder uses the SelectCommand property to retrieve
a required set of metadata automatically. If you change the
SelectCommand after the metadata has is retrieved (for example,
after the first update), you should call the RefreshSchema
method to update the metadata.
The SelectCommand must also return at least one primary key or
unique column. If none are present, an InvalidOperation
exception is generated, and the commands are not generated.
The MySqlCommandBuilder also uses the Connection,
CommandTimeout, and Transaction properties referenced by the
SelectCommand. The user should call RefreshSchema if any of
these properties are modified, or if the SelectCommand itself is
replaced. Otherwise the InsertCommand, UpdateCommand, and
DeleteCommand properties retain their previous values.
If you call Dispose, the MySqlCommandBuilder is disassociated
from the MySqlDataAdapter, and the generated commands are no
longer used.
The following properties are available:
DataAdapter: The MySqlCommandBuilder
registers itself as a listener for 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.
QuotePrefix,
QuoteSuffix: 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.
The following methods are available:
DeriveParameters: Retrieves parameter
information from the stored procedure specified in the
MySqlCommand and populates the Parameters collection of
the specified MySqlCommand object. This method is not
currently supported because stored procedures are not
available in MySql.
GetDeleteCommand: Gets the
automatically generated MySqlCommand object required to
perform deletions on the database.
GetInsertCommand: Gets the
automatically generated MySqlCommand object required to
perform insertions on the database.
GetUpdateCommand: Gets the
automatically generated MySqlCommand object required to
perform updates on the database.
RefreshSchema: Refreshes the database
schema information used to generate INSERT, UPDATE, or
DELETE statements.
The following example uses the MySqlCommand, along
MySqlDataAdapter and MySqlConnection, to select rows from a
data source. The example is passed an initialized DataSet, a
connection string, a query string that is an SQL SELECT
statement, and a string that is the name of the database
table. The example then creates a MySqlCommandBuilder.
The following example shows how to use the
MySqlCommandBuilder class with VB.NET:
Public Shared Function SelectRows(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 cb As SqlCommandBuilder = New MySqlCommandBuilder(myDataAdapter)
myConn.Open()
Dim ds As DataSet = New DataSet
myDataAdapter.Fill(ds, myTableName)
' Code to modify data in DataSet here
' Without the MySqlCommandBuilder this line would fail.
myDataAdapter.Update(ds, myTableName)
myConn.Close()
End Function 'SelectRows
The following example shows how to use the
MySqlCommandBuilder class with C#:
public static DataSet SelectRows(string myConnection, string mySelectQuery, string myTableName)
{
MySqlConnection myConn = new MySqlConnection(myConnection);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
myDataAdapter.SelectCommand = new MySqlCommand(mySelectQuery, myConn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter);
myConn.Open();
DataSet ds = new DataSet();
myDataAdapter.Fill(ds, myTableName);
//code to modify data in DataSet here
//Without the MySqlCommandBuilder this line would fail
myDataAdapter.Update(ds, myTableName);
myConn.Close();
return ds;
}
