• Application Source
• SQL Scripts

Introduction

Once upon a time web developers have to write down complex logic to render partial UI on the page. Nowadays it is very common to find several single page applications with extensive data manipulations and processing. But thanks to arrival of JQuery and similar javascript wrappers libraries that we can develop such things quite easily.

In this article we will develop Data Grid from scratch with help of Jquery based DataTable plugin  and ASP.NET generic handlers. As the backend we will be using Sql Server 2008. So, lets move straight to the topic...

Note: If you find any grammatical mistake please let me know i will fix it, article is written gradually with longer span of time. And furthermore i am NOT native english speaker :).

Background

In order to understand this article you just need to have some understanding of JQuery and Underscore utility belt. We will be dealing with JSON data back and forth from server. So, you just need to be familiar with json and generic handlers too. So, our working application may structured as;

Using the code

We will follow these steps to acheive our goal:

1. Client side UI
2. Create Data Table
3. Create Store Procedure for Data Fecthing
4. Developing Generic Handler in ASP.NET as middle layer
5. Few Things about NewtonSoft.Json
6. Add Handler in web.config

Lets go through all these steps:

1. Client side UI

Our final client side UI will look like this

Page Header

Code below show the head portion of the page. It is referencing several scripts like DataTable script link, JQuery library, noty plugin to display notifications etc. Code is heavily commented and i hope it will be very easy to grab.

Page Body

Body contains place holder for datatable where DataTable will be rendered.

Page Scripting (Overview)

Our main binding script for Datatable will mainly look like this

Little Bit about Data Table Options

These are very few and commonly used data table options in our case...

For more details about data table available options, you can visit data table official plugin site.

Custom Columns

Custom columns in above mention script will look like this

2. Create Data Table

We can create a simple table by using the SQL script, and our table will look like this;

CREATE TABLE [dbo].[hr_Customer](
    [CustomerID] [bigint] IDENTITY(1,1) NOT NULL,
    [CustomerCNIC] [varchar](15) NULL,
    [CustomerName] [varchar](200) NOT NULL,
    [CustomerAddress] [varchar](200) NOT NULL,    
    [OfficeTelephone] [varchar](50) NULL,
    [HomeTelephone] [varchar](50) NULL,
    [MobileNumber1] [varchar](50) NULL,
    [MobileNumber2] [varchar](50) NULL,    
    [Notes] [varchar](200) NULL,
    [IsActive] [bit] NOT NULL,
    [EntryDate] [datetime] NOT NULL,
    [ClosingDate] [datetime] NULL,    
    [sysDate] [datetime] NULL
 CONSTRAINT [PK_hr_Customer] PRIMARY KEY CLUSTERED 
(
    [CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] 

It is just a simple table for demostration purposes, i don't want to distract readers from essential points. This structure can easily apply to larger data tables and joined views etc.

3. Create Store Procedure for Data Fecthing

Our DataTable's all data requests will be served by the stored procedure. It seems little complex but don't worry i will try to make it simple while dealing with our handler and front end UI.

USE [CodeProject_DataTable]
GO

/*
	Author		: Muhammad Shoaib
	Date		: 22 Jun 2014 
	Description : This Procedure will fetched Latest Top [@pageSize]
	no. of items
*/

ALTER proc [dbo].[sp_hr_v1_0_get_Customers]
@currentIndex int,---current record index >>> 0 bases
@pageSize int,
@orderByClause varchar(max) = null, --optional sorting parameter,
@search varchar(max) = null, --search terms optional
@totalRecords as int output
as

set nocount on
	
declare @startIndex int
declare @endIndex int

set @startIndex = @currentIndex + 1
set @endIndex = @startIndex + @pageSize - 1

select @totalRecords = COUNT(*)	from hr_Customer c 	where c.IsActive = 1	

create table #ttt(
	RowNumber int,
	CustomerID bigint,
	CustomerCNIC varchar(15) null,
	CustomerName varchar(200) null,
	OfficeTelephone varchar(50) null,
	CustomerAddress varchar(200),	
	Notes varchar(200) null
)

--if there are some sorting columns then it will be a dynamic query 
--otherwise we will keep it simple 
if(@orderByClause is null and @search is null)
begin
	insert into #ttt(RowNumber,
	CustomerID,CustomerCNIC,CustomerName,OfficeTelephone,CustomerAddress,Notes)
	select ROW_NUMBER() over(order by CustomerID desc),  
	CustomerID,CustomerCNIC,CustomerName,OfficeTelephone,CustomerAddress,Notes 
	from hr_Customer c with(readcommitted)
	where c.IsActive = 1		
end
else
begin
	declare @searchClause varchar(max) = '1 = 1'
	
	if(@search is not null)
	begin
		set @searchClause = '(lower(CustomerName) like lower(''' + @search + ''') + ''%'' 
	or lower(CustomerAddress) like lower(''' + @search + ''') + ''%'')'
	end
	
	declare @query nvarchar(2000) = 'insert into #ttt(RowNumber,
	CustomerID,CustomerCNIC,CustomerName,OfficeTelephone,CustomerAddress,Notes)	
	select ROW_NUMBER() over(' + @orderByClause  +'),  
	CustomerID,CustomerCNIC,CustomerName,OfficeTelephone,CustomerAddress,Notes 
	from hr_Customer c with(readcommitted)
	where c.IsActive = 1
	and ' + @searchClause
		
	print @query
	
	exec sp_executesql @query
end

select * from #ttt
where RowNumber >= @startIndex and RowNumber <= @endIndex


drop table #ttt

4. Developing Generic Handler in ASP.NET as middle layer

I have been using Visual Studio.Net 2014 for development purposes, you can use choose any .NET IDE 2008 or 2010 beside 2012. For some novice users i am adding some snapshots that to do that:

Creating Empty Solution

you can create empty web solution that will be look like this. 

Inside Generic Handler

In Visual Studio.Net i am just using default setting with Framework version 4.5. It is interesting to note that we will be using a very special .NET based JSON libarary know as Newtonsoft Json library in our generic handler that i will explain later. Our Json handler look like this

In depth analysis

Lets apply divide and conqure approach to understand what is happening inside this handler; e.g. our datatable want to render its initital data it will query that from json handler, overall request will look like this

Step 1

When this request is received on server then our custom handler will execute the corresponding logic to deal with it...

Step 2

GetJsonCustomer will get resultant data from database and generate json result in data table expected format...

 

Step 3

If you want to order your data depending upon the column(s) selected in datatable then this code will do that for you. For more details look the above mentioned Request Format snap.

Step 4

Finally we will fetch data from database and send back datatable expected data in proper format.

Step 5

Following code snippet will convert .Net DataTable into proper plugin data table expected response format. you can see that we wrap all data in aaData, i.e. expected container for data table.

Generated Response from above query will look like this.

5. Few Things about Newtonsoft.Json

As i have mentioned above that we will be using Newtonsoft.Json library to serialize our objects into proper json string and de-serialize json request received from client side into proper .NET objects. First you can have a look on this library here. It is quite easy to use and understand. I will upload this library along side my project solution for quick reference. Once we have downloaded this library, its folder structure look like this;

Once we have downloaded proper assembly we just have to add its reference in project solution, it will be seems like this;

 

6. Add Generic Handler in web.config

It is quite simple to add Generic Handler in solution, just right click on Project in solution explorer and choose Add > New Item > Visual C# > Web > Generic Handler. After adding it will look like this.

Add handler section in web.config file like this

Point of Interests

If you want to build data grid using extreme low level control then bare bone generic handler will be handy solution along with jquery based datatable plugin.