CRUD operation using GridView and jQuery
In order to show this example, I have created two .aspx pages.- Default.aspx - to display the recods and fire necessary jQuery command for CRUD perration.
- GridViewData.aspx - to bind the GridView according to the command received using jQuery.
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">Code listing - 1<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></form>
<asp:BoundField DataField="AutoId" HeaderText="AutoId" /> <asp:TemplateField HeaderText="Edit"></asp:gridview> <asp:Literal runat="server" ID="litPaging" EnableViewState="False" /> <p> <asp:label id="lblMessage" runat="server" enableviewstate="false" /> </p> </div>
<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>
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 () {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.$("#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) {}); </script>
var autoId = $(this).find("td:first").text(); // remove the row from server side (the database) DeleteRecord(autoId); // remove from the clien side $(this).remove(); }})
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- 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.
- 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.
- DeleteRecord() - This method accepts integer value and deletes the records from the database.
- BindMyGrid() - This method binds the data from database to the GridView.
- 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 {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.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 {} /// <summary> /// Handles the request objects. /// </summary> private void HandleRequestObjects() {
} catch (Exception ee) { throw ee; }if (!IsPostBack){BindMyGrid();}
try {} /// <summary> /// Updates the data. /// </summary> private void UpdateInsertData(string editId) {
// check for paging if (Request.Form["startRowIndex"] != null && Request.Form["thisPage"] != null) {} catch (Exception ee) { throw ee; }
_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"]);}
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)) {} /// <summary> /// Deletes the record. /// </summary> /// <param name="id">The id.</param> private void DeleteRecord(string id) {
using (SqlCommand cmd = new SqlCommand(sql, conn)) {} lblMessage.Text = "Selected record " + message + " successfully !"; // rebind the data again BindMyGrid();
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();}
int productId = int.Parse(id); string sql = "delete SampleForTutorials where AutoId = @AutoId"; using (SqlConnection conn = new SqlConnection(_connStr)) {} /// <summary> /// Binds my grid. /// </summary> private void BindMyGrid() {
using (SqlCommand cmd = new SqlCommand(sql, conn)) {} lblMessage.Text = "Selected record deleted successfully !"; // rebind the data again BindMyGrid();
cmd.Parameters.AddWithValue("@AutoId", productId); conn.Open(); cmd.ExecuteNonQuery(); conn.Close();}
// 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)) {} // 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) {
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();}
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> | "); 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> | "); 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> | "); else strB.Append("<a href=\"javascript:LoadGridViewData(" + start + ", " + pageno + ")\" title=\"Page " + loop + "\">" + loop + "</a> | "); } } return strB.ToString() + "</div>";}
n order to bring the selected row, highlighted row and others necessary effects as shown in the below picture, I have kept couple of .css classes on this default.aspx page, they are following
<head runat="server"> <title>Edit GridView</title> <style type="text/css">th { text-align:left; } body { font-family:Arial; font-size:10pt; } #divEditBox { display:none; position:absolute; left:30%; top:30%; } .highlightRow { background-color:Yellow; } .select { background-color:#c0c0c0; }</style> <script language="javascript" src="jquery-1.4.min.js" type="text/javascript"></script> </head>
.css code on default.aspx page
The first th class simply keep the header of the GridView left aligned, body is to just make the body font as arial and font-size as 10pt. .divEditBox class is the one that shows the selected record to edit at the center of the page. .highlightRow is the class that is set to the row when mouse overing the row and .selectcss class is set to the row when user clicks it.
I have also referred the jQuery .js file that you can download from http://jquery.com/. Alternately, you can also refer the Microsfot AJAX CDN jQuery file fromhttp://www.asp.net/ajaxlibrary/cdn.ashx as shown below.
<script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.min.js" type="text/javascript"></script>
Now, lets see the HTML code from default.aspx page
<form id="form1" runat="server"> <p><a href="javascript:void(0)" onclick="LoadGridViewData(0, 1)">Load</a> | <a id="addLink" href="javascript:void(0)" title="Add">Add</a></p> <div id="divGridViewData"></div><div id="divEditBox"></form>
<table cellpadding="4" width="600" cellspacing="0" border="0" style="background-color:#efefef;border:1px solid #c0c0c0;"> <tr style="background-color:#b0b0b0;" valign="top"> <td style="width: 91%" colspan="3"> <label id="lblPopTitle">Modify Record</label></td> <td align="right" style="width: 9%;padding-right:10px;"> <a id="closeLink" href="javascript:void(0)" onclick="HideEditBox()" title="Close">Close</a> </td> </tr> <tr> <td>Name: </td><td><input type="text" id="txtEditName" /> </td> <td>Address: </td><td><input type="text" id="txtEditAddress" /></td> </tr> <tr> <td>Phone: </td><td><input type="text" id="txtEditPhone" size="10" /></td> <td>City: </td><td><input type="text" id="txtEditCity" size="10" /></td> </tr> <tr><td colspan="4" align="center"> <input type="button" value="Submit" onclick="UpdateInsertData()" /> <input type="hidden" id="editId" value="0" /> </td></tr> </table></div>
.html code on default.aspx page
This has two links called Load and Add that is used to Load the GridView data and to list the add record box as shown in the picture below. The same box is used to bring the record into Edit mode as well. Apart from these, I also have a div with Id divGridViewData but there is nothing inside as I have kept this as placeholder and I shall be using this to populate my asp:GridView control data.
On click event of the Submitt button, I have specified UpdateInsertDate() function that will send proper command to the GridViewData.aspx page (expained in the first part of this article).
To find out the selected record, I have kept a hidden field on this page named editId that is used to set the value of the selected record.
Now, lets see the jQuery functions kept on this page.
You may notice that I have used jQuery post method to post the required parameters to the GridViewData.aspx page (explained in the first part of this article). In that page, I have retrieved these parameters value using Request.Form object.
A little about jQuery post method.
jQuery post method is used to send the data to another page, it waits for the response from another page and gives you back. For example, in the below code snippets, the data is sent to the GridViewData.aspx page with two form element startRowIndex and thisPage.
function LoadGridViewData(start, pageNo) {$(document).ready(function() {}
$.post("GridViewData.aspx", { startRowIndex: start, thisPage: pageNo }, function(data) { $("div#divGridViewData").html(data); });});
On GridViewData.aspx page, I can retrive its value using following code snipept.
_startRowIndex = int.Parse(Request.Form["startRowIndex"].ToString()); _thisPage = int.Parse(Request.Form["thisPage"].ToString());
Once the data has been sent with the required parameter, it waits for the response from that page that can be catched and used. Here my response is the binded rendered GridView that I have set as innerHTML into my div with id divGridViewData.
The complete jQuery / javaScript functions of default.aspx page
<script language="javascript" type="text/javascript">// Load the gridview page data function LoadGridViewData(start, pageNo) {</script>
$(document).ready(function() {} // insert / update the data function UpdateInsertData() {
$.post("GridViewData.aspx", { startRowIndex: start, thisPage: pageNo }, function(data) { $("div#divGridViewData").html(data); });});
$(document).ready(function() {} // delete the record function DeleteRecord(id) {
$.post("GridViewData.aspx", { pName : $("#txtEditName").val(), pAddress: $("#txtEditAddress").val(), pCity : $("#txtEditCity").val(), pPhone : $("#txtEditPhone").val(), editId : $("#editId").val() }, function(data) { $("div#divGridViewData").html(data); });}); // hide the edit box HideEditBox();
$(document).ready(function() {} // show edit box when edit link is clicked function ShowEditBox(id) {
$.post("GridViewData.aspx", { deleteId: id }, function(data) { $("div#divGridViewData").html(data); });});
$("#divEditBox").slideDown("medium"); var pid = 'PName' + id; var colIndex = 0; var $tr = $("#" + pid).parent().parent(); $tr.find('td').each(function() { if (colIndex == 2) { $("#txtEditName").val($(this).text()); } else if (colIndex == 3) { $("#txtEditAddress").val($(this).text()); } else if (colIndex == 4) { $("#txtEditCity").val($(this).text()); } else if (colIndex == 5) { $("#txtEditPhone").val($(this).text()); } colIndex++; }) $("#editId").val(id);} // Hide the edit/insert box function HideEditBox() {
$("#divEditBox").slideUp("medium");} // show the box when add link is clicked $(document).ready(function() {
// when add link will be clicked $("#addLink").click(function() { $("#divEditBox").slideDown("medium"); $("#txtEditName").val(""); $("#txtEditAddress").val(""); $("#txtEditCity").val(""); $("#txtEditPhone").val(""); $("#editId").val("0"); $("#lblPopTitle").text("Add Record"); })})
.jQuery / javaScript code on default.aspx page
The above code snippet contains following javaScript functions
- LoadGridViewData() - This accepts two parameters, start (start row number from where the records should be fetched from the database) and pageNo (the current page number).
- UpdateInsertData() - This function retrives the TextBox value set by ShowEditBox() function explained below and send the data to the GridViewData.aspx page to update or insert. In case of Update the editId value is set to the AutoId column value from database else 0.
- DeleteRecord() - This function get the selected record id and send to the GridViewData.aspx page for deletion.
- ShowEditBox() - This function sets the selected record in edit mode and let the user edit the record.
- HideEditBox() - This function simply hide the EditBox.
- The last function fires when Add link is clicked from the top-left of the page.
Update Mode of the GridView
Now lets see the Code behind page of default.aspx
Look at the following code snippet, Surprise? Yes, there will not be anything in the default.aspx code behind as we are not doing anything from server side on this page. This page just do all client side activities and all server side activities (listing, deleting, adding, updating) are taken care by GridViewData.aspx page explained in the first part of this article.
using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections.Generic; using System.Linq; using System.Text; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class GridViewList : System.Web.UI.Page {/// <summary> /// Handles the Load event of the Page control. /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param> protected void Page_Load(object sender, EventArgs e) { }}
Lets Sum up what we did here
The concept of doing CRUD opertion seamlessly without even a single browser refresh in GridView using jQuery is that the default.aspx page will work as placeholder for the GridView and also work as command page where all necessary commands are fired.
All CRUD opertions that need server side activities are taken care by GridViewData.aspx page, even simple jQuery client side effects like selecting the row by clicking and mouseover effects etc are taken care by GridViewData.aspx page. Default.aspx page just instructs the GridViewData.aspx page using jQuery by sending necessary command to do the operations.
Lets go through some asp:GridView and jQuery simple tips and tricks
Highlighting an ASP.NET GridView row on Mouse Over
To highlight the row on mouse over, get the reference of the mouse hover row and add the css class named .highlightRow and if mouse Out simply remove this class.
// highlight the row when clicked $(document).ready(function () {$("#divGridView table tbody tr").mouseover(function () { $(this).addClass("highlightRow"); }).mouseout(function () { $(this).removeClass('highlightRow'); })});
Select / Highlight ASP.NET GridView row by clicking it
To select the row on which mouse click has happened, just get the reference of the clicked row and add the css class called .select.
// highlight row by clicking it $(document).ready(function () {$("#divGridView table tbody tr").click(function () { $(this).addClass("select"); })});
Remove / Delete the ASP.NET GridView row by double clicking
The same as above, get the reference of the double clicked row and get the id of that record. I have get the id of the selected record by finding the first column of the row by using td:first and retrieved its text. Once I have the id, I passed this to DeleteRecord javascript function that in tern instruct the GridViewData.aspx page to delete the record. To hide that row from client side, I removed that row using jQuery remove() method.
// 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(); }})
In case you do not want to remove the record from the data, simply comment other lines except $(this).remove();
No comments :
Post a Comment