I liked this so much I had to apply it to the product I am working on straight away. Below is how I managed a quick solution to ASP.NET/C# T-SQL integration using SQlDataSource and triggers.
Summary
This document describes how changes to data can be logged without the need to transfer “Updated_by” info via update/insert statements and provides a mechanism for logging deleted data too.
In this example the log data is stored in the data table but this need not to be the case.
The mechanism involves transferring Session Data from the ASP.NET web server to the Session Data (in the form of CONTEXT INFO) in the database. The session data remains alive from the OPEN CONNECTION call to the CLOSE CONNECTION. This means that UPDATE triggers can use the database session data to establish “Updated by”.
By using Triggers to log the changes, it is no longer necessary to pass the “Changed Date” data from the application to the database. The database trigger knows what the date is and can make use of the GetDate() function.
A procedure is created for storing SessionData and a function is created for retrieving SessionData.
While the C# code In the DB.NewCon() function can be adapted to set the session data immediately after the OPEN call to deal with connections established using “Code Behind”, a new class inherited from “SqlDataSource” is used to perform a similar action prior to actual updates etc. Other technologies may be able to implement similar solutions. LINQ ?
Procedures for setting the session variable
CREATE PROC [dbo].[usp_SetSessionUser](
@user-id INT
)
/*---------------------------------------------------------------------------DESCRIPTION Remember the User_id in the session's context_info so that it can be recalled later, e.g. in triggers
CREATED JDB 9.June.2010
---------------------------------------------------------------------------*/
AS
BEGIN
DECLARE @xml XML, @varXML VARBINARY(128)
SELECT @xml = ( SELECT @user-id AS '@UserID' FOR XML PATH('Values') ) ;
SELECT @varXML = CAST(CAST(@xml AS VARCHAR(MAX)) AS VARBINARY(128)) ;
SET CONTEXT_INFO @varXML
END
Function for retrieving the session variable
CREATE FUNCTION [dbo].[usf_GetSessionUser]()
/*---------------------------------------------------------------------------DESCRIPTION Remember the User_id in the session's context_info so that it can be recalled later, e.g. in triggers
CREATED JDB 9.June.2010
---------------------------------------------------------------------------*/
RETURNS INT
AS
begin
DECLARE @varXML VARCHAR(MAX), @xml AS XML;
DECLARE @user-id INT;
SELECT @VarXML = CAST(CONTEXT_INFO() AS VARCHAR(MAX))
SELECT @XML = CAST(REPLACE(@VarXML,CHAR(0),'') AS XML)
SELECT @user-id = x.v.value('@UserID[1]','INT')
FROM @xml.nodes('/Values') x(v);
RETURN(@UserID);
END
Code for overriding SqlDataSource
namespace SQL
{
public class MySqlDataSource : SqlDataSource
{
/* This override of the "SqlDataSource" class provides Transferrence of the the ASP.NET Session variable "user_id" to
* the database's Session variable "Context_info" so that it can be accessed in UPDATE triggers.
* Update and Delete should be tackled similarly. */
public MySqlDataSource()
{
base.Updating += new SqlDataSourceCommandEventHandler(Updating2);
}
public void Updating2(Object source, SqlDataSourceCommandEventArgs e)
{
DbCommand command = e.Command;
SqlConnection con = (SqlConnection)command.Connection;
con.Open();
string sql = @"EXEC dbo.usp_SetSessionUser " + Page.Session["user_id"].ToString();
SqlCommand command2 = new SqlCommand(sql, con);
command2.ExecuteNonQuery();
}
}
}
Code for Creating an UPDATE trigger using session data.
-- =============================================
-- Author:John David Birch
-- Create date: 9.June.2010
-- Description:Remember who changed this row
-- =============================================
CREATE TRIGGER [myUser].[tau_customer]
ON [myUser].[ customer]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @user_id INT;
SELECT @user_id = dbo.usf_GetSessionUser();
UPDATE [myUsert].[customer] SET ChangedBy = @user_id, ChangedDate=GetDate();
END