Intercepting Database Query Commands

Question

5.00/5 (3 votes)

See more:

Hi all,

I have a Microsoft SQL Server database and I am using LINQ to SQL to interact with it in my ASP.Net C# 4.0 Website. What I would like to do is have a method that before a query is submitted to the database, grabs the command that will be sent and allows me to look at it. Basically, I am trying to see how efficient my commands/requests are so that I can make more bigger requests rather than lots of small ones. I realise this can be done using Microsoft SQL Server tools but I don't actually have access the the database server itself, only debugging tools for my code.

I have tried overriding the SubmitChanges method of my DataContext to see if there was any way I could get at the outgoing commands from within that but I can't seem to find one. I also looked at the GetCommand method, but I don't want to have to go through all my 50,000 lines of code putting GetCommand methods on every LINQ statement in site... Furthermore, these would not show me the actual outgoing commands as they would include cached objects.

Is what I'm trying to do possible? Can anyone offer any insight into how to do it?

Thanks very much,
Ed

Posted 15-Feb-12 0:47am

Ed Nutting

Add a Solution

Comments

Manfred Rudolf Bihy 15-Feb-12 7:01am

Interesting question! 5+
I hope someone will come up with viable solution. :)

Ed Nutting 15-Feb-12 7:04am

Thanks :)

BobJanova 15-Feb-12 7:12am

I'm not sure this is possible, but I will also watch this question with interest.

AmitGajjar 15-Feb-12 7:13am

so, you can't use SQL Profiler nor you can not use DataContext.Log to check sql query , am i right ? or do you have one single class for executing DataContext query?

Ed Nutting 15-Feb-12 7:15am

I can't use the SQL Profiler unfortunately but what is this DataContext.Log you mention? This may be what I have been looking for! As a web application it obviously has several (if not lots) of database contexts/connections but if there is built in logging in the DataContext I might be able to use that :)

AmitGajjar 15-Feb-12 7:19am

var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var employees = from emp in ctx.Employees
where emp.LastName.StartsWith("D")
select emp;
dg.ItemsSource = employees;
MessageBox.Show(sw.GetStringBuilder().ToString());

Above is the sample code to display executed query on DataContext. hopefully if you have single class to execute Datacontext you can achieve this.

Ed Nutting 15-Feb-12 7:21am

Yeah I'm just writing code to see if this actually works for me :) Will need to create a new aspx page to work as the console but this should work. Thank you so much! I will post a proper solution if it works :)

Ed Nutting 15-Feb-12 7:25am

This solution works! Thank you so much! I will now post a solution properly for anyone who happens to want to know.

BobJanova 15-Feb-12 9:58am

5 for this if only you'd posted it as a solution :P. Interesting tip, thanks in advance for next time I might need to use this!

AmitGajjar 15-Feb-12 7:22am

also check SQL profiler using .net. it's .net application. do this work for you ?

Ed Nutting 15-Feb-12 7:24am

Unfortunately not as i don't have the SQL Profiler tools installed on my machine but thatnks for the suggestion.

AmitGajjar 15-Feb-12 7:27am

this one is another tool that you need to install.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ed Nutting · Accepted Answer · 2012-02-15T01:30:00

Thanks greatly to amitgajjar for this solution.

This solution use the in-built DataContext.Log property. Since in my situation I am running this as a website and so multiple DataContexts may exist, I used a static method to create all my DataContexts and assign to their Log property the static StringWriter I wanted to use for the log. The code looked as follows:

C#

public static class DatabaseHandler
{
    public static System.IO.StringWriter LogStream = new System.IO.StringWriter();

    internal static DatabaseDataContext CreateDatabaseContext()
    {
        DatabaseDataContext TheContext = new DatabaseDataContext();

        TheContext.Log = LogStream;

        return TheContext;
    }
}

And then I had an aspx page that simply set it's ouput to type "text" and did DatabaseHandler.LogStream.GetStringBuilder().ToString() to get the log.

This is a very efficient and neat solution, thanks so much again to amitgajjar.

Hope this helps anyone else in need,
Ed