Code “Re-Use” in SQL Server .NET Stored Procedures is Overrated

When it became possible, beginning with the release of SQL Server 2005, to write stored procedures, user-defined functions and similar database objects using .NET CLR-hosted languages like C# and VB.NET, I thought it was interesting but somewhat of a yawn.  After all, Microsoft still was selling T-SQL as the language of choice for most such work, and rightly so.  Even if you write a stored procedure in C#, you still have to perform the actual queries, inserts and updates in SQL, which means you have to know SQL.  This means that the need to learn SQL and set-oriented thinking is by no means obviated.

If SQL Server-land is like China, then being able to use a CLR-hosted language is like not having to learn Chinese.  Except that when you need to use an actual verb, you still have to use Chinese verbs anyway.  So you might as well just learn Chinese.  I never “got” what was compelling about SQL Server hosting the CLR.

I did note another selling point, though, which was that using a CLR-hosted language would provide code reuse opportunities.  I thought, well, maybe that will come in handy someday.  And certainly, having the entire CLR at your disposal might be useful for computationally or logically complex work.

I thought that someday had finally arrived last week.  I was working on a client’s database and needed to normalize part of their schema.  I thought a great way to do this was to replace the old table with an equivalent view and write a bunch of INSTEAD OF triggers to manage the updates to that view until we could wean all the client code off of the view and onto the underlying tables.

An issue I encountered was that one of the fields in the old table was a contrived search key, part of a proprietary fuzzy search methodology, that had been built entirely by client code written mostly in VB.NET with a little C# thrown in.  In other words, the values in that field were 100% determined outside of SQL Server.  But now I needed a way to generate that key within the INSTEAD OF triggers.

No problem, I says to myself, says I.  I’ll just spin up a SQL Server project and create a user defined function that exposes the already-finished function that handles generating that key, and — voila! — I’ll have solved the problem in five minutes.

Well, okay — I’m an old hand at this so I knew that in reality I’d need five hours to cover all the first-time overhead of doing a New Thing and making sure that it was done right without unwanted side effects, and all that.  But still — I had a legitimate use for this shiny “old” feature that had been around for upwards of six years now.  I don’t use new toys for their own sake, but it’s nice to have choices, right?  And since SQL Server had been through one major version update since the feature was released, and Visual Studio had been through two updates, it would have the added benefit of being a mature feature.

Well, Pilgrim, I’m here to tell you that CLR-hosted database logic is even less compelling than I thought.  Let me count the ways:

• You have to house all the .NET code in a special Visual Studio project type called a SQL Server project.
• In the resulting SQL Server solution, you can only add references to other SQL Server projects and a very limited number of namespaces.
• All methods callable from SQL Server as a stored procedure, UDF or whatever must be declared static (C#) or shared (VB).

Now let those three limitations sink in for a minute.  Make sure you’re sitting down when you do it.

Thanks.  Now let’s proceed to think through the implications for my particular case.

The original code lives in a class that must be instanced.  I can’t reference the original project anyway, because it’s not a SQL Server project, so I can’t get at the existing code in that way.

I can add the original source code files as items referenced by the new SQL Server project (and reference the C# part of the code in a separate SQL Server project of its own).  But aside from this needless awkwardness, which is already raising red flags for me, this exposes the next problem.  The existing code base has its own dependencies, including a dependency on System.Configuration (one of the many namespaces that a SQL Server project is prohibited from referencing) and uses connection strings housed in its own .config file.

Now I have a choice.  I can rewrite the original code in such a way that it can run from either the original project / solution or the new one, but that will make it seriously strange code from the perspective of one (and maybe even both) of those projects.  Basically, it’s a conflict of best practices, even if I could pull it off.  And it’s a non-straightforward organization of code that’s going to be hard to explain to other developers who work on the project in the future.

What if the original code referenced a third party library?  That’s likely a DLL reference, which as far as I can tell, is also verboten in a SQL Server project.

A SQL Server project is in essence a kind of sandbox designed to make it a known quantity to the hosting environment.  But code reuse?  In whose universe?

I ended up in this case with the bane of every developer’s professionalism, copy-and-paste.  I now have an instant new 300 line code base in order to capture all the original code and its dependencies to make this one function do its thing within the SQL Server environment.

Sure, the copy and modify process took only a few minutes plus a new regression test cycle.   Calls to utility functions were moved into the same library and I was fortunate that all the methods could be declared Shared.  But this new code base will have to be maintained in parallel with the original library — or, alternatively, I can go through some awkward contortions to incorporate the new SQL Server project into the original solution and delegate most of the responsibility for this function to the SQL Server project.  Assuming that’s even possible; I don’t know what “gotchas” I’ll run into if I attempt that.

This claim of “code reuse” reminds me a little bit of the claim, back in the days of VB6 and before, that Visual Basic was “object oriented”.  That was true enough, if you could concoct a definition of “object oriented” that didn’t include inheritance or polymorphism.  It was a true claim in the limited sense that there were supplied objects that had properties, but they were really talking about composition, not inheritance.  Similarly, in this case, we are talking about the ability to reuse existing code by copying it, but not in the true sense of literally leveraging existing project artifacts without change, which is the whole point of code reuse.

With every passing year, my instincts about these things are validated.  I was hoping to be pleasantly surprised at my wrongness this time around, but sadly, it was not to be.