Connector/Net Programming
1907
This feature exists with Connector/Net versions 6.4 and above.
Table caching is a feature that can be used to cache slow-changing datasets on the client side. This is
useful for applications that are designed to use readers, but still want to minimize trips to the server for
slow-changing tables.
This feature is transparent to the application, and is disabled by default.
Configuration
• To enable table caching, add
'table cache = true'
to the connection string.
• Optionally, specify the
'Default Table Cache Age'
connection string option, which represents
the number of seconds a table is cached before the cached data is discarded. The default value is
60
.
• You can turn caching on and off and set caching options at runtime, on a per-command basis.
20.2.5.8. Using the Connector/Net with Prepared Statements
As of MySQL 4.1, it is possible to use prepared statements with 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.
20.2.5.8.1. Preparing Statements in 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.
Visual Basic 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()
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
