ASP.NET + MSSQL + Server Side Processing + Details Hidden Row

andyle2k

Hi all,

Just want to share my experience with getting all these different technologies to work together.

I'm not a programmer, just doing this for fun so if there's anything wrong please let me know or how to do it more efficiently.

First, I must thank Allan for DT of course. This is by far the best way to represent data that I've come across. Big props to you and everyone that contributed to this project.

The ASP.NET code is heavily based on @wenyiwu's work here: http://datatables.net/forums/discussion/comment/28990

I hope this can help somebody out there who like me was struggling for a while to get all of this to work together.

So let's get into it.

ASP.NET:

Web.Config:
[code]







[/code]

[code]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

public partial class ServerSide : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string query = string.Empty;
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"];

//SEARCHING/FILTER - building the WHERE clause
StringBuilder sb = new StringBuilder();
string whereClause = string.Empty;
if (!String.IsNullOrEmpty(search))
{
string[] searchColumns = ConfigurationManager.AppSettings["searchColumns"].ToString().Split(',');
for (int i = 0; i < searchColumns.Count(); i++)
{
if (i == 0)
sb.Append(" WHERE " + searchColumns[i] + " LIKE '%" + search + "%'");
else
sb.Append(" OR " + searchColumns[i] + " LIKE '%" + search + "%'");
}
whereClause = sb.ToString();
}

//ORDERING - building the ORDER BY clause
sb.Clear();
string orderByClause = string.Empty;
string[] sortColumns = ConfigurationManager.AppSettings["sortColumns"].ToString().Split(',');
int sortIndex = int.Parse(Request.Params["iSortCol_0"]);
//checks whether the details column is configured, if yes then minus one from the index to match with columns in the database
if (ConfigurationManager.AppSettings["detailsPresent"].ToString() == "True" & int.Parse(Request.Params["iSortCol_0"]) != 0)
sortIndex = int.Parse(Request.Params["iSortCol_0"]) - 1;
else
sortIndex = int.Parse(Request.Params["iSortCol_0"]);

sb.Append(sortColumns[sortIndex] + " " + Request.Params["sSortDir_0"]);
orderByClause = sb.ToString();
if (String.IsNullOrEmpty(orderByClause))
if (ConfigurationManager.AppSettings["detailsPresent"].ToString() == "True")
orderByClause = sortColumns[1] + " ASC";
else
orderByClause = sortColumns[0] + " ASC";

orderByClause = "ORDER BY " + orderByClause;

//SQL Query - using ROW_NUMBER for pagination
sb.Clear();
string allColumnsString = ConfigurationManager.AppSettings["allColumns"].ToString();
string tableName = ConfigurationManager.AppSettings["tableName"].ToString();
query = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (" + orderByClause + ") AS RowNumber,* FROM ( SELECT ( SELECT COUNT(*) FROM " + tableName + " " + whereClause + " ) AS TotalDisplayRows, (SELECT COUNT(*) FROM " + tableName + ") AS TotalRows," + allColumnsString + " FROM " + tableName + " " + whereClause + " ) RawResults ) Results WHERE RowNumber BETWEEN " + (displayStart + 1).ToString() + " AND " + (displayStart + displayLength).ToString();

//Reading Data from SQL
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["cn2"].ConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
IDataReader rdrBrowsers = cmd.ExecuteReader();

//JSON - building JSON response string
sb.Clear();
string outputJson = string.Empty;
int totalDisplayRecords = 0;
int totalRecords = 0;
string[] allColumns = ConfigurationManager.AppSettings["allColumns"].ToString().Split(',');
while (rdrBrowsers.Read())
{
if (totalRecords == 0)
totalRecords = Int32.Parse(rdrBrowsers["TotalRows"].ToString());
if (totalDisplayRecords == 0)
totalDisplayRecords = Int32.Parse(rdrBrowsers["TotalDisplayRows"].ToString());
sb.Append("{");
for (int i = 0; i < allColumns.Count(); i++)
{
string columnName = allColumns[i];
string result = rdrBrowsers[allColumns[i]].ToString()
.Replace("\"", "'")
.Replace(@"\", @"\\")
.Replace("\r\n", @"
").Replace(System.Environment.NewLine, @"
");
if (i != allColumns.Count() - 1)
sb.Append("\"" + columnName + "\": \"" + result + "\",");
else
sb.Append("\"" + columnName + "\": \"" + result + "\"");
}

sb.Append("},");
}
//Check if whether any results were returned
if (String.IsNullOrEmpty(sb.ToString()))
{
totalRecords = 0;
totalDisplayRecords = 0;
outputJson = "";
}
else
{
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();

//RESPOND - clearing response/headers
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.Write(outputJson);
Response.Flush();
Response.End();

}
}
[/code]

andyle2k

JavaScript:


[code]

var anOpen = [];
var sImageUrl = "img/";

$(document).ready(function () {
var oTable = $('#example').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "ServerSide.aspx",
"aoColumnDefs": [
{ "bSortable": false, "aTargets": [ 0 ] }
],
"aoColumns": [
{
"mData": null,
"sClass": "control center",
"sDefaultContent": ''
},
{ "mData": "engine" },
{ "mData": "browser" },
{ "mData": "platform" },
{ "mData": "version" },
{ "mData": "grade" }
]
});

$(document).on("click", "#example td.control", function () {
var nTr = this.parentNode;
var i = $.inArray(nTr, anOpen);
if (i === -1) {
$('img', this).attr('src', "img/details_close.png");
var nDetailsRow = oTable.fnOpen(nTr, fnFormatDetails(oTable, nTr), 'details');
$('div.innerDetails', nDetailsRow).slideDown();
anOpen.push(nTr);
}
else {
$('img', this).attr('src', "img/details_open.png");
$('div.innerDetails', $(nTr).next()[0]).slideUp(function () {
oTable.fnClose(nTr);
anOpen.splice(i, 1);
});
}
});

});

function fnFormatDetails(oTable, nTr) {
var oData = oTable.fnGetData(nTr);
var sOut =
'' +
'' +
'Version' + oData.version + '' +
'' +
'';
return sOut;
}

[/code]

HTML:

[code]



Details
engine
browser
platform
version
grade




[/code]

tangerine

That's very generous, andyle2k. Almost makes me wish I used asp.net!