Kendo UI (Core JavaScript) grid Server Paging, Server Sorting and Server Filtering with Dynamic SQL queries

Introduction

Writing this post after a long time. Came across Kendo UI components in one of the projects. Kendo UI Grid (paid!) is one of the amazing html components from Kendo with excellent features like filtering, sorting, grouping, re-arranging, paging etc.

Problem

I recently came across a requirement where I had to pull data from MSSQL server and display it on a kendo grid. But it was about pulling thousands of records to the grid, which would slow down the app for the first time, but I did not want that to happen. So thought of implementing kendo Server paging which is readily available with Kendo Grid. But since I use Kendo UI core pure JavaScript framework and not the kendo wrapper for asp.net mvc etc, paging, filtering and sorting need to be handled manually by the developer. After Googling for a while, I did not find any help relevant to implementation of kendo grid server paging in client side and server side code.

So thought of doing it on my own and this post is the outcome! J

Let me explain in step by step:

Create sample sql table Employee:

Column	Type
ID	Int (identity)
Name	varchar
Email	varchar
DOB	date

Let us design the grid:

Create the div in your aspx page:

</div>

Javascript Code:

<script type="text/javascript">
$(document).ready(function () {
createGrid('Employee'); //Pass the table Name
});
function createGrid(tableName) {
$("#EmpGrid").kendoGrid({
dataSource: {
type: "json",
transport: {
read: {
url: 'Ajax.aspx/GetData',
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8"
},
parameterMap: function (data, type) {
return JSON.stringify({
tableName: tableName,
page: data.page,
pageSize: data.pageSize,
skip: data.skip,
take: data.take,
sorting: data.sort === undefined ? null : data.sort,
filter: data.filter === undefined ? null : data.filter
});
}
},
schema: {
model: {
fields: {
Name: { type: "string" },
Email: { type: "string" },
DOB: { type: "date" },
Department: { type: "string" }
}
},
data: function (data) {
// return $.parseJSON(data.d).Data || [];
if (data.d.length > 0) {
return $.parseJSON(data.d).Data || [];
}
},
total: function (data) {
if (data.d.length > 0) {
return $.parseJSON(data.d).Total || [];
}
}
},
pageSize: 10,
serverPaging: true,
serverFiltering: true,
serverSorting: true
},
noRecords: true,
groupable: true,
filterable: true,
sortable: {
mode: "multiple"
},
pageable: {
pageSizes: true,
refresh: true
},
columns: [{
field: "Name",
title: "Name"
}, {
field: "Email",
title: "E-mail"
}, {
field: "DOB",
title: "Date Of Birth",
format: "{0:MM/dd/yyyy}"
}, {
field: "Department",
title: "Department"
}]
});
}
</script>

Above grid calls the method GetData which is defined in Ajax.aspx page. As you can see ’parameterMap’, we are passing certain parameters to the method. By default Kendo passes some of this parameters to the method, since I had to send some more details I customised it using parameterMap.

Let us see the c# Code part for performing all operations and returning the data.

[WebMethod]
public static string GetData(string tableName, int page, int pageSize, int skip, int take, List<SortDescription> sorting, FilterContainer filter)
{
try
{
DataTable newDt = new DataTable();
int from = skip + 1; //(page - 1) * pageSize + 1;
int to = take * page; // page * pageSize;
string sortingStr = "";
#region Sorting
if (sorting != null)
{
if (sorting.Count != 0)
{
for (int i = 0; i < sorting.Count; i++)
{
sortingStr += ", " + sorting[i].field + " " + sorting[i].dir;
}
}
}
#endregion
#region filtering
string filters = "";
string logic;
string condition = "";
int c = 1;
if (filter != null)
{
for (int i = 0; i < filter.filters.Count; i++)
{
logic = filter.logic;
if (filter.filters[i].@operator == "eq")
{
condition = " = '" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "neq")
{
condition = " != '" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "startswith")
{
condition = " Like '" + filter.filters[i].value + "%' ";
}
if (filter.filters[i].@operator == "contains")
{
condition = " Like '%" + filter.filters[i].value + "%' ";
}
if (filter.filters[i].@operator == "doesnotcontains")
{
condition = " Not Like '%" + filter.filters[i].value + "%' ";
}
if (filter.filters[i].@operator == "endswith")
{
condition = " Like '%" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "gte")
{
condition = " >= '" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "gt")
{
condition = " > '" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "lte")
{
condition = " <= '" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "lt")
{
condition = "< '" + filter.filters[i].value + "' ";
}
filters += filter.filters[i].field + condition;
if (filter.filters.Count > c)
{
filters += logic;
filters += " ";
}
c++;
}

}
#endregion
sortingStr = sortingStr.TrimStart(',');
Dictionary<int, DataTable> dt = GetGridData(tableName, from, to, sortingStr, filters);
newDt = dt.First().Value;

string data = ConvertJson.ConvertTable(newDt);
return "{\"Data\":" + data + ",\"Total\":" + dt.First().Key + "}";
}
catch (Exception ex)
{
return "{\"Data\":[],\"Total\":" + 0 + "}";
}
}
public static Dictionary<int, DataTable> GetGridData(string TableName, int FromNumber, int ToNumber, string OrderByStr, string FilterStr)
{
string connString = ConfigurationManager.ConnectionStrings["Database_ConnectionString"].ConnectionString;
SqlDataReader rdr = null;
if (OrderByStr == "")
{
OrderByStr = null;
}
if (FilterStr == "")
{
FilterStr = null;
}
try
{
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand("GETGRIDDATA", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TableName", TableName);
cmd.Parameters.AddWithValue("@FromNumber", FromNumber);
cmd.Parameters.AddWithValue("@ToNumber", ToNumber);
if (OrderByStr != "")
{
cmd.Parameters.AddWithValue("@SQLSortString", OrderByStr);
}
if (FilterStr != "")
{
cmd.Parameters.AddWithValue("@SQLFilterString", FilterStr);
}
cmd.Parameters.Add("@TotalCount", SqlDbType.Int);
cmd.Parameters["@TotalCount"].Direction = ParameterDirection.Output;

// get query results
rdr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(rdr);
int total = Convert.ToInt32(cmd.Parameters["@TotalCount"].Value);
Dictionary<int, DataTable> dictionary = new Dictionary<int, DataTable>();
dictionary.Add(total, dt);
return dictionary;
}
}
}
catch (Exception ex)
{
return new Dictionary<int, DataTable>();
}
}

