Musings on extended stored procedures and the cloud

(The term “extended stored procedure” as used herein is shorthand for the general technique, available in many modern RDBMSes including Microsoft SQL Server, Oracle, MySQL et al., of linking in compiled code written in non-SQL languages for invocation as stored procedures, functions, and the like.)

I recently ran into a newfangled version of DLL hell (an example support post; there are many). The basic issue was that in a SQL Server CLR stored procedure of ours, the production host machines had a different version of some .NET assemblies in the GAC, or Global Assembly Cache. It was a platform assembly causing the issue, in this case resolved by running the following statement in each affected database:

ALTER ASSEMBLY [System.Runtime.Serialization] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll'


While prompted by security measures in Microsoft’s solution, this is not a very elegant or maintenance-free approach. Imagine a future patch bringing down production systems due to a newer version of a DLL suddenly included in GAC, for instance. The cloud makes things even more complex, since having to include even platform DLLs with lowered-safety permissions may make an application difficult or impossible to deploy in a cloud implementation such as Amazon’s RDS.

Unfortunately, there are some highly useful third-party libraries which may in a naive implementation require such extra problematic configuration, such as JSON.NET . Due to understandable security concerns with linking in unvetted code, most modern RDBMSes supporting extended stored procedures will make one jump through extra hoops to use any code which makes system-level calls, can perform I/O, and the like.

Problems like this can be avoided (while still reaping the benefits of stored procedures) with approaches like the following, each with pros and cons:

1. Avoid linking in general utility libraries with references to unsafe code.

2. Create a safe version of reusable utility code for inclusion in heightended-security containers and environments. Store this in a separate project or other compilable unit from the unsafe reusable code.

3. Investigate the use of data-platform features instead of potentially unsafe program code. For example, SQL Server 2016 includes certain features for working with JSON data that might make a library like JSON.NET largely unnecessary in a particular situation.

4. Find safer alternatives to popular libraries. To tack on to the JSON example, small, fast JSON parsers could avoid the need for something like JSON.NET, and even give performance enhancements to boot.

5. If all else fails, consider importing an unsafe library into your codebase and stripping out unsafe calls. This has the obvious drawback of increased code maintenance in case of any necessary patches, but could work for a stable, mature library. Freezing a version in this manner might actually improve the long-term stability of a dependent codebase in some cases.

Of course, this is all really another facet of planning for the cloud. In my current organization, we’re shifting away from using proprietary software like SQL Server in favor of open-source alternatives like MySQL, MariaDB, and MongDB partly for licensing reasons, but just as much for the benefits of broad cross-cloud support. Growing pains for companies shifting more systems to the cloud can be minimized with a shared approach, which explicitly plans a set of reusable, cloud-safe libraries from the start.

Discover all non-abstract types implementing an interface in C#

It’s sometimes useful to auto-discover types in C# and other high-level programming languages, even outside the context of coding an IoC container. In C#, robust type-discovery mechanisms make this easy. Here’s a basic formula for auto-discovery of types implementing an interface:

1. Get the list of assemblies in the application domain.
2. Get the types from each discovered assembly.
3. Test each type to see whether it implements the target interface.

This basic formula can be accomplished in a snap using Linq:

var targetType = typeof(someInterface);
var types =
    .SelectMany(a => a.GetTypes())
    .Where(t => t.GetInterfaces().Contains(targetType) && t.IsClass && !t.IsAbstract)
    .OrderBy(t => t.FullName);

Note that t.IsClass (or !t.IsInterface) in the above expression excludes the interface itself, as well as derived interfaces. The .IsAbstract check can be skipped if the goal is to discover all class types for the interface, not just those that can actually be instantiated. Lastly, the basic approach is similar but the calls not identical for .NET Core.