Building a data grid and pie chart with CRUD functionality using Knockout js, DataTables, ASP MVC and jqPlot

In this tutorial, we will create an expense tracking application. For each expense, we will track the expense date, category, description and amount. We will display the list of expenses in a grid and next to it we will show a pie chart to breakdown the expenses by category. We will allow the user to add, edit, delete and view the expense records individually. To do this, the user can simply select an expense record from the grid by clicking on it, and a form entry will appear on top of the grid that will allow the user to do CRUD operations (create, read, update, delete.) Below is some screenshots of the final product.

Live Demo

Figure 1. DataTable displaying expenses along with a pie chart next to it.

Figure 2. When user clicks on an expense entry in the Grid, The dataform with that entry will appear on top of the grid.

Figure 3. User is able to edit the selected record by clicking on the edit button. Similarly, there are buttons for add and delete records. You may notice that the edit and delete buttons are only enabled when the user selects an entry from the grid. When the user saves the changes, it will be reflected immediately on the grid and chart.

Setup the project.

The project will be composed of two components:

-          A server side component that will expose rest methods to handle saving and fetching the data. We will use ASP MVC to build this part. As for saving the data, we will simply use an in memory collection.

-          A client side component that will contain both the view and view model logic. This will be done in HTML and Javascript. We will use several javascript frameworks along the way. Mainly, knockout js to provide the binding between our view and viewmodel. DataTables to provide grid functionality with paging and sorting. And finally jqPlot for the pie chart.

Step 1: Import resources:

Open Visual Studio and create a new ASP.NET MVC web application. When asked to select a template, choose the Empty template.  I will call my solution DemoProject. Once the project is created, use nugget to install or update the following javascript frameworks:

Jquery, Jquery UI, json2, knockout js, knockout mapping, DataTables, jqPlot.

In addition, download the resources folder here (resources) and import the contents of it to the Scripts and Content folders. This includes the CSS files, images and other javascript frameworks that are needed for our project.

At this point your project structure should look like this:

Figure 4. after importing the resources for the project.

Step 2: server side:

The first thing we need to do is add a model. Right click the Models folder in your project and click Add new item. Choose a class from the list and call it Expense.cs. Modify its contents with this:

The next step is to create the controller. Right click on the Controllers folder and click on Add controller. Type HomeController as the name and click Add. Since we will store our data in memory, let’s create a static collection of Expense type. We will prepopulate the collection with some expenses for a starting point. Put the following inside the HomeController class:

If the compiler complains that the Expense type is not recognized, make sure to add the namespace of your model inside your Controller class:

We need another collection for the categories. Also we need to keep track of the ID of the last element in the expenses list (normally a database would handle this but since we are using a static list to store the data, we will need this). Copy the following code inside he homeController class:

Next, we will create the CRUD methods in the HomeController. First the method that will return the collection of expenses:

Notice that we return the data in Json format. This is important since we will call this method later from javascript.

The method for adding an expense is simple. It simply checks if all fields are populated and if so it will assign the next id to it and add it to the collection. It will return back the new id to the caller.

Notice that this method can only be called using an HTTP Post request.

Similarly copy the following two methods for Add and Edit inside the HomeController:

Finally, we need one last method to provide the data for our chart. This is the same data as for our grid except it will be grouped by category and the expense amounts are summed for each category:

So now we are done with the server side component. The rest is all done on the client side.

Step 3: client side View:

Open Views > Shared > _Layout.cshtml file and replace its content with this:

This will reference all the necessary css and javascript files that we need to use. Next we need to create our main view. Right click the Views folder and click on Add New Folder. Call it Home. Right click the newly created Home folder and click on the Add View. Call the new view Index. Replace its content with the following HTML layout:

Step 4: Client Side ViewModel:

The next step is to create our view model that will be the glue that connects our model on the server side to our html view. We will start by creating a client side model that will mirror our model on the server side. Add the following code inside the javascript tag in index.cshtml:

Notice the use of ko.observable to wrap our variables. This is the mechanism used by knockout to allow binding and change notification.

