Temporary tables, sessions, and registration in SQL Server?

Question

My problem is that I have a context that (currently) only exists in my application. I would like to push this context to the database, say, if there were such things as custom SQL Server connection properties, I would like to use this.

My idea was to create a local temporary table # with contextual information before any work is done (SQL application) and write triggers that will extract information from this temporary and summarize it in a general maintenance table.

I read - and try to figure it out - that with temporary tables there is a concept of session in SQL Server. However, I could not find the documentation of what exactly is a SQL Server session.

I would like to get an idea of ​​who best clicks the context on the database (from the application), and then do something about it. e.g. triggers, etc.

I am using Linq2Sql for my database objects, and I'm a little unsure how I could connect it so that for each DataContext.SubmitChanges an appropriate context is set for each connected connection. In my opinion, this should coincide with some custom SQL executed immediately before SubmitChanges , but in practice DataContext.ExecuteCommand and SubmitChanges are two different things and what guarantee do I have that this is the same connection or (a session like that mentioned in the documentation SQL Server on MSDN).

Update: Details

• An application is a web application, a context are some properties combined with an authenticated user (ASP.NET ASP.NET session state elements).
• I use the default connection pool, I have the intention to disable this.
• The solution should not be flexible, but it must be reliable (which is why I am moving things to the database server, the purpose of this is to maintain reliable audit information).

Answer 1

Here is my target solution.

Connect a connection status change event.

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

And then we 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(); } } 

Regardless of whether the connection pool or the connection manager of the SQL provider is connected just before the connection, but immediately after Open() local state changes. Linq2Sql will immediately end the Close() session, and therefore this works very well.

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

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

Answer 2

Custom connection properties or session contexts are not native to the SQL server world.

There is no information in your question about whether you are using a connection pool or not in your application. But if you have a pool, there is no guarantee that when two commands are executed, they will use the same physical connection (another .NET application thread can request a new connection and can get a connection from the pool previously used in the first thread, at the same time, the first thread will want to open a connection and will actually receive a second connection).

With a connection pool, a “session context" is what you need to monitor only at the application level. If you have a desktop application, you have a workaround: you can create a table in which you will have the hostnames and additional properties associated with each hostname (but we must assume that each client will start only one session). However, in web applications there is no easy answer.

Usually, if you want to implement logging using triggers, you need to work with existing variables, that is, the host name, username, application name, session identifier, time date.

I believe that if your application has some properties that are logically related to user sessions (for example, an ASP.NET session), you can assign each new GUID session and write this GUID and some other permanent (not temporary!) Table and all The custom properties you want to use for logging. Then, add a GUID column to all important tables and force the code to populate the GUID column for all changed / inserted rows. In this case, you can use this GUID and all context data in triggers.

Another way is to use stored procedures for updates / deletes and the presence of an additional GUID parameter there and the implementation of logical protocols directly in stored procedures (so you will not need to add an additional column to all tables, brrrr)

But think again, if you really need all the logical staff to work at the database level? Why not register with business applications or the data layer? Consider that some of your clients may redirect file entries instead of databases to improve performance. If you are doing logging using Log4Net or even using Diagnostics.Trace, redirecting log output is just a configuration issue, but not a redesign of the entire system. And if you want to have logs in SQL tables, for example for reports, write the logs to a file in CSV format, and then use some task to import this data to the SQL server and run this task at night (or when the server is less active).