Insert, Update, Delete in Gridview using Single Stored Procedure

In this post, I will explain GridView CRUD operations: Select, Insert, Edit, Update, and Delete using a single stored procedure with alert confirmations on delete from GridView.

I have a GridView where I want to perform insert, update, delete, and edit operations. Additionally, I want to enable deletion of records from the GridView by clicking on the delete button with an alert message 'Are you sure you want to delete?'

To achieve all these functionalities, I have utilized GridView events.

Create a table in sql sever.

CREATE TABLE [dbo].[tbluser](
[Userid] [int] IDENTITY(1,1) NOT NULL,
[Username] [varchar](100) NULL,
[password] [varchar](100) NULL,
[Address] [varchar](100) NULL
)

I have created a stored procedure for performing insert, update, delete, and edit operations. I have declared a @status parameter, and accordingly, we will perform the database operation.

As seen in the below query, I have declared nullable parameters in the stored procedure, i.e., optional parameters. If you are not familiar with the stored procedure, please read this post first.-what is Stored Procedure?

create PROCEDURE  Crudoperation
@userid int=null,
@username varchar(100)=null,
@password varchar(100)=null,
@address varchar(100)=null,
@status  varchar(50)=null
as
begin
        if(@status='select')
    begin
    select * from tbluser
    end
        if(@status='update')
    begin
    update tbluser set password=@password,Address=@address where UserId=@userid
    end
        if(@status='delete')
    begin
    delete from tbluser where UserId=@userid
    end
        if(@status='insert')
    begin
    insert into tbluser(UserName,password,Address) values(@username,@password,@address)
    end
end

1.Now open visual studio and add empty website.

2.Now add a gridview to the page

3.Right click on gridview and then click on gridview property ,double click on the following event of gridview.

i have explain some gridview view event which are very useful.

1) Onrowcancelingedit
The RowCancelingEdit event is occur when the Cancel button of a row in edit mode is clicked2) Onrowediting
The RowEditing event is fire when a row’s Edit button is clicked, but before the GridView control enters edit mode.
3) Onrowupdating
The RowUpdating event is fire when a row’s Update button is clicked, but before the GridView control updates the row
5) Onrowdeleting
The RowDeleting event is fire when a row’s Delete button is clicked, but before the GridView control deletes the row.
6)OnRowCommand
The RowCommand event is fire when a button is clicked in the GridView control.

 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <asp:GridView ID="GridView1" runat="server" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowCommand="GridView1_RowCommand" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating">


    </asp:GridView>
    
    </div>
    </form>
</body>
</html>

Event are created on codebehid of page.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {

    }

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

    }

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {

    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {

    }

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {

    }
}

now paste the following code between gridview view tag.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <asp:GridView ID="GridView1" runat="server" DataKeyNames="UserId,UserName" AutoGenerateColumns="False" Showfooter="True" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowCommand="GridView1_RowCommand" OnRowUpdating="GridView1_RowUpdating" CellPadding="4" ForeColor="#333333" GridLines="None" >
    <AlternatingRowStyle BackColor="White" />
    <Columns>
    <asp:TemplateField HeaderText="Action">
<EditItemTemplate>
    
<asp:Button ID="btnUpdate" CommandName="Update" runat="server" ToolTip="Update" Height="30px" Width="60px" Text="Update" />
<asp:Button ID="btnCancel" CommandName="Cancel"  runat="server" Text="Cancel" ToolTip="Cancel" Height="30px" Width="60px" />
</EditItemTemplate>
<ItemTemplate>
    <asp:Button ID="btnEdit" runat="server" CommandName="Edit" Text="Edit" ToolTip="Edit" Height="30px" Width="60px"/>
    <asp:Button ID="btnDelete" runat="server" Text="Delete"  CommandName="Delete" ToolTip="Delete" Height="30px" Width="60px" OnClientClick="return confirm('Are you sure you want to delete this ?');" />
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="btnAdd" runat="server" Text="AddNew" CommandName="AddNew" Width="120px" Height="30px" ToolTip="Add new User"/>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Username">
<EditItemTemplate>
<asp:Label ID="lbleditusr" runat="server" Text='<%#Eval("Username") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblitemUsr" runat="server" Text='<%#Eval("Username") %>'/>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrusrname" runat="server"/>

</FooterTemplate>
</asp:TemplateField>

    <asp:TemplateField HeaderText="password">
<EditItemTemplate>
<asp:TextBox ID="txtpassword" runat="server" Text='<%#Eval("password") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblpassword" runat="server" Text='<%#Eval("password") %>'/>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrpassword" runat="server"/>

</FooterTemplate>
</asp:TemplateField>
    <asp:TemplateField HeaderText="Address">
<EditItemTemplate>
<asp:TextBox ID="txtAddress" runat="server" Text='<%#Eval("Address") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblAddress" runat="server" Text='<%#Eval("Address") %>'/>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrAddress" runat="server"/>

</FooterTemplate>
</asp:TemplateField>
    </Columns>
    <EditRowStyle BackColor="#2461BF" />
    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#EFF3FB" />
    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#F5F7FB" />
    <SortedAscendingHeaderStyle BackColor="#6D95E1" />
    <SortedDescendingCellStyle BackColor="#E9EBEF" />
    <SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
    
    </div>
    </form>
</body>
</html>

Eval function is used to bind data from database to controls inside DataBound controls like GridView, Repeater, etc.
now your gridview should look like this

