jquery javascript ajax grid for asp.net and asp.net mvc

• Download Grid.rar - 762.2 KB

Introduction

I know the issues we developers are came across while developing espacially while display list of records where client requires sorting , serching , filtering and many more. to come out with this issue i have created my own javascript based ajax grid which can easily integrate in asp.net and asp.net MVC application.

What are the Advantages.

1. Easy integration
2. Very Fast(tested with 10 Lacs Records).
3. No external DLLs.
4. Pure Javascript.
5. Searching Option
6. Sorting
7. Ajax Paging

To achieve this i will show you step by step codes and its execution process with some images and details.

Background

What we require.

I have created this script with pure java script and jquery ajax function so you must get a reference of jquery version 1.4 or above. CSS and other image file i will attach so you not need to worry on that. now i will show you how we are going to implement code.

Using the code

First Step: To achieve this please do necessary steps mentioned below. Create Database Grid. Create table company and procedure for listing. Please execute below mentioned scripts.

////Execute below script//////////

create database Grid

create table company
(
companyid int identity(1,1) primary key,
companyname nvarchar(50),
companyemail nvarchar(100),
companywebsite nvarchar(250)

)

Create Listing Procedure:

USE [Grid]
GO

/****** Object:  StoredProcedure [dbo].[select_company_listing]    Script Date: 17-08-2014 12:55:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[select_company_listing]
(

@companyname nvarchar(50),
@companyemail nvarchar(100),
@companywebsite nvarchar(250),
@FromPage int=0,
@ToPage int=0,
@intTotal int output

)
as
declare @strsql nvarchar(4000)
declare @strCountsql nvarchar(1000)
declare @xx int
set @xx = 0
begin
set @strsql = ‘With CompanyEntities As (
Select ROW_NUMBER() Over (Order By companyname) As Row,companyid,companyname,companyemail,companywebsite
FROM Company WHERE  1=1  ‘
if @companyname <> "
begin
set @strsql= @strsql + ‘ and companyname like "%’+ @companyname + ‘%" ‘
end
if @companyemail <> "
begin
set @strsql= @strsql + ‘ and companyemail like "%’+ @companyemail + ‘%" ‘
end
if @companywebsite <> "
begin
set @strsql= @strsql + ‘ and companywebsite like "%’+ @companywebsite + ‘%" ‘
end

set @strsql= @strsql + ‘ )’

set @strsql= @strsql + ‘Select companyid,companyname,companyemail,companywebsite  From CompanyEntities Where’
set @strsql= @strsql + ‘ Row Between ‘ +  convert(varchar,@FromPage) + ‘ AND ‘ + convert(varchar,@ToPage) + ‘ Order By Row Asc  ‘

CREATE TABLE #TempLocal (ct int);

set @strCountsql = ‘ INSERT INTO #TempLocal (ct) SELECT count(companyid) FROM Company WHERE  1=1 ‘
if @companyname <> "
begin
set @strCountsql= @strCountsql + ‘ and companyname like "%’+ @companyname + ‘%" ‘
end
if @companyemail <> "
begin
set @strCountsql= @strCountsql + ‘ and companyemail like "%’+ @companyemail + ‘%" ‘
end
if @companywebsite <> "
begin
set @strCountsql= @strCountsql + ‘ and companywebsite like "%’+ @companywebsite + ‘%" ‘
end

–print @strCountsql
exec(@strsql)
exec (@strCountsql)

SELECT  @intTotal=ct FROM #TempLocal;
DROP TABLE #TempLocal;

end

 

Insert Records:

\\\\\\\\\\\\\Insert Records\\\\\\\\\\\\\\\\\\\\
DECLARE @CT INT

SET @CT = 1

WHILE @CT <> 1000001
BEGIN

INSERT INTO company
(companyname
,companyemail
,companywebsite)
VALUES
(‘A’ + CONVERT(varchar,@CT)
,’A’ + CONVERT(varchar,@CT) + ‘@test.com’
,’A’ + CONVERT(varchar,@CT) + ‘website.com’)

SET @CT = @CT + 1
END

Now Database is ready so we are now going to create one website. Follow below steps.

After creating database you need to create website in visual studio.

Open Visual Studio –> File — > New –> Website

Choose language C#.

Create Script Folder and add commongrid.js file in it.

Please copy following code in commongrid.js file. In this file we will create java script function which will accept ajax request and will generate dynamic ajax grid. Its very long code so just put it i will explain you later which function will do what.

This is a 3 tier architecture sample application where we will creating one business logic folder, data access folder to handle business logic and database related operations.

Before move ahead we will create one folder named BusinessLogic. With in that we will create file named BLCompany.cs where we will mentioned properties of the all database column like below.

  private int _companyid;
    private string _companyname;
    private string _companyemail;
    private string _companywebsite;

public int ID
    {
        get
        {
            return _companyid;
        }
        set
        {
            _companyid = value;
        }
    }
    public string companyname
    {
        get
        {
            return _companyname;
        }
        set
        {
            _companyname = value.Replace("'", "''");
        }
    }

    public string companyemail
    {
        get
        {
            return _companyemail;
        }
        set
        {
            _companyemail = value.Replace("'", "''");
        }
    }
    public string companywebsite
    {
        get
        {
            return _companywebsite;
        }
        set
        {
            _companywebsite = value.Replace("'", "''");
        }
    }

Then after we will create DataAccess folder to call store procduers and return dataset. I am not describing tons of codes here just gives overview as once you find sample application where you will get entire idea easily. i will only show how to call and generate ajax grid.

Now we are ready with our business logic and data access layer. now we are going to create our intermediate page where ajaxy request will be handle. To achieve this create a folder named ajax. Within that create page named GetCompanyList.aspx. This page will handle request from page where grid will populate. This page will accept request and return html to ajax call.

Two main method we will execute in this page are listed below

// Dataset will receive the data by calling business logic function
// ds = objdoc.SelectCompanyList();
//

// Pager class where html paging will be populate.
// GetPagging(objdoc.Total, Convert.ToInt32(ConfigurationManager.AppSettings["ListingPageSize"]), strpath, pageno);
//

Now we are done with this and moves to main page where our grid will be populate.

Create Grid.aspx page. Add reference of jquery 1.4 or above. Css and images are attached in the sample. Add commongrid.js and validate.js file as a reference.

In HTML

<input id="gridcurrentPage" value="1" style="display: none;" />
This hidden page will help ajax to handle current page.

<div id="dvgridpaging"></div> \\Ajax request will populate paging in this div.
<div id="dvgrid" class="grid-overflow"></div> \\ Ajax request will populate html grid in this div.

Please paste below html to your grid.aspx page.

<div id="disablingDiv"></div>
    <div id="divloading" style="position: absolute; display: none;">
        <img src='css/images/loading.gif' alt='loading' />
    </div>

 <div class="g12">
        <h1>Company List</h1>       
        <input id="gridcurrentPage" value="1" style="display: none;" />
        <div id="dvgridpaging"></div>
        <div id="dvgrid" class="grid-overflow"></div>
    </div>
    <script type="text/javascript" src="scripts/validate.js" language="javascript"></script>    
    <script type="text/javascript" src="scripts/commongrid.js" language="javascript"></script>        
    <script type="text/javascript" language="javascript">
        var g_columns = ['Name', 'Email', 'Website'];
        var g_columns_sorting = ['1', '1', '1', '1', '1', '1', '1', '1'];
        var g_columns_width = ['30%', '35%', '35%'];
        var g_columns_search = ['1', '1', '1'];
        var pagesize = '<%=ConfigurationManager.AppSettings["ListingPageSize"]%>';
        var requestURL = "Ajax/GetCompanyList.aspx?grid=1&amp;";
        var db_schema_name = ['companyname', 'companyemail', 'companywebsite'];
        var p = readCookie('currentpage')
        if (p) {
            if (p != "") {
                document.getElementById("gridcurrentPage").value = p;
            }
        }

        var x = readCookie('where')
        if (x) {
            if (x != "") {
                CreateDynamicGrid(g_columns, g_columns_sorting, g_columns_width, g_columns_search, pagesize, requestURL, db_schema_name, '', '', x);
            }
            else {
                CreateDynamicGrid(g_columns, g_columns_sorting, g_columns_width, g_columns_search, pagesize, requestURL, db_schema_name, '', '', '');
            }
        }
        else {
            CreateDynamicGrid(g_columns, g_columns_sorting, g_columns_width, g_columns_search, pagesize, requestURL, db_schema_name, '', '', '');
        }
        eraseCookie("column");
        eraseCookie("order");
        eraseCookie("details");
  
    </script>

Done.... Easy hun?... Yes now once page will load, it will call CreateDynamicGrid function which is in the commongrid.js file. thats it commongrid.js file will calll GetCompanyList.aspx file which is in ajax folder and it will return with grid which will populate in <div id="dvgrid" class="grid-overflow"></div> tag.

Points of Interest

Why i wrote ? When my client wants searching functionality on every column and on the top of the grid only. i serched out on the google but not found so got the idea to create simple grid and created and sharing is caring so though why cant i help others .... so shared.... happy coding....