Insert,Update,Delete Record From DataBase Using GridView Control In Asp.Net C#

2년 전

DATABASE


create database tension


create table viewer

(

vid int NOT NULL PRIMARY KEY,

vname varchar(50) NULL,

vlocation varchar(50) NULL,

vgender varchar(50) NULL,

);


CREATE PROCEDURE pro

@vid int=null,

@vname varchar(50)=null,

@vlocation varchar(50)=null,

@vgender varchar(50)=null,

@status varchar(50)=null

AS 

BEGIN 

SET NOCOUNT ON;

---INSERT NEW RECORDS

IF @status='INSERT'

BEGIN

INSERT INTO viewer(vid,vname,vlocation,vgender)VALUES(@vid,@vname,@vlocation,@vgender)

END

---SELECT RECORDS IN TABLE

IF @status='SELECT'

BEGIN

SELECT vid,vname,vlocation,vgender FROM viewer

END

---UPDATE RECORDS IN TABLE

IF @status='UPDATE'

BEGIN

UPDATE viewer SET vname=@vname,vlocation=@vlocation,vgender=@vgender WHERE vid=@vid

END

---DELETE RECORD FROM TABLE

IF @status='DELETE'

BEGIN

DELETE FROM viewer where vid=@vid

END

SET NOCOUNT OFF

END

insert into viewer values(1,'VIRAJ','MUMBAI','MALE')



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


<!DOCTYPE html>


<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

    

        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" ShowFooter="True" DataKeyNames="vid" OnRowDeleting="delete" OnRowUpdating="update" OnRowCancelingEdit="canceledit" OnRowEditing="edit" OnPageIndexChanging="Pages">

            <Columns>

                <asp:CommandField ShowSelectButton="True" />

                <asp:CommandField ShowEditButton="True" />

                <asp:CommandField ShowDeleteButton="True" />

                <asp:TemplateField HeaderText="vid">

                    <EditItemTemplate>

                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("vid") %>'></asp:TextBox>

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:Button ID="Button1" runat="server" Text="INSERT" OnClick="Button1_Click" />

                        &nbsp;&nbsp;

                        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

                    </FooterTemplate>

                    <ItemTemplate>

                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("vid") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="vname">

                    <EditItemTemplate>

                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("vname") %>'></asp:TextBox>

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>

                    </FooterTemplate>

                    <ItemTemplate>

                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("vname") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="vlocation">

                    <EditItemTemplate>

                        <asp:DropDownList ID="DropDownList1" runat="server" SelectedValue='<%# Bind("vlocation") %>'>

                            <asp:ListItem>--SELECT LOCATION--</asp:ListItem>

                            <asp:ListItem>MUMBAI</asp:ListItem>

                            <asp:ListItem>NAVI MUMBAI</asp:ListItem>

                            <asp:ListItem>THANE</asp:ListItem>

                        </asp:DropDownList>

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:DropDownList ID="DropDownList2" runat="server">

                            <asp:ListItem>--SELECT LOCATION--</asp:ListItem>

                            <asp:ListItem>MUMBAI</asp:ListItem>

                            <asp:ListItem>NAVI MUMBAI</asp:ListItem>

                            <asp:ListItem>THANE</asp:ListItem>

                        </asp:DropDownList>

                    </FooterTemplate>

                    <ItemTemplate>

                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("vlocation") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="vgender">

                    <EditItemTemplate>

                        <asp:DropDownList ID="DropDownList3" runat="server" SelectedValue='<%# Bind("vgender") %>'>

                            <asp:ListItem>MALE</asp:ListItem>

                            <asp:ListItem>FEMALE</asp:ListItem>

                        </asp:DropDownList>

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:DropDownList ID="DropDownList4" runat="server">

                            <asp:ListItem>MALE</asp:ListItem>

                            <asp:ListItem>FEMALE</asp:ListItem>

                        </asp:DropDownList>

                    </FooterTemplate>

                    <ItemTemplate>

                        <asp:Label ID="Label4" runat="server" Text='<%# Bind("vgender") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

            </Columns>

            <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />

            <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />

            <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />

            <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />

            <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />

            <SortedAscendingCellStyle BackColor="#FFF1D4" />

            <SortedAscendingHeaderStyle BackColor="#B95C30" />

            <SortedDescendingCellStyle BackColor="#F1E5CE" />

            <SortedDescendingHeaderStyle BackColor="#93451F" />

        </asp:GridView>

    

    </div>

    </form>

