Click here to Skip to main content
Rate this: bad
good
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,
Ed
Posted 15-Feb-12 0:47am
Ed Nutting34.7K
Comments
Manfred R. Bihy at 15-Feb-12 7:01am
   
Interesting question! 5+
I hope someone will come up with viable solution. :)
Edward Nutting at 15-Feb-12 7:04am
   
Thanks :)
BobJanova at 15-Feb-12 7:12am
   
I'm not sure this is possible, but I will also watch this question with interest.
amitgajjar at 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 at 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 at 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.
Edward Nutting at 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 at 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 at 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 at 15-Feb-12 7:22am
   
also check SQL profiler using .net. it's .net application. do this work for you ?
Edward Nutting at 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 at 15-Feb-12 7:27am
   
this one is another tool that you need to install.

1 solution

Rate this: bad
good
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,
Ed
  Permalink  
Comments
amitgajjar at 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)



Advertise | Privacy | Mobile
Web01 | 2.8.140926.1 | Last Updated 15 Feb 2012
Copyright © CodeProject, 1999-2014
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