Resulting CommandText when using Parameters

[bberger]

Hi folks,

I hope you can help me with my little question I have: I want to be able to log the resulting command text in certain cases when using Parameters in my MySQL query.

Let's say I use something like:

Code: Select all

c.CommandText = "INSERT (field1,field2) VALUES (:p1. :p2) INTO mytable";
c.Parameters.Add(":p1", DateTime.Now);
c.Parameters.Add(":p2", someObject.Foobar);

I now want to get the resulting query to write it into my logs if it fails like:
Query Failed: INSERT (field1, field2) VALUES (2013-10-24, 01230) INTO mytable

Is this possible? If so: how?

Thanks in advance,
Bernhard

[Pinturiccio]

You can use the MySqlMonitor object. It has the TraceEvent event which allows logging the required command. For this you should catch such events in the event handler, that have MonitorEventArgs.EventType with the MonitorEventType.Error value. MySqlMonitor does not have the functionality of displaying the query with parameter values instead of their names. You will have to implement such code yourself.

This is an approximate code implementing this feature (except for the query text containing parameter values):

Code: Select all

static void function()
{
	MySqlMonitor mon = new MySqlMonitor();
	mon.IsActive = true;
	mon.TraceEvent += new Devart.Common.MonitorEventHandler(mon_TraceEvent);
	MySqlConnection conn = new MySqlConnection("your connection string");
	conn.Open();
	MySqlCommand comm = new MySqlCommand("INSERT (field1,field2) VALUES (:p1. :p2) INTO mytable", conn);
	comm.Parameters.Add(":p1", DateTime.Now);
	comm.Parameters.Add(":p2", someObject.Foobar);
	try
	{
	    comm.ExecuteNonQuery();
	}
	catch (MySqlException ex){}
	conn.Close();
}

static void mon_TraceEvent(object sender, Devart.Common.MonitorEventArgs e)
{

	if (e.EventType == Devart.Common.MonitorEventType.Error && (sender.GetType() == typeof(MySqlCommand)))
	{
	    //Your code for query text processing and logging
	}
}