<p>In the <a href="http://herdingcode.com/" rel="nofollow noreferrer">herding code</a> podcast 14 someone mentions that stackoverflow displayed the queries that were executed during a request at the bottom of the page. </p>

<p>It sounds like an excellent idea to me. Every time a page loads I want to know what sql statements are executed and also a count of the total number of DB round trips. 
Does anyone have a neat solution to this problem? </p>

<p>What do you think is an acceptable number of queries? I was thinking that during development I might have my application throw an exception if more than 30 queries are required to render a page.</p>

<p>EDIT: I think I must not have explained my question clearly. During a HTTP request a web application might execute a dozen or more sql statements. I want to have those statements appended to the bottom of the page, along with a count of the number of statements.</p>

<p>HERE IS MY SOLUTION:</p>

<p>I created a TextWriter class that the DataContext can write to:</p>

<pre><code>public class Logger : StreamWriter
    {
        public string Buffer { get; private set; }
        public int QueryCounter { get; private set; }

        public Logger() : base(new MemoryStream())
        {}

        public override void Write(string value)
        {
            Buffer += value + "&lt;br/&gt;&lt;br/&gt;";
            if (!value.StartsWith("--")) QueryCounter++;
        }

        public override void WriteLine(string value)
        {
            Buffer += value + "&lt;br/&gt;&lt;br/&gt;";
            if (!value.StartsWith("--")) QueryCounter++;
        }
    }
</code></pre>

<p>In the DataContext's constructor I setup the logger:</p>

<pre><code>public HeraldDBDataContext()
        : base(ConfigurationManager.ConnectionStrings["Herald"].ConnectionString, mappingSource)
    {
        Log = new Logger();
    }
</code></pre>

<p>Finally, I use the <code>Application_OnEndRequest</code> event to add the results to the bottom of the page:</p>

<pre><code>protected void Application_OnEndRequest(Object sender, EventArgs e)
    {
        Logger logger = DataContextFactory.Context.Log as Logger;
        Response.Write("Query count : " + logger.QueryCounter);
        Response.Write("&lt;br/&gt;&lt;br/&gt;");
        Response.Write(logger.Buffer);
    }
</code></pre>

[{'answer_id': 29319, 'author': 'Jedi Master Spooky', 'author_id': 1154, 'author_profile': 'https://Stackoverflow.com/users/1154', 'pm_score': 2, 'selected': False, 'text': '<p>If you put .ToString() to a var query variable you get the sql.  You can laso use this in Debug en VS2008. <a href="http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx" rel="nofollow noreferrer">Debug Visualizer</a></p>\n\n<p>ex:</p>\n\n<pre><code>var query = from p in db.Table\n            select p;\n\nMessageBox.SHow(query.ToString());\n</code></pre>\n'}, {'answer_id': 29380, 'author': 'David Basarab', 'author_id': 2469, 'author_profile': 'https://Stackoverflow.com/users/2469', 'pm_score': 0, 'selected': False, 'text': '<p>From Linq in Action</p>\n\n<blockquote>\n  <p>Microsoft has a Query Visualizer tool that can be downloaded separetly from VS 2008.  it is at <a href="http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx" rel="nofollow noreferrer">http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx</a></p>\n</blockquote>\n'}, {'answer_id': 29453, 'author': 'FlySwat', 'author_id': 1965, 'author_profile': 'https://Stackoverflow.com/users/1965', 'pm_score': 2, 'selected': False, 'text': "<pre><code>System.IO.StreamWriter httpResponseStreamWriter = \nnew StreamWriter(HttpContext.Current.Response.OutputStream);\n\ndataContext.Log = httpResponseStreamWriter;\n</code></pre>\n\n<p>Stick that in your page and you'll get the SQL dumped out on the page. Obviously, I'd wrap that in a little method that you can enable/disable.</p>\n"}, {'answer_id': 68271, 'author': 'DamienG', 'author_id': 5720, 'author_profile': 'https://Stackoverflow.com/users/5720', 'pm_score': 1, 'selected': False, 'text': '<p>I have a post on my blog that covers <a href="http://damieng.com/blog/2008/07/30/linq-to-sql-log-to-debug-window-file-memory-or-multiple-writers" rel="nofollow noreferrer">sending to log files, memory, the debug window or multiple writers</a>.</p>\n'}]
['https://Stackoverflow.com/questions/29308', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2785/']