Total Pageviews

5 October 2016

Edit update and delete by using store procedure and pass parameters in c#

In this blogger we discuss about functions can have only input parameters allow giving parameters for it, whereas procedures can have I/O parameters. Here functions can be called from procedure for edit and update details by using different-different parameters, whereas procedures cannot be called from function. Procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE).


1-First of all creating table emp_details by using script


USE [mydb]
GO

/****** Object:  Table [dbo].[emp_details]    Script Date: 10/06/2016 11:06:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[emp_details](
    [emp_id] [int] IDENTITY(1,1) NOT NULL,
    [emp_name] [varchar](50) NULL,
    [emp_address] [varchar](50) NULL,
    [emp_salary] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


2-Creating procedure emp_details by using script

GO
/****** Object:  StoredProcedure [dbo].[procemp_details]    Script Date: 10/06/2016 10:20:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procemp_details]
      @Action VARCHAR(10)
      ,@emp_id INT = NULL
      ,@emp_name VARCHAR(50) = NULL
      ,@emp_address VARCHAR(50) = NULL
      ,@emp_salary INT=NULL
AS
BEGIN
      SET NOCOUNT ON;

      --SELECT
    IF @Action = 'SELECT'
      BEGIN
            SELECT emp_id, emp_name,emp_address, emp_salary
            FROM emp_deatails
      END

      --INSERT
    IF @Action = 'INSERT'
      BEGIN
            INSERT INTO emp_deatails(emp_name,emp_address,emp_salary)
            VALUES (@emp_name,@emp_address,@emp_salary)
      END

      --UPDATE
    IF @Action = 'UPDATE'
      BEGIN
            UPDATE emp_deatails
            SET emp_name =@emp_name, emp_address =@emp_address,emp_salary=@emp_salary
            WHERE emp_id =@emp_id
      END

      --DELETE
    IF @Action = 'DELETE'
      BEGIN
            DELETE FROM emp_deatails
            WHERE emp_id =@emp_id
      END
END


3-Create design page of grid view

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

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView ID="grd_details" runat="server"  AutoGenerateColumns="false" DataKeyNames="emp_id" OnRowDataBound="OnRowDataBound" OnRowEditing="OnRowEditing" OnRowCancelingEdit="OnRowCancelingEdit" OnRowUpdating="OnRowUpdating" OnRowDeleting="OnRowDeleting"  EmptyDataText="No records has been added.">
    <Columns>
    <asp:TemplateField HeaderText="Name" ItemStyle-Width="150">
    <ItemTemplate>
    <asp:Label ID="lblname" runat="server" Text='<%#Eval("emp_name") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtname" runat="server" Text='<%#Eval("emp_name") %>'></asp:TextBox>
    </EditItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Address" ItemStyle-Width="150">
    <ItemTemplate>
    <asp:Label ID="lbladdress" runat="server" Text='<%#Eval("emp_address") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtaddress" runat="server" Text='<%#Eval("emp_address") %>'></asp:TextBox>
    </EditItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Salary" ItemStyle-Width="150">
    <ItemTemplate>
    <asp:Label ID="lblsalary" runat="server" Text='<%#Eval("emp_salary") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtsalary" runat="server" Text='<%#Eval("emp_salary") %>'></asp:TextBox>
    </EditItemTemplate>
    </asp:TemplateField>
    <asp:CommandField ButtonType="Button" ShowEditButton="true" ShowDeleteButton="true"  ItemStyle-Width="150" />
    </Columns>
   
    </asp:GridView>
    <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
<tr>
    <td style="width: 150px">
        Name:<br />
        <asp:TextBox ID="txtName" runat="server" Width="140" />
    </td>
    <td style="width: 150px">
        Country:<br />
        <asp:TextBox ID="txtCountry" runat="server" Width="140" />
    </td>
    <td style="width: 150px">
        Salary:<br />
        <asp:TextBox ID="txtsalary" runat="server" Width="140" />
    </td>
    <td style="width: 100px">
        <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Insert" />
    </td>
    <td><asp:Label ID="lbl" runat="server"></asp:Label></td>
</tr>
</table>
    </div>
    </form>
</body>
</html>

Default2.aspx.cs

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.Configuration;
using System.Data.SqlClient;
public partial class Default2 : System.Web.UI.Page
{
    String s = ConfigurationManager.ConnectionStrings["conn"].ToString();
    SqlConnection con;
    SqlCommand cmd;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            this.grd_details1();
        }
    }

    private void grd_details1()
    {
        con = new SqlConnection(s);
        cmd = new SqlCommand("procemp_details");
           cmd.Parameters.AddWithValue("@Action", "SELECT");
       
             cmd.CommandType = CommandType.StoredProcedure;
              SqlDataAdapter sda = new SqlDataAdapter(cmd);
              cmd.Connection = con;
              sda.SelectCommand = cmd;
                 DataTable dt = new DataTable();
                    sda.Fill(dt);
                    grd_details.DataSource = dt;
                    grd_details.DataBind();
        
    }
    protected void Insert(object sender, EventArgs e)
    {
        string name = txtName.Text;
        string country = txtCountry.Text;
        string salary=txtsalary.Text;
        con = new SqlConnection(s);
        cmd = new SqlCommand("procemp_details");
        cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Action", "INSERT");
                cmd.Parameters.AddWithValue("@emp_name", name);
                cmd.Parameters.AddWithValue("@emp_address", country);
                cmd.Parameters.AddWithValue("@emp_salary", salary);
                cmd.Connection = con;
                con.Open();
           cmd.ExecuteNonQuery();
                con.Close();
                lbl.Visible = true;
                lbl.Text = "Records are Submitted Successfully";
               this.grd_details1();
    }
    protected void OnRowEditing(object sender, GridViewEditEventArgs e)
    {
        grd_details.EditIndex = e.NewEditIndex;
        this.grd_details1();
    }

    protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        GridViewRow row = grd_details.Rows[e.RowIndex];
        int emp_id = Convert.ToInt32(grd_details.DataKeys[e.RowIndex].Values[0]);
        string emp_name = (row.FindControl("txtName") as TextBox).Text;
        string emp_address = (row.FindControl("txtaddress") as TextBox).Text;
        string emp_salary = (row.FindControl("txtsalary") as TextBox).Text;
                con = new SqlConnection(s);
                SqlCommand cmd = new SqlCommand("procemp_details");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Action", "UPDATE");
                cmd.Parameters.AddWithValue("@emp_id", emp_id);
                cmd.Parameters.AddWithValue("@emp_name", emp_name);
                cmd.Parameters.AddWithValue("@emp_address", emp_address);
                cmd.Parameters.AddWithValue("@emp_salary",emp_salary);
                cmd.Connection = con;
                con.Open();
               cmd.ExecuteNonQuery();
               lbl.Visible = true;
               lbl.Text = "Records are updated Successfully";
                con.Close();
                
        grd_details.EditIndex = -1;
        this.grd_details1();
}
    protected void OnRowCancelingEdit(object sender, EventArgs e)
    {
        grd_details.EditIndex = -1;
        this.grd_details1();
    }

    protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int emp_id = Convert.ToInt32(grd_details.DataKeys[e.RowIndex].Values[0]);
           con = new SqlConnection(s);
           SqlCommand cmd = new SqlCommand("procemp_details");
               cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Action", "DELETE");
                cmd.Parameters.AddWithValue("@emp_id", emp_id);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                lbl.Visible = true;
                lbl.Text = "Records are Deleted Successfully";
                con.Close();
                this.grd_details1();
    }
    protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
       
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string item = e.Row.Cells[0].Text;
            foreach (Button button in e.Row.Cells[3].Controls.OfType<Button>())
            {
                if (button.CommandName == "Delete")
                {
                    button.Attributes["onclick"] = "if(!confirm('Do you want to delete " + item + "?')){ return false; };";
                }
            }
        }
    }
}

In the view of above articles helps all of you.Let me know if any query..........

Contact Form

Name

Email *

Message *