Tutorial: SQL to C# to JSON to Paging grid

[John Sourcer]

Hi All,

Quick tutorial to help C#'s prepare data for paging grid.

Firstly your SQL statement. In order to handle paging correctly you need to return a row count int for each row in order to set your 'start' and 'limit' parameters AND a total number of records. The following SQL statement does this easily using derived tables and can be adapted for larger table sets and UNIONS:



SELECT RowID, cnt, col1, col2, col3 FROM (
SELECT Row_Number() OVER (ORDER BY col1) AS RowID, count(*) over() as cnt , col1, col2, col3
FROM ( SELECT col1, col2, col3 FROM table) t
) d WHERE RowID >= @start AND RowID <= @limit
GROUP BY RowID, cnt, col1, col2, col3


The above statement gives you 'RowID' for each row and a column 'cnt' which counts the total records in the query EVEN though you are only returning a set amount. The downside is that there is an extra column but that far outweighs having to make an extra call to teh database.

Now we have our SQL prepared, let's call and convert this into JSON via C#:



//Include the nessecary using statements.
//Bare in mind that web controls etc are not needed as we are simply returning text

//String var to hold the total records count
string t = string.Empty;

//Increment you page limit by your start value
limit = limit + start;
DataSet ds = GetDS(start, limit);//Populate your dataset here calling the SQL above

//Convert your dataset mapping type to attribute based
foreach (DataColumn dc in ds.Tables[0].Columns)
dc.ColumnMapping = MappingType.Attribute;
//Get the total record count and drop the extraneous column 'cnt'
foreach (DataRow dr in ds.Tables[0].Rows)
t = Convert.ToString(dr[1]);

ds.Tables[0].Columns.RemoveAt(1);
// 'Convert' the dataset into an XmlDataDocument
XmlDataDocument oXml = new XmlDataDocument(ds);
//Format the XmlDataDocument into JSON. I am using Newtonsoft here
//but you could do this anyway you would like.
//Notice that the 't' string holding total record count has been concat'd into the JSON
string text = Newtonsoft.Json.JavaScriptConvert.SerializeXmlNode(oXml);
text = text.Replace("{NewDataSet:[{Table:", "{\"totalCount\": " + t + ", \"data\":");
text = text.Replace(",@", ",");
text = text.Replace("@RowID", "RowID");
text = text.Substring(0, text.Length - 2);

//Voila! Return the text
Response.Write(text);



Now the rest is easy:



var ds = new Ext.data.Store({
proxy: new Ext.data.HttpProxy({url: 'ReturnYourJSON.aspx'}),
reader: new Ext.data.JsonReader({
root: 'data'
,totalProperty: 'totalCount'
,id: 'col1'
}, ['col2', 'col3'])
,remoteSort: true
});

var cm = new Ext.grid.ColumnModel([
{header: "Col1", width: 250, dataIndex: 'col1'},
{header: "Col2", width: 50, dataIndex: 'col2'},
{header: "Col3", width: 95, dataIndex: 'col3'}
]);
cm.defaultSortable = true;

var grid = new Ext.grid.Grid('mygrid', {
ds: ds
,cm: cm
,autoHeight: true
,autoWidth: true
,fitToFrame: true
});

var gPanel = new Ext.GridPanel(grid,{fitToFrame: true});
layout.add('center', gPanel);
grid.render();

var gridFoot = grid.getView().getFooterPanel(true);
var paging = new Ext.PagingToolbar(gridFoot, ds, {
pageSize: 25,
displayInfo: true,
displayMsg: 'Displaying products {0} - {1} of {2}',
emptyMsg: "No products to display"
});

ds.load({params:{start: 0, limit: 25}});


Hope this helps someone out there! Shout with any comments, suggestions.

[tryanDLS]

