ASP.NET serverside example?

Lethiel

Hi,

First, your DataTables Script is awesome :-) Thank you very much for this. I spent some days trying to implement Lionheart's Script for serverside processing. But I always get the same error about a "json formatting error". Is there any full example for a serverside implementation in asp.net webforms with writing the data to a html table?

It would be really great.
Thanks.
Kevin

relish27

I also would be interested in a .NET solution... am going to try today.

Thanks!

Lethiel

Well.. finally I found a solution with the help of some excellent programmers.. Meanwhile i changed the script a few times. In my solution you can do multiple text searching (if you don't want to have a Searchfield as a pernament column in your database, just create one in the query. It reacts very fast with about 200k rows.. :)

ajaxsearch.aspx.cs
[code]

protected void Page_Load(object sender, EventArgs e)
{
/////////////////////////////////
// Paramter from JQuery - Plugin
/////////////////////////////////

int echo = Int32.Parse(Request.Form["sEcho"]);
int displayLength = Int32.Parse(Request.Form["iDisplayLength"]);
int displayStart = Int32.Parse(Request.Form["iDisplayStart"]);
string search = Request.Form["sSearch"];

/////////////////////////////////
// Search filter - build the where clause
/////////////////////////////////
int totalRecords = 0;
int rec = 0;
StringBuilder sb = new StringBuilder();
string whereClause = string.Empty;
if (!String.IsNullOrEmpty(search))
{
whereClause = " WHERE SearchField like " + "'%" + search.Replace(" ", "%' AND SearchField like '%") + "%'";
}

/////////////////////////////////
// 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", ", ConsumerId");
orderByClause = orderByClause.Replace("1", ", Firstname");
orderByClause = orderByClause.Replace("2", ", Lastname");
orderByClause = orderByClause.Replace("3", ", Birthdate");
orderByClause = orderByClause.Replace("4", ", Street");
orderByClause = orderByClause.Replace("5", ", Nbr");
orderByClause = orderByClause.Replace("6", ", Zipcode");
orderByClause = orderByClause.Replace("7", ", Location");
//Eliminate the first comma of the variable "order"
orderByClause = orderByClause.Remove(0, 1);
}
else
orderByClause = "ConsumerId ASC";
orderByClause = " ORDER BY " + orderByClause;

////////////////////////////////
//T-SQL query - ROW_NUMBER() is used for db side pagination
////////////////////////////////

sb.Clear();
if (String.IsNullOrEmpty(search))
{
goto FURTHER;
}

string query = "SELECT TOP 10 " +
"c.ConsumerId, c.Firstname, c.Lastname, c.Birthdate, c.Searchfield, a.Street, a.Nbr, a.Zipcode, a.Location " +
"FROM CONSUMER c LEFT JOIN ADDRESS a ON c.ConsumerId = a.ConsumerId";

// WHERE CLAUSE
query = query + whereClause;
// ORDERBY CLAUSE
query = query + orderByClause;

//Get result rows from DB
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["test"].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;
//int rec = 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["ConsumerId"] + "\",");
sb.Append("\"" + rdrBrowsers["Firstname"] + "\",");
sb.Append("\"" + rdrBrowsers["Lastname"] + "\",");
sb.Append("\"" + rdrBrowsers["Street"] + " " + rdrBrowsers["Nbr"] + "\",");
sb.Append("\"" + rdrBrowsers["Zipcode"] + "\",");
sb.Append("\"" + rdrBrowsers["Location"] + "\",");
sb.Append("\"" + string.Format("{0:d}", rdrBrowsers["Birthdate"]) + "\",");
sb.Append("\"" + "Edit" + "\",");
sb.Append("\"" + "Add" + "\"");
sb.Append("],");
rec++;
}

FURTHER:
if (rec != 0)
{
outputJson = sb.ToString();
outputJson = outputJson.Remove(outputJson.Length - 1);
}
else
{
outputJson = "";
}
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(rec);
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]

I know, there are some useless variables.. But I'm still coding.. :-)
Kev