Sunday, May 22, 2016

Insert Update Delete In GridView in asp.net c# using stored procedure

In this article , I am going to explain how to insert , update and delete in grid view using stored procedure.

For these operation we require a table, the given below code snippet for creating table in sql.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[userdtl](
          [Id] [int] IDENTITY(1,1) NOT NULL,
          [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
          [Age] [int] NULL,
          [Department] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
          [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
 
GO
SET ANSI_PADDING OFF

Now, given below aspx code copy in your aspx page
<%@ 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 id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <fieldset>
    <legend>Add/Edit Employee</legend>
    <table>
    <tr>
    <td>Employee Name:</td>
    <td><asp:TextBox ID="txtname" runat="server"></asp:TextBox>
    <asp:HiddenField ID="hdnid" runat="server" />
    </td>
    </tr>
    <tr>
    <td>Employee Age:</td>
    <td><asp:TextBox ID="txtAge" runat="server"></asp:TextBox></td>
    </tr>
    <tr>
    <td>Employee Department:</td>
    <td><asp:TextBox ID="txtdept" runat="server"></asp:TextBox></td>
    </tr>
    <tr>
    <td colspan="2" align="center">
    <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
    <asp:Label ID="lblErrormsg" runat="server" ForeColor="Red"></asp:Label>
    <asp:Label ID="lblSuccessmsg" runat="server" ForeColor="Green"></asp:Label>
    </td>
    </tr>
  
    </table>
    </fieldset>
 
 
    <fieldset>
    <legend>Emp Detail</legend>
    <asp:GridView ID="gvempdtl" runat="server" AutoGenerateColumns="False"
            BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"
            CellPadding="3">
    <Columns>
    <asp:TemplateField HeaderText="Id">
    <ItemTemplate>
    <asp:Label ID="lblId" runat="server" Text='<%#Eval("Id") %>'></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Name">
    <ItemTemplate>
    <asp:Label ID="lblName" runat="server" Text='<%#Eval("Name") %>'></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Age">
    <ItemTemplate>
    <asp:Label ID="lblAge" runat="server" Text='<%#Eval("Age") %>'></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>
     <asp:TemplateField HeaderText="Department">
    <ItemTemplate>
    <asp:Label ID="lblDepartment" runat="server" Text='<%#Eval("Department") %>'></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField>
    <ItemTemplate>
    <asp:Button ID="btnEdit" runat="server" Text="Edit" onclick="btnEdit_Click"/>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField>
    <ItemTemplate>
    <asp:Button ID="btnDelete" runat="server" Text="Delete" onclick="btnDelete_Click" OnClientClick="return confirm('Are you sure, you want to delete');"/>
    </ItemTemplate>
    </asp:TemplateField>
    </Columns>
        <FooterStyle BackColor="White" ForeColor="#000066" />
        <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
        <RowStyle ForeColor="#000066" />
        <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#F1F1F1" />
        <SortedAscendingHeaderStyle BackColor="#007DBB" />
        <SortedDescendingCellStyle BackColor="#CAC9C9" />
        <SortedDescendingHeaderStyle BackColor="#00547E" />
    </asp:GridView>
    </fieldset>
    </form>
</body>
</html>

Then , copy the give below cs code in your aspx.cs file.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindEmployeeDeatil();
        }
    }
 
 
    private void BindEmployeeDeatil()
    {
 
 
        SqlConnection con = new SqlConnection("Data Source=.;Persist Security Info=True;User ID=sa;Password=pramod");
        con.Open();
        SqlCommand cmd = new SqlCommand("Usp_userdtl", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@mode", "S");
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        if (ds != null && ds.Tables.Count > 0)
        {
            gvempdtl.DataSource = ds.Tables[0];
            gvempdtl.DataBind();
        }
 
 
 
    }
    private void Reset()
    {
        hdnid.Value = string.Empty;
        txtname.Text = string.Empty;
        txtdept.Text = string.Empty;
        txtAge.Text = string.Empty;
        btnSave.Text = "Save";
        BindEmployeeDeatil();
    }
 
 
    protected void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            lblSuccessmsg.Text = string.Empty;
            lblErrormsg.Text = string.Empty;
            string mode, Id = string.Empty;
            if (string.IsNullOrEmpty(hdnid.Value))
            {
                mode = "I";
                Id = "0";
            }
            else
            {
                mode = "U";
                Id = hdnid.Value;
            }
 
 
            SqlConnection con = new SqlConnection("Data Source=.;Persist Security Info=True;User ID=sa;Password=pramod");
            con.Open();
            SqlCommand cmd = new SqlCommand("Usp_userdtl", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@mode", mode);
            cmd.Parameters.AddWithValue("@Id", Id);
            cmd.Parameters.AddWithValue("@Name", txtname.Text);
            cmd.Parameters.AddWithValue("@Age", txtAge.Text);
            cmd.Parameters.AddWithValue("@Department", txtdept.Text);
            int result = cmd.ExecuteNonQuery();
            if (result > 0)
            {
                if (btnSave.Text.Trim() == "Save")
                {
                    lblSuccessmsg.Text = "Saved Successfully";
                }
                else if (btnSave.Text.Trim() == "Update")
                {
                    lblSuccessmsg.Text = "Updated Successfully";
                }
                Reset();
            }
            else
            {
                lblErrormsg.Text = "Please Contact to Administrator";
            }
        }
        catch (Exception ex)
        {
            lblErrormsg.Text = ex.Message;
        }
    }
 
 
    protected void btnEdit_Click(object sender, EventArgs e)
    {
        try
        {
            lblSuccessmsg.Text = string.Empty;
            lblErrormsg.Text = string.Empty;
 
 
            Button btn = sender as Button;
            GridViewRow row = (GridViewRow)btn.NamingContainer;
 
 
            hdnid.Value = (row.FindControl("lblId") as Label).Text;
            txtname.Text = (row.FindControl("lblName") as Label).Text;
            txtAge.Text = (row.FindControl("lblAge") as Label).Text;
            txtdept.Text = (row.FindControl("lblDepartment") as Label).Text;
            btnSave.Text = "Update";
 
 
 
 
        }
        catch (Exception ex)
        {
            lblErrormsg.Text = ex.Message;
        }
 
 
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        try {
            lblSuccessmsg.Text = string.Empty;
            lblErrormsg.Text = string.Empty;
            Button btn = sender as Button;
            GridViewRow row = (GridViewRow)btn.NamingContainer;
            SqlConnection con = new SqlConnection("Data Source=.;Persist Security Info=True;User ID=sa;Password=pramod");
            con.Open();
            SqlCommand cmd = new SqlCommand("Usp_userdtl", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@mode", "D");
            cmd.Parameters.AddWithValue("@Id", (row.FindControl("lblId") asLabel).Text);
            int result = cmd.ExecuteNonQuery();
            if (result > 0)
            {
                lblSuccessmsg.Text = "Deleted Successfully";
                Reset();
            }
            else {
                lblErrormsg.Text = "Please Contact to Administrator";
            }
        }
        catch (Exception ex)
        {
            lblErrormsg.Text = ex.Message;
        }
    }
}


Now run your website, Output is



Posted by at
Labels: ,

1 comment:

Subscribe to: Post Comments (Atom)