Bee Eee Blog

I’ve often been frustrated by the sql that linq produces.  Usually it’s really good, but occasionally it produced monstrosities.   The hard part is getting at the sql that’s produced by a function.  Hovering over the statement in the idea is a real pain so instead I created the following class that dumps the sql to a file

public class DCLogger : IDisposable
    {
        private StringWriter writer;

        public DCLogger(System.Data.Linq.DataContext dc)
        {
            writer = new StringWriter();
            dc.Log = writer;
        }

        #region IDisposable Members

        public void Dispose()
        {
            writer.Flush();
            System.IO.File.WriteAllText( "c:\\temp\\sql.log", writer.ToString() );
        }

        #endregion
    }

To use it just create it passing it you data context. The results are written to the file when it’s disposed.

  using( DCLogger( dc ) )
  {
    // ... some linq statement that executes
  }

Here’s a helpful, and perhaps dangerous, little tidbit of code for SQL Server 2005. It deletes all of the data from all of the tables. To be completely effective, the command may need to be run more than once because of foreign keys. Basically run it until there aren’t any errors. Then the db is clean and empty of all data.

exec sp_MSForeachTable "delete from ?"

the sp_MSForeachTable is a very useful system stored procedure.

In MS SQL Server 2005 the following code is how to get a new uniqueidentifier, and the current date and time.

SELECT newid(),getdate()

Produces these results.

------------------------------------ -----------------------
125700F3-8FB8-496F-8EA4-35D9CB9E8415 2008-10-29 13:09:54.557
(1 row(s) affected)