ASP.NET GridView + jQuery tips and tricks - Part 1

Introduction

GridView is an unavoidable control while developing any application. Whether you need to list records, update or delete or even to add new records and just to show a record, you can use GridView for all these activities. To read other interesting article of GridView, read here.

In this article, I will demonstrate how to do CRUD (Create, Read, Update, Delete) opeations using GridView and jQuery seamlessly and will also give some simple GridView and jQuery UI tips, like selecting records, highlighting records and deleting records by double clicking it. ALL HAPPENS WITHOUT A SINGLE PAGE REFRESH.

This is Part1, please read part 2 of this article here.

Video of this Article

 You can watch the video of this article at http://www.dotnetfunda.com/tutorials/videos/x73-aspnet-gridview--jquery-tips-and-tricks-.aspx

Pre-requisite

I assume that reader of this article have basic knowledge of ASP.NET, jQuery and ADO.NET.

CRUD operation using GridView and jQuery

In order to show this example, I have created two .aspx pages.

1. Default.aspx - to display the recods and fire necessary jQuery command for CRUD perration.
2. GridViewData.aspx - to bind the GridView according to the command received using jQuery.

Our final result will look something similar to shown below. Where Yellow row is the highlighted row on mouse over, gray row is the selected row by clicking it.

Lets start with the 2nd page, GridViewData.aspx page

In this page, I have kept a asp:GridView control, asp:Literal and asp:Label control. asp:Literal control is used to list out the number of pages for the GridView and asp:Label control has been used to show the CRUD operation messages.

The code of GridViewData.aspx page code looks like below

<form id="form1" runat="server">
<div id="divGridView">
<asp:gridview id="GridView1" runat="server" enableviewstate="False" width="100%" BorderWidth="1"
cellpadding="4" forecolor="#333333" gridlines="None" autogeneratecolumns="false"
datakeynames="AutoId" autogeneratedeletebutton="false" EmptyDataText="No records found" >
<HeaderStyle BackColor="#507CD1" HorizontalAlign="Left" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:BoundField DataField="AutoId" HeaderText="AutoId" />
<asp:TemplateField HeaderText="Edit">
<ItemTemplate>
<a href="javascript:void(0)" onclick="ShowEditBox(<%# Eval("AutoId") %>)" title="Edit">Edit</a>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<span id="PName<%# Eval("AutoId") %>"><%# Eval("Name") %></span>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="Address" DataField="Address" />
<asp:BoundField HeaderText="City" DataField="City" />
<asp:BoundField HeaderText="Phone" DataField="Phone" />
<asp:TemplateField HeaderText="Delete">
<ItemTemplate>
<span onclick="return confirm('Are you sure?')">
<a href="javascript:DeleteRecord(<%# Eval("AutoId") %>)" title="Delete"><font color="red">Delete?</font></a>
</span>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:gridview>
<asp:Literal runat="server" ID="litPaging" EnableViewState="False" />
<p>
<asp:label id="lblMessage" runat="server" enableviewstate="false" />
</p>
</div>
</form>

Code listing - 1

To make the article easy to understand, I have taken example of the simple database table structure, AutoId, Name, Address, Phone, City.

Video tutorials of hundreds of ASP.NET How to Tips and Tricks

