Managing Internal Connection Strings in SQL CLR code during testing

One thing that’s somewhat painful about testing/debugging SqlClr application code is managing the connection strings used for internal DB calls inside the system. SqlClr has the notion of a “context connection” connection string which uses the same connection and execution context as that under which the SqlClr code is executing. We use this a lot inside the Aggregation system my team developed.

It can make testing difficult, as when you’re unit testing functionality that uses the context connection and you’re hosted not within SqlServer but within a testing framework these internal calls fail with a “context connection is only available when hosted by SqlServer” error.

I’ve come up with an idiom for managing this in my unit tests. Maybe useful to others as well, so here it is.

I have a utility class with a static constructor which figures out if we’re hosted by SqlServer by inspecting the SqlContext.IsAvailable property and sets a connection string property appropriately. If we’re not hosted by SqlServer the utility class checks the ConfigurationManager for settings stored in AppSettings in our app.config file and uses those.

Here’s the relevant code:

#region Constructors
/// <summary>
/// Sets up the internal connection string used by the system.
/// </summary>
static Utility()
{
if (SqlContext.IsAvailable)
{
internalConnectionString = "context connection=true";
}
else
{
try
{
SqlConnectionStringBuilder sqlSb = new SqlConnectionStringBuilder();

      #if DEBUG
sqlSb.Pooling = false;
#endif
sqlSb.DataSource = ConfigurationManager.AppSettings["DBServer"];
sqlSb.InitialCatalog = ConfigurationManager.AppSettings["Database"];
sqlSb.IntegratedSecurity = true;
internalConnectionString = sqlSb.ToString();
}
catch (Exception e)
{
// log
internalConnectionString = "context connection=true";
}
}
}

#endregion

#region Properties

/// <summary>
/// Connection String used for DB calls within the system.
/// If hosted by SqlServer this will be a context connection,
/// otherwise we assume this is a test run and use a trusted connection
/// string with database and server as configured in app.config.
/// </summary>

public static string InternalConnectionString
{
get { return internalConnectionString; }
}

#endregion

Then in the SqlClr code we get the connection string like so:

using (SqlConnection conn = new SqlConnection(Utility.InternalConnectionString));

Now our unit tests can operate outside of the SqlServer context. This isn't always appropriate or neccessary, but for our project it's both.

Some caveats/weirdness:

1. When hosted in SqlServer you don’t usually have access to config files (or the file system as we know it) so I rename and copy the app.config for my unit test assembly to the output directory and rename it so it looks like the config file for the SqlClr assembly. This is only useful for testing, and the config file that appears to be for the SqlClr code isn’t actually deployed to SqlServer. 

2. I wouldn’t ordinarily read config settings in a static .cctor, but in SqlClr you can’t write to static members unless they’re readonly which complicates things somewhat. 

3. When debugging SqlClr you want to use a trusted connection when possible, so that’s what we do for test scenario here. This assumes whatever security context the tests are using has necessary permissions on the database. 

4. SqlContext.IsAvailable can return false for reasons other than that your code isn't hosted in SqlServer. I haven’t fully explored all the potential failure cases here yet. We protect our system against this in the SqlServer hosted case by defaulting to a context connection if any exception is caught when attempting to initialize the test connection string.

5. VS won't let you add a reference to the System.Configuration.dll assembly using the normal Add Reference context menu for SqlClr projects. Apparently this assembly was added to the "blessed assemblies" list for SqlServer hosted code late in the game and VS hasn't yet caught up. You'll need to manually edit your .csproj file and add  the reference manually like so:

     <ItemGroup>
   . . .
   <Reference Include="System.Configuration" />
   </ItemGroup>