临时表，会话和SQL Server中的日志记录? [英] Temporary tables, sessions and logging in SQL Server?

问题描述

我的问题是我拥有(当前)仅存在于我的应用程序中的上下文.我想将此上下文推送到数据库，例如，如果有诸如自定义SQL Server连接属性之类的东西，我想使用它.

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.

我的想法是在执行任何工作(特定于应用程序的SQL)之前，先创建具有上下文信息的#本地临时表，并编写触发器以从该临时表中获取信息并在通用日志表中进行汇总

My idea was 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.

在尝试弄清楚这一点时，我读到了-在临时表中有SQL Server中会话的概念.但是，我找不到任何有关SQL Server会话的确切构成的文档.

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.

我想对谁最好地(从应用程序中)将上下文推送到数据库，然后对其进行处理的一些见解.例如触发器等.

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.

我正在为我的数据库对象使用Linq2Sql，但我有点不确定如何将其连接起来，以便为每个DataContext.SubmitChanges为涉及的每个连接设置适当的上下文.在我看来，这应该等同于在SubmitChanges之前执行一些自定义SQL，但实际上DataContext.ExecuteCommand和SubmitChanges是两件事，我有什么保证，即连接或会话相同在MSDN上的SQL Server文档中进行了引用.)

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).

• 该应用程序是一个Web应用程序，上下文是与经过身份验证的用户(也称为ASP.NET会话状态项)结合使用的一些属性.
• 默认情况下，我使用的是连接池，我打算禁用它.
• 该解决方案不必具有灵活性，但必须具有鲁棒性(这就是为什么我将这些内容移入数据库服务器，其目的是维护可靠的审核信息).

推荐答案

这是我有目的的解决方案.

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);

然后，像这样实现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();
    }
}

无论是连接池还是SQL提供程序连接管理器，都在使用连接之前，但在Open()之后立即更改了本地状态. Linq2Sql将立即结束会话Close()，因此，这很好.

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.

然后要获取context_info，只需在SQL Server 2005及更高版本中执行此操作(只要它是同一会话@@ SPID，就可以在任意位置).

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

这篇关于临时表，会话和SQL Server中的日志记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！