Dropdown Selector with KnockoutJS and TypeScript

MVVM (Model-View-View-Model) is a useful pattern for developing xaml applications, but what about utilizing it in a website?  Two of the most popular solutions are AngularJS and KnockoutJS (http://knockoutjs.com/).  Another useful tool for those of us who prefer the type-safety and OO approach of C# as well as the comfort of writing all of our code in Visual Studio is a Microsoft product called TypeScript (http://www.typescriptlang.org/).

On a recent project, I and my teammates found ourselves presenting a dropdown in a ColorBox (https://www.nuget.org/packages/colorbox/) to the user on more than one occasion.  Of course, if you use it more than once that means it is a candidate for a generic tool.  But, how to do this with Knockout and Typescript, that was the problem.

First, the html itself:

1 2 3 4 5 6 7 8 9 10 11 12

<div id="DropDownSelector" data-bind="with: DropDownSelector"> <div data-bind="visible: Show"> <p>Select a <span data-bind="text: TypeOfChoice"></span> to proceed:</p><br /> <div> <select data-bind="options: Choices, optionsText: 'ChoiceText', value: SelectedChoice"></select> </div> <br /> <button type="button" data-bind="click: Ok">Ok</button> <button type="button" data-bind="click: Cancel">Cancel</button> </div> </div>

What does this do?  Let's analyze it a bit.

1. TypeOfChoice - A simple name of the choice object
2. Choices - An observable array of Choice objects (a simple view model that contains the choice object (typically a view model) and the choice text).
3. ChoicesText - The text that is displayed in the dropdown (if passing a list of view models, this would be the property name to use).
4. SelectedChoice - The choice from the dropdown that the user selected.
5. Show - Whether or not to display the dropdown.
6. Deferrer - A reference to the jQueryDeferred object which allows the dropdown to use the promise pattern.
7. DropDownSelectorHref - This is the html page name of the page I displayed above that the ColorBox control uses.

Alright, that details the properties of the object.  But, what about actually using it?  Well, first, here's how you would call the DropDownSelector observable that we defined:

1 2 3 4

this.DropDownSelector().PrompUserToMakeAChoice("Page", "PageName", this.Pages()) .done((page) => { // do something here });

In this example, we're passing in an observable array of view models.  It assumes that there is a property on the objects called PageName.  Then when the user selects a value from the dropdown we'll get the Page object and be able to react to the selection.

Notice we called a method on the dropdownselector called PromptUserToMakeAChoice.  Well, that method looks like:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

PromptUserToMakeAChoice = (typeOfChoice: string, choicesText: string, choices: Array<any>) => { this.SelectedChoice(null); this.TypeOfChoice(typeOfChoice); var choicesArray = new Array(); ko.utils.arrayForEach(choices, choice => { choicesArray.push(new ChoiceVm(choice, choicesText)); }); this.Choices(choicesArray); this.ChoicesText(choicesText); this.Deferrer = $.Deferred(); this.Show(true); this.ShowDialog(); return this.Deferrer; };

This method sets up the properties that the dropdown control uses including an observable array of ChoiceVm objects and then returns the jquery promise.  What is this ChoiceVm you ask?  Well, its a very simple view model:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

constructor() { this.TypeOfChoice = ko.observable(null); this.Choices = ko.observableArray([]); this.ChoicesText = ko.observable(null); this.SelectedChoice = ko.observable(null); this.Show = ko.observable(false); this.Deferrer = null; $(document).bind("cbox_complete", () => { $.colorbox.resize(); }); } Ok = () => { if (this.SelectedChoice() == null) { alert("You must select a " + this.TypeOfChoice() + "."); return; } this.Deferrer.resolve(this.SelectedChoice().Choice()); this.Close(); }; Cancel = () => { this.Deferrer.reject(); this.Close(); this.Deferrer = null; }; Close = () => { this.Show(false); $.colorbox.close(); }; ShowDialog = () => { var height = $(this.DropDownSelectorHref).height(); var colorBoxOptions = { href: this.DropDownSelectorHref, height: height, innerWidth: 300, minHeight: 100, }; $.colorbox(colorBoxOptions); $.colorbox.resize(); };

Some straight-forward functions that the HTML itself binds to.  That about wraps it up.  I would like to give credit to former co-worker, Drew, for his work on this as he did the heavy lifting.  I didn't want such a great piece to not be shown to the world.  Its a great piece of code and I hope it proves useful to others.  Happy coding!

SQL Server CLR and C#

Hello, here we are again.  This time I'd like to discuss C#, the CLR and SQL Server.  Combining C# and SQL Server hasn't been a common or encouraged practice at any of my previous employers and so this was a foray into a new area of development.  I'm far more comfortable writing C# and SQL directly, but combining the two has never been anything I had the need to do.

As with most jobs, odd situations do arise and you are forced to find solutions no matter how extreme.  To quickly summarize the situation, I had a business request for a SSRS or Excel report that combined multiple sets of data with varying numbers of columns and column names.  In the past the business data was stored horizontally in tables so this type of report was common and posed no difficulties to the development teams.

Within the last year, however, we had changed the organization of the data to be stored vertically and to be far more normalized in an attempt to prevent the ever-growing problem of "column creep".  Yes, at least one older table has over 100 columns!

The difficulty in achieving what had previously been easy, now proved to be quite hard.  The schema has a series of tables in which users could define parameters and these parameters were then used in calculations to generate reports. to make matters more complicated, the number of parameters (and even their names and calculation methods) can vary between clients and even the number and type of parameter sets can vary.

To generate reports for a single parameter-set we had previously used a SQL Pivot to flip the vertical parameters into horizontal columns. That would have worked here, except for the varying number of parameter-sets.We considered doing another pivot, but the potential complexity of the resulting SQL  was daunting and the time required to develop and test such a complex query was also a potential issue.

In addition, we had no applications that could generate reports of this type, nor did we have the time to write one solely for this single situation. The user also wanted the data to be generated directly into CSV (Excel) or SSRS (which could be saved as CSV/Excel).

I was confident I could do this type of organization and combination of data in C#, reliably and even quickly, and so was born the idea to use the CLR on SQL Server.  I won't bore you further with the details. Suffice to say that I tried my hardest to stick to SOLID Clean-Code principles while doing the work but SQL server was never built with that type of thing in mind.

For those who haven't done this before the approach is simple.  First, you define a partial class and a static function.  You give it the "SqlProcedure" attribute.  Then you open a connection like you would normally in C# and perform your operations. I wrapped the entire operation in a try-catch block so I could get a message output when testing in SQL Management Studio.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

public partial class StoredProcedures { [SqlProcedure] public static void MyStoredProcedure(... input parameters ...) { try { using (SqlConnection connection = new SqlConnection("context connection=true")) { connection.Open(); // do some stuff here to get your data // do some other stuff to send your data } } catch (Exception ex) { if (SqlContext.Pipe.IsSendingResults) SqlContext.Pipe.SendResultsEnd(); SqlContext.Pipe.Send(ex.ToString()); if (ex.InnerException != null) SqlContext.Pipe.Send(ex.InnerException.ToString()); } } }

One thing to make note of is that if you want your stored procedure to take in parameters, they must be a Sql CLR type, such as SqlBoolean or SqlString.

Once we had the data retrieve and combined, I began the work of generating the SQL output. This was the most complex part and took a lot of trial and error to get correct. You send SqlDataRecords back to the SqlContext.Pipe but you first must define the SqlMetaData (which essentially defines the output columns). A quick helper method allowed me to quickly build these records.

1 2 3 4 5 6

public static SqlMetaData CreateMetaData(string name, SqlDbType dbType = SqlDbType.VarChar, int maxLength = 0) { return StringDbTypes.Contains(dbType) ? new SqlMetaData(name, dbType, maxLength) : new SqlMetaData(name, dbType); }

And then you build a list of the SqlMetaData objects to pass into the SqlDataRecord constructor as below and then send it to the pipe.

1 2 3 4 5 6

var metaDataList = new List<SqlMetaData>(); metaDataList.Add(SqlClrHelper.CreateMetaData("Field1", SqlDbType.VarChar, 50)); metaDataList.Add(SqlClrHelper.CreateMetaData("Field2", SqlDbType.Int)); SqlDataRecord record = new SqlDataRecord(metaDataList.ToArray()); SqlContext.Pipe.SendResultsStart(record);

After you have build the record and sent it to the SqlContext.Pipe, you can begin sending your data rows. You can loop this, you just have to send each record individually to the pipe. Note that the Set methods only use numerical indexes and they must line up perfectly with the SqlMetaData and data types.

1 2 3

record.SetString(0, "My Value"); record.SetInt32(1, 250); SqlContext.Pipe.SendResultsRow(record);

When you have completed pushing the data rows to the SqlContext.Pipe, simply call "SqlContext.Pipe.SendResultsEnd();" to end transmission. That call should probably be the last thing your stored procedure function does (other than perhaps error handling). Note, that at the top in my catch block I checked if the pipe was in the process of sending results and if it was I ended it so that I could send the exception details. This is required because you can't call "Send" on the pipe if you have already called "SendResultsStart" without calling "SendResultsEnd".

For development of this I simply put my code into a SQL Data Project in VS2013. It allowed me to utilize the schema compare and publish features easily and seemed like a good way to organize any CLR code alongside the SQL code. You can also write unit tests on the code like you would any other C# code, which is a powerful feature that I like a lot.

One final note is that certain editions of SQL Server only support certain .NET framework versions. SQL Server 2008 (and R2) uses version 2.0 of the CLR (Common Language Runtime) which means you can use up to the .NET Framework version 3.5. If you are doing it in a SQL Server Data Project like I am, then you can do so in the project properties (SQLCLR tab). SQL Server 2012 supports the 4.0 version of the CLR (which lets you use .NET Frameworks up through 4.5).

You can read more about all of the topics I discussed here: SQL Server CLR Integration

Well, that about covers it. Happy coding and thanks!

Unit Testing, Mocking, Visual Basic and .NET 2.0

Sure, I know what you are saying... Why are you using .NET 2.0?  Or Visual Basic?  Why not something more modern like .NET 4.5 or C#?  Well, the answer is simple; Business needs and limitations.  In other words, the application was written in Visual Basic and time-constraints are insufficient to allow for a rewrite of the software.  To make matters worse, the software runs on an environment that cannot support any .NET Framework higher than .NET 2.0.

In spite of these limitations, we still have a driving need and concern to write good software using SOLID principles and to do what we can to unit test the code.  The question then becomes, how do you achieve these goals in a world that has long since moved on to far more flexible versions of the language and tools?

To achieve this, time was spent evaluating and googling for solutions.  The coding environment and unit testing framework were easy as we had both available and they do support .NET 2.0.  The mocking framework, however, proved to be a bit more troublesome.  In the end, we used Visual Studio 2013, NUnit (version 2.6.4 through NuGet) and NMock (version 2.0.0.44).

To get started you may notice that there is no built-in Test Project for .NET 2.0 in VS2013.  Don't worry, simply create a new Library Project and then add your NUnit reference.  Create a new class, add the <TestFixture> tag to the top and away you go!

.NET 2.0 is lacking many of the features you may be accustomed to with .NET 4 or 4.5, including lambdas and generics, while VB.Net does not even support LINQ.  This severely reduces the mocking frameworks available to you as most require a minimum of .NET 3.5 to operate (because they rely heavily upon generics).

So, what is a developer to do?  Introducing NMock (http://nmock.sourceforge.net/)!  I have used MOQ for years, but in the last year I have become quite familiar with NMock and have grown to accept it as a decent substitute.

Instead of MOQ's Setup function, you use Stub and Expect.  You specify the Methods and Properties to mock by name and you can even verify the incoming parameters on the methods using "NMock2.Is..." syntax.  Should you need additional matching functionality, particularly for handling lists, you will likely have to write your own or modify existing ones.  For my project, I ended up extending the IsList Matcher (found on the nmock documentation page: http://nmock.sourceforge.net/advanced.html) with functionality that allowed me to do "Contains" and "EquivalentTo" checks.

Here's it in use:

Or here with a real-world example:

That about covers it.  You can read more about using NMock on the project's sourceforge page or on StackOverflow, but it is pretty powerful, extensible, and a decent fill-in for more modern and mainstream mocking frameworks.

Enjoy and happy coding!