April 18, 2014

MSSQL Statistical Z-Score

Computing the z-score of individual values in SQL Server is not a built in function (although average and standard deviation are). The z-score tells us how far off from the average value the individual values are.
The following function computes the z-score: for a table named [data] having an [objectId] and [score] fields.

SELECT
    [D].[objectId],
    [D].[score],
    [E].[avg_score],
    [E].[stdev_score],
    ([D].[score] - [E].[avg_score]) / [E].[stdev_score] as zvalue
FROM
    [data] D
    CROSS JOIN
    (SELECT
        Avg(CAST([score] as float)) as [avg_score],
        StDevP([score]) as [stdev_score]
    FROM [data]
    ) E
;

April 17, 2014

Bootstrap Menus and EmberJS

I love Bootstrap. Mostly because I'm not a very good graphic designer; but also because I'm lazy. I like the things it does for me that make my websites suck less. One of those things is the Bootstrap menu.
One of the nice features of the Bootstrap menu is that you get a gradient highlight when the menu has a "selected" css class applied to it.

I am looking at EmberJS. Ember has lots of nifty features as well. As a Single Page Application (SPA) framework, it likes to handle URL routing, and does something especially nifty with links that it manages: It applies a "selected" class to the current URL.

Did the stars just align? Is this too good to be true? "Um, yea. Why?"

Well, they almost align. It turns out that Bootstrap wants to have a "selected" css class applied to the list item that contains the anchor tag for the menu item while Ember's linkTo helper applies it directly to the anchor tag. Ouch.

