Situational awareness with SqlContext

It's often handy for your code to know where it's running. I have a habit coding data access classes that use the caching mechanisms built into the System.Web namespace. These components can only use that cache when the assembly is hosted in IIS. This creates issues when I'm building unit tests that exercise the those classes outside of IIS. To avoid bogus caching errors the class can check to see if it's hosted by IIS by checking HttpContext.Current. If it's null then don't attempt to cache. If it's there, then go ahead and use it.

SQL Server exposes a similar context to your managed code called SqlContext. SqlContext has a number of methods to allow you to tap into the Sql Session that is calling your code. A simple check to SqlContext.IsAvailable will let you know if you're running on the server. Beyond that, you can explore the SqlContext.WindowsIdentity, TriggerContext or the Pipe. These will be topics of other posts, but for now we'll stick to basics.

It's possible for your managed stored procedures to perform data access. This feels a little inside out, but you can create an ADO connection inside your managed code and perform queries or updates. If you do this, you should use a special connection string that is only available when running on the server. Here is some code:

String conncetionString
If(SqlContext.IsAvailable){
connectionString = "context connection=true";
}else{
conncetionString = GetYourRegularConnectionString();
}
SqlConnection con = new SqlConnection(connectionString);
…

This simple check will allow you to write code that will run on the server, and still allow you to cover the code with unit tests. Hmmm…that's nice.