Logging parameterized SQL query with parameters included

Question

I have a couple of functions that take a SQL query (parameters already added) and run it against the database. When the SQL query fails, I'd like to log the complete query, parameters included, in order to see exactly what caused the failure. query.ToString() returns IBM.Data.Informix.IfxCommand, so currently I'm just capturing query.CommandText, but if the parameters are what caused the issue this doesn't tell me exactly what I'm dealing with.

Here's one of the query functions I'm using:

public DataTable CallDtQuery(IfxCommand query)
{
  DataTable dt = new DataTable();
  using (IBM.Data.Informix.IfxConnection conn = new IfxConnection(sqlConnection))
  {
    try
    {
      IBM.Data.Informix.IfxDataAdapter adapter = new IfxDataAdapter();
      query.Connection = conn;
      conn.Open();
      adapter.SelectCommand = new IfxCommand("SET ISOLATION TO DIRTY READ", conn);
      adapter.SelectCommand.ExecuteNonQuery(); //Tells the program to wait in case of a lock.     
      adapter.SelectCommand = query;
      adapter.Fill(dt);
      conn.Close();
      adapter.Dispose();
      }
    catch (IBM.Data.Informix.IfxException ex)
    {
      LogError(ex, query.CommandText);
      SendErrorEmail(ex, query.CommandText);
      DisplayError();
    }
  }
  return dt;
}

Here's the logging function:

private void LogError(IfxException ex, string query)
{ //Logs the error.
  string filename = HttpContext.Current.Server.MapPath("~") + "/Logs/sqlErrors.txt";
  System.IO.FileStream fs = new System.IO.FileStream(filename, System.IO.FileMode.Append);
  System.IO.StreamWriter sw = new System.IO.StreamWriter(fs);

  sw.WriteLine("=======BEGIN ERROR LOG=======");
  sw.WriteLine(DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString());
  sw.WriteLine("Query = " + query);
  sw.WriteLine("User = " + HttpContext.Current.Session["UserID"]);
  sw.WriteLine("Error Message = " + ex.Message);
  sw.WriteLine("Message Source:");
  sw.WriteLine(ex.Source);
  sw.WriteLine("=============================");
  sw.WriteLine("Message Target:");
  sw.WriteLine(ex.TargetSite);
  sw.WriteLine("=============================");
  sw.WriteLine("Stack Trace:");
  sw.WriteLine(ex.StackTrace);
  sw.WriteLine("========END ERROR LOG========");
  sw.WriteLine("");

  sw.Close();
  fs.Close();
}

Is there a way to pass the entire string, parameters included, for logging as I have here? The only approach I've figured out that should work is to pass the query to the logging function and build a for loop to log each parameter as a separate item. As some of these queries have a number of parameters, and as I'd not be getting the complete query in one easy string, that's not really the most ideal solution.

Rubens Farias · Accepted Answer · 2013-06-05 16:20:02Z

4

What about:

// ...
catch (IBM.Data.Informix.IfxException ex)
{
    LogError(ex, query);  // NOTE
    SendErrorEmail(ex, query.CommandText);
    DisplayError();
}

And create an overload like this:

private void LogError(IfxException ex, IfxCommand query)
{
    StringBuilder sb = new StringBuilder();
    sb.Append(String.Format("{0}\n", query.CommandText));
    foreach (IDataParameter parameter in query.Parameters)
        sb.Append(String.Format("\t{0} = {1}\n",
            parameter.ParameterName, parameter.Value));

    LogError(ex, sb.ToString());
}

answered Jun 5, 2013 at 16:20

Rubens Farias

58k8 gold badges136 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ben Over a year ago

That works, though I changed it up a bit. I added the changes as an answer so you can see what I did. Thanks!

Latheesan Over a year ago

I ran into a scenario where I had to log all the query and it's values used and came across your answer. Nice one!! does exactly what I want: i.imgur.com/Xqgl3et.png Thanks Rubens :)

Ben · Accepted Answer · 2013-06-05 17:12:34Z

Rubens:

Since I want to pass the query both to the LogError and SendErrorEmail functions, I made a unique function for this approach. I also tweaked your version to automatically replace the parameter spots (for example, "cmt_slmno = ?" in the query) instead of making a list of parameters. Here's the result:

private string RecreateQuery(IfxCommand query)
{
  StringBuilder sb = new StringBuilder();
  sb.Append(query.CommandText);
  foreach (IfxParameter parameter in query.Parameters)
    sb.Replace(" ? ", string.Format(" {0} ", parameter.Value.ToString()));
  return sb.ToString();
}

And the correspondingly adjusted catch statement:

catch (IBM.Data.Informix.IfxException ex)
{
  string errorQuery = RecreateQuery(query);
  LogError(ex, errorQuery);
  SendErrorEmail(ex, errorQuery);
  DisplayError();
}

This approach has the potential problem of a rogue ? being replaced in the text, for example if the statement has a questionmark in a string it's trying to set. That should be very rare with the data I'm using in this application, and surrounding the question mark with spaces as above should cover all but the extremely rare, oddly-formatted string a user puts in.

Collectives™ on Stack Overflow

Logging parameterized SQL query with parameters included

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related