如何跟踪Linq-to-sql DataContext上的查询

In the herding code podcast 14 someone mentions that * displayed the queries that were executed during a request at the bottom of the page.

在组合代码播客14中,有人提到*显示在页面底部的请求期间执行的查询。

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?

这听起来对我来说是个好主意。每次页面加载时我都想知道执行什么sql语句,以及DB往返总数的计数。有没有人有这个问题的巧妙解决方案?

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.

您认为可接受的查询数量是多少?我在想,在开发过程中,如果需要超过30个查询来呈现页面,我可能会让我的应用程序抛出异常。

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.

编辑:我想我一定不能清楚地解释我的问题。在HTTP请求期间,Web应用程序可能会执行十几个或更多sql语句。我希望将这些语句附加到页面底部,以及语句数量的计数。

HERE IS MY SOLUTION:

这是我的解决方案:

I created a TextWriter class that the DataContext can write to:

我创建了一个DataContext可以写入的TextWriter类:

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 + "<br/><br/>";
            if (!value.StartsWith("--")) QueryCounter++;
        }

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

In the DataContext's constructor I setup the logger:

在DataContext的构造函数中,我设置了记录器:

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

Finally, I use the Application_OnEndRequest event to add the results to the bottom of the page:

最后,我使用Application_OnEndRequest事件将结果添加到页面底部:

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

4 个解决方案

#1

If you put .ToString() to a var query variable you get the sql. You can laso use this in Debug en VS2008. Debug Visualizer

如果将.ToString()放到var查询变量中,则会得到sql。你可以在Debug en VS2008中使用它。调试Visualizer

ex:

var query = from p in db.Table
            select p;

MessageBox.SHow(query.ToString());

#2

System.IO.StreamWriter httpResponseStreamWriter = 
new StreamWriter(HttpContext.Current.Response.OutputStream);

dataContext.Log = httpResponseStreamWriter;

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.

坚持在你的页面,你会得到SQL倾倒在页面上。显然,我会用一个你可以启用/禁用的方法来包装它。

#3

I have a post on my blog that covers sending to log files, memory, the debug window or multiple writers.

我的博客上有一篇文章,内容涉及发送到日志文件,内存,调试窗口或多个编写器。

#4

From Linq in Action

来自Linq in Action

Microsoft has a Query Visualizer tool that can be downloaded separetly from VS 2008. it is at http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx

Microsoft有一个Query Visualizer工具,可以从VS 2008中逐步下载。它位于http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx

#1