Resulting CommandText when using Parameters

Resulting CommandText when using Parameters

Postby bberger » Wed 24 Oct 2012 09:44

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
bberger
 
Posts: 1
Joined: Wed 24 Oct 2012 09:39

Re: Resulting CommandText when using Parameters

Postby Pinturiccio » Tue 30 Oct 2012 12:56

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
   }
}
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for MySQL