26.2.3.4. The MySqlDataAdapter Class
The MySQLDataAdapter serves as a bridge between a DataSet and
MySQL for retrieving and saving data. The MySQLDataAdapter
provides this bridge by mapping Fill, which changes the data in
the DataSet to match the data in the data source, and Update,
which changes the data in the data source to match the data in
the DataSet, using the appropriate SQL statements against the
data source.
When the MySQLDataAdapter fills a DataSet, it will create the
necessary tables and columns for the returned data if they do
not already exist. However, primary key information will not be
included in the implicitly created schema unless the
MissingSchemaAction property is set to AddWithKey. You may also
have the MySQLDataAdapter create the schema of the DataSet,
including primary key information, before filling it with data
using FillSchema.
MySQLDataAdapter is used in conjunction with MySqlConnection and
MySqlCommand to increase performance when connecting to a MySQL
database.
The MySQLDataAdapter also includes the SelectCommand,
InsertCommand, DeleteCommand, UpdateCommand, and TableMappings
properties to facilitate the loading and updating of data.
The following properties are available:
AcceptChangesDuringFill: Gets or sets a
value indicating whether AcceptChanges is called on a
DataRow after it is added to the DataTable during any of
the Fill operations.
ContinueUpdateOnError: Gets or sets a
value that specifies whether to generate an exception when
an error is encountered during a row update.
DeleteCommand: Gets or sets an SQL
statement or stored procedure used to delete records from
the data set.
InsertCommand: Gets or sets an SQL
statement or stored procedure used to insert records into
the data set.
MissingMappingAction: Determines the
action to take when incoming data does not have a matching
table or column.
MissingSchemaAction: Determines the
action to take when existing DataSet schema does not match
incoming data.
SelectCommand: Gets or sets an SQL
statement or stored procedure used to select records in
the data source.
TableMappings: Gets a collection that
provides the master mapping between a source table and a
DataTable.
UpdateCommand: Gets or sets an SQL
statement or stored procedure used to updated records in
the data source.
The following methods are available:
Fill: Adds or refreshes rows in the
DataSet to match those in the data source using the
DataSet name, and creates a DataTable named "Table".
FillSchema: Adds a DataTable named
"Table" to the specified DataSet and configures the schema
to match that in the data source based on the specified
SchemaType.
GetFillParameters: Gets the parameters
set by the user when executing an SQL SELECT statement.
Update: Calls the respective INSERT,
UPDATE, or DELETE statements for each inserted, updated,
or deleted row in the specified DataSet.
The following example creates a MySqlCommand and a
MySqlConnection. The MySqlConnection is opened and set as the
Connection for the MySqlCommand. The example then calls
ExecuteNonQuery, and closes the connection. To accomplish
this, the ExecuteNonQuery is passed a connection string and a
query string that is an SQL INSERT statement.
The following example shows how to use the MySqlDataAdapter
class with VB.NET:
Public Function SelectRows(dataSet As DataSet, connection As String, query As String) As DataSet
Dim conn As New MySqlConnection(connection)
Dim adapter As New MySqlDataAdapter()
adapter.SelectCommand = new MySqlCommand(query, conn)
adapter.Fill(dataset)
Return dataset
End Function
The following example shows how to use the MySqlDataAdapter
class with C#:
public DataSet SelectRows(DataSet dataset,string connection,string query)
{
MySqlConnection conn = new MySqlConnection(connection);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = new MySqlCommand(query, conn);
adapter.Fill(dataset);
return dataset;
}
