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.

Combine approaches for easy, lightweight JavaScript user information detection

Basic browser/user detection can be important for providing an enhanced user experience. For example, making a best-guess detection of the user’s IP address (subject to the usual caveats about VPN use) can help in pre-selecting a user’s country of origin, enabling faster form fill-outs. The user’s preferred language, especially on first access of a site, is obviously of huge importance; detecting it early with a non-intrusive script can help avoid clunkier approaches such as passing out language-differentiated URLs to globalized versions of your site, which may be passed between users with unintended consequences.

Especially for a site which already makes use of JavaScript, an unobtrusive script can make a difference. And while there are fairly robust libraries such as platform.js, they often do not address all concerns of detecting language, geoIP, and browser. Adding extra dependencies to a site, in terms of maintenance burden and JS payload, is desirable to avoid when possible.

It’s easy enough to create a tiny script which merges freely available geoIP with basic browser/language detection, as shown below. There are many free geoIP web services available these days; a first implementation can simply rely on one, perhaps adding a fallback as necessary, though eventually a more robust solution may become necessary.

The simple demo code, with browser and OS versions not yet filled in, appears below, and may be run at JSFiddle . This could, for example, be passed as a JSON bundle in a call to a web service, or submitted with a page. While the code may be reused freely, as it essentially cobbles together multiple simple well-known methods into an easy-to-use package, the emphasis is on the approach: a lightweight script that avoids heavy dependencies and still captures useful information about a user. As always, use such information responsibly, especially keeping in mind GDPR regulations which will have taken full effect by May 2018.