You may save yourself a few ms by avoiding doing 2 queries, but you give way more than that back with this approach. The cost of serializing a SQL result to a dataset, then to XML, then to string is the far more expensive part of this operation. Unless there is a specific reason, data should never be retrieved into a Dataset - it carries far too much overhead. Instead a DataReader should be used. I don't know if Newton can serialize a DataReader to a JSON string, but even if it can't, it's probably faster to just build the string yourself looping thru the reader, skipping XML entirely. Also, rather than making your SQL complex to get a single result, you could just return the rowcount from the reader.

EDIT: In most cases, you probably want to make an initially query to get the total count, then only query for a page at a time. Unless you have a case where the total is going to change from page to page, you don't need to recount.

[John Sourcer]

Hi Tim,

Thanks for your comments.

You are quite right. It is more expensive to do follow this route. I had forgotten that I was not updating the dataset per se, also I originally returned the XmlDataDocument to an ExtJS XmlReader. I will take a look at this and test for optimization. I do have a case where total can change from page to page as the application is a multiuser item library with 10000+ users worldwide

In most cases, you probably want to make an initially query to get the total count, then only query for a page at a time. Unless you have a case where the total is going to change from page to page, you don't need to recount.

Agreed! In this instance the application is basically a search engine using a dynamic SQL statement with several parameters. Do you think doing a count first with the passed parameters followed by fetching a result set will be less expensive?

[skyey]

good,that i need.thanks

[tryanDLS]

Do you think doing a count first with the passed parameters followed by fetching a result set will be less expensive?
Yes. I wouldn't be overly concerned with executing an additional query to get the count. I'd rather let SQL Server do its job and cache and optimize queries, rather than writing a single more complex and harder to maintain query to do it in one shot.

[leo.pfeifenberger]

Hi,

I agree - a single Statement to get the count would be faster than nesting 3(!) inner Selects...(which is costly per se)

Greet,

Leo

[John Sourcer]

Thanks Leo,

My current application's query is a dynamic T-SQL startement which queries several joined table and a union. It is a search query so it also needs to match a possible search term.

I thought that running the query, (albeit not quite the same query) twice would be more costly because of A) dynamic T-SQL and B) term matching which would be needed for both the result set and the count.

Thanks for the help. Glad to learn something, sometimes the tendency is to think concentrated code is more efficient.:)

nesting 3(!) inner Selects

Another error in my post!:">

I copied my query directly and then shortened it for readability, it has a union in it:


SELECT RowID, col1, col2 FROM
(SELECT row_number() OVER (ORDER BY col1) AS 'RowID', col1, col2 FROM
(SELECT col1, col2 FROM table1 UNION SELECT col1, col2 FROM table2) t) u
WHERE RowID >= @start AND RowID <= @limit

I realise this isn't a SQL forum, but I can't get away with less then 3 selects or can I?

[leo.pfeifenberger]

SELECT RowID, col1, col2 FROM
(SELECT row_number() OVER (ORDER BY col1) AS 'RowID', col1, col2 FROM
(SELECT col1, col2 FROM table1 UNION SELECT col1, col2 FROM table2) t) u
WHERE RowID >= @start AND RowID <= @limit

I realise this isn't a SQL forum, but I can't get away with less then 3 selects or can I?

Hm, even with that UNION there are still 3 nested SELECTS...

When trying to evaluate above Statement...
the innermost Statment selects the complete(!) table1 AND table2 union joined,
than it is narrowed now by filter on @start & @end, which means with every execution the Server has to get all data from both tables, union join them, to throw most of the resultset away almost immediately.

IMHO, a Stored Procedure would be best for this kind of task...

Greets,

Leo

[John Sourcer]

Hi Leo,

Of course I use a stored procedure, dynamic as well. This is just an example:

Broken down:


SELECT col1, col2 FROM table1 UNION SELECT col1, col2 FROM table2

No problem but if you want row_number you have to use a derived table so you get:


SELECT row_number() OVER (ORDER BY col1) AS 'RowID', col1, col2 FROM
(SELECT col1, col2 FROM table1 UNION SELECT col1, col2 FROM table2) t

Now if you want to get rows beteween @start & @limit you once again have to wrap the result set in another derived table.


