Simple & Complete GridView Functionality in .Net 2.0



My this article covers GridView functionality in a Complete,Simple & Precise way in ASP.NET(2.0), C#,(SQLServer 2005 backend). This article provides a single GridView which is capable of

  • Displaying Data from database,
  • Updating it if required,
  • Deleting the existing records from database based on a Confirmation Message and
  • Inserting a new record(which is beyond the implicit usage of GridView).

The application achieves Insertion through 'Update' event of Gridview by modifying the text of 'Update' LinkButton on the click of button named 'btnAddrec' 
The application requires three simple stored procedures named empins,empupd,empdel for updation insertion and deletion respectively.
Start from the scratch:
Create a table named Employee1 by giving following DDL in the SQL Server:

CREATE TABLE guest.Employee1

(

    EmployeeID   int         IDENTITY,

    EmployeeName varchar(50) NULL,

    ContactNo    varchar(50) NULL

)

go

Now insert some dummy values if required. I assume you can do yourself like:

insert into Employee1 values("Abc","9999999999")....(here you donot need to insert EmployeeID as it is auto generated)
Make your stored procedures ready beforehand.
Stored Procedure for Insertion:

CREATE procedure empins

(

@EmployeeName varchar(50),

@ContactNo varchar(50)

)

as

insert into Employee1

values(@EmployeeName,@ContactNo)
 RETURN
Stored Procedure for Updation:

CREATE procedure empupd

(

@EmployeeID int,

@EmployeeName varchar(50),

@ContactNo varchar(50)

)

as

 

update Employee1 set EmployeeName=@EmployeeName,ContactNo=@ContactNo where EmployeeID=@EmployeeID 
 RETURN
Stored Procedure for Deletion:

CREATE PROCEDURE empdel 

 (

 @EmployeeID int

 )

 

AS

 delete from Employee1 where EmployeeID=@EmployeeID
 RETURN
Now open Visual Studio>New Website
In the source of Default.aspx file copy and paste the following html source to create your UI Presentation for GridView
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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>Untitled Page</title>

</head>

<body>

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

    <div>

        </div>

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"

            ForeColor="#333333" GridLines="None" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" style="z-index: 102; left: 43px; position: absolute; top: 125px">

            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

            <Columns>

                <asp:CommandField ShowEditButton="True" />

               

<asp:TemplateField>

<ItemTemplate>

<asp:LinkButton ID="LinkButton1" runat="server" OnClientClick="return confirm('Are you sure you want to delete this record?');" CommandName="Delete">Delete??</asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

 

 <asp:TemplateField>

           <EditItemTemplate>

               <asp:Label ID="lblEmployeeIDEdit" runat="server" Text='<%# Eval("EmployeeID") %>'

                            Width="61px"></asp:Label>

                    </EditItemTemplate>

                    <HeaderTemplate>

                        Employee ID

                    </HeaderTemplate>

                    <ItemTemplate>

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

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField>

                    <EditItemTemplate>

                        <asp:TextBox ID="txtEmployeeName" runat="server" Text='<%# Eval("EmployeeName") %>'></asp:TextBox>

                    </EditItemTemplate>

                    <HeaderTemplate>

                        Employee Name

                    </HeaderTemplate>

                    <ItemTemplate>

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

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField>

                    <EditItemTemplate>

                        <asp:TextBox ID="txtContactNo" runat="server" Text='<%# Eval("ContactNo") %>'></asp:TextBox>

                    </EditItemTemplate>

                    <HeaderTemplate>

                        Contact No

                    </HeaderTemplate>

                    <ItemTemplate>

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

                    </ItemTemplate>

                </asp:TemplateField>

            </Columns>

            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />

            <EditRowStyle BackColor="#999999" />

            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />

            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />

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

            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />

        </asp:GridView>

            <asp:Button ID="btnaddrec" runat="server" OnClick="btnaddrec_Click" Style="z-index: 101;

            left: 143px; position: absolute; top: 82px" Text="Add a New Record" />

    </form>

</body>

</html>


