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

SheoNarayan
Posted by in ASP.NET category on for Intermediate level | Views : 207384 red flag
Rating: 4.8 out of 5  
 5 vote(s)

This article demonstrate how to do CRUD operation using GridView and jQuery seamlessly (without page refresh) and also describes some simple UI effects in ASP.NET GridView control using jQuery.

This is the Part 1 of this article.

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.

Page copy protected against web site content infringement by Copyscape

About the Author

SheoNarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Poster on: 7/31/2010
Excellent article Sheo !!!

The demo sample is really slick and seamless. Awesome !

Please keep sharing.
Posted by: Raja on: 8/1/2010
True, Its an excellent article and covers many things in one.

Good one.
Posted by: Syful on: 12/29/2010 | Points: 25
where is the SampleDatabase u used???
Posted by: SheoNarayan on: 12/29/2010 | Points: 25
The SampleDatabase (datbase used in this article) structure is preety simple

AutoId - int - autoincrement
Name - varchar(50),
Address - varchar(250)
City - varchar(50)
Phone - varchar(50)

You can create at your local machine and configure the web.config file.
To see how its working, go the video url and watch it.

Thanks
Regards

Posted by: H2safety on: 1/11/2011 | Points: 25
Hi, this is a great article.
I have just one issue: I am not able to get the Gridview to refresh with the newly added data even after $.post and the server-side Gridview.DataBind() are executed. Any idea why?

Thanks in advance.
Posted by: SheoNarayan on: 1/11/2011 | Points: 25
HI Philip,

Are you getting any JavaScript error? There is no reason why your GridView should not have the newly added records. I would suggest to keep your focus on jQuery parts, something might be missing there. In any case, you can download the source code of this article from part 2 (http://www.dotnetfunda.com/articles/article971-aspnet-gridview--jquery-ui-tips-and-tricks--part-2-.aspx ) that should help you.

Thanks!
Posted by: Josengan on: 1/25/2011 | Points: 25
Hola, tengo una pregunta. Cuando se hace click en el boton de Delete veo que se refresca el grid pero el paginado regresa desde 1, hay alguna forma de hacer que se mantenga en la misma pagina donde se le dio click al boton de delete...gracias
Posted by: Mgriffiths on: 4/8/2011 | Points: 25
I have a question about the use of the endswith("0") statement in you edit method. What happens if the record id is 10 or 20, since that ends in 0, does it still work?

Thank you

Posted by: Hemanthlaxmi on: 6/30/2011 | Points: 25
Hi ,This is a Excellent Article .Keep Sharing .I request please try to provide the Script this Database also.Hope it would be helpful to learners.
Posted by: Korneelv on: 8/12/2011 | Points: 25
I tried to use this for a asp.net 4.0 web application instead of a website & i have some issues while loading the grid:
- i can see that i get a response in function LoadGridViewData(start, pageNo) {
of the $.post call but it ain't inserted into the default.aspx page.
any suggestions what goes wrong?
--> solved: i was using a vb.net project - default value of AutoeventwireUp=false in vb.net (in page redirectives) in c# this is default TRUE. (don't forget adjusting redirectives in masterpage when used :-))
Posted by: Tarantino on: 12/30/2011 | Points: 25
A small fix to be included on method DoPaging (file GridViewData.aspx.cs
) in the line 268.

....
if (loop >= endPageNoTo)
{
start = start - pageSize;
if (loop.Equals(thisPageNo))
....

And the SampleDatabase db Script


CREATE TABLE [dbo].[SampleForTutorials](
[AutoId] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Address] [nvarchar](150) NULL,
[Phone] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
CONSTRAINT [PK_SampleForTutorials] PRIMARY KEY CLUSTERED
(
[AutoId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

many thanks for this article.




Posted by: SheoNarayan on: 12/30/2011 | Points: 25
Thanks Enrico for the fix and letting us know about.

Much appreciated. Keep sharing stuffs.

Posted by: Robinsavic777 on: 10/7/2012 | Points: 25
Hi Sheo, it's a great article. I have a query that when we have unique id in the table, what is the need of having a combination of "pname" and "id" to uniquely identifying the rows? Also what exactly "var $tr = $("#" + pid).parent().parent();" does? Please explain. Thanks.

Login to post response

Comment using Facebook(Author doesn't get notification)