[+/-]
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 System.Data.DataSet
and MySQL for
retrieving and saving data. The
MySQLDataAdapter
provides this bridge by
mapping DbDataAdapter.Fill
, which changes the
data in the DataSet
to match the data in the
data source, and DbDataAdapter.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
System.Data.MissingSchemaAction
property is
set to
System.Data.MissingSchemaAction.AddWithKey
.
You may also have the MySQLDataAdapter
create
the schema of the DataSet
, including primary
key information, before filling it with data using
System.Data.Common.DbDataAdapter.FillSchema
.
MySQLDataAdapter
is used in conjunction with
MySqlConnection
and
MySqlCommand
to increase performance when
connecting to a MySQL database.
The MySQLDataAdapter
also includes the
MySqlDataAdapter.SelectCommand
,
MySqlDataAdapter.InsertCommand
,
MySqlDataAdapter.DeleteCommand
,
MySqlDataAdapter.UpdateCommand
, and
DataAdapter.TableMappings
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.
Note.
Please be aware that the 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.
Examples
The following example creates a MySqlCommand
and a MySqlConnection
. The
MySqlConnection
is opened and set as the
MySqlCommand.Connection
for the
MySqlCommand
. The example then calls
MySqlCommand.ExecuteNonQuery
, and closes the
connection. To accomplish this, the
ExecuteNonQuery
is passed a connection string
and a query string that is a SQL INSERT statement.
Visual Basic example:
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
C# example:
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; }
Overload methods for MySqlDataAdapter
Initializes a new instance of the MySqlDataAdapter class.
When an instance of MySqlDataAdapter
is
created, the following read/write properties are set to the
following initial values.
Properties | Initial Value |
MissingMappingAction
|
MissingMappingAction.Passthrough
|
MissingSchemaAction
|
MissingSchemaAction.Add
|
You can change the value of any of these properties through a separate call to the property.
Examples
The following example creates a
MySqlDataAdapter
and sets some of its
properties.
Visual Basic example:
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
C# example:
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
MySqlDataAdapter
class with the specified
MySqlCommand
as the
SelectCommand
property.
Parameters:
MySqlCommand
that is a SQL
SELECT
statement or stored procedure and is
set as the SelectCommand
property of the
MySqlDataAdapter
.
When an instance of MySqlDataAdapter
is
created, the following read/write properties are set to the
following initial values.
Properties | Initial Value |
MissingMappingAction
|
MissingMappingAction.Passthrough
|
MissingSchemaAction
|
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
MySqlCommand
, the
MySqlCommand
is not cloned. The
SelectCommand
maintains a reference to the
previously created MySqlCommand
object.
Examples
The following example creates a
MySqlDataAdapter
and sets some of its
properties.
Visual Basic example:
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
C# example:
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
MySqlDataAdapter
class with a
SelectCommand
and a
MySqlConnection
object.
Parameters: A
String
that is a SQL
SELECT
statement or stored procedure to be
used by the SelectCommand
property of the
MySqlDataAdapter
.
Parameters: A
MySqlConnection
that represents the
connection.
This implementation of the MySqlDataAdapter
opens and closes a MySqlConnection
if it is
not already open. This can be useful in a an application that
must call the DbDataAdapter.Fill
method for
two or more MySqlDataAdapter
objects. If
the MySqlConnection
is already open, you
must explicitly call MySqlConnection.Close
or MySqlConnection.Dispose
to close it.
When an instance of MySqlDataAdapter
is
created, the following read/write properties are set to the
following initial values.
Properties | Initial Value |
MissingMappingAction
|
MissingMappingAction.Passthrough
|
MissingSchemaAction
|
MissingSchemaAction.Add
|
You can change the value of any of these properties through a separate call to the property.
Examples
The following example creates a
MySqlDataAdapter
and sets some of its
properties.
Visual Basic example:
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
C# example:
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
MySqlDataAdapter
class with a
SelectCommand
and a connection string.
Parameters: A
string
that is a SQL
SELECT
statement or stored procedure to be
used by the SelectCommand
property of the
MySqlDataAdapter
.
Parameters: The connection string
When an instance of MySqlDataAdapter
is
created, the following read/write properties are set to the
following initial values.
Properties | Initial Value |
MissingMappingAction
|
MissingMappingAction.Passthrough
|
MissingSchemaAction
|
MissingSchemaAction.Add
|
You can change the value of any of these properties through a separate call to the property.
Examples
The following example creates a
MySqlDataAdapter
and sets some of its
properties.
Visual Basic example:
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
C# example:
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.
Value: A
MySqlCommand
used during
System.Data.Common.DataAdapter.Update
to
delete records in the database that correspond to deleted rows
in the DataSet
.
During
System.Data.Common.DataAdapter.Update
, if
this property is not set and primary key information is
present in the DataSet
, the
DeleteCommand
can be generated
automatically if you set the SelectCommand
property and use the MySqlCommandBuilder
.
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 MySqlCommand
, the
MySqlCommand
is not cloned. The
DeleteCommand
maintains a reference to the
previously created MySqlCommand
object.
Examples
The following example creates a
MySqlDataAdapter
and sets the
SelectCommand
and
DeleteCommand
properties. It assumes you
have already created a MySqlConnection
object.
Visual Basic example:
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
C# example:
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.
Value: A
MySqlCommand
used during
System.Data.Common.DataAdapter.Update
to
insert records into the database that correspond to new rows
in the DataSet
.
During
System.Data.Common.DataAdapter.Update
, if
this property is not set and primary key information is
present in the DataSet
, the
InsertCommand
can be generated
automatically if you set the SelectCommand
property and use the MySqlCommandBuilder
.
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 MySqlCommand
, the
MySqlCommand
is not cloned. The
InsertCommand
maintains a reference to the
previously created MySqlCommand
object.
Note.
If execution of this command returns rows, these rows may be
added to the DataSet
depending on how you
set the MySqlCommand.UpdatedRowSource
property of the MySqlCommand
object.
Examples
The following example creates a
MySqlDataAdapter
and sets the
SelectCommand
and
InsertCommand
properties. It assumes you
have already created a MySqlConnection
object.
Visual Basic example:
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
C# example:
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.
Value: A
MySqlCommand
used during
System.Data.Common.DataAdapter.Update
to
update records in the database with data from the
DataSet
.
During
System.Data.Common.DataAdapter.Update
, if
this property is not set and primary key information is
present in the DataSet
, the
UpdateCommand
can be generated
automatically if you set the SelectCommand
property and use the MySqlCommandBuilder
.
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 MySqlCommand
, the
MySqlCommand
is not cloned. The
UpdateCommand
maintains a reference to the
previously created MySqlCommand
object.
Note.
If execution of this command returns rows, these rows may be
merged with the DataSet depending on how you set the
MySqlCommand.UpdatedRowSource
property of
the MySqlCommand
object.
Examples
The following example creates a
MySqlDataAdapter
and sets the
SelectCommand
and
UpdateCommand
properties. It assumes you
have already created a MySqlConnection
object.
Visual Basic example:
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
C# example:
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.
Value: A
MySqlCommand
used during
System.Data.Common.DbDataAdapter.Fill
to
select records from the database for placement in the
DataSet
.
When SelectCommand
is assigned to a
previously created MySqlCommand
, 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 DataSet
,
and no exception is raised.
Examples
The following example creates a
MySqlDataAdapter
and sets the
SelectCommand
and
InsertCommand
properties. It assumes you
have already created a MySqlConnection
object.
Visual Basic example:
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
C# example:
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; }
É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.