Previously I had blogged about rendering the GridView rows on demand, which will render the rows while scrolling the GridView down to the end of the rows. The example in that post does not have any functionality such as updating the records, deleting the records. This post talks about doing such functionalities.
As discussed in previously post, the implementation done using AJAX concept which will rend the rows page by page from the server from Javascript. As the implementation down using AJAX, the GridView events such as Edit, Delete will not fire as usual. If we want to implement those events, we need to do that using Javascript only.
The following example implements the functionalities such as Edit, Delete on the GridView. I have done this implementation in two ways - without sorting and with sorting.
Note: For understanding the concept of which the implementation done, Please refer the previous post.
The use case of this implementation is as follows –
Implementation without sorting.
The ASPX script (AJAXGrid.aspx)
The GridView script is same as before.
The C# Code (RowsBuilder.aspx.cs)
The Javascript only has a change in this example. The Javascript as follows.
download the working example of the source code in C# here and in VB here.
As discussed in previously post, the implementation done using AJAX concept which will rend the rows page by page from the server from Javascript. As the implementation down using AJAX, the GridView events such as Edit, Delete will not fire as usual. If we want to implement those events, we need to do that using Javascript only.
The following example implements the functionalities such as Edit, Delete on the GridView. I have done this implementation in two ways - without sorting and with sorting.
Note: For understanding the concept of which the implementation done, Please refer the previous post.
The use case of this implementation is as follows –
- Show list of records in a GridView with all sort of customizations possible (such as colors, data formatting etc.,).
- The GridView should show all the records in a single page without having pagination.
- The GridView should have a scroll bar to scroll and see the records up and down.
- Initially the GridView should show only first two pages on the screen (or decided by the programmer on the code).
- When user scrolls down to see last records, the page should fetch the next 10 records (or decided by programmer on the code) and add with the records at the last shown in the GridView.
- When user scrolls further down to see the newly added records, the page should fetch the next 10 records and add at the last to the GridView. This repeats until last record reached.
- The GridView should show two columns such as Edit and Delete.
- The pressing Edit button, there should be a popup which will show the records of that particular row.
- When pressing Save button, the record I the database must get updated.
Implementation without sorting.
The ASPX script (AJAXGrid.aspx)
<div id="GridViewContainer" style="width:100%;height:400px;" onscroll="RefreshRows(this)" >
<asp:GridView ID="grdViewProducts" CssClass="ViewProducts" runat="server"
AutoGenerateColumns="False" TabIndex="1" RowStyle-CssClass="RowStyle"
AllowPaging="false" DataKeyNames="ProductID" Width="100%" GridLines="Both" CellPadding="3"
AllowSorting="false" BackColor="White">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ID"
SortExpression="ProductID">
<ItemStyle Width="5%" />
</asp:BoundField>
<asp:BoundField DataField="ProductName" HeaderText="Product Name"
SortExpression="ProductName">
<ItemStyle Width="26%" />
</asp:BoundField>
<asp:BoundField DataField="CategoryName" HeaderText="Category"
SortExpression="CategoryName" >
<ItemStyle Width="17%" />
</asp:BoundField>
<asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity/Unit"
SortExpression="QuantityPerUnit" >
<ItemStyle Width="17%" />
</asp:BoundField>
<asp:BoundField DataField="UnitPrice" HeaderText="Unit Price"
SortExpression="UnitPrice" DataFormatString="{0:#0.00}" >
<ItemStyle Width="10%" />
</asp:BoundField>
<asp:BoundField DataField="UnitsInStock" HeaderText="Units In Stock"
SortExpression="UnitsInStock" >
<ItemStyle Width="10%" />
</asp:BoundField>
<asp:BoundField DataField="UnitsOnOrder" HeaderText="Units On Order"
SortExpression="UnitsOnOrder" >
<ItemStyle Width="10%" />
</asp:BoundField>
<asp:TemplateField HeaderText="Delete">
<ItemStyle Width="5%" />
<ItemTemplate>
<input id="btnDelete" type="button" value="Delete" onclick="ProductDeleteClient(this,'<%# Eval("ProductID") %>')" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit">
<ItemStyle Width="5%" />
<ItemTemplate>
<input id="btnProductID" type="button" value="Edit" onclick="ProductEditClient(this,'<%# Eval("ProductID") %>')" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EmptyDataTemplate>
<div>
<p style="color:Red">No search results found.</p>
</div>
</EmptyDataTemplate>
<PagerSettings Position="Bottom" />
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<AlternatingRowStyle BackColor="#F7F7F7" />
<SortedAscendingCellStyle BackColor="#F4F4FD" />
<SortedAscendingHeaderStyle BackColor="#5A4C9D" />
<SortedDescendingCellStyle BackColor="#D8D8F0" />
<SortedDescendingHeaderStyle BackColor="#3E3277" />
</asp:GridView>
<div id="divChildGrid">
</div>
</div>
<input id="hiddenTotalRecords" type="hidden" value="0" runat="server" />
<input id="hiddenPageRecordCount" type="hidden" value="0" runat="server" />The C# Codebehind (AJAXGrid.aspx.cs)protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
hiddenPageRecordCount.Value = ConfigurationManager.AppSettings["PageRecordCount"].ToString();
BindGrid();
BindCategory();
}
}
private void BindGrid()
{
// Defining int variable to get the Total Record Count from Data layer
int totalRecordCount = 0;
// Getting how many records required to show in the Grid per page
int intPageRecordCount = Convert.ToInt32(hiddenPageRecordCount.Value);
ProductDAO objProductDAO = new ProductDAO();
IList<ProductView> ProductViewList = objProductDAO.GetProducts(1, intPageRecordCount, "ProductID", SortType.Ascending, out totalRecordCount); // Getting First three pages
//Adding one empty row for just to show the grid
if (ProductViewList.Count == 0)
ProductViewList.Add(new ProductView());
grdViewProducts.DataSource = ProductViewList;
grdViewProducts.DataBind();
// Assign the Total Record count to
hiddenTotalRecords.Value = totalRecordCount.ToString();
}
private void BindCategory()
{
IList<Category> category = new List<Category>();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
string strSQL = "select CategoryId, CategoryName from Categories";
SqlCommand command = new SqlCommand(strSQL, connection);
command.CommandType = CommandType.Text;
connection.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
DDLCategoryName.DataTextField = "CategoryName";
DDLCategoryName.DataValueField = "CategoryId";
DDLCategoryName.DataSource = dr;
DDLCategoryName.DataBind();
}
}The ASPX script (RowsBuilder.aspx)The GridView script is same as before.
The C# Code (RowsBuilder.aspx.cs)
protected void Page_Load(object sender, EventArgs e)
{
if ((!Page.IsPostBack) && ((Request.QueryString["PageId"] != null) || (Request.QueryString["RecordId"] != null)))
{
Response.Clear();
Response.ContentType = "text/xml";
BindGrid();
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
if (Request.QueryString["RecordId"] != null)
grdAJAXViewProducts.Rows[0].RenderControl(htmlWrite);
else
grdAJAXViewProducts.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
}
public override void VerifyRenderingInServerForm(Control control)
{
}
private void BindGrid()
{
if (Request.QueryString["PageId"] != null)
{
// Defining int variable to get the Total Record Count from Data layer
int totalRecordCount = 0;
string strSortExpression = string.Empty;
SortType sortType = SortType.Ascending;
if (Request.QueryString["SortExpression"] != null)
{
// Get the SortByExpression and SortType from Query String (by default, that will be updated in script)
strSortExpression = Request.QueryString["SortExpression"].ToString().Split(",".ToCharArray())[0];
sortType = (SortType)Enum.Parse(typeof(SortType), Request.QueryString["SortExpression"].ToString().Split(",".ToCharArray())[1], true);
}
// Getting how many records required to show in the Grid per page
int intPageRecordCount = Convert.ToInt32(Request.QueryString["PageRecordsCount"].ToString());
ProductDAO objProductDAO = new ProductDAO();
IList<ProductView> ProductViewList = objProductDAO.GetProducts(Convert.ToInt32(Request.QueryString["PageId"].ToString()), intPageRecordCount, strSortExpression, sortType, out totalRecordCount);
//Adding one empty row for just to show the grid
if (ProductViewList.Count == 0)
ProductViewList.Add(new ProductView());
grdAJAXViewProducts.DataSource = ProductViewList;
grdAJAXViewProducts.DataBind();
}
}The Javascriptvar is_ie = (navigator.userAgent.indexOf('MSIE') >= 0) ? 1 : 0;
var is_ie5 = (navigator.appVersion.indexOf("MSIE 5.5") != -1) ? 1 : 0;
var xmlHttp;
var varStartAJAXPage = 3;
var varPageOnBehind = 0;
function pageLoad() {
GetChildGrid(varStartAJAXPage);
}
window.onload = pageLoad;
var selectedControl;
function ProductDeleteClient(ctrl, Id) {
if (parseInt(Id) > 0) {
selectedControl = ctrl;
var url = 'ProjectDetails.ashx?ProductID=' + Id +
'&DateTime=' + Date() +
'&Type=Delete';
xmlHttp = createAjaxObject();
if (xmlHttp) {
xmlHttp.open('get', url, false);
xmlHttp.onreadystatechange = function () {
HandleProductDeleteResponse();
}
xmlHttp.send(null);
}
}
}
function HandleProductDeleteResponse() {
// If Response completed
if (xmlHttp.readyState == 4) {
var parenttr, tempSelectedControl = selectedControl;
do {
if (tempSelectedControl.parentNode != null) {
if (tempSelectedControl.parentNode.className == 'RowStyle') {
parenttr = tempSelectedControl.parentNode;
break;
}
else {
if (tempSelectedControl.parentNode != null)
tempSelectedControl = tempSelectedControl.parentNode;
}
}
else
break;
} while (1 == 1);
parenttr.style.display = 'none';
alert('Product Deleted Successfully');
xmlHttp.abort();
}
}
function ProductEditClient(ctrl, Id) {
if (parseInt(Id) > 0) {
document.getElementById("divHidden").style.display = 'block';
document.getElementById("ProductInfoEdit").style.display = 'block';
var url = 'ProjectDetails.ashx?ProductID=' + Id +
'&DateTime=' + Date() +
'&Type=Category';
xmlHttp = createAjaxObject();
if (xmlHttp) {
xmlHttp.open('get', url, true);
xmlHttp.onreadystatechange = function () {
HandleProductInfoResponse();
}
selectedControl = ctrl;
xmlHttp.send(null);
}
}
}
function HandleProductInfoResponse() {
// If Response completed
if (xmlHttp.readyState == 4) {
if (xmlHttp.status == 200) {
// Here is the response
var str = xmlHttp.responseText;
var productInfo = eval("(" + str + ")");
document.getElementById('lblProductID').innerHTML = productInfo["ProductID"];
document.getElementById('txtProductName').value = productInfo["ProductName"];
document.getElementById('<%= DDLCategoryName.ClientID %>').value = productInfo["CategoryID"];
document.getElementById('txtQuantityPerUnit').value = productInfo["QuantityPerUnit"];
document.getElementById('txtUnitPrice').value = productInfo["UnitPrice"];
document.getElementById('txtUnitsInStock').value = productInfo["UnitsInStock"];
document.getElementById('txtUnitsOnOrder').value = productInfo["UnitsOnOrder"];
xmlHttp.abort();
}
}
}
function ProductCancel() {
document.getElementById("divHidden").style.display = 'none';
document.getElementById("ProductInfoEdit").style.display = 'none';
}
function ProductSave() {
if (parseInt(document.getElementById('lblProductID').innerHTML) > 0) {
var url = 'ProjectDetails.ashx?Type=Save' +
'&ProductId=' + document.getElementById('lblProductID').innerHTML +
'&ProductName=' + document.getElementById('txtProductName').value +
'&CategoryID=' + document.getElementById('<%= DDLCategoryName.ClientID %>').value +
'&QuantityPerUnit=' + document.getElementById('txtQuantityPerUnit').value +
'&UnitPrice=' + document.getElementById('txtUnitPrice').value +
'&UnitsInStock=' + document.getElementById('txtUnitsInStock').value +
'&UnitsOnOrder=' + document.getElementById('txtUnitsOnOrder').value +
'&DateTime=' + Date();
xmlHttp = createAjaxObject();
if (xmlHttp) {
xmlHttp.open('get', url, false);
xmlHttp.onreadystatechange = function () {
HandleSaveResponse();
}
xmlHttp.send(null);
}
}
}
function HandleSaveResponse() {
if (xmlHttp.readyState == 4) {
GetRecord(document.getElementById('lblProductID').innerHTML);
ProductCancel();
alert('Product Saved Successfully');
xmlHttp.abort();
}
}
function GetRecord(recordId) {
if (recordId > 0) {
var url = 'ProjectDetails.ashx?DateTime=' + Date() +
'&Type=Category' +
'&ProductID=' + recordId;
xmlHttp = createAjaxObject();
if (xmlHttp) {
xmlHttp.open('get', url, false);
xmlHttp.onreadystatechange = function () {
HandleRecordResponse();
}
xmlHttp.send(null);
}
}
}
function HandleRecordResponse() {
// If Response completed
if (xmlHttp.readyState == 4) {
if (xmlHttp.status == 200) {
var parenttr, tempSelectedControl = selectedControl;
do {
if (tempSelectedControl.parentNode != null) {
if (tempSelectedControl.parentNode.className == 'RowStyle') {
parenttr = tempSelectedControl.parentNode;
break;
}
else {
if (tempSelectedControl.parentNode != null)
tempSelectedControl = tempSelectedControl.parentNode;
}
}
else
break;
} while (1 == 1);
// Here is the response
var str = xmlHttp.responseText;
var productInfo = eval("(" + str + ")");
parenttr.cells[1].innerText = productInfo["ProductName"];
parenttr.cells[2].innerText = productInfo["CategoryName"];
parenttr.cells[3].innerText = productInfo["QuantityPerUnit"];
parenttr.cells[4].innerText = productInfo["UnitPrice"];
parenttr.cells[5].innerText = productInfo["UnitsInStock"];
parenttr.cells[6].innerText = productInfo["UnitsOnOrder"];
xmlHttp.abort();
}
}
}
function CheckNumber(txtId) {
if (isNaN(document.getElementById(txtId).value)) {
alert("Please enter only numbers");
document.getElementById(txtId).focus();
}
}
/* This function requests the HTTPRequest, will be used to render the Dynamic content html markup
* and it will call HandleResponse to handle the response
*/
function GetChildGrid(pageId) {
// Get the Div
var childGridDiv = document.getElementById("divChildGrid");
if (childGridDiv) {
// If the Child Grid is not fetched, then go and fetch it.
var varTotalRecords = document.getElementById('<%= hiddenTotalRecords.ClientID %>').value;
var varPageRecordCount = document.getElementById('<%= hiddenPageRecordCount.ClientID %>').value;
if ((pageId > 0) && (parseInt(varTotalRecords) > (parseInt(varPageRecordCount) * (parseInt(pageId) - 1)))) {
var url = 'RowsBuilder.aspx?PageId=' + pageId +
'&DateTime=' + Date() +
'&PageRecordsCount=' + varPageRecordCount +
'&SortExpression=ProductID,Ascending';
xmlHttp = createAjaxObject();
if (xmlHttp) {
xmlHttp.open('get', url, true);
xmlHttp.onreadystatechange = function () {
HandleResponse();
}
varPageOnBehind = varStartAJAXPage;
xmlHttp.send(null);
}
}
}
}
/* This function is used to handler the http response */
function HandleResponse() {
var childGridDiv = document.getElementById("divChildGrid");
// If Response completed
if (xmlHttp.readyState == 4) {
if (xmlHttp.status == 200) {
// Here is the response
var str = xmlHttp.responseText;
var divRows = document.createElement('div');
divRows.id = "div_" + varPageOnBehind;
divRows.className = "PartialGrid";
divRows.innerHTML = str;
childGridDiv.appendChild(divRows);
varStartAJAXPage++;
xmlHttp.abort();
}
}
}
function RefreshRows(div) {
if (varStartAJAXPage - varPageOnBehind > 0) {
if (div.scrollHeight - (div.scrollTop + div.offsetHeight) < 200) {
varPageOnBehind = varStartAJAXPage;
GetChildGrid(varStartAJAXPage, true, true);
}
}
}
/* function to create Ajax object */
function createAjaxObject() {
var ro;
var browser = navigator.appName;
if (browser == "Microsoft Internet Explorer") {
if (xmlHttp != null) {
xmlHttp.abort();
}
ro = new ActiveXObject("Microsoft.XMLHTTP");
}
else {
if (xmlHttp != null) {
xmlHttp.abort();
}
ro = new XMLHttpRequest();
}
return ro;
}
/* Get the XML Http Object */
function GetXmlHttpObject(handler) {
var objXmlHttp = null;
if (is_ie) {
var strObjName = (is_ie5) ? 'Microsoft.XMLHTTP' : 'Msxml2.XMLHTTP';
try {
objXmlHttp = new ActiveXObject(strObjName);
objXmlHttp.onreadystatechange = handler;
}
catch (e) {
alert('Object could not be created');
return;
}
}
return objXmlHttp;
}
function xmlHttp_Get(xmlhttp, url) {
xmlhttp.open('GET', url, true);
xmlhttp.send(null);
}The StyleSheet/* A scrolable div */
#GridViewContainer
{
overflow: auto;
}
.ViewProducts
{
border-top-style:solid;
border-left-style:solid;
border-right-style:solid;
border-bottom-style:none;
}
.ViewChildProducts
{
border-top-style:solid;
border-left-style:solid;
border-right-style:solid;
border-bottom-style:none;
}
#divHidden
{
width: 100%;
height:100%;
background-color:Gray;
background: url(../../Images/hidden.png) repeat;
position: absolute;
top: 0;
left: 0;
z-index: 100;
display: none;
}
#ProductInfoEdit
{
width: 400px;
height: 210px;
background-color: White;
position: absolute;
left: 35%;
top: 35%;
margin-top: -50px;
margin-left: -168px;
vertical-align:middle;
z-index: 1001;
display: none;
border: 1px solid blue;
margin: 2px;
padding: 10px;
}The ProductDAO codepublic class ProductDAO
{
public IList<ProductView> GetProducts(int currentPageNo, int pageRecordsCount, string sortBy, SortType sortType, out int totalRecordCount)
{
SqlConnection connection = null;
SqlDataReader dataReader = null;
try
{
IList<ProductView> productViewList = new List<ProductView>();
connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
SqlDataAdapter dataAdaptor = new SqlDataAdapter("SP_GET_PRODUCTS", connection);
dataAdaptor.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
dataAdaptor.SelectCommand.Parameters.Add(new SqlParameter("@CurrentPageNo", SqlDbType.Int));
dataAdaptor.SelectCommand.Parameters["@CurrentPageNo"].Value = currentPageNo;
dataAdaptor.SelectCommand.Parameters.Add(new SqlParameter("@PageRecordsCount", SqlDbType.Int));
dataAdaptor.SelectCommand.Parameters["@PageRecordsCount"].Value = pageRecordsCount;
dataAdaptor.SelectCommand.Parameters.Add(new SqlParameter("@SortBy", SqlDbType.VarChar));
dataAdaptor.SelectCommand.Parameters["@SortBy"].Value = sortBy;
dataAdaptor.SelectCommand.Parameters.Add(new SqlParameter("@SortType", SqlDbType.VarChar));
dataAdaptor.SelectCommand.Parameters["@SortType"].Value = ((sortType == null) ? string.Empty : (sortType == SortType.Ascending ? "Asc" : "Desc"));
dataAdaptor.SelectCommand.Parameters.Add(new SqlParameter("@TotalRecordCount", SqlDbType.Int));
dataAdaptor.SelectCommand.Parameters["@TotalRecordCount"].Direction = ParameterDirection.Output;
connection.Open();
DataSet dataSet = new DataSet();
dataAdaptor.Fill(dataSet);
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
ProductView productView = new ProductView();
productView.ProductID = Convert.ToInt32(dataRow["ProductID"].ToString());
productView.ProductName = dataRow["ProductName"].ToString();
productView.CompanyName = dataRow["CompanyName"].ToString();
productView.CategoryName = dataRow["CategoryName"].ToString();
productView.QuantityPerUnit = dataRow["QuantityPerUnit"].ToString();
productView.UnitPrice = Convert.ToDouble(dataRow["UnitPrice"].ToString());
productView.UnitsInStock = Convert.ToInt32(dataRow["UnitsInStock"].ToString());
productView.UnitsOnOrder = Convert.ToInt32(dataRow["UnitsOnOrder"].ToString());
productView.ReorderLevel = Convert.ToInt32(dataRow["ReorderLevel"].ToString());
productViewList.Add(productView);
}
// To make more processing time
//for (int i = 0; i < (1000000000/2); i++) { int b = 5 + 4 + 3 + 2 - 3; }
totalRecordCount = Convert.ToInt32(dataAdaptor.SelectCommand.Parameters["@TotalRecordCount"].Value);
connection.Close();
//return
return productViewList;
}
catch (Exception ex)
{
if (connection != null) connection.Close();
if (dataReader != null) dataReader.Close();
// Log
throw ex;
}
}
}The ProjectDetails.ashx - This handler is used for getting the product details while editing the row and to save the record and delete the same.public class ProjectDetails : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
try
{
if (context.Request.QueryString["Type"] == "Category")
{
//IList<string> category = new List<string>();
ProductView product = new ProductView();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
string strSQL = "Select ProductID, ProductName, CategoryID, CategoryName, QuantityPerUnit, UnitPrice, UnitsOnOrder, UnitsInStock from ProductView Where ProductID = " + context.Request.QueryString["ProductID"].ToString();
SqlCommand command = new SqlCommand(strSQL, connection);
command.CommandType = CommandType.Text;
connection.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
product.ProductID = Convert.ToInt32(dr["ProductID"].ToString());
product.ProductName = dr["ProductName"].ToString();
product.CategoryID = Convert.ToInt32(dr["CategoryID"].ToString());
product.CategoryName = dr["CategoryName"].ToString();
product.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
product.UnitPrice = Convert.ToDouble(dr["UnitPrice"].ToString());
product.UnitsInStock = Convert.ToInt32(dr["UnitsInStock"].ToString());
product.UnitsOnOrder = Convert.ToInt32(dr["UnitsOnOrder"].ToString());
}
}
System.Web.Script.Serialization.JavaScriptSerializer objSerializer =
new System.Web.Script.Serialization.JavaScriptSerializer();
context.Response.Write(objSerializer.Serialize(product));
}
if (context.Request.QueryString["Type"] == "Delete")
{
ProductView product = new ProductView();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
string strSQL = "Delete From Products Where ProductID = " + context.Request.QueryString["ProductID"].ToString();
SqlCommand command = new SqlCommand(strSQL, connection);
command.CommandType = CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
}
context.Response.Write("Success");
}
if (context.Request.QueryString["Type"] == "Save")
{
ProductView product = new ProductView();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
string strSQL = "Update Products Set " +
"ProductName = '" + context.Request.QueryString["ProductName"].Replace("'", "''").ToString() + "', " +
"CategoryID = '" + context.Request.QueryString["CategoryID"].ToString() + "', " +
"QuantityPerUnit = '" + context.Request.QueryString["QuantityPerUnit"].ToString() + "', " +
"UnitPrice = '" + context.Request.QueryString["UnitPrice"].ToString() + "', " +
"UnitsOnOrder = '" + context.Request.QueryString["UnitsOnOrder"].ToString() + "', " +
"UnitsInStock = '" + context.Request.QueryString["UnitsInStock"].ToString() + "' " +
"Where ProductID = '" + context.Request.QueryString["ProductID"].ToString() + "' ";
SqlCommand command = new SqlCommand(strSQL, connection);
command.CommandType = CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
}
context.Response.Write("Success");
}
}
catch (Exception ex)
{
context.Response.Write("error");
}
}
public bool IsReusable
{
get
{
return false;
}
}
}Implementation with sorting.The Javascript only has a change in this example. The Javascript as follows.
var is_ie = (navigator.userAgent.indexOf('MSIE') >= 0) ? 1 : 0;
var is_ie5 = (navigator.appVersion.indexOf("MSIE 5.5") != -1) ? 1 : 0;
var xmlHttp;
var varStartAJAXPage = 3;
var varPageOnBehind = 0;
function pageLoad() {
GetChildGrid(varStartAJAXPage);
}
window.onload = pageLoad;
var selectedControl;
function ProductDeleteClient(ctrl, Id) {
if (parseInt(Id) > 0) {
selectedControl = ctrl;
var url = 'ProjectDetails.ashx?ProductID=' + Id +
'&DateTime=' + Date() +
'&Type=Delete';
xmlHttp = createAjaxObject();
if (xmlHttp) {
xmlHttp.open('get', url, false);
xmlHttp.onreadystatechange = function () {
HandleProductDeleteResponse();
}
xmlHttp.send(null);
}
}
}
function HandleProductDeleteResponse() {
// If Response completed
if (xmlHttp.readyState == 4) {
var parenttr, tempSelectedControl = selectedControl;
do {
if (tempSelectedControl.parentNode != null) {
if (tempSelectedControl.parentNode.className == 'RowStyle') {
parenttr = tempSelectedControl.parentNode;
break;
}
else {
if (tempSelectedControl.parentNode != null)
tempSelectedControl = tempSelectedControl.parentNode;
}
}
else
break;
} while (1 == 1);
parenttr.style.display = 'none';
alert('Product Deleted Successfully');
xmlHttp.abort();
}
}
function ProductEditClient(ctrl, Id) {
if (parseInt(Id) > 0) {
document.getElementById("divHidden").style.display = 'block';
document.getElementById("ProductInfoEdit").style.display = 'block';
var url = 'ProjectDetails.ashx?ProductID=' + Id +
'&DateTime=' + Date() +
'&Type=Category';
xmlHttp = createAjaxObject();
if (xmlHttp) {
xmlHttp.open('get', url, true);
xmlHttp.onreadystatechange = function () {
HandleProductInfoResponse();
}
selectedControl = ctrl;
xmlHttp.send(null);
}
}
}
function HandleProductInfoResponse() {
// If Response completed
if (xmlHttp.readyState == 4) {
if (xmlHttp.status == 200) {
// Here is the response
var str = xmlHttp.responseText;
var productInfo = eval("(" + str + ")");
document.getElementById('lblProductID').innerHTML = productInfo["ProductID"];
document.getElementById('txtProductName').value = productInfo["ProductName"];
document.getElementById('<%= DDLCategoryName.ClientID %>').value = productInfo["CategoryID"];
document.getElementById('txtQuantityPerUnit').value = productInfo["QuantityPerUnit"];
document.getElementById('txtUnitPrice').value = productInfo["UnitPrice"];
document.getElementById('txtUnitsInStock').value = productInfo["UnitsInStock"];
document.getElementById('txtUnitsOnOrder').value = productInfo["UnitsOnOrder"];
xmlHttp.abort();
}
}
}
function ProductCancel() {
document.getElementById("divHidden").style.display = 'none';
document.getElementById("ProductInfoEdit").style.display = 'none';
}
function ProductSave() {
if (parseInt(document.getElementById('lblProductID').innerHTML) > 0) {
var url = 'ProjectDetails.ashx?Type=Save' +
'&ProductId=' + document.getElementById('lblProductID').innerHTML +
'&ProductName=' + document.getElementById('txtProductName').value +
'&CategoryID=' + document.getElementById('<%= DDLCategoryName.ClientID %>').value +
'&QuantityPerUnit=' + document.getElementById('txtQuantityPerUnit').value +
'&UnitPrice=' + document.getElementById('txtUnitPrice').value +
'&UnitsInStock=' + document.getElementById('txtUnitsInStock').value +
'&UnitsOnOrder=' + document.getElementById('txtUnitsOnOrder').value +
'&DateTime=' + Date();
xmlHttp = createAjaxObject();
if (xmlHttp) {
xmlHttp.open('get', url, false);
xmlHttp.onreadystatechange = function () {
HandleSaveResponse();
}
xmlHttp.send(null);
}
}
}
function HandleSaveResponse() {
if (xmlHttp.readyState == 4) {
GetRecord(document.getElementById('lblProductID').innerHTML);
ProductCancel();
alert('Product Saved Successfully');
xmlHttp.abort();
}
}
function GetRecord(recordId) {
if (recordId > 0) {
var url = 'ProjectDetails.ashx?DateTime=' + Date() +
'&Type=Category' +
'&ProductID=' + recordId;
xmlHttp = createAjaxObject();
if (xmlHttp) {
xmlHttp.open('get', url, false);
xmlHttp.onreadystatechange = function () {
HandleRecordResponse();
}
xmlHttp.send(null);
}
}
}
function HandleRecordResponse() {
// If Response completed
if (xmlHttp.readyState == 4) {
if (xmlHttp.status == 200) {
var parenttr, tempSelectedControl = selectedControl;
do {
if (tempSelectedControl.parentNode != null) {
if (tempSelectedControl.parentNode.className == 'RowStyle') {
parenttr = tempSelectedControl.parentNode;
break;
}
else {
if (tempSelectedControl.parentNode != null)
tempSelectedControl = tempSelectedControl.parentNode;
}
}
else
break;
} while (1 == 1);
// Here is the response
var str = xmlHttp.responseText;
var productInfo = eval("(" + str + ")");
parenttr.cells[1].innerText = productInfo["ProductName"];
parenttr.cells[2].innerText = productInfo["CategoryName"];
parenttr.cells[3].innerText = productInfo["QuantityPerUnit"];
parenttr.cells[4].innerText = productInfo["UnitPrice"];
parenttr.cells[5].innerText = productInfo["UnitsInStock"];
parenttr.cells[6].innerText = productInfo["UnitsOnOrder"];
xmlHttp.abort();
}
}
}
function CheckNumber(txtId) {
if (isNaN(document.getElementById(txtId).value)) {
alert("Please enter only numbers");
document.getElementById(txtId).focus();
}
}
/* This function requests the HTTPRequest, will be used to render the Dynamic content html markup
* and it will call HandleResponse to handle the response
*/
function GetChildGrid(pageId) {
// Get the Div
var childGridDiv = document.getElementById("divChildGrid");
if (childGridDiv) {
// If the Child Grid is not fetched, then go and fetch it.
var varTotalRecords = document.getElementById('<%= hiddenTotalRecords.ClientID %>').value;
var varPageRecordCount = document.getElementById('<%= hiddenPageRecordCount.ClientID %>').value;
var varSortExpression = document.getElementById('<%= hiddenSortExpression.ClientID %>').value;
if ((pageId > 0) && (parseInt(varTotalRecords) > (parseInt(varPageRecordCount) * (parseInt(pageId) - 1)))) {
var url = 'RowsBuilder.aspx?PageId=' + pageId +
'&DateTime=' + Date() +
'&PageRecordsCount=' + varPageRecordCount +
'&SortExpression=' + varSortExpression;
xmlHttp = createAjaxObject();
if (xmlHttp) {
xmlHttp.open('get', url, true);
xmlHttp.onreadystatechange = function () {
HandleResponse();
}
varPageOnBehind = varStartAJAXPage;
xmlHttp.send(null);
}
}
}
}
/* This function is used to handler the http response */
function HandleResponse() {
var childGridDiv = document.getElementById("divChildGrid");
// If Response completed
if (xmlHttp.readyState == 4) {
if (xmlHttp.status == 200) {
// Here is the response
var str = xmlHttp.responseText;
var divRows = document.createElement('div');
divRows.id = "div_" + varPageOnBehind;
divRows.className = "PartialGrid";
divRows.innerHTML = str;
childGridDiv.appendChild(divRows);
varStartAJAXPage++;
xmlHttp.abort();
}
}
}
function RefreshRows(div) {
if (varStartAJAXPage - varPageOnBehind > 0) {
if (div.scrollHeight - (div.scrollTop + div.offsetHeight) < 200) {
varPageOnBehind = varStartAJAXPage;
GetChildGrid(varStartAJAXPage, true, true);
}
}
}
/* function to create Ajax object */
function createAjaxObject() {
var ro;
var browser = navigator.appName;
if (browser == "Microsoft Internet Explorer") {
if (xmlHttp != null) {
xmlHttp.abort();
}
ro = new ActiveXObject("Microsoft.XMLHTTP");
}
else {
if (xmlHttp != null) {
xmlHttp.abort();
}
ro = new XMLHttpRequest();
}
return ro;
}
/* Get the XML Http Object */
function GetXmlHttpObject(handler) {
var objXmlHttp = null;
if (is_ie) {
var strObjName = (is_ie5) ? 'Microsoft.XMLHTTP' : 'Msxml2.XMLHTTP';
try {
objXmlHttp = new ActiveXObject(strObjName);
objXmlHttp.onreadystatechange = handler;
}
catch (e) {
alert('Object could not be created');
return;
}
}
return objXmlHttp;
}
function xmlHttp_Get(xmlhttp, url) {
xmlhttp.open('GET', url, true);
xmlhttp.send(null);
}SQL ProcedureCREATE View [dbo].[OrderView]
As
SELECT Orders.OrderID,
Orders.CustomerID,
Customers.CompanyName [CustomerName],
Orders.ShipVia [ShipVia],
Shippers.CompanyName [ShipViaName],
Orders.OrderDate,
Orders.ShipName,
Orders.ShipCity,
Orders.ShipCountry
FROM Orders
JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
GOALTER PROCEDURE [dbo].[SP_GET_PRODUCTS] @CurrentPageNo AS INT, @PageRecordsCount AS INT, @SortBy AS NVARCHAR(250), @SortType AS NVARCHAR(250), @TotalRecordCount AS INT OUTPUT AS SET NOCOUNT ON DECLARE @SQL AS NVARCHAR(1000) DECLARE @RecordsFrom AS INT DECLARE @RecordsTo AS INT -- RecordsFrom => For Ex: CurrentPage 2, PageRecordCount = 10, then (((2-1) * 10) + 1) => ((1 * 10) + 1) => 11 SET @RecordsFrom = (((@CurrentPageNo - 1) * @PageRecordsCount) + 1) -- @RecordsTo => For Ex: CurrentPage 2, PageRecordCount = 10, then (2 * 10) => 20 SET @RecordsTo = (@CurrentPageNo * @PageRecordsCount) IF @SortBy = '' OR @SortBy = NULL SET @SortBy = 'ProductName' IF @SortType = '' OR @SortType = NULL SET @SortType = 'Asc' -- Getting the Total Record Count SELECT @TotalRecordCount = COUNT(*) FROM ProductView -- Getting the records for a particular page SET @SQL = 'SELECT * FROM ' + '( ' + ' SELECT ' + ' ROW_NUMBER() OVER (ORDER BY ' + @SortBy + ' ' + @SortType + ') AS RowNumber, *' + ' FROM ProductView ' + ') AS RECORDS ' + 'Where RowNumber between ' + CAST(@RecordsFrom AS VARCHAR) + ' and ' + CAST(@RecordsTo AS VARCHAR) EXECUTE(@SQL)The output of this example would be -
download the working example of the source code in C# here and in VB here.
nice sample thanks