SELECT RowID, col1, col2 FROM
(SELECT row_number() OVER (ORDER BY col1) AS 'RowID', col1, col2 FROM
(SELECT col1, col2 FROM table1 UNION SELECT col1, col2 FROM table2) t) u
WHERE RowID >= @start AND RowID <= @limit

Is there a better way?:-?

[leo.pfeifenberger]

Why you did that was clear to me :)

But the main point was: is it better to de 2 Selects (one for total rows, one for Query)
or gong for that "all-in-one" solution

i attached a execution plan for this query...

I just had a look how my fav. DALLayer Generator does this - they use something like the following (this is based on a (Log-)Table which holds Error Information) via a Temp Table


BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
--SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[ERROR_IDx] numeric(18, 0)
)

-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ERROR_IDx)'
SET @SQL = @SQL + ' SELECT [ERROR_IDx]'
SET @SQL = @SQL + ' FROM dbo.[ErrorsOccurred]'
IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END

IF LEN(@OrderBy) > 0
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END

-- Populate the temp table
exec sp_executesql @SQL

-- Return total count
SELECT @@ROWCOUNT

--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn

-- Return paged results
SELECT O.[ERROR_IDx], O.[ER_TIME], O.[ER_NUMBER], O.[ER_SOURCE], O.[ER_PAGE], O.[ER_DESC], O.[ER_CODE], O.[ER_LINE], O.[ER_REMOTE_ADDR], O.[ER_REMOTE_HOST], O.[ER_LOCAL_ADDR], O.[SESSION_USER_NAME], O.[DEV_INFO]
FROM
dbo.[ErrorsOccurred] O,
#PageIndex PageIndex
WHERE
O.[ERROR_IDx] = PageIndex.[ERROR_IDx] AND PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID <= @PageUpperBound
ORDER BY
PageIndex.IndexID

END


If that's the fastest way?. Dunno. Don't think so.

You might want to try to come up with different solutions, and profile them...

[Sai Dasika]

Hi,
I am a newbie to EXTJS,I am working with ASP.Net C#,generic handlers to handle my Script. The thing is,i need to know how the Page number is sent to the handler to request the data. How do we get to know which rows are to be displayed when the a button press over page next or page previous is performed i.e., how do we actually know which button (page next or page previous) is pressed :-/.

I am able to display the general rows in the grid,but not able to page them using the paging toolbar.

HELP Needed!:(

[djarquin]

I TRY LINQ TO SQL, AND IS VERY EASY TO PAGING RESULTS, EXAMPLE.



var db = LinqUtil.GetContext();
var query = from ct in db.catalog_tasks
where ct.name.Contains(name)
select ct;
int total = query.Count();
query = query.Skip(start).Take(limit);
//Modified by Juan Fuentes - JSON INSTEAD OF XML
List<JsCatalog> list = new List<JsCatalog>();
foreach (var row in query)
{
list.Add(new JsTaskCatalog
{
id = row.catalog_task_id,
name = row.name
});
}
//Refactor by: David Jacob Jarquin - Oct 2009 -- DELETE
return list.ToJsonString();


We use a List of JsCatalog then we convert that list into JSON using System.Runtime.Serialization.Json, our JsCatalog could be (all properties with [DataMember], will be serialized to javascript):




using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;

public class JsTaskCatalog
{

[DataMember]
public int id;

[DataMember]
public string name;

public JsTaskCatalog()
{

}
}


And finally we add an extention method ToJsonString() object.ToJsonString() will return any object serialized into a json string

To do this we need a static class and static method:



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
using System.IO;
using System.Text;

public static class GenericExtensions
{
//Added: David Jacob Jarquin -- remove Aug 2009
public static string ToJsonString<T>(this T input)
{
DataContractJsonSerializer serializer = new DataContractJsonSerializer(input.GetType());
using (MemoryStream ms = new MemoryStream())
{
serializer.WriteObject(ms, input);
ms.Position = 0;
using (StreamReader reader = new StreamReader(ms))
{
return reader.ReadToEnd();
}
}
}
}