Menu ▼



Posted By :
avatarmrkraju
Total Views : 65056

Asp.net Gridview basic example for insert update and delete the records

Here I am going to give an example about asp.net gridvew which covers the following points.
  • Insert the data in database 
  • Binding the data to gridview from database 
  • Grid view row editing
  • Updating the records in database
  • Grid view row deleting and taking confirmation before deleting the record
Output screen shots:






SQL query to create table tblCustomers :

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[tblCustomers](

      [CustomerID] [int] IDENTITY(1,1) NOT NULL,

      [CustomerName] [varchar](50) NULL,

      [PhoneNumber] [varchar](10) NULL,

      [Address] [varchar](200) NULL,

      [IsActive] [bit] NULL,

 CONSTRAINT [PK_tblCustomers] PRIMARY KEY CLUSTERED

(

      [CustomerID] 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

GO

.

GridviewSample.aspx

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

 

<!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>Gridview Basics example.</title>

    <style type="text/css">

        .gv

        {

            font-family: Arial;

            margin-top: 30px;

            font-size: 14px;

        }

        .gv th

        {

            background-color: #5D7B9D;

            font-weight: bold;

            color: #fff;

            padding: 2px 10px;

        }

        .gv td

        {

            padding: 2px 10px;

        }

        input[type="submit"]

        {

            margin: 2px 10px;

            padding: 2px 20px;

            background-color: #5D7B9D;

            border-radius: 10px;

            border: solid 1px #000;

            cursor: pointer;

            color: #fff;

        }

        input[type="submit"]:hover

        {

            background-color: orange;

        }

    </style>

</head>

<body>

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

    <div>

        <table align="center" style="position: relative; top: 20px;">

            <tr>

                <td>

                    <table align="center">

                        <tr>

                            <td>

                                Customer Name :

                            </td>

                            <td>

                                <asp:TextBox ID="txtCustomerName" runat="server" MaxLength="50" Width="250px"></asp:TextBox>

                            </td>

                        </tr>

                        <tr>

                            <td>

                                Phone Number :

                            </td>

                            <td>

                                <asp:TextBox ID="txtPhoneNumber" runat="server" MaxLength="10" Width="250px"></asp:TextBox>

                            </td>

                        </tr>

                        <tr>

                            <td>

                                Address :

                            </td>

                            <td>

                                <asp:TextBox ID="txtAddress" runat="server" MaxLength="200" Width="250px"></asp:TextBox>

                            </td>

                        </tr>

                        <tr>

                            <td colspan="2" align="center">

                                <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />

                                <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click"

                                    Visible="false" />

                                <asp:Button ID="btnClear" runat="server" Text="Clear" OnClick="btnClear_Click" />

                            </td>

                        </tr>

                    </table>

                </td>

            </tr>

            <tr>

                <td align="center">

                    <br />

                    <asp:Label ID="lblMessage" runat="server" EnableViewState="false" ForeColor="Blue"></asp:Label>

                </td>

            </tr>

            <tr>

                <td>

                    <asp:GridView ID="gvDepartments" runat="server" AutoGenerateColumns="False" ShowHeaderWhenEmpty="True"

                        EmptyDataText="No Records Found" GridLines="both" CssClass="gv" EmptyDataRowStyle-ForeColor="Red">

                        <Columns>

                            <asp:TemplateField HeaderText="Customer Name">

                                <ItemTemplate>

                                    <asp:Label ID="lblCustomerName" runat="server" Text='<%#Eval("CustomerName") %>'></asp:Label>

                                </ItemTemplate>

                            </asp:TemplateField>

                            <asp:TemplateField HeaderText="Phone Number">

                                <ItemTemplate>

                                    <asp:Label ID="lblPhoneNumber" runat="server" Text='<%#Eval("PhoneNumber") %>'></asp:Label>

                                </ItemTemplate>

                            </asp:TemplateField>

                            <asp:TemplateField HeaderText="Address">

                                <ItemTemplate>

                                    <asp:Label ID="lblAddress" runat="server" Text='<%#Eval("Address") %>'></asp:Label>

                                </ItemTemplate>

                            </asp:TemplateField>

                            <asp:TemplateField HeaderText="Action">

                                <ItemTemplate>

                                    <asp:Button ID="btnEdit" runat="server" Text="Edit" OnClick="btnEdit_Click" />

                                    <asp:Button ID="btnDelete" runat="server" Text="Delete" OnClientClick="return confirm('Are you sure? want to delete the department.');"

                                        OnClick="btnDelete_Click" />

                                    <asp:Label ID="lblCustomerID" runat="server" Text='<%#Eval("CustomerID") %>' Visible="false"></asp:Label>

                                </ItemTemplate>

                            </asp:TemplateField>

                        </Columns>

                    </asp:GridView>

                </td>

            </tr>

        </table>

        <input type="hidden" runat="server" id="hidCustomerID" />

    </div>

    </form>

</body>

</html>

 

 

GridviewSample.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.SqlClient;

using System.Data;

 

public partial class GridviewSample : System.Web.UI.Page

{

    SqlConnection con = new SqlConnection(@"Data Source=localhost\sqlexpress;persist security info=True; Integrated Security=SSPI; Database=Sample;");

 

    protected void Page_Load(object sender, EventArgs e)

    {

        try

        {

            txtCustomerName.Focus();

            if (!IsPostBack)

            {

                FillGrid();

            }

        }

        catch

        {

 

        }

 

    }

 

    void FillGrid()

    {

        try

        {

            SqlCommand cmd = new SqlCommand();

            cmd.CommandText = "Select CustomerID,CustomerName,PhoneNumber,Address from tblCustomers where IsActive=1";

            cmd.Connection = con;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();

            da.Fill(ds);

            gvDepartments.DataSource = ds;

            gvDepartments.DataBind();

        }

        catch

        {

 

        }

    }

 

    void ClearControls()

    {

        try

        {

            txtCustomerName.Text = "";

            txtPhoneNumber.Text = "";

            txtAddress.Text = "";

            hidCustomerID.Value = "";

            btnSave.Visible = true;

            btnUpdate.Visible = false;

        }

        catch

        {

 

            throw;

        }

    }

 

    protected void btnSave_Click(object sender, EventArgs e)

    {

        try

        {

            SqlCommand cmd = new SqlCommand();

            cmd.CommandText = "insert into tblCustomers (CustomerName,PhoneNumber,Address,IsActive) values (@CustomerName,@PhoneNumber,@Address,1)";

            cmd.Parameters.AddWithValue("@CustomerName", txtCustomerName.Text);

            cmd.Parameters.AddWithValue("@PhoneNumber", txtPhoneNumber.Text);

            cmd.Parameters.AddWithValue("@Address", txtAddress.Text);

            cmd.Connection = con;

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

            FillGrid();

            ClearControls();

            lblMessage.Text = "Saved Successfully.";

        }

        catch

        {

 

        }

        finally

        {

            if (con.State == ConnectionState.Open)

                con.Close();

        }

    }

 

    protected void btnClear_Click(object sender, EventArgs e)

    {

        try

        {

            ClearControls();

        }

        catch

        {

 

        }

    }

 

    protected void btnEdit_Click(object sender, EventArgs e)

    {

        try

        {

            ClearControls();

            Button btn = sender as Button;

            GridViewRow grow = btn.NamingContainer as GridViewRow;

            hidCustomerID.Value = (grow.FindControl("lblCustomerID") as Label).Text;

            txtCustomerName.Text = (grow.FindControl("lblCustomerName") as Label).Text;

            txtPhoneNumber.Text = (grow.FindControl("lblPhoneNumber") as Label).Text;

            txtAddress.Text = (grow.FindControl("lblAddress") as Label).Text;

            btnSave.Visible = false;

            btnUpdate.Visible = true;

        }

        catch

        {

 

        }

    }

 

    protected void btnUpdate_Click(object sender, EventArgs e)

    {

        try

        {

            SqlCommand cmd = new SqlCommand();

            cmd.CommandText = "update tblCustomers set CustomerName=@CustomerName,PhoneNumber=@PhoneNumber,Address=@Address where CustomerID=@CustomerID";

            cmd.Parameters.AddWithValue("@CustomerName", txtCustomerName.Text);

            cmd.Parameters.AddWithValue("@PhoneNumber", txtPhoneNumber.Text);

            cmd.Parameters.AddWithValue("@Address", txtAddress.Text);

            cmd.Parameters.AddWithValue("@CustomerID", hidCustomerID.Value);

            cmd.Connection = con;

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

            FillGrid();

            ClearControls();

            lblMessage.Text = "Updated Successfully.";

        }

        catch

        {

 

        }

        finally

        {

            if (con.State == ConnectionState.Open)

                con.Close();

        }

    }

 

    protected void btnDelete_Click(object sender, EventArgs e)

    {

        try

        {

            ClearControls();

            Button btn = sender as Button;

            GridViewRow grow = btn.NamingContainer as GridViewRow;

            SqlCommand cmd = new SqlCommand();

            cmd.CommandText = "update tblCustomers set IsActive=0 where CustomerID=@CustomerID";

            cmd.Parameters.AddWithValue("@CustomerID", (grow.FindControl("lblCustomerID") as Label).Text);

            cmd.Connection = con;

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

            FillGrid();

            lblMessage.Text = "Deleted Successfully.";

        }

        catch

        {

 

        }

        finally

        {

            if (con.State == ConnectionState.Open)

                con.Close();

        }

    }

}

 







comments powered by Disqus


Search dotnetlearners.com