Delete row from GridView and Database using JQuery in asp.net

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

1	CREATE TABLE [dbo].[User_Table](

2	[Id] [int] NOT NULL,

3	[User_Name] [varchar](30) NOT NULL,

4	[First_Name] [varchar](50) NOT NULL,

5	[Last_Name] [varchar](50) NOT NULL,

6	[Middle_Name] [varchar](50) NOT NULL,

7	[Email_Id] [varchar](70) NOT NULL

8	) 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

1	CREATE PROCEDURE [dbo].[GetUserRecords]

2

AS

3

BEGIN

4

5	SELECT * FROM dbo.User_Table

6

7

END

and this stored procedure one is for Deleting records

1	CREATE PROCEDURE [dbo].[DeleteUser](@id int)

2

AS

3

BEGIN

4

5	DELETE FROM dbo.User_Table WHERE id = @id

6

7

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

01	<asp:GridView CellPadding="5" CellSpacing="5" ID="GridView1" runat="server" RowStyle-CssClass="record" AutoGenerateColumns="False" >

02	<RowStyle CssClass="record"></RowStyle>

03

<Columns>

04	<asp:BoundField DataField="ID" HeaderText="ID" />

05	<asp:BoundField DataField="User_Name" HeaderText="User Name" />

06	<asp:BoundField DataField="First_Name" HeaderText="First Name" />

07	<asp:BoundField DataField="Middle_Name" HeaderText="Middle Name" />

08	<asp:BoundField DataField="Last_Name" HeaderText="Last Name" />

09	<asp:BoundField DataField="Email_Id" HeaderText="Email Id" />

10	<asp:TemplateField>

11	<ItemTemplate>

12	<a href="#" id='<%# Eval("ID") %>' class="delbutton"> <img  border="0" src="Images/delete.jpg" alt="Delete" /></a>

13	</ItemTemplate>

14	</asp:TemplateField>

15	</Columns>

16	</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!!!

Download
Shibashish_634020323215182304_JQuerySample.zip

Thanks shibashish mohanty