Other Classes Used:

public static class ConvertJson
{
public static string ConvertTable(DataTable table)
{
JsonSerializerSettings serializerSettings = new JsonSerializerSettings();
serializerSettings.Converters.Add(new DataTableConverter());
string jsonData = JsonConvert.SerializeObject(table, Formatting.None, serializerSettings);
return jsonData;
}
public static string ConvertToJson(object data)
{
JObject o = JObject.FromObject(new
{
Table = data
});
JsonSerializerSettings serializerSettings = new JsonSerializerSettings();
//serializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
string jsonData = JsonConvert.SerializeObject(data);
return jsonData;
}
public static string SerializeObject(object data)
{
return JsonConvert.SerializeObject(data);
}
}
public class SortDescription
{
public string field { get; set; }
public string dir { get; set; }
}
public class FilterContainer
{
public List<FilterDescription> filters { get; set; }
public string logic { get; set; }
}
public class FilterDescription
{
public string @operator { get; set; }
public string field { get; set; }
public string value { get; set; }
}

As you can see I am calling a Dynamic stored procedure, which would work perfectly for retrieving data from any table for any given sorting, filtering and paging condition, which perfectly suits with our kendo grid server paging requirement.

Also you must have noticed I am converting the DataTable to JSON using Newtonsoft json convert DLL (http://www.newtonsoft.com/json).

Please find the Stored Procedure Used:

--Created By: Vikas Kottari
CREATE PROCEDURE GETGRIDDATA
@TableName nvarchar(50)=NULL,
@FromNumber nvarchar(3)=NULL,
@ToNumber nvarchar(3)=NULL,
@SQLSortString nvarchar(1000)=NULL,
@SQLFilterString nvarchar(1000)=NULL,
@TotalCount int OUTPUT
AS
BEGIN
DECLARE @SQLQuery nvarchar(1000);
SET @SQLQuery=N'WITH Data AS
(
SELECT top 100 percent *,';
IF @SQLSortString IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'ROW_NUMBER() OVER (ORDER BY '+@SQLSortString+') AS ''RowNumber'''
END
ELSE
BEGIN
SET @SQLQuery=@SQLQuery+N'ROW_NUMBER() OVER (ORDER BY ID) AS ''RowNumber'''
END
IF @SQLFilterString IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName+' WHERE '+@SQLFilterString+''
END
ELSE
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName
END
SET @SQLQuery=@SQLQuery+')
SELECT *
FROM Data'
IF @FromNumber IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N' WHERE RowNumber BETWEEN '+@FromNumber+' AND '+@ToNumber --BETWEEN is inclusive
END
--PRINT @SQLQuery
EXECUTE sp_executesql @SQLQuery;
IF @SQLFilterString IS NOT NULL
BEGIN
DECLARE @query NVARCHAR(1000)=N'SELECT @TotalCount=COUNT(*) from '+@TableName+' WHERE ' +@SQLFilterString
DECLARE @TempCount varchar(20)
set @TempCount='0'
EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
ELSE
BEGIN
SET @query =N'SELECT @TotalCount=COUNT(*) from '+@TableName
EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
END

Please find Stored Procedure file here SP.sql

Please find C# code here: C# Code

and the JavaScript Code: javascript code

And Finally here is how my grid would look like: (click on the image for better view)

Search This Blog

SharePoint Shell