How to Insert, Delete and Update Data into table using stored procedure and show data in GridView

1. Design file code here we use two textboxes and one button.

<form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td>
                <asp:Literal ID="litname" runat="server" Text="Name"></asp:Literal>
            </td>
            <td>
                <asp:TextBox ID="txtname" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                <asp:Literal ID="litrollno" runat="server" Text="Roll No"></asp:Literal>
            </td>
            <td>
                <asp:TextBox ID="txtrollno" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                <asp:Button ID="btnsubmit" runat="server" Text="Submit" OnClick="btnsubmit_Click" />
            </td>
            <td>
                <asp:Label ID="lblmsg" ForeColor="Red" runat="server"></asp:Label>
            </td>
        </tr>
       
    </table>
        <asp:HiddenField ID="hdnID" runat="server" />
        <asp:HiddenField ID="hdnFlag" runat="server" />
         <asp:GridView ID="GridView1" AutoGenerateColumns="False" DataKeyNames="id" runat="server" BackColor="#CCCCCC" BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px" CellPadding="4" CellSpacing="2" ForeColor="Black">
             
             <Columns>
                 <asp:BoundField DataField="name" HeaderText="Name" />
                 <asp:BoundField DataField="rollno" HeaderText="RollNo" />
                 <asp:TemplateField HeaderText="Operation">
                     <ItemTemplate>
                         
                         <asp:LinkButton ID="lnkEdit" runat="server" Text="Edit" ForeColor="Red" OnClick="lnkEdit_Click"></asp:LinkButton>
                         <asp:LinkButton ID="lnkdel" runat="server" Text="Delete" OnClick="lnkdel_Click" OnClientClick=" return confirm('Are u sure delete')"></asp:LinkButton>
                         
                     </ItemTemplate>
                 </asp:TemplateField>

             </Columns>
             <FooterStyle BackColor="#CCCCCC" />
             <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
             <PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
             <RowStyle BackColor="White" />
             <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
             <SortedAscendingCellStyle BackColor="#F1F1F1" />
             <SortedAscendingHeaderStyle BackColor="#808080" />
             <SortedDescendingCellStyle BackColor="#CAC9C9" />
             <SortedDescendingHeaderStyle BackColor="#383838" />
             
        </asp:GridView>
    </div>
    </form>

2.   Code file for performing operations.
In this code we use following functions:

1. 1. fillgrid(): for binding data on gridview.
2. 2. btnsubmit_click(): for inserting,deleting and update data.
3. 3. lnk_del(): for deleting.
4. 4. lnk_edit(): for editing.
5. 5. reset(): to empty all textboxes and empty the value from hidden-fields.

string CS = ConfigurationManager.ConnectionStrings["DS"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
           fillgrid();
        }
    }
    
    public void reset()
    {
        txtname.Text = "";
        txtrollno.Text = "";
        hdnFlag.Value = "";
        hdnID.Value = "";
        btnsubmit.Text = "Submit";
    }
    public void fillgrid()
    {
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("select * from Data", con);
            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            GridView1.DataSource = rdr;
            GridView1.DataBind();
        }
    }
    protected void btnsubmit_Click(object sender, EventArgs e)
    {
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("sp_operations", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", hdnID.Value);
            cmd.Parameters.AddWithValue("@name", txtname.Text);
            cmd.Parameters.AddWithValue("@rollno", txtrollno.Text);
            cmd.Parameters.AddWithValue("@flag",hdnFlag.Value==""?"I":""+hdnFlag.Value+"");
            SqlParameter para = new SqlParameter();
            para.ParameterName = "@RetSuccess";
            para.DbType = System.Data.DbType.Int32;
            para.Direction = System.Data.ParameterDirection.Output;
            cmd.Parameters.Add(para);
            con.Open();
            cmd.ExecuteNonQuery();
            int returnedvalue = Convert.ToInt32(para.Value.ToString());
            if (returnedvalue == 1)
            {
                lblmsg.Text = "Data Inserted Successfully";

            }
            else if (returnedvalue == 2)
            {
                lblmsg.Text = "Data Updated Successfully";

            }
            else if (returnedvalue == 3)
            {
                lblmsg.Text = "Data Deleted Successfully";

            }
    
            fillgrid();
            reset();
        }
    }
    
    protected void lnkdel_Click(object sender, EventArgs e)
    {
        LinkButton lnkdel = sender as LinkButton;
        GridViewRow gvrow = lnkdel.NamingContainer as GridViewRow;
        hdnID.Value = GridView1.DataKeys[gvrow.RowIndex].Value.ToString();
        hdnFlag.Value = "D";
        btnsubmit_Click(sender, e);
       
    }
    protected void lnkEdit_Click(object sender, EventArgs e)
    {
        LinkButton lnkdel = sender as LinkButton;
        GridViewRow gvrow = lnkdel.NamingContainer as GridViewRow;
        hdnID.Value = GridView1.DataKeys[gvrow.RowIndex].Value.ToString();        
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("select * from Data where id=@id", con);
            cmd.Parameters.AddWithValue("@id",hdnID.Value);            
            con.Open();
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    txtname.Text = rdr["name"].ToString();
                    txtrollno.Text = rdr["rollno"].ToString();
                    btnsubmit.Text = "Update";
                    hdnFlag.Value = "U";
                }
            }
        }

    }

3. Create table having three columns (1. id (autoincremented), 2- (name nvarchar(255)), 3- rollno int) and assign name to table in sql server.

4. Add the connection string in your website web.config file:
 <connectionStrings>
    <add name="DS" connectionString="data source=.; database=a; integrated security=SSPI" providerName="System.Data.SqlClient"/>

  </connectionStrings>

5. Create Stored procedure for operations:

USE [a]
GO
/****** Object:  StoredProcedure [dbo].[sp_operations]    Script Date: 3/23/2019 11:24:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_operations](
@id int = nul,
@name nvarchar(255) = null,
@rollno int = null,
@flag char(1) =null,
@Retsuccess int = null output
)
as

begin
if(@flag = 'S')
begin
      select * from Data
end
      if(@flag = 'I')
  begin            
       insert into Data values(@name,@rollno)
   set @Retsuccess=1
  end
  if(@flag = 'U')
  begin            
       update Data set name=@name, rollno=@rollno where id=@id
   set @Retsuccess=2
  end
  if(@flag = 'D')
  begin            
       Delete from Data where id=@id
   set @Retsuccess=3
  end
  if(@flag = 'E')
  begin
       select * from Data where id=@id
  end
  return @Retsuccess 

end

6. After run this above stored procedure run your webpage.
following screenshot shows how is gui and data after binding to gridview