Sending the output from Linq to SQL to a Log file

I've recently started using LinqToSql for some of newer projects. The reason its been such a long time before I've started using LinqToSql is because all our websites get hosted on WebCentral. WebCentral has only offered .NET 3.5 in the last couple of months. We have a policy on not using new technologies until they are offered as an option on our hosting partner.

Anyway... as I said, I've just started using LinqToSql. Recently I was trying to save some data to the database, and getting a very strange error returned. For me to debug the error I needed to know what the query was that was being run on my SQL box. A quick bit of googling led me to an article by Kris Vandermotten on how to send LinqToSql log output to the debug window in Visual Studio. From here I made a few quick changes, and got the output going to a Logger.

Setting up my DataContext

I use a singleton object in my business logic layer to control all access to my DataContext object. This means that all properties (for example logging) are maintained in one place, and I don't have lots of calls to "new DataContext()" throughout my ASP.NET code.

public class DataProvider
 {
     private NorthwindDataContext _context = null;
     public NorthwindDataContext DataContext
     {
         get
         {
             if (_context == null)
             {
                 _context = new NorthwindDataContext (System.Configuration.ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString);

                 _context.Log = new NLogTextWriter();
             }

             return _context;
         }
     }


     public static DataProvider Instance
     {
         get
         {
             if (System.Web.HttpContext.Current.Items["NorthwindDataProvider"] == null)
             {
                 DataProvider dp = new DataProvider();
                 System.Web.HttpContext.Current.Items.Add("NorthwindDataProvider", dp);
             }

             return System.Web.HttpContext.Current.Items["NorthwindDataProvider"] as DataProvider;
         }
     }
 }

In my ASP.NET code, I can then get access to my datacontext as follows:


DataProvider.Instance.DataContext.Table.First(ii => ii.Id == Request.QueryString["id"]);

 

Logging the output of LinqToSql to a Log File

We use NLog as a logging mechanism. In the example below you could equally use Log4Net, ELMAH, Microsoft Enterprise Library, text files.... whatever you want.

public class NLogTextWriter : System.IO.TextWriter
 {
     private readonly static NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();

     public NLogTextWriter()
     {

     }

     public override void Write(string value)
     {
         logger.Trace("SQL: {0}", value);
     }

     public override void Write(char[] buffer, int index, int count)
     {
         logger.Trace("SQL: {0}", new string(buffer, index, count));
     }


     public override Encoding Encoding
     {
         get
         {
             return System.Text.Encoding.UTF8;
         }
     }
 }

You will notice in my DataContext code, that _context.Log gets set to a new instance on NLogTextWriter. What happens now, is all logging gets pushed to my log provider.

Credits

Credit to Kris Vandermotten for his article on sending the Log output to the DEBUG window as a start for this article. You can read his article here

3 Comments

  • Using singleton for LINQ context is a actually bad idea. I had big issues in production because of this. The thing is that it is not thread safe and when 2 asp.net requests are using the same LINQ Context, it can end with unpredictable results. You should rely on a pattern that would create a Context instance for each Request instead.

  • @Stelian - I do use a Context for the Singleton. I put the datacontext in System.Web.HttpContext.Current.Items which is valid for the just the Request we're on. The Singleton only exists for the lifetime of the Request, and it is scoped to just the current Request.

    2 hits at once will result in 2 requests, and 2 Singleton objects.

    Grant

  • This is not such a good approach (singleton datacontext) as you can only specify load options once which is a shame :( So if you ever want relational data in one hit, you'll have to null off the context anyways.

Comments have been disabled for this content.