Represents a set of data commands and a database connection that are used to fill a dataset and update a MySQL database. This class cannot be inherited.

The MySQLDataAdapter, serves as a bridge between a and MySQL for retrieving and saving data. The MySQLDataAdapter provides this bridge by mapping , which changes the data in the DataSet to match the data in the data source, and , 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 property is set to . You may also have the MySQLDataAdapter create the schema of the DataSet, including primary key information, before filling it with data using .

MySQLDataAdapter is used in conjunction with and to increase performance when connecting to a MySQL database.

The MySQLDataAdapter also includes the , , , , and properties to facilitate the loading and updating of data.

When an instance of MySQLDataAdapter is created, the read/write properties are set to initial values. For a list of these values, see the MySQLDataAdapter constructor.

Please be aware that the class allows only Int16, Int32, and Int64 to have the AutoIncrement property set. If you plan to use autoincremement columns with MySQL, you should consider using signed integer columns.

The following example creates a and a . The MySqlConnection is opened and set as the for the MySqlCommand. The example then calls , and closes the connection. To accomplish this, the ExecuteNonQuery is passed a connection string and a query string that is a SQL INSERT statement. `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` `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; }`
Initializes a new instance of the MySqlDataAdapter class.

When an instance of is created, the following read/write properties are set to the following initial values.

Properties
Initial Value
MissingMappingAction.Passthrough
MissingSchemaAction.Add

You can change the value of any of these properties through a separate call to the property.

The following example creates a and sets some of its properties. `Public Sub CreateSqlDataAdapter() Dim conn As MySqlConnection = New MySqlConnection("Data Source=localhost;" & _ "database=test") Dim da As MySqlDataAdapter = New MySqlDataAdapter da.MissingSchemaAction = MissingSchemaAction.AddWithKey da.SelectCommand = New MySqlCommand("SELECT id, name FROM mytable", conn) da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _ "VALUES (@id, @name)", conn) da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=@id, name=@name " & _ "WHERE id=@oldId", conn) da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=@id", conn) da.InsertCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id") da.InsertCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name") da.UpdateCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id") da.UpdateCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name") da.UpdateCommand.Parameters.Add("@oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original da.DeleteCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original End Sub` `public static void CreateSqlDataAdapter() { MySqlConnection conn = new MySqlConnection("Data Source=localhost;database=test"); MySqlDataAdapter da = new MySqlDataAdapter(); da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.SelectCommand = new MySqlCommand("SELECT id, name FROM mytable", conn); da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " + "VALUES (@id, @name)", conn); da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=@id, name=@name " + "WHERE id=@oldId", conn); da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=@id", conn); da.InsertCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id"); da.InsertCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name"); da.UpdateCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id"); da.UpdateCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name"); da.UpdateCommand.Parameters.Add("@oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original; da.DeleteCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original; }`
Initializes a new instance of the class with the specified as the property.
that is a SQL SELECT statement or stored procedure and is set as the property of the .

When an instance of is created, the following read/write properties are set to the following initial values.

Properties
Initial Value
MissingMappingAction.Passthrough
MissingSchemaAction.Add

You can change the value of any of these properties through a separate call to the property.

When SelectCommand (or any of the other command properties) is assigned to a previously created , the MySqlCommand is not cloned. The SelectCommand maintains a reference to the previously created MySqlCommand object.

The following example creates a and sets some of its properties. `Public Sub CreateSqlDataAdapter() Dim conn As MySqlConnection = New MySqlConnection("Data Source=localhost;" & _ "database=test") Dim cmd as new MySqlCommand("SELECT id, name FROM mytable", conn) Dim da As MySqlDataAdapter = New MySqlDataAdapter(cmd) da.MissingSchemaAction = MissingSchemaAction.AddWithKey da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _ "VALUES (@id, @name)", conn) da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=@id, name=@name " & _ "WHERE id=@oldId", conn) da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=@id", conn) da.InsertCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id") da.InsertCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name") da.UpdateCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id") da.UpdateCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name") da.UpdateCommand.Parameters.Add("@oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original da.DeleteCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original End Sub` `public static void CreateSqlDataAdapter() { MySqlConnection conn = new MySqlConnection("Data Source=localhost;database=test"); MySqlCommand cmd = new MySqlCommand("SELECT id, name FROM mytable", conn); MySqlDataAdapter da = new MySqlDataAdapter(cmd); da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " + "VALUES (@id, @name)", conn); da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=@id, name=@name " + "WHERE id=@oldId", conn); da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=@id", conn); da.InsertCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id"); da.InsertCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name"); da.UpdateCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id"); da.UpdateCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name"); da.UpdateCommand.Parameters.Add("@oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original; da.DeleteCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original; }`
Initializes a new instance of the class with a and a object.
A String that is a SQL SELECT statement or stored procedure to be used by the property of the .
A that represents the connection.

