Figure 1. Source Location
In a prior post ( Trace SQL From Database to Source Code ), I showed how to enable SQL trace capabilities for java/MySQL application to trace SQL statements from the database to the exact line of code from which the statement was executed (see Figure 1). In this post, I’ll enable SQL tracing in the sample C# application, which is included with the MySQL Connector/NET (MySQL’s ADO.NET provider ) install.
The following instructions assume that the MySQL Enterprise Agent and Monitor is already installed. The Monitor is available for support customers at http://customer.mysql.com or a trial is available at http://www.mysql.com/trials .
Step #1. Download and install the Connector/NET and the MySQL Enterprise Plugin for Connector/NET
- Connector/NET 6.2.3 or newer version. The assembly is MySQL.Data.dll Select the option to install the C# samples.
- MySQL Enterprise Plugin for Connnector/NET ( http://customer.mysql.com or http://www.mysql.com/trials ). The assembly is MySQL.MonitorPlugin.dll
Step #2. Copy the plugin, MySQL.MonitorPlugin.dll to your application directory. In my case, I’m building and debugging the sample application using Microsoft Visual C# 2008 Express Edition. The MySQL.MonitorPlugin.dll needs to reside in the same directly as the application’s executable. Copy the dll to C:\Program Files\MySQL\MySQL Connector Net 6.2.3\Samples\Table Editor\cs\bin\Debug
Step #3. Open the sample project TableEditor.csproj ( C:\Program Files\MySQL\MySQL Connector Net 6.2.3\Samples\Table Editor\cs\TableEditor.csproj ) in Microsoft Visual Studio and add an App.config file to the project. For this exercise, I’m using Microsoft Visual C# 2008 Express Edition.
To add the App.config file, select Solution Explorer on the View menu, right-click on TableEditor, the project name. Point to Add, New Item, and choose Application Configuration File.
In Solution Explorer, double-click App.config to open the file. Replace the contents of the file with the XML below. Note: replace the items in brackets [ ] with the appropriate information for your server. (Omit the brackets) Likewise, the UserID and Password should match the credentials that the agent uses to authenticate to the Monitor.
Copy and paste the following into App.config:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<system.diagnostics>
<sources>
<source name="mysql" switchName="SourceSwitch" switchType="System.Diagnostics.SourceSwitch">
<listeners>
<add name="EMTrace" type="MySql.EMTrace.EMTraceListener, MySql.MonitorPlugin"
initializeData=""
Host="[http://yourServer.com:18080]"
PostInterval="60"
UserId="[agent]"
Password="[mysql]"/>
</listeners>
</source>
</sources>
<switches>
<!-- You can set the level at which tracing is to occur -->
<add name="SourceSwitch" value="All"/>
</switches>
</system.diagnostics>
</configuration>
[Note: if you're following the readme or documentation, you'll notice that I omitted the <system.data> section. I believe this to be an error in the documentation. In my testing, it never works with the <system.data> entry.]
Step #4. Modify the connection string to include “logging = true”
Here’s the full connection string that I used while running the sample application:
string connStr = String.Format(“server={0};user id={1}; password={2}; database=mysql; port=3307; pooling=false; logging=true”,server.Text, userid.Text, password.Text );
Step #5. Compile the application ( Debugging | Start Debugging (F5) ). On Form1, enter the appropriate Server, User ID and Password for the MySQL Server and toggle the databases and tables. Watch the Output window, select Output from the View menu in the IDE, and you should start seeing SQL debug information, which will be passed to the MySQL Enterprise Monitor. In the App.config, the PostInterval is set to 30, meaning that it will take 30 seconds before the data is sent to the monitor.
If you’re a .NET developer, please give it a try. The query analyzer provides a great view into your application and the database.
