Visual Studio 2010: Enabling Older DotNet Frameworks (or how I learnt to love multi-targeting)

I've been told that SQL 2005 doesn't support CLR functions created with DotNet Framework 4.0 - I haven't tested it cos When attempting to create a new SQL 2005 CLR project using Visual Studio 2010, I found myself at a loss to work out how to create a .NET Framework 3.0 project ie using the new and improved multi-targeting feature.

The only option available to me in the 'New Project' dialog box was a 4.0 Framework. Plus a handy 'More Frameworks" option which took me to the download page for various dotnet frameworks. I already had 3.0, 2.0 and 1.1 installed on the machine so the need to redownload and reinstall them seemed irritatingly redundant.

So after much googling, I found the solution.

You have to install .NET Framework 3.5.

Ummm... that's it.

Close VS, install 3.5 framework, open VS and wacko!

Contrary to the installer instructions, I did not have to reboot.

SQL CLR .Net Function - Split

Having needed a split function many times in the past, I've always utilised patindex and done it the hard way…. And when it comes to nvarchar values, forget about it! So today I took a few minutes to write a SQL CLR function to do it.

The function takes a string and a delimiter and returns a table of string values.

Here are the instructions for those who may want to do the same thing…

1. Create a Visual Studio project of the type Database Projects \ SQL Server\ SQL-CLR \ C# SQL Server Project – I called it udfclrSplitString but you know, call it whatever you like!
   
2. Add the following code:
   

   using System;
   using System.Data;
   using System.Data.SqlClient;
   using System.Data.SqlTypes;
   using Microsoft.SqlServer.Server;
   using System.Collections;

   public class CSRSplitString
   {
   [SqlFunction(FillRowMethodName = "FillRow")]
   

   public static IEnumerable udfclrSplitString(SqlString InputString,SqlString Delimiter)
   
   {
   
   return InputString.Value.Split(Delimiter.Value.ToCharArray(0, 1));
   }
   
   public static void FillRow(object row, out string InputString)
   {
   InputString = (string)row;
   InputString = InputString.Trim();
   }
   }
   

3. Build that sucker
   
4. Drop the dll onto the SQL server somewhere relevant
   
5. If CLR is not already enabled on the server, execute the TSQL code:
   

   sp_configure 'clr enabled', 1
   GO
   RECONFIGURE
   GO
   

6. In the desired database, execute the following TSQL code:
   

   CREATE ASSEMBLY CSRSplitString
   FROM 'D:\<YourDLLName>.dll'
   WITH PERMISSION_SET = SAFE
   

7. In the desired database, execute the following TSQL code:
   

   CREATE FUNCTION udfclrSplitString
   (@String nvarchar(4000),@Delimiter nvarchar(1))
   RETURNS
   TABLE (ArrayItem nvarchar(4000))
   AS EXTERNAL NAME CSRSplitString.CSRSplitString.udfclrSplitString;
   
   Where external name is MyDotNETAssembly.MyClassname.Method

8. To test it, try:
   

   select *
   from udfclrSplitString('Kristen,Patrick,Julian,Werner,Koe',',')
   
   You should get 5 rows back …

9. If you want to test it using a table column as the input try something like:
   
   Select a.name as DbNameOriginal,s.*
   from msdb.sys.databases a cross
   apply udfclrSplitString(a.name,'_') s
   where a.name like '%_%'
   
   You will need to have the function in the msdb database for this example to work
   
   
10. To make this function automatically available in all new databases created on that server, execute steps 6 + 7 in the model system database.