This implementation of the opens and closes a if it is not already open. This can be useful in a an application that must call the method for two or more MySqlDataAdapter objects. If the MySqlConnection is already open, you must explicitly call or to close it.

When an instance of is created, the following read/write properties are set to the following initial values.

Properties
Initial Value
MissingMappingAction.Passthrough
MissingSchemaAction.Add

You can change the value of any of these properties through a separate call to the property.

The following example creates a and sets some of its properties. `Public Sub CreateSqlDataAdapter() Dim conn As MySqlConnection = New MySqlConnection("Data Source=localhost;" & _ "database=test") Dim da As MySqlDataAdapter = New MySqlDataAdapter("SELECT id, name FROM mytable", conn) da.MissingSchemaAction = MissingSchemaAction.AddWithKey da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _ "VALUES (@id, @name)", conn) da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=@id, name=@name " & _ "WHERE id=@oldId", conn) da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=@id", conn) da.InsertCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id") da.InsertCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name") da.UpdateCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id") da.UpdateCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name") da.UpdateCommand.Parameters.Add("@oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original da.DeleteCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original End Sub` `public static void CreateSqlDataAdapter() { MySqlConnection conn = new MySqlConnection("Data Source=localhost;database=test"); MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, name FROM mytable", conn); da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " + "VALUES (@id, @name)", conn); da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=@id, name=@name " + "WHERE id=@oldId", conn); da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=@id", conn); da.InsertCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id"); da.InsertCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name"); da.UpdateCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id"); da.UpdateCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name"); da.UpdateCommand.Parameters.Add("@oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original; da.DeleteCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original; }`
Initializes a new instance of the class with a and a connection string.
A that is a SQL SELECT statement or stored procedure to be used by the property of the .
The connection string

When an instance of is created, the following read/write properties are set to the following initial values.

Properties
Initial Value
MissingMappingAction.Passthrough
MissingSchemaAction.Add

You can change the value of any of these properties through a separate call to the property.

The following example creates a and sets some of its properties. `Public Sub CreateSqlDataAdapter() Dim da As MySqlDataAdapter = New MySqlDataAdapter("SELECT id, name FROM mytable", "Data Source=localhost;database=test") Dim conn As MySqlConnection = da.SelectCommand.Connection da.MissingSchemaAction = MissingSchemaAction.AddWithKey da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _ "VALUES (@id, @name)", conn) da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=@id, name=@name " & _ "WHERE id=@oldId", conn) da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=@id", conn) da.InsertCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id") da.InsertCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name") da.UpdateCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id") da.UpdateCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name") da.UpdateCommand.Parameters.Add("@oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original da.DeleteCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original End Sub` `public static void CreateSqlDataAdapter() { MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, name FROM mytable", "Data Source=localhost;database=test"); MySqlConnection conn = da.SelectCommand.Connection; da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " + "VALUES (@id, @name)", conn); da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=@id, name=@name " + "WHERE id=@oldId", conn); da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=@id", conn); da.InsertCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id"); da.InsertCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name"); da.UpdateCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id"); da.UpdateCommand.Parameters.Add("@name", MySqlDbType.VarChar, 40, "name"); da.UpdateCommand.Parameters.Add("@oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original; da.DeleteCommand.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original; }`
Gets or sets a SQL statement or stored procedure used to delete records from the data set.
A used during to delete records in the database that correspond to deleted rows in the .

During , if this property is not set and primary key information is present in the , the DeleteCommand can be generated automatically if you set the property and use the . Then, any additional commands that you do not set are generated by the MySqlCommandBuilder. This generation logic requires key column information to be present in the DataSet.

