Recently, one of my peers asked a question regarding the ability of SQL CLR classes to read configuration data from an Application Configuration file. After doing some quick Google searches on the topic, I came up with a couple of links that indicated the potential, but no concrete HOWTO on getting it all wired up.
Now, I don’t want to debate whether this makes good architectural sense to store static settings in the file system when you’re already working inside a database management system that could just as easily save these settings to a table. A scenario that you might leverage this capability is to re-use an existing Application Configuration file, specifically, the <appSettings> element from an already built and working assembly from another application. One of the blog posts I found from Bob Beauchemin [1] states the same issue and scenario of use. Bob also kind of discuses high level what needs to happen; but, again, no step-by-step wire-up exists.
Turns out, after I sent the links to my peer indicating that it’s possible, I decided to try it out and get a quick sample running. I’m glad I did, as it turns out it wasn’t as straightforward as expected and I encountered a small anomaly that if you don’t know a workaround you’d come to a rapid conclusion that it’s not possible to read <appSettings> from the configuration file after all.
Sample Solution Files
SQL Server Configuration
The first step before you can run any CLR code inside of SQL 2005, you must enable it. You can search MSDN or Books Online for the topic “Enabling CLR Integration”. The following set of commands enables the CLR in SQL 2005:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO |
Visual Studio 2005 SQL Project
Now, launch Visual Studio 2005 and startup a new project. Choose “SQL Server Project” which, depending upon your profile settings, is listed under your language of choice and the database folder.
Visual Studio creates a SQL project that allows for debugging directly into the called CLR code by automatically attaching to the SQL process and executing the debug session. I’m not going to go into SQL CLR debugging as it’s covered fairly well in Books Online.
Visual Studio Project File Manual Edit
In order to access the System.Configuration namespace that’s part of .NET 2.0, it’s necessary to close down the solution & project that was just created and edit the project file directly. Generally, inside of Visual Studio you’d right click on a project, choose “Add Reference”, find the Assembly and you’re done.
The System.Configuration.dll assembly is on the SQL 2005 Approved assembly list [2]; however, since it was added as approved at the last moment before RTM it’s not possible to add it by using the normal method inside of Visual Studio. So, to work around the limitation inside of Visual Studio, we're just going to add the reference manually to the Visual Studio project file.
So, close down the project file (shut Visual Studio). Navigate to the folder where the “.csproj” file (or “.vbproj” file) is then open up with your favorite text editor.
Find the first <ItemGroup> element whose sub-elements are <Reference>. Once you have that section, add a new child element as follows:
| <Reference Include="System.configuration" /> |
So, you should end up with the following <ItemGroup> section:
<ItemGroup>
<Reference Include="System" />
<Reference Include="System.Data" />
<Reference Include="System.XML" />
<Reference Include="System.configuration" />
</ItemGroup> |
Now, re-launch your project back in Visual Studio. Check to see that the references are OK and you should see the following:
Next, add a new item to the project and choose “User Defined Function”. Basically, all this does is add a file that has the correct attribute on the method that is part of the template. For our needs the attribute is Microsoft.SqlServer.Server.SqlFunction.
Take a look at the included code for the complete class file. Here’s the complete listing.
using System.Configuration;
namespace CedarLogic.SqlTest
{
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetAppSetting( string key )
{
try
{
/* The following call is made in order to workaround an "anomaly"
* where reading an appSettings key value results in the error:
* "The value of the property 'key' cannot be parsed.
* The error is: Request failed. (sqlservr.exe.Config line X)"
*
* So, just a call to another section alleviates the issue.
* This error only occurs on the first call to an assembly after
* it's been loaded by the sqlservr.exe
*/
int i = ConfigurationManager.ConnectionStrings.Count;
//here, simply use configuration manager to read the key to
//a temp variable - doesn't need to be a temp var, but here
//for simple debugging.
string s = ConfigurationManager.AppSettings[key.ToString()];
return "OK: " + s;
}
catch( Exception ex )
{
return ex.Message;
}
}
}
} |
Application Configuration File
If you haven't worked much with .NET, a little background on Application Configuration [3] files is helpful. .NET provides a Configuration namespace (System.Configuration) that provides the ability to provide runtime configurable application options from an XML based file. The solution files for this sample contains a "sqlservr.exe.config" file. The contents are as follows:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="MyKey" value="MyValue" />
</appSettings>
</configuration> |
The <appSettings> element provides a simple name-value pair of application settings that the .NET runtime de-serializes into an in-memory object. These settings are read using a "key" to retrieve the value.
The file is read once by the runtime and to change any value requires restarting the process - or more specifically the Application Domain [4] - but that's another discussion.
In your .NET code, to read the values, simple leverage the System.Configuration.ConfigurationManager type, specifically the static property accessor AppSettings. This method returns a string object:
| string myAppValue = System.Configuration.ConfigurationManager.AppSettings["MyKey"]; |
The next step is to provide the SQL server instance with an Application Configuration file. Since the CLR is to be loaded inside of the SQL Server process, the name of the configuration file is just the exe name with “.config” appended to it. It’s also necessary to ensure you have the instance’s correct path to put the configuration file in. There are 2 ways to obtain that path. The first is to use SQL Server Management studio, right-click on the connected instance, then check the “Root Directory” setting. This is the base path of the instance. In the \Binn subdirectory is where the “sqlservr.exe” main binary is and this is the directory where the “sqlservr.exe.config” file must go in order for the CLR to load it.
The other way to obtain the Root path is to run the following command in the query window.
declare @smoRoot varchar(1024)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
select @smoRoot |
Now, the included project has a sample “sqlservr.exe.config” file that will work with the sample project.
Once this file is placed in the SQL \Binn directory, you must restart the SQL Server instance (using Windows Service Manager is one way) in order for the file to be utilized. In fact, any changes you make to this file, you must restart the SQL Server instance to read the new values – another reason that this may not be the best option for application settings.
Assembly Deployment
Visual Studio 2005’s SQL Project makes building and deploying the compiled assembly very easy. In fact, it hides quite a bit of what is happening each time you choose “Deploy” from the build menu. To see some details of how to deploy manually, the solution files with this article includes a file called “AddAssembly.sql” that provides the steps necessary to both install the assembly and declare the user defined function. This script assumes 2 things – 1) the name of the database and 2) the path to the Assembly. On your environment these most likely will be different.
So, from within Visual Studio, ensure that you have a database reference to the database you want to deploy to. You can check or change by select project properties and navigating to the Database tab. Here you can change the connection string that will be used for deployment and other SQL Project needs (such as reading what other Assemblies are available for references).
Once you have a valid database reference for your project, you can deploy the assembly choosing "Deploy" from the build menu or right-click on the project in Solution Explorer, and select "Deploy" as follows:
Executing Your SQL CLR User Defined Function
Once it’s deployed, it’s just a matter of making a call to the function. For that launch SQL Server Management studio, start a new query window and choose the database that the Assembly was deployed to.
From the query window just enter the command to execute the function (note that the DB name in this line is 'AJunkDb'):
SELECT [AJunkDb].[dbo].[GetAppSetting] ('MyKey') |
This should provide a result in the query results window that corresponds to the value stored in the “sqlservr.exe.config” file – which is just “MyValue”. What the runtime and your CLR based function has done is read the "sqlservr.exe.config" file, looked up the key value provided - 'MyKey' - and returned the associated value - 'MyValue' - that was stored in the file.
Issues
One of the issues I encountered (a bug) is if the first call through ConfigurationManager is to the static AppSettings method an error is returned indicating it couldn’t read the key value. That error is:
"The value of the property 'key' cannot be parsed.
* The error is: Request failed. (sqlservr.exe.Config line X)".
|
Now, the line that causes this exception is:
| string s = ConfigurationManager.AppSettings[key.ToString()]; |
There’s absolutely nothing wrong with that line of code. And, there’s nothing wrong with the configuration file.
So, after some debugging, I found that if I make some “dummy” call through ConfigurationManager to another method, then I can make the call to AppSettings with absolutely no issue. So, the following line is added just to provide a kludge of a workaround on line 36:
| int i = ConfigurationManager.ConnectionStrings.Count; |
Now, this line does nothing other than initialize ConfigurationManager and force a de-serialization of the configuration file.
To verify that this is the issue, comment out line 36 above in the source code, and be sure to “Deploy” the project again (this forces SQL to unload and reload the Assembly from the CLR). This is important as the problem only occurs on the 1st time an Assembly is loaded and the 1st call is to AppSettings. If any other call (such as line 36) is made to ConfigurationManager then the problem doesn’t reveal itself.
Summary
To summarize the steps that provides a way to read Application Configuration settings from within SQL CLR:
- Enable SQL CLR capability for the SQL Server instance by using the 'clr enabled' option via 'sp_configure'
- Create a Visual Studio 2005 SQL Server Project
- Manually edit the Visual Studio Project file adding a reference to the SQL CLR Approved assemby "System.Configuration"
- Develop my CLR Function
- Create a "sqlservr.exe.config" file and deploy to the correct Binn directory for my SQL Server instance
- Restart my SQL Server instance to ensure the Application Configuration file is read
So, a couple of things were uncovered in this sample.
- Adding System.Configuration to the project file manually is no problem as it’s an approved assembly for SQL 2005, but the Visual Studio 2005 RTM isn’t aware of it.
- An anomaly in System.Configuration.ConfigurationManager with the AppSettings method being called first may cause a conclusion that it’s not possible to read App.config settings
- The Assembly itself that is deployed to the SQL Instance only needs a Safe access level even though it’s accessing the file system. This to me is probably an oversight by the SQL team given the policy for any external resource (file system) would normally require either External or Unsafe level
- Consideration should be made if this is the right way to store settings for the Db tier for a couple of good reasons: 1) it violates the reason why your using a Db – the capability to store objects in tables, and 2) requires a restart of the SQL Instance if any changes are made to the configuration file; so, if it's about re-use it's another capability that's at your disposal
About
Shawn Cicoria is a Consultant with Avanade (www.Avanade.com) the leading Systems Integration Consultancy focused on the Microsoft Platform. Shawn is also a MCT training instructor with SetFocus (www.Setfocus.com) located in Parsippany NJ. Focused on distributed technologies such as COM[+], J2EE, and for the past 5 years .NET, SOAP, BizTalk, and Database technologies -- and now WinFX..
[1] Using System.Configuration.dll in .NET sprocs and UDFs
[2] Supported .NET Framework Libraries
[3] Application Configuration Files - MSDN Online
[4] Application Domains - MSDN Online