serverside processing for asp.net c# with sql server

wenyiwu

Allen, first, thank you so much for your plug-in and awesome support work. it is truly wonderful.
below is the c# server side processing code that i wrote after viewing the classic asp example from the gallery. so you may see a bit similarity in the approach at the beginning. hopefully this will be helpful to others.

[code]
using System;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace jQuery.datatable
{
public partial class ServerSideProcessor : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int echo = Int32.Parse(Request.Params["sEcho"]);
int displayLength = Int32.Parse(Request.Params["iDisplayLength"]);
int displayStart = Int32.Parse(Request.Params["iDisplayStart"]);
string search = Request.Params["sSearch"];

///////////
//SEARCH (filter)
//- build the where clause
////////
StringBuilder sb = new StringBuilder();
string whereClause = string.Empty;
if (!String.IsNullOrEmpty(search))
{
sb.Append(" WHERE engine LIKE '%");
sb.Append(search);
sb.Append("%' OR browser LIKE '%");
sb.Append(search);
sb.Append("%' OR platform LIKE '%");
sb.Append(search);
sb.Append("%' OR version LIKE '%");
sb.Append(search);
sb.Append("%' OR grade LIKE '%");
sb.Append(search);
sb.Append("%'");
whereClause = sb.ToString();
}

///////////////
//ORDERING
//- build the order by clause
//////////////
sb.Clear();
string orderByClause = string.Empty;
//Check which column is to be sorted by in which direction
for (int i = 0; i < 11; i++)
{
if (Request.Params["bSortable_" + i] == "true")
{
sb.Append(Request.Params["iSortCol_" + i]);
sb.Append(" ");
sb.Append(Request.Params["sSortDir_" + i]);
}
}
orderByClause = sb.ToString();
//Replace the number corresponding the column position by the corresponding name of the column in the database
if (!String.IsNullOrEmpty(orderByClause))
{
orderByClause = orderByClause.Replace("0", ", engine");
orderByClause = orderByClause.Replace("1", ", browser");
orderByClause = orderByClause.Replace("2", ", platform");
orderByClause = orderByClause.Replace("3", ", version");
orderByClause = orderByClause.Replace("4", ", grade");
//Eliminate the first comma of the variable "order"
orderByClause = orderByClause.Remove(0, 1);
}
else
orderByClause = "engine ASC";
orderByClause = "ORDER BY " + orderByClause;

/////////////
//T-SQL query
//- ROW_NUMBER() is used for db side pagination
/////////////
sb.Clear();
string query = "SELECT * FROM ( SELECT ROW_NUMBER() OVER ({0}) AS RowNumber,* FROM ( SELECT ( SELECT COUNT(*) FROM Ajax {1} ) AS TotalDisplayRows, (SELECT COUNT(*) FROM Ajax) AS TotalRows,engine,browser,platform,version,grade FROM Ajax {1} ) RawResults ) Results WHERE RowNumber BETWEEN {2} AND {3}";
query = String.Format(query, orderByClause, whereClause, displayStart + 1, displayStart + displayLength);

//Get result rows from DB
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString
);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
IDataReader rdrBrowsers = cmd.ExecuteReader();

/////////////
/// JSON output
/// - build JSON output from DB results
/// ///////////
sb.Clear();
string outputJson = string.Empty;
int totalDisplayRecords = 0;
int totalRecords = 0;
while (rdrBrowsers .Read())
{
if (totalRecords == 0)
totalRecords = Int32.Parse(rdrBrowsers ["TotalRows"].ToString());
if (totalDisplayRecords == 0)
totalDisplayRecords = Int32.Parse(rdrBrowsers ["TotalDisplayRows"].ToString());
sb.Append("[");
sb.Append("\"" + rdrBrowsers ["engine"] + "\",");
sb.Append("\"" + rdrBrowsers ["browser"] + "\",");
sb.Append("\"" + rdrBrowsers ["platform"] + "\",");
sb.Append("\"" + rdrBrowsers ["version"] + "\",");
sb.Append("\"" + rdrBrowsers ["grade"] + "\"");
sb.Append("],");
}
outputJson = sb.ToString();
outputJson = outputJson.Remove(outputJson.Length - 1);
sb.Clear();

sb.Append("{");
sb.Append("\"sEcho\": ");
sb.Append(echo);
sb.Append(",");
sb.Append("\"iTotalRecords\": ");
sb.Append(totalRecords);
sb.Append(",");
sb.Append("\"iTotalDisplayRecords\": ");
sb.Append(totalDisplayRecords);
sb.Append(",");
sb.Append("\"aaData\": [");
sb.Append(outputJson);
sb.Append("]}");
outputJson = sb.ToString();

/////////////
/// Write to Response
/// - clear other HTML elements
/// - flush out JSON output
/// ///////////
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.Write(outputJson);
Response.Flush();
Response.End();
}
}
}
[/code]

