As of MySQL 4.1, it is possible to use prepared statements
with MySQL Connector/NET. Use of prepared statements can provide
significant performance improvements on queries that are
executed more than once.
Prepared execution is faster than direct execution for
statements executed more than once, primarily because the
query is parsed only once. In the case of direct execution,
the query is parsed every time it is executed. Prepared
execution also can provide a reduction of network traffic
because for each execution of the prepared statement, it is
necessary only to send the data for the parameters.
Another advantage of prepared statements is that it uses a
binary protocol that makes data transfer between client and
server more efficient.
26.2.4.3.2. Preparing Statements in MySQL Connector/NET
To prepare a statement, create a command object and set the
.CommandText property to your query.
After entering your statement, call the
.Prepare method of the
MySqlCommand object. After the statement is
prepared, add parameters for each of the dynamic elements in
the query.
After you enter your query and enter parameters, execute the
statement using the .ExecuteNonQuery(),
.ExecuteScalar(), or
.ExecuteReader methods.
For subsequent executions, you need only modify the values of
the parameters and call the execute method again, there is no
need to set the .CommandText property or
redefine the parameters.
[VB]
Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
conn.ConnectionString = strConnection
Try
conn.Open()
cmd.Connection = conn
cmd.CommandText = "INSERT INTO myTable VALUES(NULL, ?number, ?text)"
cmd.Prepare()
cmd.Parameters.Add("?number", 1)
cmd.Parameters.Add("?text", "One")
For i = 1 To 1000
cmd.Parameters("?number").Value = i
cmd.Parameters("?text").Value = "A string value"
cmd.ExecuteNonQuery()
Next
Catch ex As MySqlException
MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
[C#]
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
conn.ConnectionString = strConnection;
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO myTable VALUES(NULL, ?number, ?text)";
cmd.Prepare();
cmd.Parameters.Add("?number", 1);
cmd.Parameters.Add("?text", "One");
for (int i=1; i <= 1000; i++)
{
cmd.Parameters["?number"].Value = i;
cmd.Parameters["?text"].Value = "A string value";
cmd.ExecuteNonQuery();
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
