Click here to Skip to main content
12,949,601 members (67,206 online)
Rate this:
Please Sign up or sign in to vote.
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,
Posted 15-Feb-12 0:47am
Ed Nutting39.2K
Manfred R. Bihy 15-Feb-12 7:01am
Interesting question! 5+
I hope someone will come up with viable solution. :)
Edward 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?
Edward 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;

Above is the sample code to display executed query on DataContext. hopefully if you have single class to execute Datacontext you can achieve this.
Edward 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 :)
Edward 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 ?
Edward 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

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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:

    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,
amitgajjar 15-Feb-12 7:43am
Thanks Edward.

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

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 5,489
CHill60 3,380
Maciej Los 2,913
Jochen Arndt 1,935
ppolymorphe 1,820

Advertise | Privacy | Mobile
Web01 | 2.8.170524.1 | Last Updated 15 Feb 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100