How to perform Insert Edit Update Delete Cancel and Print Operations in GridView Using Stored Procedure with Example

On By
Hi Friend ,Today we will learn "How to Perform Insert,Edit,Update,Delete and Cancel operations in Gridview Easily".Here I have using Stored Procedure for this operations. This concepts is very good and easy.For this You have to knowledge of Stored Procedure. First we will create Stored Procedure after that use it in different operations in Gridview. For More Knowledge, you have read below link that will be very helpful to you.
There are some steps to implement this whole concepts as given below:-
Step 1 :-First open Open SQL Server Management Studio-->Create a table (Student_Details) with an  identity column as given below:-


CREATE TABLE Student_Details
 (
 St_Id INT IDENTITY,
 St_Name varchar(40),
 St_Age INT,
 St_College varchar(50)
 )
See like that:-
Step 2 :- Create Stored procedure  (Some_Operations) as given below:-

CREATE PROCEDURE Some_Operations
 @St_Id int = 0,
 @St_Name varchar(40)=null,
 @St_Age int =0,
 @St_College varchar(50)=null,
 @St_Operation varchar(50)
 AS
 BEGIN
 SET NOCOUNT ON;
 --Insert new values in Student_Details table
 IF @St_Operation='INSERT'

 BEGIN
 INSERT INTO Student_Details(St_Name,St_Age,St_College) VALUES(@St_Name,@St_Age,@St_College)
 END
 -- Select Records in Table
 IF @St_Operation='SELECT'
 BEGIN
 SELECT ST_id,St_Name,St_Age,St_College FROM Student_Details
 END
 --- Update Records in Table
 IF @St_Operation='UPDATE'
 BEGIN
 UPDATE Student_Details SET St_Name=@St_Name,St_Age=@St_Age,St_College=@St_College WHERE St_Id=@St_Id
 END
 --- Delete Records from Table
 IF @St_Operation='DELETE'
 BEGIN
 DELETE FROM Student_Details where St_Id=@St_Id
 END
 SET NOCOUNT OFF
  END
Step 3 :- Now Run Stored Procedure(Some Operations) in sql server as shown below:-

Step 4 :- Now open Your Visual Studio --> File --> New-->Select ASP.NET Empty Website-->OK--> Open Solution Explorer Window--> Add a New Item (Default.aspx) page--> Open Source file --> Now open Toolbox --> drag and drop Ajax Script Manager and Update Panel-->After that bind the Gridview Data as given Below in HTML Codes:-

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>MY.NET Tutorials</title>
     <script type="text/javascript">
         function print_page() {
             window.print();
         }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <a href="#" onclick="javascript :print_page();"><strong><span class="style1">Take print out</span></strong></a>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
       
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
            <asp:GridView ID="GridView1" runat="server" ShowFooter="true" AllowPaging="true" PageSize="5" 
            AutoGenerateColumns="false" DataKeyNames="St_Id,St_Name"  OnPageIndexChanging="indexchanging" 
            OnRowEditing="Row_Editing" OnRowUpdating="Row_Updating" OnRowDeleting="Row_Deleting" OnRowCommand="Row_Command" 
            onrowcancelingedit="GridView1_RowCancelingEdit" >
                <HeaderStyle CssClass="headerstyle" />
            <Columns>
            <asp:BoundField DataField="St_Id" HeaderText="Student_Id" ReadOnly="true"/>
            <asp:TemplateField HeaderText="Student Name">
            <ItemTemplate>
            <asp:Label ID="Label1" runat="server" Text='<%# Eval("St_Name")%>'/>
            </ItemTemplate>
            <EditItemTemplate>
            <asp:TextBox ID="txtename" runat="server" Text='<%# Eval("St_Name")%>'/>
            </EditItemTemplate>
            <FooterTemplate>
            <asp:TextBox ID="txtfname" runat="server" />
            </FooterTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText = "Student_Age">
            <ItemTemplate>
            <asp:Label ID="Label2" runat="server" Text='<%# Eval("St_Age")%>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
            <asp:TextBox ID="txtage" runat="server" Text='<%# Eval("St_Age")%>'/>
            </EditItemTemplate>
            <FooterTemplate>
            <asp:TextBox ID="txtfage" runat="server"/>
            </FooterTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText = "Student_College">
            <ItemTemplate>
            <asp:Label ID="Label3" runat="server" Text='<%# Eval("St_College")%>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
            <asp:TextBox ID="txtecollege" runat="server" Text='<%# Eval("St_College")%>'/>
            </EditItemTemplate>
            <FooterTemplate>
            <asp:TextBox ID="txtfcollege" runat="server"/>
            <asp:Button ID="btn" CommandName="AddNew" ShowEditButton="True" runat="server" Text="Add_Row" />
            </FooterTemplate>
            </asp:TemplateField>
            <asp:CommandField ShowEditButton="True" ShowDeleteButton="true"/>
            </Columns>
            </asp:GridView>
            <asp:Label ID="Label4" runat="server" style="color: #FF0066"></asp:Label>
        </ContentTemplate>
        <Triggers>
       <asp:AsyncPostBackTrigger ControlID="GridView1" />
        </Triggers>
        </asp:UpdatePanel>
    </div>
    </form>
