Class MySqlCommandBuilder

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

Inheritance

System.Object

System.MarshalByRefObject

System.ComponentModel.Component

System.Data.Common.DbCommandBuilder

MySqlCommandBuilder

Implements

System.ComponentModel.IComponent

System.IDisposable

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data.dll

Version: 9.1.0

Syntax

public sealed class MySqlCommandBuilder : DbCommandBuilder, IComponent, IDisposable

Remarks

The MySqlDataAdapter does not automatically generate the SQL statements required to reconcile changes made to a System.Data.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 RowUpdating 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 System.Data.Common.DbDataAdapter.SelectCommand property to retrieve a required set of metadata automatically. If you change the System.Data.Common.DbDataAdapter.SelectCommand after the metadata has is retrieved (for example, after the first update), you should call the method to update the metadata.

The System.Data.Common.DbDataAdapter.SelectCommand must also return at least one primary key or unique column. If none are present, an System.InvalidOperationException exception is generated, and the commands are not generated.

The MySqlCommandBuilder also uses the Connection, CommandTimeout, and Transaction properties referenced by the System.Data.Common.DbDataAdapter.SelectCommand. The user should call System.Data.Common.DbCommandBuilder.RefreshSchema() if any of these properties are modified, or if the System.Data.Common.DbDataAdapter.SelectCommand itself is replaced. Otherwise the InsertCommand, UpdateCommand, and DeleteCommand properties retain their previous values.

If you call System.Data.Common.DbCommandBuilder.Dispose(System.Boolean), the MySqlCommandBuilder is disassociated from the MySqlDataAdapter, and the generated commands are no longer used.

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.

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

Constructors

MySqlCommandBuilder()

Initializes a new instance of the MySqlCommandBuilder class.

Declaration

public MySqlCommandBuilder()

MySqlCommandBuilder(MySqlDataAdapter)

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

Declaration

public MySqlCommandBuilder(MySqlDataAdapter adapter)

Parameters

Type	Name	Description
MySqlDataAdapter	adapter	The MySqlDataAdapter to use.

Remarks

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.

Properties

DataAdapter

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

Declaration

public MySqlDataAdapter DataAdapter { get; set; }

Property Value

Type	Description
MySqlDataAdapter	A MySqlDataAdapter object.

Remarks

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.

Methods

ApplyParameterInfo(DbParameter, DataRow, StatementType, Boolean)

Allows the provider implementation of the System.Data.Common.DbCommandBuilder class to handle additional parameter properties.

Declaration

protected override void ApplyParameterInfo(DbParameter parameter, DataRow row, StatementType statementType, bool whereClause)

Parameters

Type	Name	Description
System.Data.Common.DbParameter	parameter	A System.Data.Common.DbParameter to which the additional modifications are applied.
System.Data.DataRow	row	The System.Data.DataRow from the schema table provided by GetSchemaTable(DbCommand).
System.Data.StatementType	statementType	The type of command being generated; INSERT, UPDATE or DELETE.
System.Boolean	whereClause	true if the parameter is part of the update or delete WHERE clause, false if it is part of the insert or update values.

Overrides

System.Data.Common.DbCommandBuilder.ApplyParameterInfo(System.Data.Common.DbParameter, System.Data.DataRow, System.Data.StatementType, System.Boolean)

DeriveParameters(MySqlCommand)

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 since stored procedures are not available in MySQL.

Declaration

public static void DeriveParameters(MySqlCommand command)

Parameters

Type	Name	Description
MySqlCommand	command	The MySqlCommand referencing the stored procedure from which the parameter information is to be derived. The derived parameters are added to the Parameters collection of the MySqlCommand.

Exceptions

Type	Condition
System.InvalidOperationException	The command text is not a valid stored procedure name.

GetDeleteCommand()

Gets the delete command.

Declaration

public MySqlCommand GetDeleteCommand()

Returns

Type	Description
MySqlCommand	The MySqlCommand object required to perform deletions.

GetInsertCommand()

Gets the insert command.

Declaration

public MySqlCommand GetInsertCommand()

Returns

Type	Description
MySqlCommand	The MySqlCommand object required to perform inserts.