When DeleteCommand is assigned to a previously created , the MySqlCommand is not cloned. The DeleteCommand maintains a reference to the previously created MySqlCommand object.

The following example creates a and sets the and DeleteCommand properties. It assumes you have already created a object. `Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter Dim da As MySqlDataAdapter = New MySqlDataAdapter() Dim cmd As MySqlCommand Dim parm As MySqlParameter ' Create the SelectCommand. cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=@id AND name=@name", conn) cmd.Parameters.Add("@id", MySqlDbType.VarChar, 15) cmd.Parameters.Add("@name", MySqlDbType.VarChar, 15) da.SelectCommand = cmd ' Create the DeleteCommand. cmd = New MySqlCommand("DELETE FROM mytable WHERE id=@id", conn) parm = cmd.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id") parm.SourceVersion = DataRowVersion.Original da.DeleteCommand = cmd Return da End Function` `public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn) { MySqlDataAdapter da = new MySqlDataAdapter(); MySqlCommand cmd; MySqlParameter parm; // Create the SelectCommand. cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=@id AND name=@name", conn); cmd.Parameters.Add("@id", MySqlDbType.VarChar, 15); cmd.Parameters.Add("@name", MySqlDbType.VarChar, 15); da.SelectCommand = cmd; // Create the DeleteCommand. cmd = new MySqlCommand("DELETE FROM mytable WHERE id=@id", conn); parm = cmd.Parameters.Add("@id", MySqlDbType.VarChar, 5, "id"); parm.SourceVersion = DataRowVersion.Original; da.DeleteCommand = cmd; return da; }`
Gets or sets a SQL statement or stored procedure used to insert records into the data set.
A used during to insert records into the database that correspond to new rows in the .

During , if this property is not set and primary key information is present in the , the InsertCommand can be generated automatically if you set the property and use the . Then, any additional commands that you do not set are generated by the MySqlCommandBuilder. This generation logic requires key column information to be present in the DataSet.

When InsertCommand is assigned to a previously created , the MySqlCommand is not cloned. The InsertCommand maintains a reference to the previously created MySqlCommand object.

If execution of this command returns rows, these rows may be added to the DataSet depending on how you set the property of the MySqlCommand object.

The following example creates a and sets the and InsertCommand properties. It assumes you have already created a object. `Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter Dim da As MySqlDataAdapter = New MySqlDataAdapter() Dim cmd As MySqlCommand Dim parm As MySqlParameter ' Create the SelectCommand. cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=@id AND name=@name", conn) cmd.Parameters.Add("@id", MySqlDbType.VarChar, 15) cmd.Parameters.Add("@name", MySqlDbType.VarChar, 15) da.SelectCommand = cmd ' Create the InsertCommand. cmd = New MySqlCommand("INSERT INTO mytable (id,name) VALUES (@id, @name)", conn) cmd.Parameters.Add( "@id", MySqlDbType.VarChar, 15, "id" ) cmd.Parameters.Add( "@name", MySqlDbType.VarChar, 15, "name" ) da.InsertCommand = cmd Return da End Function` `public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn) { MySqlDataAdapter da = new MySqlDataAdapter(); MySqlCommand cmd; MySqlParameter parm; // Create the SelectCommand. cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=@id AND name=@name", conn); cmd.Parameters.Add("@id", MySqlDbType.VarChar, 15); cmd.Parameters.Add("@name", MySqlDbType.VarChar, 15); da.SelectCommand = cmd; // Create the InsertCommand. cmd = new MySqlCommand("INSERT INTO mytable (id,name) VALUES (@id,@name)", conn); cmd.Parameters.Add("@id", MySqlDbType.VarChar, 15, "id" ); cmd.Parameters.Add("@name", MySqlDbType.VarChar, 15, "name" ); da.InsertCommand = cmd; return da; }`
Gets or sets a SQL statement or stored procedure used to updated records in the data source.
A used during to update records in the database with data from the .

During , if this property is not set and primary key information is present in the , the UpdateCommand can be generated automatically if you set the property and use the . Then, any additional commands that you do not set are generated by the MySqlCommandBuilder. This generation logic requires key column information to be present in the DataSet.