Next, we will create our view model. Copy the code below inside the javascript tag of index.chstml. An explanation of the code will follow:

Our view model has the following properties and method:

Properties:

Data : this is the collection of expense entries.
ChartData: expense entries grouped by categories and summed on expense amount.
Categories: json object of available categories and their ids.
selectedEntry: selected expense item from the grid.
originalEntry: original entry before user starts editing ( used to restore value upon cancel).
newEntry: the new record being added by the user.
Mode: whether we are in on of four modes: read, new, edit or hide.
errorMsg: holds the error messages when modifying or adding records.
canEdit: whether the edit and delete  buttons are enabled or disabled.

Methods:

beginEdit: save original entry and change mode to edit.
endEdit: Try to save edited entry. If succeeded, update data with changes and refresh chart. Otherwise set error message.
cancelEdit: restore original entry and change mode to read.
deleteEntry: remove selected entry from data collection and refresh chart data.
beginNewEntry: set the mode to new.
endNewEntry: try to save new entry. If succeeded, add to data collection and change mode to read and refresh chart data. Else set error message.
CancelNewEntry: Change mode to either hidden or read depending if any entry is selected from the grid.
refreshData: refreshes data collection from server.
refreshChartData: refreshes chartdata collection from server.

helper functions:

setSelectedEntry: update the values of the selected Entry.
copyEntry: copy values from t one expense object to another.
clearErrors: clears the value of the erroMsg property.
resetEntry: clears the values of the passed expense object.

Step 5: wire up the bindings:

We are done with the view model. Now we need to bind our view model to the html view. The way to do this using knockout js is to use data-bind attributes as follows:

Knockout js by default provides binding handlers for most of html form elements. However for the DataTables and jqPlot there is no default binding. We already added custom binding for those when we added a reference to the knockout.bindings.dataTables.js and knockout.bindings.jqPlot.js which you downloaded from the resources folder.
The last function that we added below the bindings was adding a click event handler for the grid table. This is so we can get the expense item the user selects and then update the view model’s selected Entry. In theory, we should write a binding handler for the grid selection. For now, I’ll leave that as such since it works fine.

The last thing we need to do is to add a progress indicator whenever we are doing an ajax call. Add the following inside the javascript tags in index.cshtml:

We are done. Run the application to see the result. Download the complete solution: here

8 comments on “Building a data grid and pie chart with CRUD functionality using Knockout js, DataTables, ASP MVC and jqPlot

  1. Thanks for your posting. I did find one thing missing in the documentation that caused me a little time to determine. The DOM definition for your ExpenseTable is missing. Again, this is from the inline notes above. It might be correct in the document download.

    For example, you only show this.

    instead of

  2. Sorry…that did not come through due to the HTML. Here is what I was saying was missing from the inline notes.

    table cellpadding=”0″ cellspacing=”0″ border=”0″ class=”ExpenseTable” id=”example”

  3. You have a great series on DataTable and Knockout, thank you.

    One thing I am not able to find is how to handle filtering, paging and searching on server side.
    I will have to treat a lot of data and I didn’t want to have everything on the client side.

    Have you ever have to implement such a solution ?

    Thank you !

  4. hello
    nice work.
    is it posible to use localStorage to save grid data without any server side
    i’m building something similar to this and i would appreciate a litle help with an advice about client side scripting

    thank you

  5. Hello Myer

    Nice work, can you add more features like:

    - Show the section for Add/Edit in popup box.
    - Search section like in datatable grid if we have huge data.
    - Grid support column filter.

    Thank you.

  6. Pingback: Exemple de pie chart avec KnockoutJS « Pragonas

  7. How can we read data from database table for categories? right now it is hard coded. Can you post how to do that? Your code is awesome and it is great learning experience…thanks a bunch for that.

  8. Hi nice job, I have seen that you have built a piechart binding. Recently I have done a similar one for D3JS. I have make it accept a “transformation” function which transforms any collcetion into a collection of a good shape for the chart. This can cleanup a bit your viewmodel. Check it out if you want: https://github.com/hoonzis/KoExtensions

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">