Dumping SQL queries made using LINQ to the browser in ASP.NET MVC

MVC has become the new hot thing that is being implemented in different programming languages. There are at least 20 MVC frameworks that I know of. Unlike my previous posts which were about CakePHP. This post is about my experience with ASP.NET MVC 2.

The story begins when I was debugging an application built with ASP.NET MVC 2 and I noticed that the object I am debugging nearly contains all the information in the database. I thought of implementing a nice sql dump of all the queries that are being executed with each request and print it to the page being rendered to for performance review - cheating the idea from CakePHP, I know!

After some searching, I found a great implementation of TextWriter that writes to the Debugger output window. However, lazy as I am, I do not want to switch from the browser window back to Visual studio then maximize the debugger window to see the queries. I just took the code that Kris Vandermotten wrote and made few modifications to it to output the queries to the web page in a div tag. You can set the div to be hidden if it is breaking the page layout.

  1. using System;
  2. using System.Diagnostics;
  3. using System.Globalization;
  4. using System.IO;
  5. using System.Text;
  6.  
  7. namespace Nba.Models.DataRepository
  8. {
  9.     /// <summary>
  10.     /// Implements a <see cref="TextWriter"/> for writing information to the debugger log.
  11.     /// </summary>
  12.     /// <seealso cref="Debugger.Log"/>
  13.     public class SqlDebuggerWriter : TextWriter
  14.     {
  15.         private bool isOpen;
  16.         private static UnicodeEncoding encoding;
  17.         private readonly int level;
  18.         private readonly string category;
  19.         public static string SqlLog;
  20.  
  21.         /// <summary>
  22.         /// Initializes a new instance of the <see cref="DebuggerWriter"/> class.
  23.         /// </summary>
  24.         public SqlDebuggerWriter()
  25.             : this(0, Debugger.DefaultCategory)
  26.         {
  27.         }
  28.  
  29.         /// <summary>
  30.         /// Initializes a new instance of the <see cref="DebuggerWriter"/> class with the specified level and category.
  31.         /// </summary>
  32.         /// <param name="level">A description of the importance of the messages.</param>
  33.         /// <param name="category">The category of the messages.</param>
  34.         public SqlDebuggerWriter(int level, string category)
  35.             : this(level, category, CultureInfo.CurrentCulture)
  36.         {
  37.         }
  38.  
  39.         /// <summary>
  40.         /// Initializes a new instance of the <see cref="DebuggerWriter"/> class with the specified level, category and format provider.
  41.         /// </summary>
  42.         /// <param name="level">A description of the importance of the messages.</param>
  43.         /// <param name="category">The category of the messages.</param>
  44.         /// <param name="formatProvider">An <see cref="IFormatProvider"/> object that controls formatting.</param>
  45.         public SqlDebuggerWriter(int level, string category, IFormatProvider formatProvider)
  46.             : base(formatProvider)
  47.         {
  48.             this.level = level;
  49.             this.category = category;
  50.             this.isOpen = true;
  51.         }
  52.  
  53.         protected override void Dispose(bool disposing)
  54.         {
  55.             isOpen = false;
  56.             base.Dispose(disposing);
  57.         }
  58.  
  59.         public override void Write(char value)
  60.         {
  61.             if (!isOpen)
  62.             {
  63.                 throw new ObjectDisposedException(null);
  64.             }
  65.             SqlLog += value.ToString();
  66.             Debugger.Log(level, category, value.ToString());
  67.         }
  68.  
  69.         public override void Write(string value)
  70.         {
  71.             if (!isOpen)
  72.             {
  73.                 throw new ObjectDisposedException(null);
  74.             }
  75.             if (value != null)
  76.             {
  77.                 SqlLog += value;
  78.                 Debugger.Log(level, category, value);
  79.             }
  80.         }
  81.  
  82.         public override void Write(char[] buffer, int index, int count)
  83.         {
  84.             if (!isOpen)
  85.             {
  86.                 throw new ObjectDisposedException(null);
  87.             }
  88.             if (buffer == null || index < 0 || count < 0 || buffer.Length - index < count)
  89.             {
  90.                 base.Write(buffer, index, count); // delegate throw exception to base class
  91.             }
  92.             SqlLog += new string(buffer, index, count);
  93.             Debugger.Log(level, category, new string(buffer, index, count));
  94.         }
  95.  
  96.         public override Encoding Encoding
  97.         {
  98.             get
  99.             {
  100.                 if (encoding == null)
  101.                 {
  102.                     encoding = new UnicodeEncoding(false, false);
  103.                 }
  104.                 return encoding;
  105.             }
  106.         }
  107.  
  108.         public int Level
  109.         {
  110.             get { return level; }
  111.         }
  112.  
  113.         public string Category
  114.         {
  115.             get { return category; }
  116.         }
  117.     }
  118. }

Then the following lines:

In the model

  1. db.Log = new Nba.Models.DataRepository.SqlDebuggerWriter();

In the controller

  1. ViewData["SqlLog"] = SqlDebuggerWriter.SqlLog;

In the view

  1. <div style="width:800px;display:none" >
  2.          <pre>
  3.                  <%= ViewData["SqlLog"]%>
  4.          </pre>
  5. </div>