Oct 21, 2011

Setup SQL Server Compact 4 to unit test NHibernate related code

Most of the time to fake data base SQL lite data base is used. But it has certain differences from SQL server. With release of SQL Compact 4 it becomes really good choice to mock data base calls. So in this post I will describe a way of setting up NHibernate to work with SQL CE local data base. So here are our goals:

  1. Each test fixture has its own fresh DB instance
  2. NHibernate SessionFactory is same for all the tests (performance is still important in tests)
  3. Caching of NHibernate doesn’t stands on isolation way (each test will have clean factory, without any cached entities)
  4. Works just after getting from source control with no additional configuration

First of all we need to install sql ce tools for visual studio. After done that we can add an empty data base file to our tests project:

Sql server compact 4.0 local data base

lets call it db. This file is going to be the one that is going to be copied for each test and where NHibernate will create its tables.

Now lets create a base class for tests that are going to use NHibernate:

public class DbTests
{
    protected static ISessionFactory factory;
    static Configuration nhConfig;

    static DbTests()
    {
        File.Copy("db.sdf", "Temp.sdf", true);
        nhConfig = NhConfigure();
        factory = nhConfig.BuildSessionFactory();
    }

    [TestFixtureSetUp]
    public void Setup()
    {
        File.Copy("db.sdf", "Temp.sdf", true);
        new SchemaExport(nhConfig).Execute(true, true, false);
    }

    [TestFixtureTearDown]
    public void TearDown()
    {
        File.Delete("Temp.sdf");
    }

    static Configuration NhConfigure()
    {
        DomainMapper mapper = new DomainMapper();
        HbmMapping mappings = mapper.CompileMappingFor(new[] { typeof(TestEntity) });

        var configuration = new Configuration();
        configuration.SessionFactory()
            .Integrate.Using<MsSqlCe40Dialect>()
            .Connected.By<SqlServerCeDriver>()
            .Using("Data Source=Temp.sdf");

        configuration.AddDeserializedMapping(mappings, "domain");
        
        return configuration;
    }
}

What is done here is pretty straight forward. Each test fixture will get its own empty data base with new schema installed.

Also you will need to install SqlServerCompact package from nuget in order to get SqlServerCeDriver support.

There is an interesting bug when using identity columns with SQL CE. You can get NHibernate.AssertionFailure : null identifier exception. Here is how you can solve it.

The last thing we want to take care about is NHibernate cache. Each test probably will have its own ISession instance, so only second level caching should be handled. Here is how we can clean up it:

static void ClearCache()
{
    factory.EvictQueries();
    foreach (var collectionMetadata in factory.GetAllCollectionMetadata()) 
        factory.EvictCollection(collectionMetadata.Key);
    foreach (var classMetadata in factory.GetAllClassMetadata()) 
        factory.EvictEntity(classMetadata.Key);
}

Just add this method call to Setup and that it. As always working sample attached:

Source code sample doesn’t contain nuget packages and uses this way of working. So don’t be scared of everything red in ReSharper after open solution. Just build it.

Oct 7, 2011

Getting started with knockout.js

Recently I’ve had some time to learn knockout.js. It’s a javascript library for building rich internet applications. In spite of wonderful tutorials section on main site learning wasn’t as smooth as I would like it to be. Mainly because of some changes in 1.3 version and version 1.2 (that is currently used for tutorials).

Before reading further I would suggest you to watch a great video about knockout.js. After seeing it I’m not sure that you need to read further Smile.

Now we are going to build an editing form for Northwind data base products. The desired result is the following form:

Products editing form

So when page is loaded only categories list is visible at the left. When user selects category, list of products is shown where user is able to select concrete product to edit and save.

So lets create a new ASP.NET MVC 3 internet web application. As data access layer we’ll use Entity Framework. So add new ADO.NET Entity Data Model, name it Northwind and point to your DB instance.

To get started we need a list of categories, so navigate to HomeController and add the following code to Index action:

public ActionResult Index()
{
    using (Northwind context = new Northwind())
    {
        var categories = context.Categories.Select(c => new {c.CategoryID , c.CategoryName}).ToList();
        ViewBag.Categories = categories;

        return View();
    }
}

That’s it for now on server side. Now navigate to Index.cshtml view where everything interesting is going to happen. In order to get knockout working execute next nuget command: Install-Package Knockoutjs. It will download latest version of knockout library and place it under Scripts folder. Include it in your view.

First of all we need to render a list of categories. Knockout 1.3 has build in ability to generate html based on template. So to render list of categories add the following html with javascript on view:

<ul data-bind="foreach: categories">
    <li>
        <a data-bind="text: $data.CategoryName"
            href="javascript:void(0);">
        </a>
    </li>
