Friday, June 22, 2012

Realtime data editing using an Excel-like grid and KnockoutJs

Sometimes you need to edit a batch data requiring an agile UI that allow:
  • Send changes without need confirmation
  • Quickly Navigate to any field that you want edit
  • Provide a visual indicator of the updated fields
An example: Realtime Weather Forecast Editor
On this example, the main requirement is allow quickly edit the cities temperatures at different day times. Basically for each city could edit the morning, afternoon or midnight temperature. For simplicity, the cities list are stored in a session variable.
This is a list of the implementation techniques for the app main features:
  • Updates are sent back to the server when textboxes lost focus: using a KnockoutJs extender the temperature fields changes are captured and sent to server using ajax calls.
  • Updated values are highlighted: a css binding changes the textbox background color after a field is updated successfully on server
  • Key based navigation: a jQuery handler allow navigate between textboxes using the arrows keys
App Screenshot

Using a KnockoutJs extender to send the realtime updates
A new extender named "realtime" is added to the Knockout extenders. At the initialization, it stores in two private fields (cityId and dayTime) the values required to identify the time slot to be updated on the ajax call after the observable property value changes. Also, a sub-observable (hasChanged) is used to notify the highlighter databinder.
ko.extenders.realtime = function(target, options) {
    // store private vars to use as parameters on updates
    // cityId: the key of entity to be updated
    // daytime: the field of entity to be updated (Morning/Afternoon/Midnight)
    target.cityId = options[0];
    target.daytime = options[1];
    //add a sub-observable to mantain a changed flag
    target.hasChanged = ko.observable(false);

    //define a function to send updates
    function update(newValue) {
                 url: "/home/updateforecast", 
                data: { cityId: target.cityId, daytime: target.daytime, value: newValue },
                type: "post", 
                success: function(result) 

    //update whenever the value changes

    //return the original value
    return target;

Applying the extender
The extender is applied on the ForecastModel temperature fields
var model = @Html.Raw(new JavaScriptSerializer().Serialize(Model));
var viewModel = { 
    forecasts :  ko.observableArray(ko.utils.arrayMap(model, function(forecast) {
        return new ForecastModel(forecast);
function ForecastModel(forecast) {
    this.City = ko.observable(forecast.City);
    // extend the observable fields that will be updated in realtime
    this.Morning = ko.observable(forecast.Morning).extend({realtime:[forecast.Id, "Morning"]});
    this.Afternoon = ko.observable(forecast.Afternoon).extend({realtime:[forecast.Id, "Afternoon"]});
    this.Midnight = ko.observable(forecast.Midnight).extend({realtime:[forecast.Id, "Midnight"]});

Configuring the View
A table (tblForecasts) is used to display the cities names and edit the different day times temperatures. On each temperature textbox the "value binding" is applied to each temperature field. Also, a "css binding" to the hasChanged sub-observable is applied in order to highlight the textbox background color when a change callback returns successfully from server.
    Realtime Weather Forecast Editor</h1>
<table id="tblForecasts">
    <tbody data-bind="template: { name: 'forecastRowTemplate', foreach: forecasts }">
<script type="text/html" id="forecastRowTemplate"> 
    <td><span data-bind="text: City"/></td> 
    <td><input data-bind="value: Morning, css: { hasChanged: Morning.hasChanged }"/></td> 
    <td><input data-bind="value: Afternoon, css: { hasChanged: Afternoon.hasChanged }"/></td> 
    <td><input data-bind="value: Midnight, css: { hasChanged: Midnight.hasChanged }"/></td> 
Adding keyboard navigation to table
A simple handler is attached to the textboxes keydown event to detect the arrows keys pressing and move focus
$("#tblForecasts").on("keydown", "input:text", function(e) {
        // detect arrows pressing
        if (e.keyCode < 37 || e.keyCode > 40)

        var target;
        var cellAndRow = $(this).parents('td,tr');
        var cellIndex = cellAndRow[0].cellIndex;
        var rowIndex = cellAndRow[1].rowIndex;

        switch (e.keyCode) {
            // left arrow                 
            case 37:
                cellIndex = cellIndex - 1;                        
            // right arrow                 
            case 39:
               cellIndex = cellIndex + 1;                        
            // up arrow                 
            case 40:
                rowIndex = rowIndex + 1;
            // down arrow                 
            case 38:
                rowIndex = rowIndex - 1;
        target = $('table tr').eq(rowIndex).find('td').eq(cellIndex).find("input:text");
        if (target != undefined) {
Server side code
The Forecast Model
public class Forecast
    public int Id { get; set; }
    public string City { get; set; }
    public int Morning { get; set; }
    public int Afternoon { get; set; }
    public int Midnight { get; set; }
The Controller
Just contains the code to initialize the cities list on session and a method to update the temperatures
private const string C_ForecastModels = "ForecastModels";

// Expose the stored Forecasts from Session
public IEnumerable<Forecast> ForecastModels
        if (Session[C_ForecastModels] == null)
            Session[C_ForecastModels] = new[] {
                new Forecast { Id=1, City="Barcelona", Morning=90, Afternoon=45, Midnight=40},                new Forecast { Id=2, City="Berlin", Morning=78, Afternoon=34, Midnight=0 },
                new Forecast { Id=3, City="New York", Morning=45, Afternoon=35, Midnight=20 },
                new Forecast { Id=4, City="Sydney", Morning=65, Afternoon=45, Midnight=40 },
                new Forecast { Id=5, City="Tokio", Morning=74, Afternoon=70, Midnight=60 }
        return Session[C_ForecastModels] as IEnumerable<Forecast>;
    set { Session[C_ForecastModels] = value; }

 public ActionResult Index()
     // Return the Session stored Forecasts
     return View(ForecastModels);

 // Update Forecast value for selected daytime (Morning/Afternoon/Midnight)
 public JsonResult Updateforecast(int cityId, string daytime, int value)
     var cityForecast = ForecastModels.SingleOrDefault(forecastModel => forecastModel.Id == cityId);
     if (cityForecast == null)
         return Json(false);
     switch (daytime)
         case "Morning":
             cityForecast.Morning = value;
         case "Afternoon":
             cityForecast.Afternoon = value;
         case "Midnight":
             cityForecast.Midnight = value;
     return Json(true);
This is a basic example to demonstrate the KnockoutJs extenders usage. The realtime edition on the grid and the keyboard navigation resembles the old DOS applications, but still is a powerful technique for some specific scenarios (I've worked in some projects on that this type of UI was explicitly required by the client or app end users).
Project files

Tuesday, September 15, 2009

Telerik + Nettiers series

Radgrid paging, sorting, grouping and sorting with Nettiers objectdatasource

This post starts a new serie of post to demostrate the combination of two cools tools: Telerik Suite & Nettiers
This example shows the use of the more common features of Radgrid as paging, sorting, filtering and grouping with a Nettiers object datasource.
Some helpfull notes:
1) The objectdatasource method used to retrieve the data is GetPaged.
2) The code in OnSelecting event of the object datasource set correct the parameters for the objectdatasource.
3) The EnableLinqExpressions of radgrid is set to false to avoid by pass the objectdatasource results.
4) The CanRetrieveAllData of mastertableview is set to false in order to get a correct sorting.

The aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Radgrid.aspx.cs" Inherits="Radgrid" %>

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="">
<head runat="server">
<form id="form1" runat="server">
<telerik:RadScriptManager ID="RadScriptManager1" runat="server">
<telerik:RadGrid ID="RadGrid1" ShowGroupPanel="True" GridLines="None" DataSourceID="EmployeeDataSource1"
AllowFilteringByColumn="True" AllowAutomaticDeletes="True" AllowAutomaticUpdates="True"
AllowAutomaticInserts="True" OnPreRender="RadGrid1_PreRender" AllowPaging="True"
AllowSorting="True" Skin="WebBlue" PageSize="10" EnableLinqExpressions="false"
AllowCustomPaging="true" runat="server">
<MasterTableView GroupLoadMode="client" AllowMultiColumnSorting="true" CommandItemDisplay="TopAndBottom"
DataSourceID="EmployeeDataSource1" DataKeyNames="EmployeeId" CanRetrieveAllData="false"
<PagerStyle AlwaysVisible="true" />
<ClientSettings AllowDragToGroup="True" AllowColumnsReorder="True" ReorderColumnsOnClient="True">
<Selecting AllowRowSelect="True" />
<data:EmployeeDataSource ID="EmployeeDataSource1" runat="server" EnablePaging="True"
EnableSorting="True" SelectMethod="GetPaged" EnableCaching="false" OnSelecting="EmployeeDataSource1_OnSelecting">

The code behind

using System;
using System.Web.UI.WebControls;

public partial class Radgrid : System.Web.UI.Page
string masterTableFilter = string.Empty;

protected void Page_Load(object sender, EventArgs e)


protected void EmployeeDataSource1_OnSelecting(object sender, ObjectDataSourceSelectingEventArgs e)
// Set the pagesize in each selecting to avoid the Int32.MaxValue (2147483647) passed by some Radgrid versions when filtering
e.Arguments.MaximumRows = RadGrid1.PageSize;

// Clear the master table filterExpression
if (!string.IsNullOrEmpty(RadGrid1.MasterTableView.FilterExpression))
masterTableFilter = RadGrid1.MasterTableView.FilterExpression;
RadGrid1.MasterTableView.FilterExpression = string.Empty;

// Set the Nettiers Datasource parameters
e.InputParameters["WhereClause"] = masterTableFilter;
e.InputParameters["OrderBy"] = RadGrid1.MasterTableView.SortExpressions.GetSortString();

// Restore the master table filterExpression
protected void RadGrid1_PreRender(object sender, EventArgs e)
RadGrid1.MasterTableView.FilterExpression = masterTableFilter;

Thursday, July 23, 2009

Efficient Nettiers Deepload - Filling object graphs in just one database call

After using several ORM's I found that when need a complete object graph or some related entities (INNER/LEFT JOIN "like") they haven't an efficient approach built-in.
Almost all ORM can lazy load related properties (collections or instances) that means additional db calls and specifically in Nettiers filling an object with n-childs implies (n+1) database calls with the related performance penalty.
On this post, I want expose one alternative to populate an entire object graph in just one database call.
The concept it's very simple and consists on deserialize a string returned from the database that contains an xml with the representation of the entire object graph.

"In one application that uses the AdventureWorks database you need to get the orders with their order details and the product referenced in the order detail for a selected customer."

1) First develop a simple method to take a look to the string that the deserializer is expecting.
string sampleData = GetSampleData(676);

protected string GetSampleData(int customerId)
SalesOrderHeaderService salesOrderHeaderService = new SalesOrderHeaderService();
Type[] childTypes = new Type[] { typeof(TList<SalesOrderDetail>), typeof(Product) };
TList<SalesOrderHeader> salesOrders = salesOrderHeaderService.DeepLoadByCustomerID(customerId, true, DeepLoadType.IncludeChildren, childTypes);
return EntityHelper.SerializeXml<SalesOrderHeader>(salesOrders);
2) Create an stored procedure that returns an xml with the representation of the entire object graph
CREATE PROCEDURE [dbo].[SalesOrdersGetWithDetailsAndProductsByCustomerId]
@customerId int,
@result ntext OUTPUT

SET @result=
'<?xml version="1.0" encoding="utf-16"?><ArrayOfSalesOrderHeader xmlns:xsi="" xmlns:xsd="">'
(SELECT SalesOrderHeader.*,
'<SalesOrderDetailCollection>' +
(SELECT SalesOrderDetail.*,
(SELECT Product.*
FROM Production.Product Product
WHERE Product.ProductID = SalesOrderDetail.ProductID
FOR XML RAW ('ProductIDSource'), ELEMENTS)
FROM Sales.SalesOrderDetail SalesOrderDetail
WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
+ '</SalesOrderDetailCollection>'
Sales.SalesOrderHeader SalesOrderHeader
WHERE SalesOrderHeader.CustomerID = @customerId

SELECT @result


3) Call the SP and deserialize the result
TList<SalesOrderHeader> customerSalesOrders = SalesOrdersGetWithDetailsAndProductsByCustomerId(676);

protected TList<SalesOrderHeader> SalesOrdersGetWithDetailsAndProductsByCustomerId(int customerId)
SqlCommand command = new SqlCommand("SalesOrdersGetWithDetailsAndProductsByCustomerId");
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@result", SqlDbType.VarChar);
param.Direction = ParameterDirection.Output;
param.Size = 10000;
command.Parameters.Add(new SqlParameter("@customerId", customerId));
string resultParameter = command.Parameters[0].Value.ToString().Replace("&lt;", "<").Replace("&gt;", ">").Replace("&amp;lt;", "<").Replace("&amp;gt;", ">");
return EntityHelper.DeserializeListXml<SalesOrderHeader>(resultParameter);
- Remove XmlIgnore Attribute for the related entities that you want populate. You can identify them by the "Source" sufix (ProductIDSource in the example).
- Attributes serialization is case sensitive, if you has applied some style on the UsePascalCasing codesmith template property, your class fields names casing could be different that the db field definition. In this case use field aliasing in the stored procedure select clause.
- To deepload a collection put the subquery on the select fields and enclose with the collections delimiter ('SalesOrderDetailCollection' in the example).
- To deepload a one to one relation put the subquery on the select fields and use for "RAW" in FOR XML clause to overwrite the row element name generated automatically ('ProductIDSource' in the example).

Wednesday, October 29, 2008

Codesmith Method Picker Property

I just published my first project on Codeplex. The project is a Codesmith add-in, below is the project Url, description and a screenshot. Hope that the Codesmith users find this helpful.

Project Description
This project is a custom property for Codesmith that allows select methods from assemblies.
You can choose a set of methods and use this list to any purpose as generate a webservice to expose them, make method tests or a javascript method call wrapper.

Project Url
Codesmith Method Picker Property

Sunday, June 29, 2008

jQuery Carousel Example

A cool way to show a items gallery is using a scrollable div as Amazon displays related books for the current selected book. This technique is known as "carousel". I made an example (Download source (85Kb)) that shows a list of motherboards using a jQuery Carousel Component populated dinamically via Ajax queries (useful when haves a large items quantity to show)

Server side pagination method
The request/response to the method that returns a "page" of the carousel are maded using JSON.
For simplicity the pagination is done against a generic collection, but easily can be a database query.


[ScriptMethod(ResponseFormat = ResponseFormat.Json)]

public static List<Motherboard> GetItems(int pageIndex, int pageSize)


List<Motherboard> motherboards = new List<Motherboard>


new Motherboard("Asus P5Q Deluxe", "Asus P5Q Deluxe Motherboard", "Intel Socket T LGA775, P45 Express, ATX, 16GB DDR2, 1600MHz FSB"),

new Motherboard("GigaByte GA-P35-DS3L", "GigaByte GA-P35-DS3L Motherboard", "Intel Socket T LGA775, P35 Express, ATX, 8GB DDR, 1333MHz FSB "),

new Motherboard("Intel D5400XS", "Intel D5400XS Motherboard", "Intel Socket J LGA771, P35 Express, Extended ATX, 8GB DDR2, 1600MHz FSB "),

new Motherboard("Asus M3N-HT Deluxe Mempipe", "Asus M3N-HT Deluxe Mempipe Motherboard", "Socket AM2+, nVIDIA nForce 780a, ATX, 8GB DDR2, 2600MHz FSB"),

new Motherboard("ABIT IP35 PRO XE", "ABIT IP35 PRO XE Motherboard", "Intel Socket T LGA775, P35 Express, ATX, 8GB DDR2, 1600MHz FSB"),

new Motherboard("Asus Striker Extreme", "Asus Striker Extreme Motherboard", "Quad-Core/Core 2 Duo Extreme/Core 2 Duo/Pentium D/Celeron D, Socket T"),

new Motherboard("XFX MB-N780-ISH9", "XFX MB-N780-ISH9 Motherboard", "Intel Socket T LGA775, nForce 780i SLI, ATX, 8GB DDR2, 1333MHz FSB "),

new Motherboard("HP Asus P5BW-LA", "HP Asus P5BW-LA Motherboard", "Intel Socket T LGA775, P965 Express, Micro ATX, 8GB DDR2, 1066MHz FSB"),

new Motherboard("Biostar P4M900-M4", "Biostar P4M900-M4 Motherboard", "Core 2 Quad/Pentium D/Pentium 4/Celeron D, Socket T SLI, ATX, 8GB DDR2, 1333MHz Bus"),

new Motherboard("MSI P6N Diamond", "MSI P6N Diamond Motherboard", "Intel Socket T LGA775, nForce 680i SLI, ATX, 8GB DDR2, 1333MHz FS"),

new Motherboard("Intel D915GAG", "Intel D915GAG Motherboard", "Pentium 4/Celeron D, Socket T, 915G, mATX, 4GB DDR, 800MHz FSB"),

new Motherboard("ABIT IP35-E", "ABIT IP35-E Motherboard", "Intel Socket T LGA775, P35 Express, ATX, 8GB DDR2, 1333MHz FSB "),


return motherboards.GetRange((pageIndex - 1) * pageSize, pageSize);


Client side pagination method


function mycarousel_makeRequest(carousel, first, last, per_page, page)


// Lock carousel until request has been made


// Call the server side method with pageIndex and pageSize parameters


type: "POST",

url: "Default.aspx/GetItems",

data: "{'pageIndex':'" + page + "', 'pageSize':'" + per_page + "'}",

contentType: "application/json; charset=utf-8",

dataType: "json",

success: function(data) {

mycarousel_itemAddCallback(carousel, first, last, data.d, page);




function mycarousel_itemAddCallback(carousel, first, last, data, page)


// Unlock


// Set size


var per_page = carousel.last - carousel.first + 1;

for (var i = first; i <= last; i++)


var pos = i - 1;

var idx = Math.round(((pos / per_page) - Math.floor(pos / per_page)) * per_page);

carousel.add(i, mycarousel_getItemHTML(data[idx]));




* Buil item html


function mycarousel_getItemHTML(mother)


var item = "<div>";

item += "<img src='images/" + mother.Image + ".jpg' alt='" + mother.Title + "'>";

item += "</div>";

item += "<div class='Title'>";

item += mother.Title;

item += "</div>";

item += "<div class='Description'>";

item += mother.Description;

item += "</div>";

return item;



Download source: jQuery Carousel in (85Kb)

Monday, May 5, 2008

Generics Examples - Generating instances with two methods or how to simplify the DAL

In this delivery two methods to generate instances or collections of our entities, simplifying therefore the code of our DAL (traditionally composed by a great amount of methods and mappings “field to field” from tables to objects). Once collected the data using a DataReader, with these methods is possible to obtain a single entity instance or a strong typed collection of instances.

The Methods

// A single instance
public static void FillEntity<T>(T instance, IDataRecord datarecord)
Type instanceType = instance.GetType();
for (int i = 0; i < datarecord.FieldCount; i++)
if (datarecord[i] != DBNull.Value)
string propName = datarecord.GetName(i);
PropertyInfo propInfo = instanceType.GetProperty(propName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
if (propInfo != null)
propInfo.SetValue(instance, datarecord[propInfo.Name], null);

// A instance collection
public static List<T> FillEntities<T>(IDataReader dr)
List<T> entities = new List<T>();
while (dr.Read())
T instance = Activator.CreateInstance<T>();
Utils.FillEntity<T>(instance, dr);
return entities;

Using the methods

// A Single Instance
public static UserInfo UserGetById(string userid)
UserInfo userInfo = new UserInfo();
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("usergetbyid");
db.AddInParameter(dbCommand, "userid", DbType.String, userid);
IDataReader idr = db.ExecuteReader(dbCommand);
if (idr.Read())
Utils.FillEntity<UserInfo>(userInfo, idr);
return userInfo;

// A instance collection
public static List<OrderInfo> GetOrders(int customerid)
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("getorders");
db.AddInParameter(dbCommand, "customerid", DbType.Int32, customerid);
return Utils.FillEntities<OrderInfo>(db.ExecuteReader(dbCommand));

Friday, April 25, 2008

Generics Examples - Passing paramaters to Stored Procedures

In this post begins a series of examples of application of Generics that were useful to me throughout several developments. In this delivery a generic method that receives an instance of some entity of our domain and a DbCommand, and based on the SqlParameterAttributes attributes “reflected” from the entity maps the value of the properties with the parameters of stored procedure of the received DbCommand. Using this only method and “decorating” with the SqlParameterAttribute attributes the suitable properties of our classes it is possible to simplify the stored procedures calls.

The Method

public static void MatchStoreProceduresParams<T>(T entity, DbCommand command)
Type type = entity.GetType();
foreach (PropertyInfo entityProperty in type.GetProperties())
SqlParameterAttribute[] propSqlAttributeArray = (SqlParameterAttribute[])entityProperty.GetCustomAttributes(typeof(SqlParameterAttribute), false);
if (propSqlAttributeArray.Length > 0)
SqlParameterAttribute propSqlAttribute = propSqlAttributeArray[0];
if (propSqlAttribute != null)
SqlParameter sqlParam = new SqlParameter();
sqlParam.ParameterName = propSqlAttribute.Name;
sqlParam.SqlDbType = propSqlAttribute.SqlDbType;
sqlParam.Value = entityProperty.GetValue(entity, null);
if (propSqlAttribute.IsDirectionDefined)
sqlParam.Direction = propSqlAttribute.Direction;


Using the method

public static int CustomerSave(CustomerInfo customer)
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("CustomerUpdate");
Utils.MatchStoreProceduresParams(customer, dbCommand);
return db.ExecuteNonQuery(dbCommand);

Decorando la clase

public class CustomerInfo
int _id;
string _name;
int _age;
decimal _balance;

public CustomerInfo()


[SqlParameter("@CustomerId", System.Data.SqlDbType.Int)]
public int Id
get { return _id; }
set { _id = value; }

[SqlParameter("@FirstName", System.Data.SqlDbType.Char)]
public string Name
get { return _name; }
set { _name = value; }

[SqlParameter("@Age", System.Data.SqlDbType.Int)]
public int Age
get { return _age; }
set { _age = value; }

[SqlParameter("@Balance", System.Data.SqlDbType.Decimal)]
public decimal Balance
get { return _balance; }
set { _balance = value; }