Programming Is Problem Solving
A number of years ago, as our programming staff started getting into deeper and deeper technical problems, we bantered around the idea of starting a blog about the solutions. It would be part sharing information and part storing it so we could look up the solution when the same problem arose years down the road and we couldn't remember how we solved it.
Like so many things people talk about in the workplace, that idea sounded great at the time, but was ultimately abandoned due to time constraints. Well, that and the fact that we moved onto other problems and forget the past problems, let alone the solutions.
This week, I decided to resurrect the idea because of a couple new things that have popped up that I likely won't have the opportunity to explore again for quite some time. Most notably, I finally had the chance to implement a production version of an SQL Server CLR stored procedure.
For those unfamiliar with SQL Server CLR stored procedures, they're .NET code compiled into a DLL and executed within SQL Server. Regular stored procedures in SQL Server rely on SQL code to retrieve results or alter existing data. They're powerful tools when they're done correctly and - as an added bonus - are easy to write and maintain. The downside to traditional SQL stored procedures lies in the fact that they're still built off the structured query language. CLR stored procedures are written in a .NET language (C#, in my case). C# allows a lot more flexibility in manipulating result sets than SQL does. Most of the time, SQL works brilliantly, but every now and then a problem arises that needs a touch you can only get from a more full-featured language.
The case in point was a report for a client. The data for the report was scattered across a handful of tables and a simple join put it all together. That was fine save for one minor detail: we were storing information for a program, not a human. To make things human readable, I had to take a long string of train numbers and corridor IDs and swap the IDs for names. So, instead of x^1,y^2,z^3, I needed be able to generate x on corA, y on corB, and c on cor3.
The data string was built the way it was because splitting strings in C# is a trivial affair. So, to cut down on data transfer into a GTFS service, we went with a minimal data footprint. Besides, no one really expected anyone would want a report on that part of the data. There are ways to split strings in SQL Server, but it's not really one of the things SQL excels at and, in this case, the whole string needed to be split on the commas and then each piece needed to be split on the carats. From there, the second piece in the array needed to be looked up and modified. Then the whole thing could be reassembled and returned with the result set where it would be rendered into an easy-to-read report.
My first cut at the problem was a traditional stored procedure, which worked nicely except for the aforementioned part. Now, it actually is possible to split strings in SQL, but it's something of a tedious affair and it requires some finagling to get around the issue of not being able to create side-effecting operators in user-defined functions because string splitters usually work with temp tables.
Rather than spin my wheels fighting with functions, I rewrote the whole thing as an SQL CLR stored procedure. CLR allowed me to treat the data as objects and manipulate it like any of list of objects, including taking advantage of C#'s native string splitters. It took a little time to spin up on the CLR part of writing CLR procedures, but in less than a day, I had my solution written, tested, and deployed. And it let me leverage my existing knowledge of C#.
CLR procedures leverage ADO.NET techniques and allow you to work with object-oriented code. If you're familiar with ADO.NET, you're golden. If you can juggle lists of objects, you're even better off. They're not necessarily recommended for simple tasks and can take longer to implement (my average stored proc takes about half an hour to write, unless it's tiny), but the trade off is power.
As with all things in the programming world, you have to look at the pros and cons of doing something. Sure, a CLR procedure is going to be more difficult to update and can take longer to write, but they expose .NET languages. On the other hand, SQL stored procedures are usually quick and easy to write, but are more difficult to implement for complicated query processing.
There is probably someone out there who could have done the whole thing in SQL. Given enough time, I probably could have figured out a solution, too. The thing is, though, programming is problem solving and it doesn't always matter how we get to the final result, only that we do get there. The more tools and techniques you have available to you, the better the chance you'll solve the problem, and the better the chance you'll have of solving it in a timely manner.
As for me, I don't know that I'll come across a problem that requires SQL CLR stored procedures again, but that technology certainly helped out on this one.
For more information on CLR Stored Procedures, check out MSDN's documentation on them.