</ul>
<script type="text/javascript">
   var viewModel = {
        categories: @Html.Raw(Json.Encode(ViewBag.Categories))
    };
   ko.applyBindings(viewModel);
</script>

It will render li with anchor for each category that came from server. Notice that in order to get JSON representation of categories list Json.Encode method is used.

On this small example we already can see the MVVM pattern in action. View is bounded to model that is stored in js objects. So how we have strong separation of data from its representation even on client side. We can apply unit testing of javascript without messing with UI and have all the procs of concerns separation (e.g. build other UI for mobile devices).

Now lets work with some events. We want to show products when some category is selected. Lets create a server side logic for retrieving products in category. Add a new controller like this:

public class ProductsController : Controller
{
    public ActionResult InCategory(int id)
    {
        using (Northwind context = new Northwind())
        {
            var result = context.Products.Where(x => x.CategoryID == id)
                                         .Select(p => new {p.ProductID, p.ProductName})
                                         .ToList();

            return Json(result, JsonRequestBehavior.AllowGet);
        }
    }
}

So when category is clicked we should somehow call this method and show returned results. Lets add a method for our view model that should be called when category is selected and bind click event to it. Here what we should get:

<a data-bind="text: $data.CategoryName,
              click: function(){ viewModel.selectCategory($data.CategoryID); }"
    href="javascript:void(0);">
</a>
<script type="text/javascript">
   var viewModel = {
        categories: @Html.Raw(Json.Encode(ViewBag.Categories)),
        selectCategory: function(categoryId) {
               console.log(categoryId);
        }
   };
   ko.applyBindings(viewModel);
</script>

Now if you open FireBug and refresh a page when you click on category you will see its id being printed in console.  Now lets store selected category id (we will need it further in tutorial). In order to do it, lets add a new property for viewModel and set in selectCategory method:

selectedCategory: ko.observable(),
selectCategory: function(categoryId) {
    this.selectedCategory(categoryId);
}

Couple of things needs to be noticed here: initial value of the selectedCategory is ko.observable – it will create an empty value, but when this value is changed all interested in it parts of application will be notified. Second thing is that assigning value is done not via =, but with calling that property and passing value. Now we are ready to display list of products. In order to do it lets add a table template:

<table>
    <thead>
        <th>
            ProductName
        </th>
    </thead>
    <tbody data-bind="foreach: products">
        <tr>
            <td>
                <a data-bind="text: $data.ProductName"
                   href="javascript:void(0);">                    
                </a>
            </td>
        </tr>
    </tbody>
</table>

So we have a table that is bound to the products field of view model. Now we need fill this collection:

viewModel.products = ko.observableArray([]);

ko.dependentObservable(function() {
    if(this.selectedCategory()) {
       $.get('@Url.Action("InCategory", "Products")/' + this.selectedCategory(), this.products);
    }
}, viewModel);

With the help of dependentObservable method we can create a property that is going to change when another property changes. Knockout will figure out by himself that this method should be called when selectedCategory method is called. So now you should have working list of categories with ability to view products in it.

Next step is displaying and edit form. Steps should be already familiar. Lets add a server side method for retrieving order by its id:

public ActionResult Get(int id)
{
    using (Northwind context = new Northwind())
    {
        var result = from p in context.Products
                     where p.ProductID == id
                     select new { p.ProductID, p.UnitPrice, p.ProductName, p.UnitsInStock, p.UnitsOnOrder };
        return Json(result.FirstOrDefault(), JsonRequestBehavior.AllowGet);
    }
}

And on the client side:

viewModel.selectedProductId = ko.observable();
viewModel.selectedProduct = ko.observable(‘’);

viewModel.selectProduct = function(productId) {
  viewModel.selectedProductId(productId);
};

ko.dependentObservable(function() {
     if(this.selectedProductId()) {
         $.get('@Url.Action("Get", "Products")/' + this.selectedProductId(), this.selectedProduct);
     }
}, viewModel);

After binding a click event of product anchor to selectProduct method we need last thing to do – implement template for editing:

<fieldset>
    <legend data-bind="text: selectedProduct().ProductName">       
    </legend>
    <dl>
        <dt>
            Product name
        </dt>
        <dt>
            <input type="text" name="ProductName" data-bind="value: selectedProduct().ProductName" />
        </dt>
    </dl>
</fieldset>

So now user is able to select category and product. I won’t cover saving data to db in this tutorial. Lets add some nice features. For example we want to highlight selected category and selected product in order to show user where he is now. Lets add a new style called current:

.selected
{
    background-color: Aqua;
}

And we want apply it to current category and product. Now we can do it with just only bindings:

css: {selected: $data.CategoryID === viewModel.selectedCategory()}
css: {selected: $data.ProductID === viewModel.selectedProductId()}

First one goes for category anchor template, second one is for product item template in list.

Full source code for this example: