Using System.Configuration.dll in .NET sprocs and UDFs

System.Configuration.dll was the last assembly added to the "SQL Server 2005 approved assembly" list, that is, the list of BCL assemblies approved for SQL Server consumption. These are loaded from the GAC, rather than having to be explicitly added to the database by the user. System.Configuration was added SO late that it doesn't show up in a Visual Studio Database assembly project in the "Add References" dialog with all of its collegues. You have to use a normal Visual Studio Class Library project (or other means) to reference it.

When students in my beta classes would try and fiddle with the config file information used to determine which version of an assembly should be loaded by putting these settings in a file aptly named sqlservr.exe.config, these would be ignored. I'd thought that the entire config file was discarded, and rebuilt so that SQL Server would have its own version configuration information. Just before SQL Server 2005 shipped, the ability to run CREATE ASSEMBLY on multiple versions of the same user assembly was dropped (you can still have multiple language-specific resource sattelite assemblies cataloged).

So what do these two things have to do with one another?

Someone asked me recently if you could access configuration strings by making a sqlservr.exe.config unrelated to assembly loading I didn't think so, but if not, what is System.Configuration doing on the approved list? Let's try it in a .NET user-defined function…

public static string GetConfigString()
  // This statement requires referencing System.Configuration.dll
  ConnectionStringSettings s = ConfigurationManager.ConnectionStrings["pubs"];
  if (s != null)
    return s;
    return "no config";

This works fine. The .NET UDF can even be cataloged and run as SAFE. So now that we've gone this far, a better question might be "why do you want to put configuration strings in files"? You're running IN A DATABASE after all, how 'bout storing them in a TABLE? Perhaps so that the same code would work inside SQL Server or in middle tier server? OK, I'll buy that.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.