AnthonyV

Thank you very much for sharing. I was trying to figure out what to do about the paging and searching on oracle.

aplav

Thank you. Could you also show the code for the client-side?

wenyiwu

Client-side datatable initialization code

[code]
idTable = $('#tblOrgs').dataTable({
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "ServerSideProcessor.aspx"
});
[/code]

the server side code is in ServerSideProcessor.aspx.cs

wenyiwu

After I wrote my code, I was able to find a better solution online. Check out the post below where the author uses a WCF service and he goes more in depth too. Good article.

http://rantdriven.com/post/Using-Datatablesnet-JQuery-Plug-in-with-WCF-Services.aspx

aplav

Hello,
Please explain the query in your serverside processing. What does OVER {0},FROM Ajax {1} and WHERE RowNumber BETWEEN {2} AND {3} mean? I never saw this syntax. Thanks. string query = "SELECT * FROM ( SELECT ROW_NUMBER() OVER ({0}) AS RowNumber,* FROM ( SELECT ( SELECT COUNT(*) FROM Ajax {1} ) AS TotalDisplayRows, (SELECT COUNT(*) FROM Ajax) AS TotalRows,engine,browser,platform,version,grade FROM Ajax {1} ) RawResults ) Results WHERE RowNumber BETWEEN {2} AND {3}";

aplav

I tried to run query and it's giving multiple errors. For example about {} . Could you please verify the query? thanks.

dhrobbins

Here is a .Net solution for WebForms:

http://activeengine.wordpress.com/2011/02/09/datatablepager-now-has-multi-column-sort-capability-for-datatables-net/

wenyiwu

@aplav, the "{0}", "{1}", etc are .NET ways of formatting strings. if you are not developing in .net, you have to substitute them w/ actual values...please see the link below for more details

http://msdn.microsoft.com/en-us/library/system.string.format.aspx

Nico

I tried to create a ServerSideProcessor.aspx with VS2003 net 1.1
If I try to navigate http://localhost/.../ServerSideProcessor.aspx?sEcho=0&iDisplayLength=30&iDisplayStart=0.
This work and return data like this
{"sEcho": 0,"iTotalRecords": 1007,"iTotalDisplayRecords": 1007,"aaData": [["02010U001","02010","dato1","",""],["02010U002","02010","dato2","123456",""],.....]}

I use the server_side.html example downloaded from datatables.net.
I modified the js section

[code] $(document).ready
(
function() {
$('#example').dataTable({
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource":"/.../ServerSideProcessor.aspx"
} );
} );[/code]

But the table doesn't work !
Remain "Loading data from server"

What is wrong ?

Thanks

[modified]

OPSS sorry

Now work ;-)

Great job !!

Raven

Hey this post is really cool and trying to make it work.
Help me out just abit. i use store procedures to connect to my sql database, i cant use behind hard code, a query like that. I notice the {0} {1} arguements in the string which seems to be needed.
So im gonna try to leave it out and convert to store proc and use the arguements differently.
How Would something like be correct if compared to the ajax query
[code]
Create proc Test
as
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (order by RawResults.TotalDisplayRows) AS RowNumber,*
FROM
(
SELECT
(
SELECT COUNT(*)
FROM NormalFine n (nolock)
)
AS TotalDisplayRows,
(
SELECT COUNT(*) FROM NormalFine n (nolock)
)
AS TotalRows,
n.ReferenceNumber,n.Amount,n.DateOfOffence
FROM NormalFine n (nolock)
)
RawResults
)
Results
WHERE RowNumber BETWEEN 10 AND 20

[/code]
[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER ({0}) AS RowNumber,*
FROM
(
SELECT
(
SELECT COUNT(*)
FROM Ajax {1}
)
AS TotalDisplayRows,
(
SELECT COUNT(*)
FROM Ajax
)
AS TotalRows,sReferenceNumber,sRegistrationNumber,DateOffence,dAmount,sName,TimeOfoffence
FROM Ajax {1}
)
RawResults
)
Results
WHERE RowNumber BETWEEN {2} AND {3}
[/code]

Raven

Hi guys,

I did the same thing that @Nico did
http://localhost/.../ServerSideProcessor.aspx?sEcho=0&iDisplayLength=30&iDisplayStart=0.
and it only displays a page with my data, no table or anything so it looks more like jeberish.

what am i doing wrong? Im using the coding Wenyiwu uses,

my page with the repeater on and jquery is Main.aspx, Wenyiwu is in the behind coding.
[code]

$(document).ready(function () {
$('#tblOscarNominees').dataTable({
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "Main.aspx"
});
});

[/code]

any reply will be helpfull than where i am at now.

Raven

i feel so demotivated, im properly the only guy on this site that cant get serverside processing right, and because i cant get it right, i cant include it into any of my companies projects