JqGrid in ASP.NET MVC3 Code First Project

JqGrid is one of the most popular and flexible Jquery based component which is available for ASP.NET and PHP. These components are for sale included in a bundle of other useful Jquery plugins such as JqChart, JqTreeGrid and many more. If you can afford, then you can buy the plug-in already integrated to ASP.NET MVC 2+. But for those who are interested to learn, Pinoy Brown Bag Sessions will show you how its done in a simple way. 

Using JqGrid in Asp.NET MVC 3 with VB.NET

I'v been using JqGrid plugin during my college years. The plug-in was still in its earlier version and is available opensource for PHP developers only. Back then, using JqGrid in ASP.NET was still an issue in forums and other blogs. a documentation of JqGrid for PHP is available in this website which I have used for 3 years by now and still it does work for me. 

For this brown bag session download the opensource demo package here. You can also download from github here. All you need to do is run the demo in your local server and try some of the examples provided for you. NOTE that the demo you downloaded is for PHP. Based on that demo we can arrive with a JqGrid in our ASP.NET MVC 3 project with VB.NET. 

To start lets create the view for the JqGrid. In our previews post I have already included the <div> with the id "jqgrid_product" for the grid view. The code is shown below. 

<div id="toystoregrid">
        <table id="jqgrid_product" class="scroll" cellpadding="0" cellspacing="0" style="cursor: pointer;
            -moz-user-select: none;">
        </table>
        <div id="jqpager_product" class="scroll" style="text-align: center;">
        </div>
        <input id="setcols" type="button" value="Set Columns" />
    </div>

Next we then add the javascript for this plug-in to identify its properties such as column name, column format, caption and more. To do this lets insert a Jquery AJAX function just before the last closing div tag of our view in index.vbhtml file.

<script type="text/javascript">
    $.getScript("@Url.Content("~/jsx/Home.js")");
</script>

This function loads a javascript file named Home.js in our directory. The purpose of this function was just actually to separate the javascript from the view or HTML in order to give you a clearer view on how its done. Next in our Home.js file lets add some Jquery and the JqGrid plug-in as well. Here is the code snippet for shown below.

Home.js

$(function () {
    $("#jqgrid_product").jqGrid({
        scrollrows: true,
        url: "Home/jsontry",
        datatype: "json",
        colNames: ['ID', 'Name', 'Description', 'Price'],
        colModel: [
                { name: 'Tbl_Id', index: 'Tbl_Id', width: 150, resizable: false, sortable: false },
                { name: 'Tbl_Name', index: 'Tbl_Name', width: 250, resizable: false, sortable: false },
                { name: 'Tbl_Description', index: 'Tbl_Description', width: 250, resizable: false, sortable: false },
                { name: 'Tbl_Price', index: 'Tbl_Price', width: 150, resizable: false, sortable: false }
                ],
        mtype: "POST",
        imgpath: 'themes/basic/images',
        pager: $('#jqpager_product'),
        rowNum: '10',
        rowList: [10, 20, 30],
        rownumbers: true,
        viewrecords: true,
        caption: "Sample JQgrid",
        height: '200',
        width: '1024',
        autowidth: true
    });
});

You can see from above the code snippet for our JqGrid. Notice that the JqGrid function is in-closed with a Jquery which is equivalent to a document.ready() function in javascript. If you downloaded the demo of JqGrid, the script shown above is the Loading Data demonstration on JSON. JavaScript Object Notation (JSON) is a lightweight data-interchange format. It is easy for humans to read and write. I prefer JSON than XML in terms of speed and readability. Also that ASP.NET MVC 3 supports JSON. I will show you that not long from the code behind. 