</body>

</html>



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 _Default : System.Web.UI.Page

{

    SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

    protected void Page_Load(object sender, EventArgs e)

    {

        SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

        if(!Page.IsPostBack)

        {

            filldata();

        }

     

    }

    public void filldata()

    {

        SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

        SqlDataAdapter da = new SqlDataAdapter("select *from viewer",con);

        DataSet ds = new DataSet();

        da.Fill(ds);

        GridView1.DataSource = ds.Tables[0];

        GridView1.DataBind();

        GridView1.SelectedIndex = 0;

    }

    protected void delete(object sender,GridViewDeleteEventArgs e)

    {

        SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

        SqlCommand cmd = new SqlCommand("delete from viewer where vid=@vid",con);

        cmd.Parameters.Add(new SqlParameter("@vid",GridView1.DataKeys[e.RowIndex].Values[0]));

        con.Open();

        cmd.ExecuteNonQuery();

        con.Close();

        filldata();

    }

    protected void update(object sender,GridViewUpdateEventArgs e)

    {

        SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

        TextBox vid = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox1");

        TextBox vname = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox2");

        DropDownList vlocation = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("DropDownList1");

        DropDownList vgender = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("DropDownList3");

        SqlCommand cmd = new SqlCommand("update viewer set vname=@vname,vlocation=@vlocation,vgender=@vgender where vid=@vid",con);

        cmd.Parameters.Add(new SqlParameter("@vid",vid.Text));

        cmd.Parameters.Add(new SqlParameter("@vname", vname.Text));

        cmd.Parameters.Add(new SqlParameter("@vlocation", vlocation.SelectedItem.Text));

        cmd.Parameters.Add(new SqlParameter("@vgender", vgender.SelectedItem.Text));

        con.Open();

        cmd.ExecuteNonQuery();

        con.Close();

        GridView1.EditIndex = -1;

        filldata();

    }

    protected void canceledit(object sender, GridViewCancelEditEventArgs e)

    {

        GridView1.EditIndex = -1;

        filldata();

    }

    protected void edit(object sender, GridViewEditEventArgs e)

    {

        GridView1.EditIndex = e.NewEditIndex;

        filldata();

    }

    protected void Pages(object sender,GridViewPageEventArgs e)

    {

        GridView1.EditIndex = e.NewPageIndex;

        filldata();

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

        TextBox vid = (TextBox)GridView1.FooterRow.FindControl("TextBox3");

        TextBox vname = (TextBox)GridView1.FooterRow.FindControl("TextBox4");

        DropDownList vlocation = (DropDownList)GridView1.FooterRow.FindControl("DropDownList2");

        DropDownList vgender = (DropDownList)GridView1.FooterRow.FindControl("DropDownList4");

        SqlCommand cmd = new SqlCommand("insert into viewer values(@vid,@vname,@vlocation,@vgender)",con);

        cmd.Parameters.Add(new SqlParameter("@vid", vid.Text));

        cmd.Parameters.Add(new SqlParameter("@vname", vname.Text));

        cmd.Parameters.Add(new SqlParameter("@vlocation", vlocation.SelectedItem.Text));

        cmd.Parameters.Add(new SqlParameter("@vgender", vgender.SelectedItem.Text));

        con.Open();

        cmd.ExecuteNonQuery();

        con.Close();

        filldata();

    }

}

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
STEEMKR.COM IS SPONSORED BY
ADVERTISEMENT
Sort Order:  trending

Congratulations @virajtakke09! You received a personal award!

Happy Birthday! - You are on the Steem blockchain for 1 year!

You can view your badges on your Steem Board and compare to others on the Steem Ranking

Do not miss the last post from @steemitboard:

SteemitBoard - Witness Update
Vote for @Steemitboard as a witness to get one more award and increased upvotes!