Delete row or records from GridView and Database using JQuery in asp.net.
Introduction
As we know JQuery can be seen as an alternate of ajax hence we can do some operation in GridView using JQuery like deleting the records or row from GridView is much easier.
As we all know that GridView is render in the client browser as HTML mark up tag like table <TABLE>, <TR> as GridView row and <TD> as GridView cell. Actually in this article we will remove the <TR> that means entire row from gridview.
We are going to use css to recognize the <tr> as GridView row and using JQuery we are going to remove the tag which means use will get a feel that on click of delete image or button
In this article we are not going to use Update panel but using JQuery it will give similar feel mean without page postback delete the records.
Other Articles on JQuery:
GridView and JQuery
you can find the other articles on JQuery to call server side function here
To Check the username availability live using JQuery you can visit this article
To Perform various validation in asp.net application using JQuery you can visit this post.
1st create user table with below columns in sql server database
Craete SQL Server Table
CREATE TABLE [dbo].[User_Table](
[Id] [int] NOT NULL,
[User_Name] [varchar](30) NOT NULL,
[First_Name] [varchar](50) NOT NULL,
[Last_Name] [varchar](50) NOT NULL,
[Middle_Name] [varchar](50) NOT NULL,
[Email_Id] [varchar](70) NOT NULL
) ON [PRIMARY]
You can add some records manually in the newly created table
Now lets create 2 stored procedure one to fetch records from database and display to GridView and other one is for deleting the records from database table.
This sp is for Fetching Records from User_Table
Craete Stored Proceure
CREATE PROCEDURE [dbo].[GetUserRecords]
AS
BEGIN
SELECT * FROM dbo.User_Table
END
and this stored procedure one is for Deleting records
CREATE PROCEDURE [dbo].[DeleteUser](@id int)
AS
BEGIN
DELETE FROM dbo.User_Table WHERE id = @id
END
Now Drag and Drop the GridView from the toolbox of the visual studio and place into the web form and depending upon the your requirement you can set the autogenerated column to false and Bound column or template column, Its totally up to you and your requirement.
In the below example I have taken 6 bound column and one template column for delete hyperlink and image button
GridView in .aspx page
<asp:GridView CellPadding="5" CellSpacing="5" ID="GridView1" runat="server" RowStyle-CssClass="record" AutoGenerateColumns="False" >
<RowStyle CssClass="record"></RowStyle>
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" />
<asp:BoundField DataField="User_Name" HeaderText="User Name" />
<asp:BoundField DataField="First_Name" HeaderText="First Name" />
<asp:BoundField DataField="Middle_Name" HeaderText="Middle Name" />
<asp:BoundField DataField="Last_Name" HeaderText="Last Name" />
<asp:BoundField DataField="Email_Id" HeaderText="Email Id" />
<asp:TemplateField>
<ItemTemplate>
<a href="#" id='<%# Eval("ID") %>' class="delbutton"> <img border="0" src="Images/delete.jpg" alt="Delete" /></a>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
In the above GridView code I have used <RowStyle CssClass="record"></RowStyle> to set the class to record and its important to delete row using JQuery
In the above declaration of GridView I also used anchor tag and set its class to delbutton and its id value to the correspondent to the database id column so that we can get the record id in JQuery function.
Now assign the data to the gridView in the page load event
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadData();
}
}
and here is the LoadData function which will get the data from SQL Server using Stored Procedure and bind to GridView
private void LoadData()
{
//Get the connection string from the web.config file
string conString = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(conString);
try
{
SqlCommand sqlCmd = new SqlCommand("GetUserRecords", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlConn.Open();
SqlDataReader rdr = sqlCmd.ExecuteReader();
//assign datareader to GridView you can assign to any datasource for e.g DataTable or dataSet
GridView1.DataSource = rdr;
GridView1.DataBind();
//Close DataReader
rdr.Close();
}
catch
{
//Handle Error if any occurred
}
finally
{
//Close Database connection
sqlConn.Close();
}
}
Once you bind the data into grridview you will see this
And Below is the code which is used to delete records from database and will be called using Jquery. Don't forget to add [System.Web.Services.WebMethod] like this
[System.Web.Services.WebMethod]
public static void DeleteUser(string args)
{
string conString = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(conString);
try
{
SqlCommand sqlCmd = new SqlCommand("DeleteUser", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@id", Convert.ToInt32(args.Trim()));
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
}
catch
{
//Handle Exception
}
finally
{
sqlConn.Close();
}
}
Now Here is the JQuery code sample
JQuery Delete function
<head runat="server">
<title>GridView and JQuery</title>
<script src="Script/jquery-1.4.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function() {
$("#.delbutton").click(function() {
//Get the Id of the record to delete
var record_id = $(this).attr("id");
//Get the GridView Row reference
var tr_id = $(this).parents("#.record");
// Ask user's confirmation before delete records
if (confirm("Do you want to delete this record?")) {
$.ajax({
type: "POST",
//GridViewDelete.aspx is the page name and DeleteUser is the server side method to delete records in GridViewDelete.aspx.cs
url: "GridViewDelete.aspx/DeleteUser",
//Pass the selected record id
data: "{'args': '" + record_id + "'}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function() {
// Change the back color of the Row before deleting
tr_id.css("background-color", "lightgreen");
// Do some animation effect
tr_id.fadeOut(500, function() {
//Remove GridView row
tr_id.remove();
});
}
});
}
return false;
});
});
</script>
</head>
If you wun the application and click on any delete image it will ask the confirmation like below image and if you say yes it will delete the omage from the UI and Database
Hope this will help all. You can also donwload the sample application by licking the below download link
Cheers
Pankaj!!!