Insert, Update, Delete operation in Database using Stored Procedure for controles like Radio Button, FileUplod , CheckBox and DropDownList in Gridview in asp.net with c#- Example

Introduction about Stored Procedure:-


                             A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure.

My Procedure:-

 CREATE PROCEDURE [dbo].[AbyProc]
  (

    @nm nvarchar(50)=null,
    @gen nvarchar(50)=null,
    @quly bit=null,
    @post nvarchar(50)=null,
    @resume nvarchar(50)=null,
   
    @id int = null
  )

AS  
If @id IS NULL

    Begin
        insert into aby_JobApplication(nm,gen,quly,post,resume) values(@nm, @gen, @quly, @post, @resume)
    End

Else
    if @nm IS NOT NULL
        begin
            update aby_JobApplication set nm=@nm, gen=@gen, quly=@quly, post=@post, resume=@resume where id=@id
        End
    else
        Begin
            delete from aby_JobApplication where id=@id
        End





Source Code :-(in ASP.NET)


<%@ page language="C#" autoeventwireup="true" codefile="Proc.aspx.cs" inherits="Proc" %>

<!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>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
        .style2
        {
            width: 129px;
        }
        .style3
        {
            width: 129px;
            height: 26px;
        }
        .style4
        {
            height: 26px;
        }
        .style5
        {
            width: 129px;
            height: 56px;
        }
        .style6
        {
            height: 56px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <table class="style1">
        <tr>
            <td class="style3">
                Name
            </td>
            <td class="style4">
                <asp:textbox id="txtnm" runat="server" maxlength="40"></asp:textbox>
                <asp:requiredfieldvalidator id="RequiredFieldValidator1" runat="server" controltovalidate="txtnm"
                    errormessage="RequiredFieldValidator" validationgroup="vjobapp"></asp:requiredfieldvalidator>
            </td>
        </tr>
        <tr>
            <td class="style5">
                gender
            </td>
            <td class="style6">
                <asp:radiobuttonlist id="rblgen" runat="server" height="39px" width="131px">

                    <asp:ListItem Selected="True" Value="0">MALE</asp:ListItem>
                    <asp:ListItem Value="1">FEMALE</asp:ListItem>
                </asp:radiobuttonlist>
           </td>
        </tr>
        <tr>
            <td class="style2">
                Qualification
            </td>
            <td>
                <asp:checkboxlist id="cbquly" runat="server">
                    <asp:ListItem>Deploma</asp:ListItem>
                    <asp:ListItem>Becholar</asp:ListItem>
                    <asp:ListItem>Post Gradute</asp:ListItem>
                </asp:checkboxlist>
            </td>
        </tr>
        <tr>
            <td class="style2">
                Appied For Post
            </td>
            <td>
                <asp:dropdownlist id="ddlpost" runat="server">
                    <asp:ListItem Value="0">&lt;SELECT POST&gt;</asp:ListItem>
                    <asp:ListItem>Project Manager</asp:ListItem>
                    <asp:ListItem>Tester</asp:ListItem>
                    <asp:ListItem>Developer</asp:ListItem>
                    <asp:ListItem>HR</asp:ListItem>
                    <asp:ListItem>System Support</asp:ListItem>
                </asp:dropdownlist>
                <asp:requiredfieldvalidator id="RequiredFieldValidator3" runat="server" controltovalidate="ddlpost"
                    errormessage="RequiredFieldValidator" initialvalue="0" validationgroup="vjobapp"></asp:requiredfieldvalidator>
            </td>
        </tr>
        <tr>
            <td class="style2">
                Image
            </td>
            <td>
                <asp:fileupload id="FileUplodResume" runat="server" />
                <asp:label id="lblpath" runat="server" visible="False"></asp:label>
                <asp:requiredfieldvalidator id="RequiredFieldValidator4" runat="server" controltovalidate="FileUplodResume"
                    errormessage="RequiredFieldValidator" validationgroup="vjobapp"></asp:requiredfieldvalidator>
            </td>
        </tr>
        <tr>
            <td class="style2">
                &nbsp;
            </td>
            <td>
                <asp:button id="btnadd" runat="server" onclick="btnAdd_Click" text="Add" validationgroup="vjobapp" />
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <asp:button id="btnCancle" runat="server" text="Cancle" onclick="btnCancle_Click" />
                <asp:hiddenfield id="hid" runat="server" />
            </td>
        </tr>
        <tr>
            <td class="style2">
            </td>
            <td>
                <asp:gridview id="GridView1" runat="server" autogeneratecolumns="False" cellpadding="4"
                    forecolor="#333333" gridlines="None">
                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />

                   <Columns>
                       <asp:TemplateField Visible="false">
                            <ItemTemplate>
                                <asp:Label ID="lid" runat="server" Text='<%#Eval("id")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Name">
                            <ItemTemplate>
                                <asp:Label ID="lnm" runat="server" Text='<%#Eval("nm")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Gender">
                            <ItemTemplate>
                                <asp:Label ID="lgen" runat="server" Text='<%# Eval("gen").ToString()=="0" ? "MALE":"FEMAlE" %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Qualification">
                            <ItemTemplate>
                                <asp:Label ID="lquly" runat="server" Text='<%# Eval("quly")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                      
                         <asp:TemplateField HeaderText="Post">
                            <ItemTemplate>
                                <asp:Label ID="lpost" runat="server" Text='<%#Eval("post")%>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Name">
                            <ItemTemplate>
                              
                                <asp:Image ID="lresume" runat="server" Height="200px" Width="200px" ImageUrl='<%#Eval("resume") %>'/>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField>
                            <ItemTemplate>
                                <asp:LinkButton ID="lbedit" runat="server" OnClick="editDetail"
                                    CommandArgument='<% #Eval("id")%>'>Edit</asp:LinkButton>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField>
                            <ItemTemplate>
                                <asp:LinkButton ID="lbdelete" runat="server" OnClick="deleteDetail" CommandArgument='<% #Eval("id")%>'>Delete</asp:LinkButton>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <EditRowStyle BackColor="#999999" />
                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                </asp:gridview>
           </td>
       </tr>
    </table>
    </form>
</body>
</html>


Code Behind (in C#):-


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;
public partial class Proc : System.Web.UI.Page
{
      static string str = @"Data Source=SQLDB;Persist Security Info=True;User ID=Demod;Password=Demo1@";
        public static SqlConnection con = new SqlConnection(str);
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                gridbind();
            }
        }
        private void gridbind()
        {
            GridView1.DataSource = Data_of_Grid("select * from aby_JobApplication");
            GridView1.DataBind();
        }
        public DataTable Data_of_Grid(string q)
        {
            DataTable dt = new DataTable();
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                SqlCommand cmd = new SqlCommand(q, con);
                SqlDataAdapter sd = new SqlDataAdapter();
                sd.SelectCommand = cmd;
                sd.Fill(dt);

            }
            catch (Exception err)
            {
                //TODO
            }
            finally
            {
                con.Close();
            }
            return dt;
        }
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            string cbl = "";
            foreach (ListItem li in cbquly.Items)
            {
                if (li.Selected == true)
                {
                    cbl += li.Text + ",";
                }
            }

            string ig = FileUplodResume.FileName.ToString();
            string url = "photo/" + ig;
            FileUplodResume.SaveAs(Server.MapPath("~//") + url);
            SqlCommand command = new SqlCommand("[AbyProc]", con);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@nm", txtnm.Text);
            command.Parameters.AddWithValue("@gen", rblgen.SelectedValue);
            command.Parameters.AddWithValue("@quly", cbl);
            command.Parameters.AddWithValue("@post", ddlpost.SelectedItem.Text);
            command.Parameters.AddWithValue("@resume", url);
            if (btnadd.Text == "Add")
            {
                //command.Parameters.AddWithValue("@id", null);
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                command.ExecuteNonQuery();
                con.Close();
            }
            else
            {
                command.Parameters.AddWithValue("@id", Convert.ToInt32(hid.Value));
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                command.ExecuteNonQuery();
                con.Close();
                btnadd.Text = "Add";
                lblpath.Visible = false;
            }
            clr();
            gridbind();
        }
        protected void editDetail(object sender, EventArgs e)
        {
            LinkButton lb = (LinkButton)sender;
            GridViewRow gr = (GridViewRow)lb.NamingContainer;
            hid.Value = lb.CommandArgument;
            Label lnm1 = (Label)gr.FindControl("lnm");
            txtnm.Text = lnm1.Text;
            Label lgen1 = (Label)gr.FindControl("lgen");
            rblgen.SelectedValue = lgen1.Text == "MALE" ? "0" : "1";
            Label lquly1 = (Label)gr.FindControl("lquly");
            foreach (ListItem li in cbquly.Items)
            {
                if (lquly1.Text.Contains(li.Text))
                {
                    li.Selected = true;
                }
            }

            //cbquly.Checked = lquly1.Text == "POST GRADUATED" ? true : false;
            Label lpost1 = (Label)gr.FindControl("lpost");
            ddlpost.SelectedValue = lpost1.Text;
            Image im = (Image)gr.FindControl("lresume");
            lblpath.Text = im.ImageUrl;
            lblpath.Visible = true;
            gridbind();
            btnadd.Text = "Update";
        }
        protected void deleteDetail(object sender, EventArgs e)
        {
            LinkButton lb = (LinkButton)sender;
            SqlCommand command = new SqlCommand("[AbyProc]", con);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@id", Convert.ToInt32(lb.CommandArgument));

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            command.ExecuteNonQuery();
            con.Close();

            gridbind();
            clr();

            btnadd.Text = "Add";

        }

        protected void btnCancle_Click(object sender, EventArgs e)
        {
            btnadd.Text = "Add";
            clr();
        }
        private void clr()
        {
            lblpath.Visible = false;
            txtnm.Text = "";
            rblgen.SelectedValue = "0";
            
            foreach (ListItem li in cbquly.Items)
            {
                li.Selected = false;
            }
            ddlpost.SelectedValue = "0";

        }

}

3 comments

Nice post Brother...keep it up......

Reply

helpful post aby...carry on

Reply

Post a Comment