Prepared statements can provide significant performance improvements on queries that are executed more than one time. Prepared execution is faster than direct execution for statements executed more than once, primarily because the query is parsed only one time. In the case of direct execution, the query is parsed every time it is executed. In addition, prepared execution 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, with server-side prepared statements enabled, it uses a binary protocol that makes data transfer between client and server more efficient.

To prepare a statement, use the following sequence of steps:

For subsequent executions, you need only modify the values of the parameters and call the execute method again, there is no need to set theCommandTextproperty or redefine the parameters.

C# Code Example

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.AddWithValue("@number", 1); cmd.Parameters.AddWithValue("@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); }

Visual Basic Code Example

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.AddWithValue("@number", 1) cmd.Parameters.AddWithValue("@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