As shown in the picture above, the very first column is the AutoId, that is the Auto increment, unique primary key that will be used to edit, update and delete recods. The second column has Edit link that fires ShowEditBox javascript box, that exists in the default.aspx page (will show later in the Part 2 of this article). The third column displays the name inside the <span> with a unique id formed by suffixing AutoID in "PName" string. This will help us in finding the selected row for Edit or Delete records (This might not be the best way of finding the selected row, however works great and it's simple too). The very last column is the Delete column that fires DeleteRecord javascript function from default.aspx page (will show you later in Part 2 of this article).

Lets look at the jQuery functions kept on this page.

<script language="javascript" type="text/javascript">
 
// highlight the row when clicked
$(document).ready(function () {
$("#divGridView table tbody tr").mouseover(function () {
$(this).addClass("highlightRow"); 
}).mouseout(function () { $(this).removeClass('highlightRow'); })
});
 
// highlight row by clicking it
$(document).ready(function () {
$("#divGridView table tbody tr").click(function () {
$(this).addClass("select");
})
});
 
// double click delete rows
$(document).ready(function () {
$("#divGridView table tbody tr").dblclick(function () {
// find the id first
var v = confirm('Are you sure to delete?');
if (v) {
var autoId = $(this).find("td:first").text();
// remove the row from server side (the database)
DeleteRecord(autoId);
// remove from the clien side
$(this).remove(); 
}
})
});
</script>

These jQuery functions are used to highlight the row on mouse over, select the row by clicking it and delete the record from daabase as well as from GridView (cliend side) respectively. I have explained these functions and couple of .css classes used to highlight, selected row effect (these .css classes have been kept in default.aspx page) in the Part 2 of this article.

Now lets see the code for the code behind page of the GridViewData.aspx page (GridViewData.aspx.cs page).

In the below code snippet, I have following methods

1. HandleRequestObjects() -  This method handles different request that comes from default.aspx and shows appropriate messages and fires certain methods to Update, Insert or Delete records.
2. UpdateInsertData() - This method accepts an integer parameter, if the parameter value is 0 then it inserts new record into the database and if not equals 0 then update the record into the database.
3. DeleteRecord() - This method accepts integer value and deletes the records from the database.
4. BindMyGrid() - This method binds the data from database to the GridView.
5. DoPaging() -  This method accepts different parameters like current page number, total Record Count and number of records to be displayed in the page and writes the page numbers as it is appearing below the GridView in the picture.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
public partial class GridViewData : System.Web.UI.Page
{
string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
int _startRowIndex = 0;
int _pageSize = 4;
int _thisPage = 1;
 
protected void Page_Load(object sender, EventArgs e)
{
HandleRequestObjects();
try
{
if (!IsPostBack)
{
BindMyGrid();
}
}
catch (Exception ee)
{
throw ee;
}
}
 
/// <summary>
/// Handles the request objects.
/// </summary>
private void HandleRequestObjects()
{
try
{
// check for paging
if (Request.Form["startRowIndex"] != null && Request.Form["thisPage"] != null)
{
_startRowIndex = int.Parse(Request.Form["startRowIndex"].ToString());
_thisPage = int.Parse(Request.Form["thisPage"].ToString());
}
// check for edit
if (Request.Form["editId"] != null)
{
UpdateInsertData(Request.Form["editId"]);
}
// check for deletion
if (Request.Form["deleteId"] != null)
{
DeleteRecord(Request.Form["deleteId"]);
}
}
catch (Exception ee)
{
throw ee;
}
}
 
/// <summary>
/// Updates the data.
/// </summary>
private void UpdateInsertData(string editId)
{
string sql = string.Empty;
string message = "Added";
if (editId.EndsWith("0"))
{
sql = "insert into SampleForTutorials (Name, Address, Phone, City) values " +
" (@Name, @Address, @Phone, @City)";
}
else
{
message = "Update";
sql = "Update SampleForTutorials set Name = @Name, Address = @Address, " +
" Phone = @Phone, City = @City WHERE AutoId = @AutoId";
}
// get the data now
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = CommandType.Text;
SqlParameter p = new SqlParameter("@Name", SqlDbType.VarChar, 50);
p.Value = Request.Form["pName"];
cmd.Parameters.Add(p);
p = new SqlParameter("@Address", SqlDbType.VarChar, 150);
p.Value = Request.Form["pAddress"];
cmd.Parameters.Add(p);
p = new SqlParameter("@Phone", SqlDbType.VarChar, 50);
p.Value = Request.Form["pPhone"];
cmd.Parameters.Add(p);
p = new SqlParameter("@City", SqlDbType.VarChar, 50);
p.Value = Request.Form["pCity"];
cmd.Parameters.Add(p);
p = new SqlParameter("@AutoId", SqlDbType.Int);
p.Value = int.Parse(editId);
cmd.Parameters.Add(p);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
lblMessage.Text = "Selected record " + message + " successfully !";
// rebind the data again
BindMyGrid(); 
}
 
/// <summary>
/// Deletes the record.
/// </summary>
/// <param name="id">The id.</param>
private void DeleteRecord(string id)
{
int productId = int.Parse(id);
string sql = "delete SampleForTutorials where AutoId = @AutoId";
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@AutoId", productId);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
lblMessage.Text = "Selected record deleted successfully !";
// rebind the data again
BindMyGrid();
}
 
/// <summary>
/// Binds my grid.
/// </summary>
private void BindMyGrid()
{
// sql for paging. In production write this in the Stored Procedure
string sql = "SELECT * FROM ( " +
" Select SampleForTutorials.*, ROW_NUMBER() OVER (ORDER BY AutoId DESC) as RowNum " +
" FROM SampleForTutorials) as AddressList " +
" WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1 " +
"ORDER BY AutoId DESC";
 
DataTable table = new DataTable();
int totalCount = 0;
// get the data now
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = CommandType.Text;
SqlParameter p = new SqlParameter("@startRowIndex", SqlDbType.Int);
p.Value = _startRowIndex + 1;
cmd.Parameters.Add(p);
p = new SqlParameter("@pageSize", SqlDbType.Int);
p.Value = _pageSize;
cmd.Parameters.Add(p);
conn.Open();
// get the data first
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
ad.Fill(table);
}
// get the total count of the records now
sql = "select count(AutoId) from SampleForTutorials";
cmd.Parameters.Clear();
cmd.CommandText = sql;
object obj = cmd.ExecuteScalar();
totalCount = Convert.ToInt32(obj);
conn.Close();
}
}
// do the paging now
litPaging.Text = DoPaging(_thisPage, totalCount, _pageSize);
 