Now copy paste the follwing C# code corresponding to each event:

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient; 

 

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

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

            BindData();

    }

 

    private void BindData() /*this function loads the GridView with the data*/

    {

        try

        {

            string connectionString = "Data Source=MDYNYCCMTDEV;Initial Catalog=pubs;User ID=rast;Password=rast05";

            string commandString = "select * from Employee1 order by EmployeeID asc";

            SqlConnection con = new SqlConnection(connectionString);

            SqlDataAdapter da = new SqlDataAdapter(commandString, con);

            con.Open();

            DataSet ds = new DataSet();

            da.Fill(ds); 

            GridView1.DataSource = ds;

            GridView1.DataBind();

            con.Close();

        }

        catch (Exception ex)

        {

            Response.Write(ex.Message);

        }

    }

 

    /*the following event creates a place holder in the GridView to enable insertion by adding a new row.When button btnAddrec is clicked,apart from data binding,the text of inherent 'Update' LinkButton is changed to 'Insert' */

 

    protected void btnaddrec_Click(object sender, EventArgs e)

    { 

        SqlConnection con = new SqlConnection("Data Source=MDYNYCCMTDEV;Initial Catalog=pubs;User ID=rast;Password=rast05");

        SqlDataAdapter da = new SqlDataAdapter("SELECT * from Employee1", con);

        DataTable dt = new DataTable();

        da.Fill(dt); 

        DataRow dr = dt.NewRow();

        dt.Rows.InsertAt(dr, 0); 

        GridView1.EditIndex = 0;

        GridView1.DataSource = dt;

        GridView1.DataBind(); 

        ((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text = "Insert";  /*this line tracks the GridView's 'Update' LinkButton and replaces its text to 'Insert'*/ 

    }

 

 

    /*the following event changes the GridView to editable mode so that it can enable Updation. By default edit index is -1*/ 

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

    {

        GridView1.EditIndex = e.NewEditIndex;

        BindData();

    }

 

    /*the following event handles Updation as well as Insertion based on the click status of button btnAddrec*/

 

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

    {

        /*if LinkButton text is 'Insert' the Insertion code is followed*/

 

        if (((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text == "Insert")

        {

            TextBox TextBox1;

            string EmployeeName, ContactNo;

            TextBox1 = (TextBox)GridView1.Rows[0].FindControl("txtEmployeeName");

            EmployeeName = TextBox1.Text;

            TextBox1 = (TextBox)GridView1.Rows[0].FindControl("txtContactNo");

            ContactNo = TextBox1.Text; 

            SqlConnection con = new SqlConnection("Data Source=MDYNYCCMTDEV;Initial Catalog=pubs;User ID=rast;Password=rast05"); 

            SqlCommand cmd = new SqlCommand("empins", con);

            cmd.CommandType = CommandType.StoredProcedure; 

            cmd.Parameters.Add("@EmployeeName", SqlDbType.VarChar, 50);

            cmd.Parameters.Add("@ContactNo", SqlDbType.VarChar, 50); 

            cmd.Parameters["@EmployeeName"].Value = EmployeeName;

            cmd.Parameters["@ContactNo"].Value = ContactNo; 

            con.Open(); 

            cmd.ExecuteNonQuery();

            con.Close();

            GridView1.EditIndex = -1; 

            BindData(); 

        }

 

 /*otherwise Updation code is followed*/

        else

        { 

            Label Label1;

            TextBox TextBox1; 

            string EmployeeID, EmployeeName, ContactNo;

            Label1 = (Label)GridView1.Rows[e.RowIndex].FindControl("lblEmployeeIDEdit");

            EmployeeID = Label1.Text;

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

            EmployeeName = TextBox1.Text;

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

            ContactNo = TextBox1.Text; 

            SqlConnection con = new SqlConnection("Data Source=MDYNYCCMTDEV;Initial Catalog=pubs;User ID=rast;Password=rast05"); 

            SqlCommand cmd = new SqlCommand("empupd", con);

            cmd.CommandType = CommandType.StoredProcedure; 

            cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);

            cmd.Parameters.Add("@EmployeeName", SqlDbType.VarChar, 50);

            cmd.Parameters.Add("@ContactNo", SqlDbType.VarChar, 50); 

            cmd.Parameters["@EmployeeID"].Value = EmployeeID;

            cmd.Parameters["@EmployeeName"].Value = EmployeeName;

            cmd.Parameters["@ContactNo"].Value = ContactNo; 

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close(); 

            GridView1.EditIndex = -1; 

            BindData();

        } 

    } 

 

    /*here is what will happen if Cancel button is clicked,GridView comes in Non Editable mode*/

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

    {

        GridView1.EditIndex = -1;

        BindData();

    }

 

    /*Finally follows the Deletion code */

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

    {

        Label Label1;

        string EmployeeID; 

        Label1 = (Label)GridView1.Rows[e.RowIndex].FindControl("lblEmployeeID"); 

        EmployeeID = Label1.Text; 

        SqlConnection con = new SqlConnection("Data Source=MDYNYCCMTDEV;Initial Catalog=pubs;User ID=rast;Password=rast05"); 

        SqlCommand cmd = new SqlCommand("empdel", con);

        cmd.CommandType = CommandType.StoredProcedure; 

        cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);

        cmd.Parameters["@EmployeeID"].Value = EmployeeID; 

        con.Open();

        cmd.ExecuteNonQuery();

        con.Close(); 

        GridView1.EditIndex = -1; 

        BindData();

    } 

}


Happy Coding.


Similar Articles