Skip to main content

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

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

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

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();
        }
    }
}



Labels:

Comments

Post a Comment

Popular posts from this blog

Asp.net grid CRUD with ADO.NET

Asp.net grid CRUD with ADO.NET A GridView is a graphical control element that presents a tabular view of data. A typical Grid View also supports some or all of the following: Clicking a column header to change the sort order of the Grid. Dragging column headers to change their size and their order. The GridView control displays the values of a data source in a table. Each column represents a field, while each row represents a record. Support to data source controls, such as SqlDataSource. Support sort capabilities. Support update and delete capabilities. Support paging capabilities. Support row selection capabilities. Code behind feature access to the GridView object model to dynamically set properties, handle events, and so on. Many key fields. Many data fields for the hyperlink columns. Customized style layout through themes and styles using css and javascript. The following operations can be performed using GridView control in ASP.NET using C# code behind. Fea
1 comment
Read more

MVC Action Filters using log4net

Add log4net dll reference to your MVC project ------------------------------------------------------------------------------ Create following  model inside your models folder ------------------------------------------------------------------------------- using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Text; using System.Web; using System.Web.Mvc; namespace MVCWebApp.Models {     public class LoggingFilterAttribute : ActionFilterAttribute     {         #region Logging         /// <summary>         /// Access to the log4Net logging object         /// </summary>         protected static readonly log4net.ILog log =           log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);         private const string StopwatchKey = "DebugLoggingStopWatch";         #endregion         public override void OnActionExecuting(ActionExecutingContext filterContext)
4 comments
Read more

Cascading Dropdown in Angular 7

Cascading Dropdown in Angular 7 And Web API Prerequisite Angular 7  Web API HTML/Bootstrap SQL Server Cascading DropDownList means a series of dependent DropDownLists where one DropDownList is dependent on another DropDownList. Child DropDownLists are populated based on the item selected in dropdownlist by a user. For example, loading all states in a country. There are three parts of this article. Create a SQL Server database with two tables, parent and child.  Create a Web API using ASP.NET Web API Project template Create an Angular 7  app Part 1. Create a Database For this article, I have created a database and two tables. If you already have data representing a parent-children form, you may skip this step. Step 1.  Open SQL Server Management Studio, connect to a server, and get ready to execute SQL.  Step 2. Create a database using the following query. create   Database  CaseCaddingDDL   Step 3.  Create a table, CountryMaster, usin
1 comment
Read more