// bind the data to the grid
GridView1.DataSource = table;
GridView1.DataBind();
}
 
/// <summary>
/// Do the paging now
/// </summary>
/// <param name="thisPageNo"></param>
/// <param name="totalCount"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
private string DoPaging(int thisPageNo, int totalCount, int pageSize)
{
if (totalCount.Equals(0))
{
return "";
}
 
int pageno = 0;
int start = 0;
int loop = totalCount / pageSize;
int remainder = totalCount % pageSize;
int startPageNoFrom = thisPageNo - 6;
int endPageNoTo = thisPageNo + 6;
int lastRenderedPageNo = 0;
StringBuilder strB = new StringBuilder("<div>Page: ", 500);
// write 1st if required
if (startPageNoFrom >= 1)
{
strB.Append("<a href=\"javascript:LoadGridViewData(0, 1)\" title=\"Page 1\">1</a> | ");
if (!startPageNoFrom.Equals(1))
{
strB.Append(" ... | ");
}
}
for (int i = 0; i < loop; i++)
{
pageno = i + 1;
if (pageno > startPageNoFrom && pageno < endPageNoTo)
{
if (pageno.Equals(thisPageNo))
strB.Append("<span>" + pageno + "</span>&nbsp;| ");
else
strB.Append("<a href=\"javascript:LoadGridViewData(" + start + ", " + pageno + ")\" title=\"Page " + pageno + "\">" + pageno + "</a> | ");
lastRenderedPageNo = pageno;
}
start += pageSize;
}
// write ... if required just before end
if (!pageno.Equals(lastRenderedPageNo))
{
strB.Append(" ... | ");
}
if (remainder > 0)
{
pageno++;
if (pageno.Equals(thisPageNo))
strB.Append("<span>" + pageno + "</span>&nbsp;| ");
else
strB.Append("<a href=\"javascript:LoadGridViewData(" + start + ", " + pageno + ")\" title=\"Page " + pageno + "\">" + pageno + "</a> | ");
}
else // write last page number
{
if (loop >= endPageNoTo)
{
if (loop.Equals(thisPageNo))
strB.Append("<span>" + loop + "</span>&nbsp;| ");
else
strB.Append("<a href=\"javascript:LoadGridViewData(" + start + ", " + pageno + ")\" title=\"Page " + loop + "\">" + loop + "</a> | ");
}
}
return strB.ToString() + "</div>";
}
}

I am not going to explain the logic inside any of the above methods here as it is easily understandable except the DoPaging logic. DoPaging logic is a simple algorithm that accepts thisPageNo, totalCount and the pageSize and write appropriate number of pages as link in the asp:Literal control as displayed in the picture above. All the paging links, fires javascript LoadGridViewData javascript function (written in the default.aspx, will describe in the Part 2 of this article) that accepts the startRow position and the current page number and sends the appropriate command to this page (GridViewData.aspx) that fetches records from the database accordingly and bind to the asp:GridView conrol.

As this article content has become lengthy because of the code snippets (and I want to keep the code snippet along with the article as at few places the download is restricted) so I am taking liberty to split this article into two parts, please read the 2nd part that explains about default.aspx that actually does all the tricks.

Go to second part ASP.NET GridView + jQuery UI tips and tricks - Part 2 that explains the default.aspx page and also shows simple ASP.NET GridView + jQuery tips and tricks.