SQL Server CLR and C#

Hello, here we are again.  This time I'd like to discuss C#, the CLR and SQL Server.  Combining C# and SQL Server hasn't been a common or encouraged practice at any of my previous employers and so this was a foray into a new area of development.  I'm far more comfortable writing C# and SQL directly, but combining the two has never been anything I had the need to do.

As with most jobs, odd situations do arise and you are forced to find solutions no matter how extreme.  To quickly summarize the situation, I had a business request for a SSRS or Excel report that combined multiple sets of data with varying numbers of columns and column names.  In the past the business data was stored horizontally in tables so this type of report was common and posed no difficulties to the development teams.

Within the last year, however, we had changed the organization of the data to be stored vertically and to be far more normalized in an attempt to prevent the ever-growing problem of "column creep".  Yes, at least one older table has over 100 columns!

The difficulty in achieving what had previously been easy, now proved to be quite hard.  The schema has a series of tables in which users could define parameters and these parameters were then used in calculations to generate reports. to make matters more complicated, the number of parameters (and even their names and calculation methods) can vary between clients and even the number and type of parameter sets can vary.

To generate reports for a single parameter-set we had previously used a SQL Pivot to flip the vertical parameters into horizontal columns. That would have worked here, except for the varying number of parameter-sets.We considered doing another pivot, but the potential complexity of the resulting SQL  was daunting and the time required to develop and test such a complex query was also a potential issue.

In addition, we had no applications that could generate reports of this type, nor did we have the time to write one solely for this single situation. The user also wanted the data to be generated directly into CSV (Excel) or SSRS (which could be saved as CSV/Excel).

I was confident I could do this type of organization and combination of data in C#, reliably and even quickly, and so was born the idea to use the CLR on SQL Server.  I won't bore you further with the details. Suffice to say that I tried my hardest to stick to SOLID Clean-Code principles while doing the work but SQL server was never built with that type of thing in mind.

For those who haven't done this before the approach is simple.  First, you define a partial class and a static function.  You give it the "SqlProcedure" attribute.  Then you open a connection like you would normally in C# and perform your operations. I wrapped the entire operation in a try-catch block so I could get a message output when testing in SQL Management Studio.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

public partial class StoredProcedures { [SqlProcedure] public static void MyStoredProcedure(... input parameters ...) { try { using (SqlConnection connection = new SqlConnection("context connection=true")) { connection.Open(); // do some stuff here to get your data // do some other stuff to send your data } } catch (Exception ex) { if (SqlContext.Pipe.IsSendingResults) SqlContext.Pipe.SendResultsEnd(); SqlContext.Pipe.Send(ex.ToString()); if (ex.InnerException != null) SqlContext.Pipe.Send(ex.InnerException.ToString()); } } }

One thing to make note of is that if you want your stored procedure to take in parameters, they must be a Sql CLR type, such as SqlBoolean or SqlString.

Once we had the data retrieve and combined, I began the work of generating the SQL output. This was the most complex part and took a lot of trial and error to get correct. You send SqlDataRecords back to the SqlContext.Pipe but you first must define the SqlMetaData (which essentially defines the output columns). A quick helper method allowed me to quickly build these records.

1 2 3 4 5 6

public static SqlMetaData CreateMetaData(string name, SqlDbType dbType = SqlDbType.VarChar, int maxLength = 0) { return StringDbTypes.Contains(dbType) ? new SqlMetaData(name, dbType, maxLength) : new SqlMetaData(name, dbType); }

And then you build a list of the SqlMetaData objects to pass into the SqlDataRecord constructor as below and then send it to the pipe.

1 2 3 4 5 6

var metaDataList = new List<SqlMetaData>(); metaDataList.Add(SqlClrHelper.CreateMetaData("Field1", SqlDbType.VarChar, 50)); metaDataList.Add(SqlClrHelper.CreateMetaData("Field2", SqlDbType.Int)); SqlDataRecord record = new SqlDataRecord(metaDataList.ToArray()); SqlContext.Pipe.SendResultsStart(record);

After you have build the record and sent it to the SqlContext.Pipe, you can begin sending your data rows. You can loop this, you just have to send each record individually to the pipe. Note that the Set methods only use numerical indexes and they must line up perfectly with the SqlMetaData and data types.

1 2 3

record.SetString(0, "My Value"); record.SetInt32(1, 250); SqlContext.Pipe.SendResultsRow(record);

When you have completed pushing the data rows to the SqlContext.Pipe, simply call "SqlContext.Pipe.SendResultsEnd();" to end transmission. That call should probably be the last thing your stored procedure function does (other than perhaps error handling). Note, that at the top in my catch block I checked if the pipe was in the process of sending results and if it was I ended it so that I could send the exception details. This is required because you can't call "Send" on the pipe if you have already called "SendResultsStart" without calling "SendResultsEnd".

For development of this I simply put my code into a SQL Data Project in VS2013. It allowed me to utilize the schema compare and publish features easily and seemed like a good way to organize any CLR code alongside the SQL code. You can also write unit tests on the code like you would any other C# code, which is a powerful feature that I like a lot.

One final note is that certain editions of SQL Server only support certain .NET framework versions. SQL Server 2008 (and R2) uses version 2.0 of the CLR (Common Language Runtime) which means you can use up to the .NET Framework version 3.5. If you are doing it in a SQL Server Data Project like I am, then you can do so in the project properties (SQLCLR tab). SQL Server 2012 supports the 4.0 version of the CLR (which lets you use .NET Frameworks up through 4.5).

You can read more about all of the topics I discussed here: SQL Server CLR Integration

Well, that about covers it. Happy coding and thanks!