<docs>
<class>
<summary>
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.
</summary>
<remarks>
<para>
The <B>MySQLDataAdapter</B>, serves as a bridge between a <see cref="System.Data.DataSet"/>
and MySQL for retrieving and saving data. The <B>MySQLDataAdapter</B> provides this
bridge by mapping <see cref="DbDataAdapter.Fill"/>, which changes the data in the
<B>DataSet</B> to match the data in the data source, and <see cref="DbDataAdapter.Update"/>,
which changes the data in the data source to match the data in the <B>DataSet</B>,
using the appropriate SQL statements against the data source.
</para>
<para>
When the <B>MySQLDataAdapter</B> fills a <B>DataSet</B>, 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
<see cref="System.Data.MissingSchemaAction"/> property is set to <see cref="System.Data.MissingSchemaAction.AddWithKey"/>.
You may also have the <B>MySQLDataAdapter</B> create the schema of the <B>DataSet</B>,
including primary key information, before filling it with data using <see cref="System.Data.Common.DbDataAdapter.FillSchema"/>.
</para>
<para><B>MySQLDataAdapter</B> is used in conjunction with <see cref="MySqlConnection"/>
and <see cref="MySqlCommand"/> to increase performance when connecting to a MySQL database.
</para>
<para>The <B>MySQLDataAdapter</B> also includes the <see cref="MySqlDataAdapter.SelectCommand"/>,
<see cref="MySqlDataAdapter.InsertCommand"/>, <see cref="MySqlDataAdapter.DeleteCommand"/>,
<see cref="MySqlDataAdapter.UpdateCommand"/>, and <see cref="DataAdapter.TableMappings"/>
properties to facilitate the loading and updating of data.
</para>
<para>When an instance of <B>MySQLDataAdapter</B> is created, the read/write properties
are set to initial values. For a list of these values, see the <B>MySQLDataAdapter</B>
constructor.
</para>
<note>
Please be aware that the <see cref="DataColumn"/> class in .NET 1.0 and 1.1 does not allow columns
with type of UInt16, UInt32, or UInt64 to be autoincrement columns. If you plan to use autoincremement
columns with MySQL, you should consider using signed integer columns.
</note>
</remarks>
<example>
The following example creates a <see cref="MySqlCommand"/> and a <see cref="MySqlConnection"/>.
The <B>MySqlConnection</B> is opened and set as the <see cref="MySqlCommand.Connection"/> for the
<B>MySqlCommand</B>. The example then calls <see cref="MySqlCommand.ExecuteNonQuery"/>, and closes
the connection. To accomplish this, the <B>ExecuteNonQuery</B> is
passed a connection string and a query string that is a SQL INSERT
statement.
<code lang="Visual Basic">
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
</code>
<code lang="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;
}
</code>
</example>
</class>
<Ctor>
<overloads></overloads>
<summary>
Initializes a new instance of the MySqlDataAdapter class.
</summary>
<remarks>
<para>
When an instance of <see cref="MySqlDataAdapter"/> is created,
the following read/write properties are set to the following initial
values.
</para>
<list type="table">
<listheader><term>Properties</term><term>Initial Value</term></listheader>
<item><term><see cref="MissingMappingAction"/></term><term><B>MissingMappingAction.Passthrough</B></term></item>
<item><term><see cref="MissingSchemaAction"/></term><term><B>MissingSchemaAction.Add</B></term></item>
</list>
<para>
You can change the value of any of these properties through a separate call
to the property.
</para>
</remarks>
<example>
The following example creates a <see cref="MySqlDataAdapter"/> and sets some of
its properties.
<code lang="Visual Basic">
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
</code>
<code lang="C#">
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;
}
</code>
</example>
</Ctor>
<Ctor1>
<summary>
Initializes a new instance of the <see cref="MySqlDataAdapter"/> class with
the specified <see cref="MySqlCommand"/> as the <see cref="SelectCommand"/>
property.
</summary>
<param name="selectCommand">
<see cref="MySqlCommand"/> that is a SQL SELECT statement or stored procedure and is set
as the <see cref="SelectCommand"/> property of the <see cref="MySqlDataAdapter"/>.
</param>
<remarks>
<para>
When an instance of <see cref="MySqlDataAdapter"/> is created,
the following read/write properties are set to the following initial
values.
</para>
<list type="table">
<listheader><term>Properties</term><term>Initial Value</term></listheader>
<item><term><see cref="MissingMappingAction"/></term><term><B>MissingMappingAction.Passthrough</B></term></item>
<item><term><see cref="MissingSchemaAction"/></term><term><B>MissingSchemaAction.Add</B></term></item>
</list>
<para>
You can change the value of any of these properties through a separate call
to the property.
</para>
<para>
When <B>SelectCommand</B> (or any of the other command properties) is assigned
to a previously created <see cref="MySqlCommand"/>, the <B>MySqlCommand</B> is not cloned.
The <B>SelectCommand</B> maintains a reference to the previously created <B>MySqlCommand</B>
object.
</para>
</remarks>
<example>
The following example creates a <see cref="MySqlDataAdapter"/> and sets some of
its properties.
<code lang="Visual Basic">
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
</code>
<code lang="C#">
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;
}
</code>
</example>
</Ctor1>
<Ctor2>
<summary>
Initializes a new instance of the <see cref="MySqlDataAdapter"/> class with
a <see cref="SelectCommand"/> and a <see cref="MySqlConnection"/> object.
</summary>
<param name="selectCommandText">
A <b>String</b> that is a SQL SELECT statement or stored procedure to be used by
the <see cref="SelectCommand"/> property of the <see cref="MySqlDataAdapter"/>.
</param>
<param name="connection">A <see cref="MySqlConnection"/> that represents the connection.</param>
<remarks>
<para>
This implementation of the <see cref="MySqlDataAdapter"/> opens and closes a <see cref="MySqlConnection"/>
if it is not already open. This can be useful in a an application that must call the
<see cref="DbDataAdapter.Fill"/> method for two or more <B>MySqlDataAdapter</B> objects.
If the <B>MySqlConnection</B> is already open, you must explicitly call
<see cref="MySqlConnection.Close"/> or <see cref="MySqlConnection.Dispose"/> to close it.
</para>
<para>
When an instance of <see cref="MySqlDataAdapter"/> is created,
the following read/write properties are set to the following initial
values.
</para>
<list type="table">
<listheader><term>Properties</term><term>Initial Value</term></listheader>
<item><term><see cref="MissingMappingAction"/></term><term><B>MissingMappingAction.Passthrough</B></term></item>
<item><term><see cref="MissingSchemaAction"/></term><term><B>MissingSchemaAction.Add</B></term></item>
</list>
<para>
You can change the value of any of these properties through a separate call
to the property.
</para>
</remarks>
<example>
The following example creates a <see cref="MySqlDataAdapter"/> and sets some of
its properties.
<code lang="Visual Basic">
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
</code>
<code lang="C#">
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;
}
</code>
</example>
</Ctor2>
<Ctor3>
<summary>
Initializes a new instance of the <see cref="MySqlDataAdapter"/> class with
a <see cref="SelectCommand"/> and a connection string.
</summary>
<param name="selectCommandText">
A <see cref="string"/> that is a SQL SELECT statement or stored procedure to
be used by the <see cref="SelectCommand"/> property of the <see cref="MySqlDataAdapter"/>.
</param>
<param name="selectConnString">The connection string</param>
<remarks>
<para>
When an instance of <see cref="MySqlDataAdapter"/> is created,
the following read/write properties are set to the following initial
values.
</para>
<list type="table">
<listheader><term>Properties</term><term>Initial Value</term></listheader>
<item><term><see cref="MissingMappingAction"/></term><term><B>MissingMappingAction.Passthrough</B></term></item>
<item><term><see cref="MissingSchemaAction"/></term><term><B>MissingSchemaAction.Add</B></term></item>
</list>
<para>
You can change the value of any of these properties through a separate call
to the property.
</para>
</remarks>
<example>
The following example creates a <see cref="MySqlDataAdapter"/> and sets some of
its properties.
<code lang="Visual Basic">
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
</code>
<code lang="C#">
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;
}
</code>
</example>
</Ctor3>
<DeleteCommand>
<summary>
Gets or sets a SQL statement or stored procedure used to delete records from the data set.
</summary>
<value>
A <see cref="MySqlCommand"/> used during <see cref="System.Data.Common.DataAdapter.Update"/> to delete records in the
database that correspond to deleted rows in the <see cref="DataSet"/>.
</value>
<remarks>
<para>During <see cref="System.Data.Common.DataAdapter.Update"/>, if this property is not set and primary key information
is present in the <see cref="DataSet"/>, the <B>DeleteCommand</B> can be generated
automatically if you set the <see cref="SelectCommand"/> property and use the
<see cref="MySqlCommandBuilder"/>. Then, any additional commands that you do not set are
generated by the <B>MySqlCommandBuilder</B>. This generation logic requires key column
information to be present in the <B>DataSet</B>.
</para>
<para>
When <B>DeleteCommand</B> is assigned to a previously created <see cref="MySqlCommand"/>,
the <B>MySqlCommand</B> is not cloned. The <B>DeleteCommand</B> maintains a reference
to the previously created <B>MySqlCommand</B> object.
</para>
</remarks>
<example>
The following example creates a <see cref="MySqlDataAdapter"/> and sets the
<see cref="SelectCommand"/> and <B>DeleteCommand</B> properties. It assumes you have already
created a <see cref="MySqlConnection"/> object.
<code lang="Visual Basic">
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
</code>
<code lang="C#">
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;
}
</code>
</example>
</DeleteCommand>
<InsertCommand>
<summary>
Gets or sets a SQL statement or stored procedure used to insert records into the data set.
</summary>
<value>
A <see cref="MySqlCommand"/> used during <see cref="System.Data.Common.DataAdapter.Update"/> to insert records into the
database that correspond to new rows in the <see cref="DataSet"/>.
</value>
<remarks>
<para>During <see cref="System.Data.Common.DataAdapter.Update"/>, if this property is not set and primary key information
is present in the <see cref="DataSet"/>, the <B>InsertCommand</B> can be generated
automatically if you set the <see cref="SelectCommand"/> property and use the
<see cref="MySqlCommandBuilder"/>. Then, any additional commands that you do not set are
generated by the <B>MySqlCommandBuilder</B>. This generation logic requires key column
information to be present in the <B>DataSet</B>.
</para>
<para>
When <B>InsertCommand</B> is assigned to a previously created <see cref="MySqlCommand"/>,
the <B>MySqlCommand</B> is not cloned. The <B>InsertCommand</B> maintains a reference
to the previously created <B>MySqlCommand</B> object.
</para>
<note>
If execution of this command returns rows, these rows may be added to the <B>DataSet</B>
depending on how you set the <see cref="MySqlCommand.UpdatedRowSource"/> property of the <B>MySqlCommand</B> object.
</note>
</remarks>
<example>
The following example creates a <see cref="MySqlDataAdapter"/> and sets the
<see cref="SelectCommand"/> and <B>InsertCommand</B> properties. It assumes you have already
created a <see cref="MySqlConnection"/> object.
<code lang="Visual Basic">
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
</code>
<code lang="C#">
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;
}
</code>
</example>
</InsertCommand>
<UpdateCommand>
<summary>
Gets or sets a SQL statement or stored procedure used to updated records in the data source.
</summary>
<value>
A <see cref="MySqlCommand"/> used during <see cref="System.Data.Common.DataAdapter.Update"/> to update records in the
database with data from the <see cref="DataSet"/>.
</value>
<remarks>
<para>During <see cref="System.Data.Common.DataAdapter.Update"/>, if this property is not set and primary key information
is present in the <see cref="DataSet"/>, the <B>UpdateCommand</B> can be generated
automatically if you set the <see cref="SelectCommand"/> property and use the
<see cref="MySqlCommandBuilder"/>. Then, any additional commands that you do not set are
generated by the <B>MySqlCommandBuilder</B>. This generation logic requires key column
information to be present in the <B>DataSet</B>.
</para>
<para>
When <B>UpdateCommand</B> is assigned to a previously created <see cref="MySqlCommand"/>,
the <B>MySqlCommand</B> is not cloned. The <B>UpdateCommand</B> maintains a reference
to the previously created <B>MySqlCommand</B> object.
</para>
<note>
If execution of this command returns rows, these rows may be merged with the DataSet
depending on how you set the <see cref="MySqlCommand.UpdatedRowSource"/> property of the <B>MySqlCommand</B> object.
</note>
</remarks>
<example>
The following example creates a <see cref="MySqlDataAdapter"/> and sets the
<see cref="SelectCommand"/> and <B>UpdateCommand</B> properties. It assumes you have already
created a <see cref="MySqlConnection"/> object.
<code lang="Visual Basic">
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
</code>
<code lang="C#">
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;
}
</code>
</example>
</UpdateCommand>
<SelectCommand>
<summary>
Gets or sets a SQL statement or stored procedure used to select records in the data source.
</summary>
<value>
A <see cref="MySqlCommand"/> used during <see cref="System.Data.Common.DbDataAdapter.Fill"/> to select records from the
database for placement in the <see cref="DataSet"/>.
</value>
<remarks>
<para>When <B>SelectCommand</B> is assigned to a previously created <see cref="MySqlCommand"/>,
the <B>MySqlCommand</B> is not cloned. The <B>SelectCommand</B> maintains a reference to the
previously created <B>MySqlCommand</B> object.
</para>
<para>
If the <B>SelectCommand</B> does not return any rows, no tables are added to the
<see cref="DataSet"/>, and no exception is raised.
</para>
</remarks>
<example>
The following example creates a <see cref="MySqlDataAdapter"/> and sets the
<see cref="SelectCommand"/> and <B>InsertCommand</B> properties. It assumes you have already
created a <see cref="MySqlConnection"/> object.
<code lang="Visual Basic">
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
</code>
<code lang="C#">
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;
}
</code>
</example>
</SelectCommand>
</docs>
Submit an article or tip