In other words, this ember code doesn't work
<li>{{#linkTo "home"}}home{{/linkto}}</li>
What you end up with is html that looks like this
<li><a href='#home' class='selected'>home</a></li>
Unless we want to change bootstrap (again, did I mention that I'm lazy), what we really want is something that looks like this
<li class='selected'><a href='#home'>home</a></li>
Well, it turns out that the ember linkTo block helper can be manipulated to work with bootstrap.
{{#linkTo "home" tagName="li" href=false}}<a {{bindAttr href="view.href" }}>Home</a>{{/linkTo}} 
What this little gem does is override the tag name for the linkTo helper, and tell it not to have a href property. Then, inside the anchor tag, we reconstruct the link using the href property from the containing view.

April 15, 2014

Access web.config settings from your JavaScript

When you create a set of interconnected web applications, or a mobile application that accesses a web service back-end, you have different service addresses that need to make their way into your JavaScript. Visual studio has XML transformations that apply to the app.config or web.config files, depending upon your build configuration, but that doesn't do you a lot of good if your client-side JavaScript is looking for the appropriate settings.
You can isolate those settings in a single JavaScript file, but that file needs to change when you deploy to the web or the cloud.
Here is a simple solution:
Create an IHTTPHandler that reads all the application settings from your web.config file and transforms them into a constant object (named appSettings) containing all the application settings. Add the reference to this "dynamic" JavaScript in your master page template.
As a HTTP Handler, it is fast, and because IsReusable is set, it is cached too.
    /// 
    /// Summary description for EnvironmentToJavascript1
    /// 
    /// 
    ///     Include a link to the dynamically generated script to the scripts section of the web page
    ///     
    /// 
public class AppSettings : IHttpHandler
    {
        /// 
        /// Create an "appSettings" namespace in a javascript file containing all the app settings from the web.config file
        /// 
        /// 
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/javascript";
            context.Response.Write("var appSettings = {");
            foreach (var key in ConfigurationManager.AppSettings.AllKeys)
            {
                context.Response.Write(string.Format("'{0}': '{1}',", key, ConfigurationManager.AppSettings[key]));
            }
            context.Response.Write("'loaded': true };");
        }

        /// 
        /// No dynamic values here, so feel free to cache the results. 
        /// If the web.config file changes, the app pool will also reset
        /// 
        public bool IsReusable
        {
            get
            {
                return true;
            }
        }
    }

April 7, 2014

KnockoutJS, WebAPI, and TypeScript

JavaScript is not Java

As Brent so famously once said "JavaScript is not Java". My reply was "Dynamic, loosely typed, AND case sensitive...yea, that'll work". I tried to ignore JavaScript, hoping it would go away. jQuery helped a lot, but it still felt loose and dirty. Somebody out there heard my complaints. My biggest complaint with JavaScript has always been that it didn't have any concept of types, and you got very poor build-time support from development environments.

Enter TypeScript

I believe that Anders Hejlsberg would have been one of the great contributors to software developers productivity just for TurboPascal, Delphi, or C#, or .NET, but his work to modernize without breaking JavaScript through TypeScript, I predict will be far more reaching than any of the others. Add to that the fact that Microsoft has made TypeScript open source, and that it has IDE support in Eclipse and Visual Studio (among others), and you have the makings of a perfect storm.

What is TypeScript?

TypeScript lets you write JavaScript the way you really want to. TypeScript is a typed superset of JavaScript that compiles to plain JavaScript. Any browser. Any host. Any OS. Open Source.

What about KnockoutJS and WebAPI?

KnockoutJS allows you to simply create bindings to HTML content. It is not as big as Angular or Ember, but does it's job. Combine it with a nice WebAPI back end and you have (IMHO) a nice entry into single page applications that you can apply incrementally. Lots of bang for little bucks. This blog is for things that I want to remember, not necessarily for external consumption. In this case, there are a couple of tricks to get TypeScript, Knockout, jQuery, and (to a lesser extent WebAPI) to work together seamlessly. Oh, and I threw in Bootstrap just because it is awesome.

If you'd like to jump to the end, you can access the complete visual studio project from my bit bucket repository.

So, let's get started.
  1. Hook up knockoutJS to a simple web page
  2. Use TypeScript as the knockout model
  3. Create the world's simplest WebAPI service
  4. Persist changes to the web API using jQuery
What?!?! too much to read? Ok. I'll skip to the good parts: We'll just talk about the TypeScript model and UI.
This is the guts of the HTML for our mini single page application (SPA). If you are familiar with Knockout, it will look very familiar to you. The first section is an "Add student area". The second section is our "List View", and the third is our "Details Dialog". The extra styling is all bootstrap. The script at the end of the page includes the JavaScript that gets included in the next section as well as some start up code that calls our web API, retrieves the initial list of items (students) and initializes the knockout view model from the resulting values.
<div class="row">
    <div class="col-sm-6 col-sm-offset-3">
        <form role="form" data-bind="submit: addItem">
            <div class="panel panel-default">
                <div class="panel-heading">
                    <span class="panel-title">Add Student</span>
                    <button class="close" type="submit" data-bind="enable: givenNames().length > 0 && familyNames().length > 0"><span class="glyphicon glyphicon-plus"></span></button>
                </div>
                <div class="panel-body">
                    <div class="form-group">
                        <input type="text" class="form-control" placeholder="given names" data-bind='value: givenNames, valueUpdate: "afterkeydown"' />
                    </div>
                    <div class="form-group">
                        <input type="text" class="form-control" placeholder="family names" data-bind='value: familyNames, valueUpdate: "afterkeydown"' />
                    </div>
                </div>
            </div>
        </form>
    </div>
</div>

<div class="row">
    <div class="col-sm-6 col-sm-offset-3">
        <div class="panel panel-default">
            <div class="panel-heading"><span class="panel-title" data-bind="text: itemCount()"></span></div>
            <ul class="list-group" data-bind="foreach: items">
                <li class="list-group-item">
                    <span data-bind="text: GivenNames"></span> <span data-bind="    text: FamilyNames"></span>
                    <button type="button" class="close" aria-hidden="true" data-bind="click: $root.editItem" data-toggle="modal" data-target="#editItem"><span class="glyphicon glyphicon-pencil"></span></button>
                </li>
            </ul>
        </div>
    </div>
</div>

<div id="editItem" class="modal fade">
    <div class="modal-dialog modal-sm">
        <div class="modal-content" data-bind="with: editItem">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-hidden="true" data-bind="click: function (data) { $root.getItem(data); }"><span class="glyphicon glyphicon-remove"></span></button>
                <h4 class="modal-title">Edit Student</h4>
            </div>
            <div class="modal-body">
                <div class="form-group">
                    <input type="text" class="form-control" placeholder="given names" data-bind='value: GivenNames, valueUpdate: "afterkeydown"' />
                </div>
                <div class="form-group">
                    <input type="text" class="form-control" placeholder="family names" data-bind='value: FamilyNames, valueUpdate: "afterkeydown"' />
                </div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal" data-bind="click: function (data) { $root.getItem(data); }">Close</button>
                <button type="button" class="btn btn-primary" data-dismiss="modal" data-bind="click: function (data) { $root.saveItem(data); }">Save</button>
                <button type="button" class="btn btn-warning" data-dismiss="modal" data-bind="click: function (data) { $root.deleteItem(data); }">Delete</button>
            </div>
        </div>
    </div>
</div>
@section scripts{
    <script type="text/javascript" src="~/Scripts/WebService.js"></script>

    <script type="text/javascript">
        $(function () {
            $.getJSON("/api/Student", null, function (data) {
                ko.applyBindings(new WebServiceModel(data));
            });
        });
    </script>
}

Ok, now lets look at the TypeScript code for the view model.
This is where it gets interesting. The apparent comments at the top of the file actually are imported references to the other libraries used in this script. We need jQuery, Knockout, and KnockoutMapping. I imported these using NuGet. Once I'd done that, I have many strong types available to me: KnockoutObservable instead of ko.observable(), for example. I have two different models here: the Student model, and the WebServiceModel as a View Model for the whole page.
The next interesting thing we come upon is the student map. This is a knockout option that allows us to use our student id as a key so that Knockout Mapping can keep track of new and updated entities.
Now for a really important part. My item count computed observable has an optional parameter: the owner property. This property permanently maps the this function to the class we are interested in without a manual closure.
The constructor is called at start up and initializes our objects to the items list.
The jquery AJAX calls work with their HTML bindings above to make sure we have a correct reference to the WebServiceModel when the calls return. The HTML is very important because it overrides the binding that Knockout would normally apply. Inside the success callbacks, we have two options, one is to use function notation and change the context to 'this'. Using this method, when the jQuery callback returns, it remembers that the WebServiceModel was the context for our call. Without it, we would get a jQuery selector as 'this'. The other way is to use a lambda function (due for inclusion in the next version of JavaScript). A lambda function is supposed to preserve the calling context when the call returns, removing the need to create a closure. However, when TypeScript compiles to javascript, a _this reference is introduced and used as a closure. This mechanism works fine for us as well, so we don't need to change the jQuery context to preserve our 'this'. Either way works fine, it is just a matter of taste. I've coded the 'deleteItem(item)' method using this style.
///
///
///

class Student {
    Id: KnockoutObservable;
    GivenNames: KnockoutObservable;
    FamilyNames: KnockoutObservable;
}

class WebServiceModel {
    private studentMap: KnockoutMappingOptions = {
        key: d => ko.utils.unwrapObservable(d.id)
    };
    items: KnockoutObservableArray = ko.observableArray([]);
    givenNames: KnockoutObservable = ko.observable("");
    familyNames: KnockoutObservable = ko.observable("");
    editItem: KnockoutObservable = ko.observable(null);
    itemCount: KnockoutComputed = ko.computed(
        {
            owner: this,
            read: function () {
                if (typeof this.items === "undefined") return "";
                switch (this.items().length) {
                    case 0:
                        return "The list is empty";
                    case 1:
                        return "There is only one student";
                    default:
                        return "There are " + this.items().length + " students";
                }
            }
        });

    constructor(json: any) {
        ko.mapping.fromJS(json, this.studentMap, this.items);
    }

    addItem() {
        $.ajax({
            context: this,
            type: "POST",
            url: '/api/Student',
            dataType: 'json',
            data: { GivenNames: this.givenNames(), FamilyNames: this.familyNames() },
            success: function (d) {
                this.givenNames("");
                this.familyNames("");
                this.items.push(ko.mapping.fromJS(d));
            }
        });
    }

    getItem(item) {
        $.ajax({
            context: this,
            dataType: "json",
            url: "/api/Student",
            data: null,
            success: function (d) {
                ko.mapping.fromJS(d, this.studentMap, this.items);
            }
        });
    }

    saveItem(item) {
        $.ajax({
            context: this,
            type: 'PUT',
            dataType: "json",
            url: '/api/Student/' + item.Id(),
            data: ko.mapping.toJS(item),
            success: function (d) {
                ko.mapping.fromJS(d, this.studentMap, this.items);
            }
        });
    }

    deleteItem(item) {
        $.ajax({
            url: '/api/Student/' + item.Id(),
            type: 'DELETE',
            success: d =>; {
                this.items.remove(item);
            }
        });
    }
}
There are going to be a few disconnects between some libraries, TypeScript introduces its own conventions for 'compiling' to JavaScript. The real issue here was a combination of TypeScript conventions, jQuery conventions, and Knockout bindings. By understanding what each library was trying to do to work around the dirty edges of JavaScript, we were able to create a simple pattern that blends the three nicely.