When UpdateCommand is assigned to a previously created , the MySqlCommand is not cloned. The UpdateCommand maintains a reference to the previously created MySqlCommand object.

If execution of this command returns rows, these rows may be merged with the DataSet depending on how you set the property of the MySqlCommand object.

The following example creates a and sets the and UpdateCommand properties. It assumes you have already created a object. `Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter Dim da As MySqlDataAdapter = New MySqlDataAdapter() Dim cmd As MySqlCommand Dim parm As MySqlParameter ' Create the SelectCommand. cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=@id AND name=@name", conn) cmd.Parameters.Add("@id", MySqlDbType.VarChar, 15) cmd.Parameters.Add("@name", MySqlDbType.VarChar, 15) da.SelectCommand = cmd ' Create the UpdateCommand. cmd = New MySqlCommand("UPDATE mytable SET id=@id, name=@name WHERE id=@oldId", conn) cmd.Parameters.Add( "@id", MySqlDbType.VarChar, 15, "id" ) cmd.Parameters.Add( "@name", MySqlDbType.VarChar, 15, "name" ) parm = cmd.Parameters.Add("@oldId", MySqlDbType.VarChar, 15, "id") parm.SourceVersion = DataRowVersion.Original da.UpdateCommand = cmd Return da End Function` `public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn) { MySqlDataAdapter da = new MySqlDataAdapter(); MySqlCommand cmd; MySqlParameter parm; // Create the SelectCommand. cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=@id AND name=@name", conn); cmd.Parameters.Add("@id", MySqlDbType.VarChar, 15); cmd.Parameters.Add("@name", MySqlDbType.VarChar, 15); da.SelectCommand = cmd; // Create the UpdateCommand. cmd = new MySqlCommand("UPDATE mytable SET id=@id, name=@name WHERE id=@oldId", conn); cmd.Parameters.Add("@id", MySqlDbType.VarChar, 15, "id" ); cmd.Parameters.Add("@name", MySqlDbType.VarChar, 15, "name" ); parm = cmd.Parameters.Add( "@oldId", MySqlDbType.VarChar, 15, "id" ); parm.SourceVersion = DataRowVersion.Original; da.UpdateCommand = cmd; return da; }`
Gets or sets a SQL statement or stored procedure used to select records in the data source.
A used during to select records from the database for placement in the .

When SelectCommand is assigned to a previously created , the MySqlCommand is not cloned. The SelectCommand maintains a reference to the previously created MySqlCommand object.

If the SelectCommand does not return any rows, no tables are added to the , and no exception is raised.

The following example creates a and sets the and InsertCommand properties. It assumes you have already created a object. `Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter Dim da As MySqlDataAdapter = New MySqlDataAdapter() Dim cmd As MySqlCommand Dim parm As MySqlParameter ' Create the SelectCommand. cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=@id AND name=@name", conn) cmd.Parameters.Add("@id", MySqlDbType.VarChar, 15) cmd.Parameters.Add("@name", MySqlDbType.VarChar, 15) da.SelectCommand = cmd ' Create the InsertCommand. cmd = New MySqlCommand("INSERT INTO mytable (id,name) VALUES (@id, @name)", conn) cmd.Parameters.Add( "@id", MySqlDbType.VarChar, 15, "id" ) cmd.Parameters.Add( "@name", MySqlDbType.VarChar, 15, "name" ) da.InsertCommand = cmd Return da End Function` `public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn) { MySqlDataAdapter da = new MySqlDataAdapter(); MySqlCommand cmd; MySqlParameter parm; // Create the SelectCommand. cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=@id AND name=@name", conn); cmd.Parameters.Add("@id", MySqlDbType.VarChar, 15); cmd.Parameters.Add("@name", MySqlDbType.VarChar, 15); da.SelectCommand = cmd; // Create the InsertCommand. cmd = new MySqlCommand("INSERT INTO mytable (id,name) VALUES (@id,@name)", conn); cmd.Parameters.Add("@id", MySqlDbType.VarChar, 15, "id" ); cmd.Parameters.Add("@name", MySqlDbType.VarChar, 15, "name" ); da.InsertCommand = cmd; return da; }`