Changes to VS Mapping of CLR Objects to Yukon Data Types under Whidbey Beta2

  • Article

I'm working on a project that makes
extensive use of Yukon's CLR integration. This makes things that have
traditionally been difficult to do in T-SQL (like string manipulation)
a whole lot easier. If you aren't familiar with the basics of SQLCLR
integration, MSDN has several good overviews. Pablo Castro's new article is a great place to start and covers some of the changes in Beta 2.  

Visual Studio does a great job of hiding
the gory details of deploying .Net assemblies and mapping CLR data
types to SQL data types behind a simple "deploy" button. Sometimes it
does such a great job that you take what it's doing for granted and get
bit.

I got so bit yesterday while working on
migrating a prototype from Whidbey PD7 to Beta 2. I have a simple
SqlFunction that takes a string (containing XML data), does a
Regex.Replace, and returns the modifies string. Worked fine under PD7,
but under Beta 2 I started seeing unexpected end of input Exceptions
from the downstream XML parser. Stepping through the SqlFunction in the
debugger I found that the input param string was being truncated to
4000 characters. Hmm.

Turns out that under Beta 2 Visual
Studio's "auto-registration" of CLR objects (the mapping of CLR data
types to SQL data types when you deploy a SQLCLR assembly) has
changed. Under previous builds any string or char[] params were mapped to nvarchar(max) in the Yukon world. Under Beta 2 the mappings have changed, presumably for efficiency reasons.

The new mappings are as follows:

 String           NVarchar(4000)
SQLString        NVarchar(4000)              
Char[]           NVarchar(4000)
SQLChars         NVarchar(MAX)             

 Byte[]           Varbinary(8000)
SQLBinary        Varbinary(8000)             
SQLBytes         Varbinary(MAX)

What this means is that when you want to
pass string data that may be > 4000 characters long into (or out of)
a SQLCLR function or SPROC you need to specify the params as SQLChars rather than string. Similar changes have been made for varbinary data.

As an aside: it's pretty amazing to be
able to step pretty much effortlessly from CLR code into a T-SQL SPROC
and then into SQLCLR code and out again from within the Visual Studio
debugger.