Kendo UI (Core JavaScript) grid Server Paging, Server Sorting and Server Filtering with Dynamic SQL queries
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 id="EmpGrid">
</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.
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)
And Finally here is how my grid would look like: (click on the image for better view)
HI,
ReplyDeleteI need to bind dynamic column in kendo grid with server side pagging due to large amount of data.
i am using stored procedure instead of linq to fetch data from database