function UserInfo() {
  var self = this;
  this.language = ((navigator.languages ? navigator.languages[0] : (navigator.language || navigator.userLanguage)) + '').substring(0, 2);
  this.browserName = '';
  this.browserVersion = '';
  this.osName = '';
  this.osVersion = ''; = '';
  this.ipAddress = ''; = '';
  this.region = '';
  this.postalCode = '';
  if (navigator && navigator.userAgent) {
    var ua = (navigator.userAgent + '').toLowerCase();
    var platform = ua;
    var i = platform.indexOf('(');
    if (i > -1)
      platform = platform.substring(i + 1);

    i = platform.indexOf(')');
    if (i > -1)
      platform = platform.substring(0, i);
    if (platform.indexOf('windows') > -1)
      this.osName = 'Windows';
    else if (platform.indexOf('android') > -1)
      this.osName = 'Android';
    else if (platform.indexOf('macintosh') > -1 || platform.indexOf('macos') > -1)
      this.osName = 'macOS';
    else if (platform.indexOf('iphone') > -1 || platform.indexOf('ipad') > -1)
      this.osName = 'iOS';

  if (!! && !!
    this.browserName = 'Chrome';
  else if (typeof InstallTrigger !== 'undefined')
    this.browserName = 'Firefox';
  else if (/*@cc_on!@*/false || !!document.documentMode)
    this.browserName = 'IE';
  else if (/constructor/i.test(window.HTMLElement) || (function (p) { return p.toString() === "[object SafariRemoteNotification]"; })(!window['safari'] || safari.pushNotification))
    this.browserName = 'Safari';
  else if ((!!window.opr && !!opr.addons) || !!window.opera || navigator.userAgent.indexOf(' OPR/') >= 0)
    this.browserName = 'Opera';
  else if (!!window.StyleMedia)
    this.browserName = 'Edge';
try {
    $.getJSON('//', function(data) {
      self.ipAddress = data.ip + ''; = + ''; = + '';      
} catch (err) {}

  this.ids = []; 
  this.addID = function(sn, st, idt, i) {
    var newID = {};
    newID.systemName = sn + '';
    newID.systemType = st + '';
    newID.idType = idt + ''; = i + '';      


The minified version is relatively compact, at 1560 characters:

function UserInfo(){var i=this;if(this.language=((navigator.languages?navigator.languages[0]:navigator.language||navigator.userLanguage)+"").substring(0,2),this.browserName="",this.browserVersion="",this.osName="",this.osVersion="","",this.ipAddress="","",this.region="",this.postalCode="",navigator&&navigator.userAgent){var e=(navigator.userAgent+"").toLowerCase(),o=e.indexOf("(");o>-1&&(e=e.substring(o+1)),(o=e.indexOf(")"))>-1&&(e=e.substring(0,o)),e.indexOf("windows")>-1?this.osName="Windows":e.indexOf("anddroid")>-1?this.osName="Android":e.indexOf("macintosh")>-1||e.indexOf("macos")>-1?this.osName="macOS":(e.indexOf("iphone")>-1||e.indexOf("ipad")>-1)&&(this.osName="iOS")}"Chrome":"undefined"!=typeof InstallTrigger?this.browserName="Firefox":document.documentMode?this.browserName="IE":/constructor/i.test(window.HTMLElement)||"[object SafariRemoteNotification]"===(!window.safari||safari.pushNotification).toString()?this.browserName="Safari":window.opr&&opr.addons||window.opera||navigator.userAgent.indexOf(" OPR/")>=0?this.browserName="Opera":window.StyleMedia?this.browserName="Edge":(isChrome||isOpera)&&window.CSS&&(this.browserName="Blink");try{$.getJSON("//",function(e){i.ipAddress=e.ip+"","",i.region=e.region_code+"","",i.postalCode=e.zip_code+""})}catch(i){}this.ids=[],this.addID=function(i,e,o,s){var t={};t.systemName=i+"",t.systemType=e+"",t.idType=o+"","",this.ids.push(t)}}


JavaScript: to prototype or not?

A recurring topic of some discussion among JavaScript users is whether to use prototype or instance/constructor-inner methods when defining JavaScript objects.

Prototype methods can be incredibly useful, but are in essence much like C# extension methods (which are incredibly useful for similar reasons). The primary use of both is to add functionality to a class/object type over which one does not have control. In this way, both C# extension methods and JavaScript prototype methods can even add functionality to foundational platform types in each language or environment. (Proponents of using prototype methods for everything sometimes point out that there is a small memory-use difference between prototype and non-prototype functions, but that difference is completely negligible for client-side development.)

Methods defined inside an object constructor, directly on “this”, have a major advantage in correct object-oriented design, however. That’s because methods inside the constructor can also access private properties/variables defined inside the constructor. Prototype methods cannot do this, as this example clearly demonstrates. A minor additional benefit is that instance methods defined this way are contained inside the constructor, a more object-oriented style than the extension-method feel of prototype methods.

This difference means that constructor-inner methods are the only correct choice for constructing object-oriented JavaScript. The alternate, using prototype methods only, would be akin to designing a C# or Java class with no private fields or properties, a clear violation of the encapsulation principle.

It’s clear that at least for client-side development, prototype methods may be useful to decorate a pre-existing API–but when designing one’s own object-oriented JavaScript, there is only one appropriate choice.

Scale text with a minimum size using pure CSS

Creating a responsive design is best done without unnecessary reliance on toolkits, frameworks and the like, especially where basic formatting can be controlled by CSS instead of JavaScript. The set of tools in CSS3 is of course limited compared to that in JavaScript, but one aspect of responsive design–scaling text–can often by performed satisfactorily in pure CSS.

One part of the puzzle is to scale text smoothly according to the size of the viewport. For this, the oft-overlooked vw unit can work wonders. However, without a min-font-size property available in CSS3, simply scaling to zero wouldn’t work in a wide range of situations, and a pure percentage-based scaling without a foundation might also not scale appropriately. In general, for usability purposes it’s best to use the smallest size of text that satisfies both the readability requirement and relative size requirement at the smallest viewport size, and scale up from there.

Luckily, by using the CSS calc() function, we can supply a floor value for font sizes and scale up from there. This provides a functional equivalent for the missing min-font-size, since the vw-based component of the calculated size can safely go to zero, approaching the limit of the constant base size. Using this approach, one might create some classes to apply standard font sizes as in the following, apply them to heading tags, etc.

Try it for yourself! Note that the linked demo includes a moderate amount of viewport height in the calculation as well, to theoretically better fill a proposed space, and also includes the viewport height in scaling the line height for each font size (again above a safe minimum).

.fontSizeXXSmallScaled { font-size:calc(.8em + 1vw); }
.fontSizeXSmallScaled { font-size:calc(.8em + 1.2vw); }
.fontSizeSmallScaled { font-size:calc(.9em + 1.4vw); }
.fontSizeMediumScaled { font-size:calc(1em + 1.5vw); }
.fontSizeLargeScaled { font-size:calc(1.25em + 1.6vw); }
.fontSizeXLargeScaled { font-size:calc(1.5em + 1.7vw); }
.fontSizeXXLargeScaled { font-size:calc(1.75em + 1.8vw); }

Dynamically resize elements sans JavaScript with the help of clutter-free CSS

I’m a big fan of code, including CSS, that’s as easy to use as possible. I’m not a fan of using certain large frameworks such as Bootstrap just to get responsive design onto a site, when something much smaller and simpler will often do. With a few CSS tricks, it’s possible to create CSS containers that will behave responsibly at your chosen viewport break-points, but that can be stripped down to a single class added to a container in many cases.

To that end, observe the following example, which applies table-style formatting at a large/desktop screen size wraps items into a grid-style layout at a medium size, with configurable options for numbers of columns at different numbers of child items; and finally collapses to an item width of 100%. You may also try it for yourself.

* { box-sizing: border-box }

.columnContainer:after {
   content: " ";
   visibility: hidden;
   display: block;
   height: 0;
   clear: both;

@media all and ( min-width: 768px ) {

  .columnContainer { 
    display: table;
    width: 100%;
  .columnContainer > div { 
    display: table-cell;


@media all and ( max-width: 767px ) and ( min-width: 481px ) {

  .columnContainer { 
     width: 100%;

  .columnContainer > div {
    float: left;
    width: 100%;

  .columnContainer > div:first-child:nth-last-child(2), .columnContainer > div:first-child:nth-last-child(2) ~ div {  width: 50%;  } 

  .columnContainer > div:first-child:nth-last-child(3), .columnContainer > div:first-child:nth-last-child(3) ~ div {  width: 33.3333%;  } 

  .columnContainer > div:first-child:nth-last-child(4), .columnContainer > div:first-child:nth-last-child(4) ~ div {  width: 50%;  } 
  .columnContainer > div:first-child:nth-last-child(4) ~ div:nth-child(2n+1) { clear: both !important; } 

  .columnContainer > div:first-child:nth-last-child(5), .columnContainer > div:first-child:nth-last-child(5) ~ div {  width: 20%; } 

  .columnContainer > div:first-child:nth-last-child(6), .columnContainer > div:first-child:nth-last-child(6) ~ div {  width: 33.3333%;  } 
  .columnContainer > div:first-child:nth-last-child(6) ~ div:nth-child(3n+1) { clear: both; } 
  .columnContainer > div:first-child:nth-last-child(7), .columnContainer > div:first-child:nth-last-child(7) ~ div {  width: 14.2857%; } 

  .columnContainer > div:first-child:nth-last-child(8), .columnContainer > div:first-child:nth-last-child(8) ~ div {  width: 25%;  } 
  .columnContainer > div:first-child:nth-last-child(8) ~ div:nth-child(4n+1) { clear: both !important; } 

  .columnContainer > div:first-child:nth-last-child(9), .columnContainer > div:first-child:nth-last-child(9) ~ div {  width: 33%;  } 
  .columnContainer > div:first-child:nth-last-child(9) ~ div:nth-child(3n+1) { clear: both !important; } 
  .columnContainer > div:first-child:nth-last-child(10), .columnContainer > div:first-child:nth-last-child(10) ~ div {  width: 20%;  } 
  .columnContainer > div:first-child:nth-last-child(10) ~ div:nth-child(5n+1) { clear: both !important; } 

  .columnContainer > div:first-child:nth-last-child(11), .columnContainer > div:first-child:nth-last-child(11) ~ div {  width: 9.0909%; } 

  .columnContainer > div:first-child:nth-last-child(12), .columnContainer > div:first-child:nth-last-child(12) ~ div {  width: 25%;  } 
  .columnContainer > div:first-child:nth-last-child(12) ~ div:nth-child(4n+1) { clear: both !important; } 


@media all and ( max-width: 480px ) {
  .columnContainer > div { width: 100% }

This implementation depends on some CSS3 features working in tandem. At the medium viewport size, by combining the :first-child and :nth-last-child(n) (with following siblings) selectors in tandem, the widths and wrapping for child items can be set specifically based on the number of child items. To create a new row, the nth child is then selected for clearing. An implementation without using floats is also possible; the main technique here is the selection of different layout techniques based on the number of children.

Once this has been implemented, adding a single class, in this case “columnContainer”, on a parent element will hook all this up so it just works. It would also be possible to combine this technique, of course, with named custom classes that applied different widths to columns and the like.

Applying shadow overlays with pure CSS

Shadow or darkening overlays can be useful for providing contrast between a container’s background and content, as well as providing an easy way to differentiate presentation for button mouseovers and similar situations. Older methods of providing such useful inner shadows relied on using an extra div or other container, to which one would apply a color and opacity. However, there’s a better way.

The desired effect can be achieved using two related tricks: the now well-supported opacity feature of rgba colors, as well as the “inset” property of the CSS3 box shadow which applies the box shadow inside an element. The trick is to set the shadow width to greater than half the size of the element (any greater size will do), to ensure that the shadow covers the entire element. Using this approach, one might specify the following to achieve a 50% shading:

box-shadow: rgba(0, 0, 0, .5) 0 0 0 1000000px inset;

Try it for yourself.

Dynamically adding JavaScript and CSS using a resource-tracking object type

This is a quick demonstration of a few techniques in basic JavaScript: creating a custom JavaScript object type with properties and methods; dynamically adding both JavaScript and CSS references in a cross-browser-safe way; adding JavaScript async; and adding inline JavaScript and CSS. The resource loader tracks added resources, to ensure that they are loaded only once. Try it for yourself (open your browser’s developer tools to see logging output).

// A JavaScript type which manages references to scripts and stylesheets
function ResourceManager() {
  var addedReferences = {};
  var addedResources = {};
  this.isLoggingEnabled = false;
  this.referenceExists = function(url) {
  	if (url in addedReferences) {
    	this.log('Resource ' + url + ' already added');
      return true;
    else return false;
  this.resourceExists = function(text) {
  	if (text in addedResources) {
    	this.log('Text resource already added');
      return true;
    else return false;
  this.addScriptReference = function(url, addAsync, addDeferred) {
    if (typeof(url) == 'undefined' || this.referenceExists(url)) return;
    this.log('Adding script reference: ' + url + (addAsync ? ' [async]' : '') + (addAsync ? ' [deferred]' : ''));
    var script = document.createElement('script');
    script.src = url;
    if (addAsync) script.async = true;
    if (addDeferred) script.defer = true;
    addedReferences[url] = url;
  this.addScript = function(text) {
    if (typeof(text) == 'undefined' || this.resourceExists(text)) return;
    this.log('Adding script');
    var script = document.createElement('script');
    addedResources[text] = text;
  this.addStyleSheetReference = function(url) {
    if (typeof(url) == 'undefined' || this.referenceExists(url)) return;
    this.log('Adding script reference: ' + url + (addAsync ? ' [async]' : '') + (addAsync ? ' [deferred]' : ''));
    var ss = document.createElement('link');
    ss.rel = 'stylesheet';
    ss.type = 'text/css';
    ss.href = url;
    addedReferences[url] = url;
  this.addStyleSheet = function(text) {
    if (typeof(text) == 'undefined' || this.resourceExists(text)) return;
    this.log('Adding style sheet');
    var ss = document.createElement('style');
    ss.type = 'text/css';
    if (ss.styleSheet){
      ss.styleSheet.cssText = text;
    } else {
    addedResources[text] = text;
  this.log = function(message) { if (this.isLoggingEnabled) console.log(message); };

Selecting unthemed HTML descendant elements using CSS :not()

In my recent work, I’ve been constructing a CSS framework that needs to support multiple themes: the ability to override many aspects of display formatting by applying a CSS class to a parent element, affecting all descendants. However, in order to make this work properly in actual practice, it’s may be desirable to apply default theming rules to even children without an ancestor theme-tagged element. (In my situation, this requirement applies because the mentioned CSS framework will be used to support microsites, which should be presented with a “good” look and feel even if the implementation of the site has been somewhat sloppy.)

The CSS :not() selector works well for this, as implemented in CSS3:

:not([class*=Theme]) * {
  // Sample selecting all unthemed elements, where themes are applied with *Theme class names on ancestors

This can easily, of course, be applied with different descendant selectors. Try the example.

Easily execute dynamic C# using extension methods

Code has now been released under the SharpByte project to execute dynamic C# scripts (and evaluate statements) more easily than ever before. Dynamic code execution during earlier days of .NET was a sore spot, with many lamenting the lack of a functional equivalent to the JavaScript eval() function. For years many developers attempted hacks like using ASP.NET’s DataBinder.Eval(), but results were often subpar and performance was lackluster. Compiling to the CodeDom and the newer .NET Compiler Platform, a.k.a. Roslyn, can be moderately simple to complex depending on need, but many developers just want a simple, easy-to-reuse solution for supporting dynamic code entry in an application.

Further documentation on the easy-to-use API will be forthcoming, but for now these steps will suffice for anyone wishing to play with the code:

1. Either build and reference the project’s core assembly in your project, or import the code directly into your project.

2. If the code was built with conditional compilation symbol GLOBAL_EXTENSIONS, all objects will be able to use the dynamic-execution extension methods. Otherwise, if COLOCATE_EXTENSIONS was used, add a using statement for the System.Runtime.CompilerServices namespace; if neither GLOBAL_EXTENSIONS nor COLOCATE_EXTENSIONS was used, add a using statement for the SharpByte.Dynamic namespace.

3. Call any version of .Execute() or .Evaluate() directly on any object. The former will execute any C#-compliant script composed of properly semicolon-terminated lines of code, with “this” references executed on the object on which the extension methods are called (i.e. the context object for the call); the latter will evaluate a C# expression and return the result.

Once these steps are done, calling a script is as easy as this:

someObject.Execute("[script statements go here, and may be many lines]");

To anyone curious enough to understand the working of these extension methods, the code will be illustrative. Essentially, the extension methods call into a facade for compilation features of the .NET framework, and can be used to front-end calls to the CodeDom, the .NET Compiler Platform (“Roslyn”), or any other compiler, vastly simplifying the most-needed dynamic code compilation and execution features of each.

Here’s an example of the relatively complex task of building code using the CodeDom (without any attempt to slam the useful-looking code at this page, just provide an example of the complexity hidden away). Roslyn provides many enhancements over CodeDom, but still to simply execute a script, such as user-entered code in a CMS or other data system, isn’t always completely simplified as it could be.

The provided reference code compiles code constructed on-the-fly using the referenced compiler. A code formatter emits source code, without needing any special knowledge of the underlying compiler. A quick review of the System.Object extension methods involved shows how easy it is to retain a reference to the compiled IExecutable instance as well, which can be used to inspect the built-in execution log and timings, as well as any exception generated by the last run of a compiled executable. A unique signature based on the executable code type (expression/statement or script), parameter names, and source code is used on subsequent calls to check for pre-existing compiled executables, stored in the ExecutableFactory hybrid factory/collection for reuse.

Each executable can be compiled successfully with numbered placeholder values, a la string.Format() (but using triple curly braces to avoid .NET and Handlebars-style format interference) and/or named parameter values. As mentioned above, the context object (when using extension methods, the object on which the method is called) is used for any references to “this” within any script or expression.

Since an object from each compiled executable type has a Copy() method, it can safely and cheaply be used to create further executables of the same type. Calling Execute() on any particular executable is guaranteed to be thread-safe due to use of synchronization; for that reason, it’s easiest to cache local copies of reusable expressions/scripts.

Performance-wise, on a fairly low-spec laptop, formatting source code for a new class tests in the 1-2 microsecond range; post-compilation, execution of a script or expression can take well under a microsecond (e.g. a two-parameter complex mathematical expression which tests in the 800-nanosecond range), depending of course on complexity. The bulk of this fairly small overhead is due to the use of dynamic variables within the compiled classes themselves. If warranted, type safety may be added to the context object and/or named parameters to boost peformance further.

This generic utility code was originally developed in support of the SharpByte CMS, but is provided separately under the MIT License. Happy coding!