Let me discuss some important lines with you as we go along. We have this line $("#jqgrid_product").jqGrid({, the id of the table we created earlier in our view. This identifies the target element of the plug-in. In order to connect to our database we have the URL given in this line url: "Home/jsontry",. The URL identifies "Home" which is the controller and "jsontry" which is that method that we will create later. colNames: and colModel: are the properties that identify our column and the model. You can read more about this properties in the documentation available in PDF format here. You can try to experiment with the other properties and see how it affects your grid. 

We are now ready to do the code behind. But first lets create a simple class that would contain the data that we return to our grid as JSON. The code blow shows the jqgriddata.vb class. 

jqgriddata.vb  

Public Class Jqgriddata
    Private _id As Object
    Private _cell As Object

    Public ReadOnly Property id As Object
        Get
            Return _id
        End Get
    End Property

    Public ReadOnly Property cell As Object
        Get
            Return _cell
        End Get
    End Property

    Public Sub New(ByVal idpass As Object, ByVal cellpass As Object)
        _id = idpass
        _cell = cellpass
    End Sub
End Class

I will discuss the two properties of the class later in this brown bag as we finish the code for the controller.

Now lets do code behind for our HomeController.vb. For those who have downloaded or created the project the code below will replace the code from your controller as we do the jquery method. For the first method we have this code below. 

Function Index() As ActionResult
            Return View()
End Function
       
Function jsontry(ByVal data As FormCollection) As ActionResult
            Dim page = data("page") 'the current page for paging 
            Dim limit = data("rows") 'number of rows per page
            Dim sidx = data("sidx") 'pass the row value used for sorting
            Dim sord = data("sord") 'sort order (asc or desc)
            Dim countrows As Integer
            Dim totalpages As Integer

            If sidx.ToString <> "" Then
                sidx = 1
            End If
            countrows = (From p In _entities.Products _
                   Select p).Count() 'linq query to count for number of rows
            If countrows > 0 Then
                totalpages = Math.Ceiling(countrows / limit)
            Else
                totalpages = 1
            End If

            If page > totalpages Then
                page = totalpages
            End If

            Dim start As Integer
            start = limit * page - limit

            Dim result = (From p In _entities.Products _
                             Select p) 'the query to fetch the row values from the database

            Dim cell = result.ToArray()
            Dim celly = result.ToList()
            Dim response As New Dictionary(Of Object, Object)
            Dim rows As New ArrayList

            response.Add("page", page)
            response.Add("total", totalpages)
            response.Add("records", countrows)

            Dim i = 0
            Dim x = 0
            'some loop in order to extract the data from our entity to an arraylist
            For Each row In cell
                Dim cellx As New ArrayList
                For Each c As System.Reflection.PropertyInfo In row.GetType().GetProperties()
                    cellx.Insert(i, row.GetType().GetProperty(c.Name).GetValue(row, Nothing))
                    i = i + 1
                Next
                rows.Insert(x, New Jqgriddata(row.Id, cellx)) 'the data is added to a Dictionary basically an array
                i = 0
                x = x + 1
            Next
            response.Add("rows", rows)
            Return Json(response) 'return the response as JSON. 
End Function

Wow! Quite a long VB script you have their. I added some comments though to discuss how the code behind produced the return value of the method. I only applied the PHP script and manage to somehow convert it to VB.NET. My first project doing this was with C# which was the easier version I guess. Yet here's a VB example for you VB users. With this code I am sure you can easily do it with C#.

And we are done with the the brown bag session. Try to run the code and with that your grid must appear smoothly in you index page. We just have made our JqGrid in ASP.NET MVC 3. I used firebug to display the post data from the grid as well as the response from the code behind. 

Sample JqGrid in ASP.NET MVC 3 with VB.NET.

I added some JqueryUI themes that you can download from their website. This is a Dot Luv theme from long ago. They have a lot more options to choose from with their latest version of JqueryUI nowadays. I also added a property from JqGrid which enables you to set the columns to display. Try to read and explore the documentation for this function. Here I have the image below.

JqGrid Set Columns function example.

You can view some result and also help you with debugging by using some tools like FireBug. If you are using  FireFox web browser the plug in is available from the website. Here are some results from my FireBug. 

JqGrid post data for the MVC controller method.

Some JSON results from ASP.NET MVC 3.

Other interesting results from FireBug.

That was all for now. I hope you have learned some few things from our short brown bag sessions. The next post will be on the CRUD. I will demonstrate how I used Jqury Populate and Validation in my ASP.NET MVC 3 application. Stay tune for more of Pinoy Brown Bag Sessions tutorials. Please feel free to as questions and feedback to improve the project. Thank you!