Skip navigation links
25.2.3.3 MySqlConnection »
Section Navigation      [Toggle]

25.2.3.2. MySqlCommandBuilder

[+/-]

25.2.3.2.1. Class MySqlCommandBuilder Constructor
25.2.3.2.2. Class MySqlCommandBuilder Constructor Form 1
25.2.3.2.3. Class MySqlCommandBuilder Constructor Form 2
25.2.3.2.4. Class MySqlCommandBuilder Constructor Form 3
25.2.3.2.5. DataAdapter
25.2.3.2.6. QuotePrefix
25.2.3.2.7. QuoteSuffix
25.2.3.2.8. DeriveParameters
25.2.3.2.9. GetDeleteCommand
25.2.3.2.10. GetInsertCommand
25.2.3.2.11. GetUpdateCommand
25.2.3.2.12. RefreshSchema

Automatically generates single-table commands used to reconcile changes made to a DataSet with the associated MySQL database. This class cannot be inherited.

The MySqlDataAdapter does not automatically generate the SQL statements required to reconcile changes made to a System.Data.DataSetDataSet 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 MySqlDataAdapter.SelectCommandSelectCommand 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 MySqlDataAdapter.OnRowUpdatingRowUpdating 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 MySqlCommand.ConnectionConnection, MySqlCommand.CommandTimeoutCommandTimeout, and MySqlCommand.TransactionTransaction 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 MySqlDataAdapter.InsertCommandInsertCommand, MySqlDataAdapter.UpdateCommandUpdateCommand, and MySqlDataAdapter.DeleteCommandDeleteCommand properties retain their previous values.

If you call Dispose, the MySqlCommandBuilder is disassociated from the MySqlDataAdapter, and the generated commands are no longer used.

Note.  Caution must be used when using MySqlCOmmandBuilder on MySql 4.0 systems. With MySql 4.0, database/schema information is not provided to the connector for a query. This means that a query that pulls columns from two identically named tables in two or more different databases will not cause an exception to be thrown but will not work correctly. Even more dangerous is the situation where your select statement references database X but is executed in database Y and both databases have tables with similar layouts. This situation can cause unwanted changes or deletes. This note does not apply to MySQL versions 4.1 and later.

Examples

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

Visual Basic example: 

  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

C# example: 

  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;
  }
25.2.3.2.1. Class MySqlCommandBuilder Constructor

Initializes a new instance of the MySqlCommandBuilder class.

25.2.3.2.2. Class MySqlCommandBuilder Constructor Form 1

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.

25.2.3.2.3. Class MySqlCommandBuilder Constructor Form 2

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.

25.2.3.2.4. Class MySqlCommandBuilder Constructor Form 3

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.

25.2.3.2.5. DataAdapter

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.

25.2.3.2.6. QuotePrefix

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.

25.2.3.2.7. QuoteSuffix

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.

25.2.3.2.8. DeriveParameters

25.2.3.2.9. GetDeleteCommand

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.

25.2.3.2.10. GetInsertCommand

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.

25.2.3.2.11. GetUpdateCommand

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.

25.2.3.2.12. RefreshSchema

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.

Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.