</body>
</html>
Step 5 :- Now Write the C# codes in Default.aspx.cs page as given below:-

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind_dataGridview();
        }
    }
    protected void Bind_dataGridview()
    {
        SqlConnection con = new SqlConnection("Data Source=RAMASHANKAR-PC;Integrated Security=Yes;Database=master");
        con.Open();
        SqlCommand cmd = new SqlCommand("Some_Operations", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@St_Operation","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());
            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 Row_Command(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("AddNew"))
        {
            TextBox Name = (TextBox)GridView1.FooterRow.FindControl("txtfname");
            TextBox Age = (TextBox)GridView1.FooterRow.FindControl("txtfage");
            TextBox College = (TextBox)GridView1.FooterRow.FindControl("txtfcollege");
            Some_Operations("INSERT",0,Name.Text,Age.Text,College.Text);
        }

    }
    protected void Row_Editing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        Bind_dataGridview();
    }

    protected void Row_Updating(object sender, GridViewUpdateEventArgs e)
    {
        int stid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["St_Id"].ToString());
        TextBox txtname1 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtename");
        TextBox txtage1 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtage");
        TextBox txtcollege1 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtecollege");
        Some_Operations("UPDATE",stid,txtname1.Text, txtage1.Text, txtcollege1.Text);
    }

    protected void Some_Operations(string St_Operation,int St_Id,string St_Name,string St_Age,string St_College)
    {
        SqlConnection con = new SqlConnection("Data Source=RAMASHANKAR-PC;Integrated Security=Yes;Database=master");
        con.Open();
        SqlCommand cmd = new SqlCommand("Some_Operations", con);
        cmd.CommandType = CommandType.StoredProcedure;
        if (St_Operation == "INSERT")
        {
            cmd.Parameters.AddWithValue("@St_Operation",St_Operation);
            cmd.Parameters.AddWithValue("@St_Name",St_Name);
            cmd.Parameters.AddWithValue("@St_Age",St_Age);
            cmd.Parameters.AddWithValue("@St_College",St_College);
        }
        else if (St_Operation=="UPDATE")
        {
            cmd.Parameters.AddWithValue("@St_Operation",St_Operation);
            cmd.Parameters.AddWithValue("@St_Id",St_Id);
            cmd.Parameters.AddWithValue("@St_Name",St_Name);
            cmd.Parameters.AddWithValue("@St_Age",St_Age);
            cmd.Parameters.AddWithValue("@St_College",St_College);
            
        }
        else if (St_Operation=="DELETE")
        {
            cmd.Parameters.AddWithValue("@St_Operation",St_Operation);
            cmd.Parameters.AddWithValue("@St_Id", St_Id);
        }
        cmd.ExecuteNonQuery();
        Label4.ForeColor = Color.DarkRed;
        Label4.Text = "Operation perform successfully,Thank you";
        GridView1.EditIndex = -1;
        Bind_dataGridview();
    }
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        Bind_dataGridview();
    }
    protected void indexchanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.EditIndex = e.NewPageIndex;
        Bind_dataGridview();
    }
    protected void Row_Deleting(object sender, GridViewDeleteEventArgs e)
    {
        int student_Id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["St_Id"].ToString());
        string student_name = GridView1.DataKeys[e.RowIndex].Values["St_Name"].ToString();
        Some_Operations("DELETE",student_Id,student_name,"","");
    }

}

Step 6 :- Now  open your page in Design View --> You will see following output as shown below:-

Step 7 :-Now Run the Application (Press F5)--> You will see following Output as shown below:-
Step 8 :- You can see whole operations in below video also.  

For More...
  1. How to use virtual keyboard in asp.net website
  2. How to create secure login and registration page in asp.net
  3. Learn complete Interview Questions and answers
  4. How to save image in database and print in picture box
  5. How to use md5 and sh1 Algorithms  in asp.net
Download Whole Attached File From Below:-
                 Download
Subscribe through email and get Free updates in inbox
Please like and share this post with your friends...

You May Also Like...

1 comment:

Powered by Blogger.