Good or bad, but habits

Ok, that one is a bit hardcore but helped me a few times...

Here I'll demonstrate how with a very little impact on your code you can pass a variable from C# code to Sql Server to make it available as a session variable during your connection lifetime.
Let's try and explain through a concrete example:

You are architect at Colors, you know, the multi-all company (multi-national, multi-technology, multi-department, multi-cultural, multi-millionaire, etc...) and at Colors all applications have the following use case defined:

• Mr Pink: "Cool! A Manager should be able to know who did what on every single piece of data... anywhere, anytime"
• Me: "What for?"
• Mr Brown: "Security reason! Just do it!"
• Me: "And what about the existing half-a-dozen applications we maintain?"
• Mr Pink: "Cool! I told them every single piece, anywhere, anytime should not be a problem..."

Mmmm...looks like we have a slight issue. Ok... let's go then!

In my opinion it's better doing this at database level rather than code : faster, less bandwidth consumption, etc...
I'll probably explain my solution to that "requirement" in a later post but the thing to do is, somehow, log all CUD operations (CRUD with no Read) with their respective operator identifier, yes UserId if you prefer....

Unfortunately your half-a-dozen applications makes usage of Forms authentication which means:

• Username and Password hash sits in the db
• The application process runs and connects to the database through a Service Account (IUSR_MyMachine, ASPNet or whatever account the IT guy, Mr Grey, has chosen when setting up the application in the IIS box).

Too bad: sql server won't be able to tell you who is connected to your database...

4 options then:

1. Kill mr Pink. Could be fun but you are not sure you'll be allowed to use a computer in jail. So, geeky, as you don't want to spend the rest of your days locked in a cell without your best friend: don't choose that one!
2. Randomize the UserId in the CUD operations log: by the time someone discovers you have done that then you might be far...
3. Ask your offshore team to rewrite the DAL of your applications so that you only use stored procedures which accepts a UserId (or SessionId, or whatever allows you to identify your user) as an input parameter. Why not? Need to open a work item in TFS + follow it up. That's what I call a boring job. Plus I don't like opening this kind of work items in TFS: bad for my stats...
4. Use the Sql Server context. What's that? Every time you open a connection to Sql Server you participate in a session. That session can hold a 'little' piece of context data in CONTEXT_INFO. As the doc says, CONTEXT_INFO is a 128 bytes long binary values. We could probably use that to store the UserId of the connected user when we open a connection from C# code and then reuse it into a trigger to log data changes. How can I do that???

The trick

Populate the context info from your .Net code by :

1. Transform your data into a byte array
2. Convert it to an hexadecimal string (see HexadecimalConverter class)
3. On each sql server connection opening execute a command to set the context info

In this example, I use a COMB Guid (see great article by Jimmy Nilsson) as my UserId and a Linq DataContext to access my SQL Server DB.

public partial class myDataContext 
{
    partial void OnCreated()
    {
        // subscribes to the data context connection StateChange event
        this.Connection.StateChange += 
            new StateChangeEventHandler(Connection_StateChange);
    }

    void Connection_StateChange(object sender, StateChangeEventArgs e)
    {
        // When connection opens
        if (e.CurrentState == ConnectionState.Open)
        {
            // use the standard ExecuteCommand query provided by 
            // DataContext to set data into the Sql Server 
            base.ExecuteCommand(
                String.Format
                    (
                    "SET CONTEXT_INFO 0x{0}",
                    // the current UserId is provided by an extension 
                    // methods as well as the conversion to hexa string 
                    this.UserId().ToByteArray().ToHexadecimalString()
                    )
                );
        }
    }
}

Now you are ready to consume the context info from any stored procedure, trigger or whatever by writing the following code:

// converts back the 128 bytes long binary values into a Guid
select convert(uniqueidentifier, context_info())

public static class HexadecimalConverter
    {
        static readonly string[] fLookupTable;
        static HexadecimalConverter()
        {
            // Populates cache
            fLookupTable = new string[256];
            for (int i = 0; i < fLookupTable.Length; i++)
                fLookupTable[i] = i.ToString("X2");
        }

        // converts a byte array to an hexa string
        public static string ToHexString(byte[] bytes)
        {
            if (bytes == null || bytes.Length == 0) return "";

            char[] lChars = new char[bytes.Length * 2];
            for (int i = 0, j = -1; i < bytes.Length; i++)
            {
                string lHex = fLookupTable[bytes[i]];
                lChars[++j] = lHex[0];
                lChars[++j] = lHex[1];
            }

            return new string(lChars);
        }

        // Extension Method
        public static string ToHexadecimalString(this byte[] bytes)
        { 
            return HexadecimalConverter.ToHexString(bytes);
        }
    }

It is very likely that one day or other you'd want to run a sql task at startup of your server. What for? You probably know better than I do but here are a few ideas:

• Create a denormalized table in tempdb
• Check sanity of the server
• Start a monitoring process

Whatever... The following script configures your db server for launching (in each database) the stored procedure called _startup when present:

use master;

if exists 
( 
    select name 
        from master.sys.objects 
        where name = '_startup' 
        and type_desc = 'SQL_STORED_PROCEDURE'
) drop procedure _startup;
go

create procedure _startup
as
    declare @sql varchar(max)
    declare @dbName varchar(50)

    declare curs 
        insensitive cursor for 
            select name 
                from master.sys.databases 
                where database_id > 4
    
    open curs
    fetch curs into @dbName
    
    while(@@fetch_status = 0) begin
        select @sql = 'use ['+ @dbName + ']'
        exec(@sql)
        select @sql = '
        if exists(
            select * 
                from [' + @dbName + '].sys.objects 
                where type_desc = ''SQL_STORED_PROCEDURE'' 
                and name = ''_startup''
            ) exec(''[' + @dbName + '].dbo._startup'')'
        exec (@sql)
        fetch curs into @dbName
    end
    
    close curs
    deallocate curs
go

exec sp_procoption '_startup', 'startup', 1