Use of Multiple DataKeyNames (DataKeys) in ASP.Net GridView, How to find datakey value of gridview

The DataKeyNames property allows us to set the names of the column fields in a GridView that we want to use in our code but do not want to display. For example, primary keys, IDs, or usernames.

In my example, I don't want to display the UserID, but I want to use it in the code-behind for deleting and updating records.

IsPostBack is a property of the ASP.NET page that checks whether the page is being loaded for the first time or if it is being loaded due to a button click on the page. Now, I have written code for operations on the event of the GridView.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
    if (!IsPostBack)
        {
            userDetails();   //check page is loaded first time or it's loaded due to button on page

        }

    }
    public void userDetails()
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("Crudoperation", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@status", "select");
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
    if (ds.Tables[0].Rows.Count > 0)
        {
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
    else
        {
            ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());  // if record not found then returning a blank table structure
            GridView1.DataSource = ds;
            GridView1.DataBind();
    int columncount = GridView1.Rows[0].Cells.Count;
            GridView1.Rows[0].Cells.Clear();
            GridView1.Rows[0].Cells.Add(new TableCell());
            GridView1.Rows[0].Cells[0].ColumnSpan = columncount;
            GridView1.Rows[0].Cells[0].Text = "No Records Found";
        }
    }

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex; //this open new index that is edit mode
        userDetails();
    }


    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

    int userid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["UserId"].ToString());  //finding Datakeynames from gridview
    string username = GridView1.DataKeys[e.RowIndex].Values["UserName"].ToString();
        con.Open();
        SqlCommand cmd = new SqlCommand("Crudoperation", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@userid", userid);
        cmd.Parameters.AddWithValue("@status", "delete");
    int result = cmd.ExecuteNonQuery();
        con.Close();
    if (result == 1)
        {
            userDetails();
            Response.Write("<script language=javascript>alert('" + username + "'+'details deleted successfully');</script>");
        }
    }


    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;   //after cancel button want go to one index back that's y -1
        userDetails();
    }

    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {

    if (e.CommandName.Equals("AddNew"))
        {
            TextBox txtUsrname = (TextBox)GridView1.FooterRow.FindControl("txtftrusrname");
            TextBox txtpwd = (TextBox)GridView1.FooterRow.FindControl("txtftrpassword"); //find textbox control on footer with crrosponding id 
            TextBox txtadress = (TextBox)GridView1.FooterRow.FindControl("txtftrAddress");
            con.Open();
            SqlCommand cmd = new SqlCommand("Crudoperation", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@username", txtUsrname.Text);
            cmd.Parameters.AddWithValue("@password", txtpwd.Text);
            cmd.Parameters.AddWithValue("@address", txtadress.Text);
            cmd.Parameters.AddWithValue("@status","insert");
         
    int result = cmd.ExecuteNonQuery();
            con.Close();
    if (result == 1)
            {
                userDetails();
                Response.Write("<script language=javascript>alert('" + txtUsrname.Text + "'+'Details inserted successfully');</script>");
            }
    else
            {
                Response.Write("<script language=javascript>alert('" + txtUsrname.Text + "'+' Details not inserted');</script>");
            }
        }
    }

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
    int userid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
    string username = GridView1.DataKeys[e.RowIndex].Values["UserName"].ToString();
        TextBox txtpassword = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtpassword");
        TextBox txtAddress = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtAddress");
        con.Open();
        SqlCommand cmd = new SqlCommand("Crudoperation", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@password", txtpassword.Text);
        cmd.Parameters.AddWithValue("@address", txtpassword.Text);
        cmd.Parameters.AddWithValue("@userid", userid);
        cmd.Parameters.AddWithValue("@status", "update");
        cmd.ExecuteNonQuery();
        con.Close();
        Response.Write("<script language=javascript>alert('" + username + "'+' Details Updated successfully!');</script>");
        GridView1.EditIndex = -1;
        userDetails();
    }
}

Now run code and perform gridview operation using storeprocedure

Page Load:

It checks whether the page is loaded for the first time or due to a postback. If it's the first time, it calls the userDetails method to populate the GridView with data.

userDetails Method:

This method executes a stored procedure named "Crudoperation" with the parameter @status set to "select".
It populates a DataSet with the results of the stored procedure.
If there are records in the DataSet, it binds the data to the GridView (GridView1). If no records are found, it adds a new row to the DataSet to display a message ("No Records Found") in the GridView.

GridView Events:

GridView1_RowEditing: Puts the GridView in editing mode for the selected row.
GridView1_RowDeleting: Deletes a record from the database based on the selected row in the GridView.
GridView1_RowCancelingEdit: Cancels editing mode for the GridView.
GridView1_RowCommand: Handles commands such as adding new records to the GridView.
GridView1_RowUpdating: Updates a record in the database based on the edited values in the GridView.

Database Operations:

Database connections are opened and closed as needed.
The stored procedure "Crudoperation" is executed to perform CRUD operations on the database table.
Parameters are passed to the stored procedure based on the action being performed (select, insert, update, delete).

User Interaction:

JavaScript alerts are used to provide feedback to the user after certain actions, such as successful deletion or insertion of records.

Above code demonstrates how to create a basic CRUD functionality using ASP.NET Web Forms and interact with a SQL Server database using stored procedures.

Insert, Update, Delete in Gridview using Single Stored Procedure

Use of Multiple DataKeyNames (DataKeys) in ASP.Net GridView, How to find datakey value of gridview

Read Similar Articles