Temporary tables, sessions and logging in SQL Server?

Question

I've read --while trying to figure this out-- that with temporary tables there's the notion of a session in SQL Server. However, I have not been able to find any documentation regarding what exactly constitutes as a SQL Server session.

With this information, I'd like to implement some basic logging functionality.

My problem is that I have context which (currently) exists only in my application. I'd like to push this context to the database, say, if there was such as thing as custom SQL Server connection properties, I would wanna use that.

My idea is to create a # local temporary table with the context information prior to any work (application specific SQL) being executed and to write triggers that would fetch information from this temporary and summarize that in a general purpose logging table.

I'd like some insight on who to best push context onto the database (from an application) and then do something with it. e.g. triggers and such.

I'm using Linq2Sql for my database objects and I'm a bit unsure how I would be able to hook this up so that for each DataContext.SubmitChanges the appropriate context is set for each connection involved. In my mind, this should equate to some custom SQL being executed just before SubmitChanges but in practice DataContext.ExecuteCommand and SubmitChanges are two different things and what guarantee do I have, that it's the same connection or (session as it's being referred to in the SQL Server documentation on MSDN).

Update: Details

• The application is a web application, the context is some properties in conjunction with an authenticated user (a.k.a. ASP.NET session state items).
• I'm using connection pooling by default, I have on intent of disabling this.
• The solution does not have to be flexible, but it will have to be robust (this is why I move things into the database server, the purpose of this is to maintain dependable auditing information).

Answer 1

Custom connection properties or session contexts is something that is not native to SQL server world.

There is no information in your question about whether you are using connection pooling or not in your application. But if you have pooling, there is no guarantee that when you execute 2 commands they will use the same physical connection (another thread of .NET application may ask for a new connection and can get the connection from pool, previously used by first thread, at the same time first thread will want to open connection and will get actually 2nd connection).

With the connection pooling "session context" is something that you need to track only on application level. If you have a desktop application you have a workaround : you can create a table where you will have host names and additional properties, associated with each host name (but we need to suppose here that each client will start only one session). However in Web applications there is no simple answer.

Normally, if you want to implement logging with triggers, you have to operate with existing variables, i.e. host name, user name, application name, session id, datetime.

I suppose if your application has some properties logically associated with user sessions (for example with ASP.NET session) you can assign each new session a GUID, and write to the some permanent (not temporary!) table this GUID and all custom properties you want to have for logging. Then to all important tables you should add GUID column and force you code to fill GUID column for all modified/inserted rows. In this case you will be able to use this GUID and all context data in triggers.

Another way is to use stored procedures for updates/deletes and have an additional GUID parameter there and implement logging logics directly in stored procedures (so you will not have to add additional column to all tables, brrrr)

But think again if you really need all logic staff to work on the database level? Why not implement logging in application business or data level? Consider that some of your customers may want to redirect logging into file instead of database in order to improve performance. If you do logging with Log4Net or even with Diagnostics.Trace, redirecting logs output is just a matter of configuration, but not redesign of the whole system. And if you want to have logs in SQL tables, for example for reporting - write logs to file in CSV format and then use some job to import this data in SQL server and run this job at night (or when server is less active).

Answer 2

Here's my purposed solution.

Hook the connection state change event.

using System.Data;

var db = new DataContext(); // Change to your typical DataContext 
db.Connection.StateChange += new StateChangeEventHandler(StateChange);

And then, implement StateChange as such:

using System.Data;
using System.Data.SqlClient;

// State changed to Open
if (e.CurrentState == ConnectionState.Open)
{
    var conn = sender as SqlConnection;
    if (conn != null)
    {
        // Figure out what context applies
        int? loginID = null;
        if (Session.IsAuthenticated)
        {
            loginID = Session.Login;
        }
        // Create local temporary context table
        var cmd = new SqlCommand();
        cmd.Parameters.AddWithValue("@p0", loginID ?? 0);
        cmd.CommandText = @"SET CONTEXT_INFO @p0";
        cmd.Connection = conn;
        cmd.ExecuteNonQuery();
    }
}

No matter, the connection pooling or SQL provider connection manager in question, just before the connection is used, but right after Open() the local state is changed. Linq2Sql will immediately end the session Close() and so, this works very well.

Then to get the context_info you do this (anywhere you like as long as it's the same session @@SPID), SQL Server 2005 and later, only.

DECLARE @pContextInfo int
SELECT @pContextInfo = CAST(context_info AS int) 
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID