JQGrid with asp.net and handlers in vb.net

Total votes: 0
Views: 3,473
Comments: 0
Category: ASP.NET
Print: Print Article

Please login to rate or to leave a comment.

Published: 22 Jun 2011
By: Abhijeet Pandit
Download Sample Code

This article discusses how to control JQGrid using familiar ASP.Net page life cycle and data sources.

Contents [hide]
1 Introduction 2 Basics of JQGrid 3 Implement JQgrid along with ASP.net (VB.NET) and handlers (.ashx) 3.1 Table structure in SQL server database: 3.2 Define JQGrid structure in the .aspx page 4 BusinessLogic 5 BusinessObject 6 DataAccess 7 Define handler (.ashx) 8 JQGrid 9 Summary

Introduction

After implementing JQGrid, one of the most powerful JQuery plug-in, I thought about sharing my experience with users. This plug-in is Ajax-enabled JavaScript control. This control is mainly client side script and can be used to display the records in the grid format along with other useful features. I have explained how to control JQGrid using familiar ASP.Net page life cycle, data sources etc.

Basics of JQGrid

JQGrid can represent dynamic data over the web, and can be implemented by using following

web server (IIS, Apache, Tomcat),
database (MSSQL, MySQL, Oracle), and
server-side scripting language (ASP.Net, PHP)

You can achieve following features in minimum steps of code by using JQGrid package:

Retrieve data from various format, local data(array data, data stored in an xml file, or data stored in a JSON file) and also from SQL server database
Display data in client-side grid format
Navigation, Pagination
Search through records
Add/Modify/Delete records
Sub grids
Manage layout with JQuery UI Plugin

Implement JQgrid along with ASP.net (VB.NET) and handlers (*.ashx)

You can download JQGrid package from JQGrid Home Page. This article is basically explaining how to retrieve data from SQL server database by using handlers.

Table structure in SQL server database:

For this code a simple table with following fields is used:

Table 1: Agency_Mater

Column name	Data type
Agency_code	Int
Agency_name	Varchar(500)

Define JQGrid structure in the .aspx page

We will look at retrieving data in jqGrid and will focus on native API mechanisms. But first let's prepare some common configuration.

After you download the JQGrid package you define the grid structure as follows:

Add .js reference in the head tag:

We have referred to JQGrid scripts and jQuery UI component in the above mentioned code. You download custom themes from the jQuery sites as per your choice. The most important is not only the JavaScript which you use, but how and in which order you loads the JavaScript files. You should include all files which you need in the correct order.

The usage of jQuery.ajax with async:false is not the best way. If you not use XHTML having <?xml version="1.0? encoding="utf-8??> as the first line the solution with document.writeln in the <head> will work better because it loads many JavaScript files parallel and then execute it sequential in the correct order. So the loading will be quickly.

Additionally starting with jqGrid 4.0 you can use jquery.jqGrid.src.js with the full code of jqGrid in one file. The only problem of the way is that debugger (for example IE developer tools) works slowly with one large file. So I personally prefer to include all the modules with separate <script> statement. The way work perfect in all browsers, debugging is quickly and if I post the bug report I can do it in the original jqGrid modules. Moreover in the case you can place <script> inside of <body> (if you prefer the style) without any disadvantages of the document.writeln method in the case.

This is JavaScript file code where the grid itself is configured in the .aspx file:

In the first part of code, we have defined the column names of the databases like agency_code and agency_name.

In the second part we have defined add/modify/delete functions which can use for further customization like validations, business logics etc.

Important to note here are:

colNames, the grid column captions the user sees.
colModel,which includes the property name, which is the field name in the returned JSON data which supplies the data for that column, and index, which is the name which will be sent to the handler as the sidx (field name to sort on) argument when you click on a grid header. There are a number of other properties you can set here which control editing, with, formatting, etc. See the documentation for full details. Importantly, colNames and colModel must have the same number of entries, and be in the same order.
url, which tells the grid where to get its data. You'll notice that the value I've passed is the name of the handler file.

In further part, we are defining the .net code to fetch the data and send it back to JQGrid. This will make supplying data to the grid easy. We are now ready to put together a code behind in the application.

For server side scripting, we will define following folder structure in the app_code folder at root directory.

BusinessLogic
BusinessObject
Collections
DataAccess
BusinessLogic

For this purpose we will be using JavaScriptSerializer class to convert the dataset into the readable format for JQGrid. This class is used to develop code behind for building the JSON object through VB.NET.

BusinessLogic

This folder will mainly contain manager class for agency. This will define all the required methods to interact with database. However, this code will not contain any implementation.

This AgencyManager.vb will contain all code for manager classes as follows:

For methods:

BusinessObject

We will define all the public and private variables required for agency in this folder. Typically, we can define 2 variables and new and hidden for each field in the table. These get and set methods will be accessed during the data manipulation.

This Agency.vb will contain code as follows:

Imports System
Imports System.ComponentModel
Imports System.Diagnostics
Namespace BO
<DebuggerDisplay("Agency: {agency_code, nq} {agency_name, nq} {hid_agency_code, nq} {agency_name, nq}")> _
    Public Class Agency
#Region "Private Variables"
        Private hid_agency_code As Integer = -1
        Private agency_code As Integer = -1
        Private hid_agency_name As String = [String].Empty
        Private agency_name As String = [String].Empty
#End Region
#Region "Public Properties"
        <DataObjectFieldAttribute(True, True, False, False)> _
        Public Property Agencycode() As Integer
            Get
                Return agency_code
            End Get
            Set(ByVal value As Integer)
                agency_code = value
            End Set
        End Property
        Public Property Hid_Agencycode() As Integer
            Get
                Return hid_agency_code
            End Get
            Set(ByVal value As Integer)
                hid_agency_code = value
            End Set
        End Property
        Public Property Agencyname() As String
            Get
                Return agency_name
            End Get
            Set(ByVal value As String)
                agency_name = value
            End Set
        End Property
        Public Property Hid_Agencyname() As String
            Get
                Return hid_agency_name
            End Get
            Set(ByVal value As String)
                hid_agency_name = value
            End Set
        End Property
#End Region
End Class
End Namespace

We will additionally define AgencyList.vb in the Collections folder as follows. These collection classes can be used for further during record sets.

DataAccess

This folder will contain AgencyDB.vb which will actually interact with the database and will create record set to pass to the manage class.

This Agency.vb will contain code as follows:

Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Imports BO
Namespace Dal
    Public Class AgencyDB
#Region "Public Methods"
        Public Shared Function GetList() As AgencyCollection
            Dim tempList As AgencyCollection = Nothing
            Using myConnection As New SqlConnection(AppConfiguration.ConnectionString)
                Dim myCommand As New SqlCommand("sprocAgencySelectList", myConnection)
                myCommand.CommandType = CommandType.StoredProcedure
                myConnection.Open()
                Using myReader As SqlDataReader = myCommand.ExecuteReader()
                    If myReader.HasRows Then
                        tempList = New AgencyCollection()
                        While myReader.Read()
                            tempList.Add(FillDataRecord(myReader))
                        End While
                    End If
                    myReader.Close()
                End Using
            End Using
            Return tempList
        End Function
Private Shared Function FillDataRecord(ByVal myDataRecord As IDataRecord) As Agency
  Dim myAgency As New Agency()
  myAgency.Agencycode = myDataRecord.GetInt32(myDataRecord.GetOrdinal("agency_code"))
  myAgency.Agencyname = myDataRecord.GetString(myDataRecord.GetOrdinal("agency_name"))
  Return myAgency
End Function
#End Region
    End Class
End Namespace

Additionally, we will defined "AppConfiguration.vb" which will take care of connection string from the web.config file will supply it to the all required class

Code for AppConfiguration.vb

Define handler (*.ashx)

This handler will be called from .aspx page to fetch the data from database.

Public Class agency_page : Implements IHttpHandler
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        context.Response.ContentType = "application/json"
        Dim data As String = String.Empty
        Dim t As String = context.Request.QueryString("t").ToString()
        'type of query select or edit
        Select Case t
            Case "e"
                Dim forms As System.Collections.Specialized.NameValueCollection = context.Request.Form
                Dim oper As String = forms.[Get]("oper")
                Dim agencyid As String = String.Empty
                Dim agencyName As String = String.Empty
                Select Case oper
                    Case "edit"
                        'Do your database update here
                        Exit Select
                    Case "add"
                        'Do your database add here                      
                        Exit Select
                    Case "del"
                        'Do your database delete here
                        Exit Select
                    Case Else
                        Exit Select
                End Select
                'editing mode
                Exit Select
            Case "s"
                Dim page As Integer = Convert.ToInt32(context.Request.QueryString("page").ToString())
                Dim limit As Integer = Convert.ToInt32(context.Request.QueryString("rows").ToString())
                Dim sidx As String = context.Request.QueryString("sidx").ToString()
                Dim sord As String = context.Request.QueryString("sord").ToString()
                Dim sortexp As String = sidx & " " & sord
                data = getagency(7, sortexp, page)
                'My function to return the Baby's Data
                Exit Select
            Case Else
                'do nothing
                Exit Select
        End Select
        context.Response.Write(data)
    End Sub
    Public Function getagency(ByVal caseid As Integer, ByVal sortexp As String, ByVal page As Integer) As String
        Dim dt As New DataTable()
        Dim sb As New StringBuilder()
        Dim cb As New Record_Collection()
        Try
            Dim AgencyCollection_1 As AgencyCollection
            AgencyCollection_1 = Bll.AgencyManager.GetList()
            dt = Record_Collection.ListToDataTable(AgencyCollection_1)
            dt.DefaultView.Sort = sortexp
            Dim dv As DataView = dt.DefaultView
            dt = dv.ToTable()
            cb.page = page
            cb.total = CInt(Math.Ceiling(CSng(dt.Rows.Count) / CSng(10)))
            cb.records = dt.Rows.Count.ToString()
            cb.rows = New RowElement(dt.Rows.Count - 1) {}
            For i As Integer = 0 To dt.Rows.Count - 1
                cb.rows(i) = New RowElement()
                cb.rows(i).id = Convert.ToString(i + 1)
                cb.rows(i).cell = New String(1) {}
                cb.rows(i).cell(0) = dt.Rows(i)("agencycode").ToString()
                cb.rows(i).cell(1) = dt.Rows(i)("agencyname").ToString()
            Next
            Dim js As New JavaScriptSerializer()
            js.Serialize(cb, sb)
        Catch
        End Try
        Return sb.ToString()
    End Function
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property
End Class

I wrote this method to hold the data which the grid needs. You can actually use an anonymous type for this; the JQGrid grid obviously doesn't care if the data it receives comes from an anonymous type or not, but because I am writing handler methods, it was convenient for me to have a non-anonymous type.

JQGrid

The .aspx page will call handler to fetch the data from server and final output will be as displayed in the image:

Figure 1: JQGrid page

JQGrid page

Summary

You can refer to the attached code for further details. Same handler and VB.NET files can be further use for add/modify/delete functionality.

<< Previous Article

Continue reading and see our next or previous articles

Next Article >>

About Abhijeet Pandit

Sorry, no bio is available

This author has published 2 articles on DotNetSlackers. View other articles or the complete profile here.

You might also be interested in the following related blog posts

Its a Small World, After All, part 2 VB, C#, and GPS (Matt Gertz) read more
Happy New Year! (Lisa Feigenbaum) read more
ASP.NET MVC View Engine using VB.NET XML Literals read more
On C# and VB read more
TL12: Future Directions for Microsoft Visual Basic (Lisa Feigenbaum) read more
Community Interview with Jonathan Aneja (Beth Massi) read more
Murach's ASP.NET Web Programming with VB.NET read more
The Everlasting Question - Should I choose VB.NET of C# read more
VB.NET or C# - which one to choose? I choose BOTH :-) read more
STAThread vs. MTAThread (WHorst) read more

Top

Please login to rate or to leave a comment.

Free Agile Project Management Tool from Telerik
TeamPulse Community Edition helps your team effectively capture requirements, manage project plans, assign and track work, and most importantly, be continually connected with each other.

Login

Lost Password?