GetParameterName(Int32)

Returns the name of the specified parameter in the format of @p#. Use when building a custom command builder.

Declaration

protected override string GetParameterName(int parameterOrdinal)

Parameters

Type	Name	Description
System.Int32	parameterOrdinal	The number to be included as part of the parameter's name.

Returns

Type	Description
System.String	The name of the parameter with the specified number appended as part of the parameter name.

Overrides

System.Data.Common.DbCommandBuilder.GetParameterName(System.Int32)

GetParameterName(String)

Returns the full parameter name, given the partial parameter name.

Declaration

protected override string GetParameterName(string parameterName)

Parameters

Type	Name	Description
System.String	parameterName	The partial name of the parameter.

Returns

Type	Description
System.String	The full parameter name corresponding to the partial parameter name requested.

Overrides

System.Data.Common.DbCommandBuilder.GetParameterName(System.String)

GetParameterPlaceholder(Int32)

Returns the placeholder for the parameter in the associated SQL statement.

Declaration

protected override string GetParameterPlaceholder(int parameterOrdinal)

Parameters

Type	Name	Description
System.Int32	parameterOrdinal	The number to be included as part of the parameter's name.

Returns

Type	Description
System.String	The name of the parameter with the specified number appended.

Overrides

System.Data.Common.DbCommandBuilder.GetParameterPlaceholder(System.Int32)

GetSchemaTable(DbCommand)

Returns the schema table for the MySqlCommandBuilder

Declaration

protected override DataTable GetSchemaTable(DbCommand sourceCommand)

Parameters

Type	Name	Description
System.Data.Common.DbCommand	sourceCommand	The System.Data.Common.DbCommand for which to retrieve the corresponding schema table.

Returns

Type	Description
System.Data.DataTable	A System.Data.DataTable that represents the schema for the specific System.Data.Common.DbCommand.

Overrides

System.Data.Common.DbCommandBuilder.GetSchemaTable(System.Data.Common.DbCommand)

GetUpdateCommand()

Gets the update command.

Declaration

public MySqlCommand GetUpdateCommand()

Returns

Type	Description
MySqlCommand	The MySqlCommand object required to perform updates.

QuoteIdentifier(String)

Given an unquoted identifier in the correct catalog case, returns the correct quoted form of that identifier, including properly escaping any embedded quotes in the identifier.

Declaration

public override string QuoteIdentifier(string unquotedIdentifier)

Parameters

Type	Name	Description
System.String	unquotedIdentifier	The original unquoted identifier.

Returns

Type	Description
System.String	The quoted version of the identifier. Embedded quotes within the identifier are properly escaped.

Overrides

System.Data.Common.DbCommandBuilder.QuoteIdentifier(System.String)

Exceptions

Type	Condition
System.ArgumentNullException	If the unquotedIdentifier is null.

SetRowUpdatingHandler(DbDataAdapter)

Registers the MySqlCommandBuilder to handle the MySql.Data.MySqlClient.MySqlCommandBuilder.RowUpdating(System.Object,MySql.Data.MySqlClient.MySqlRowUpdatingEventArgs) event for a System.Data.Common.DbDataAdapter.

Declaration

protected override void SetRowUpdatingHandler(DbDataAdapter adapter)

Parameters

Type	Name	Description
System.Data.Common.DbDataAdapter	adapter

Overrides

System.Data.Common.DbCommandBuilder.SetRowUpdatingHandler(System.Data.Common.DbDataAdapter)

UnquoteIdentifier(String)

Given a quoted identifier, returns the correct unquoted form of that identifier, including properly un-escaping any embedded quotes in the identifier.

Declaration

public override string UnquoteIdentifier(string quotedIdentifier)

Parameters

Type	Name	Description
System.String	quotedIdentifier	The identifier that will have its embedded quotes removed.

Returns

Type	Description
System.String	The unquoted identifier, with embedded quotes properly un-escaped.

Overrides

System.Data.Common.DbCommandBuilder.UnquoteIdentifier(System.String)

Exceptions

Type	Condition
System.ArgumentNullException	If the quotedIdentifier is null.

Implements

System.ComponentModel.IComponent

System.IDisposable