Edit, Update and Delete in Gridview using three tier architecture in Asp.net using SqlHelper - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

Edit, Update and Delete in Gridview using three tier architecture in Asp.net using SqlHelper

Introduction: In this article I have explained how we can Bind, Edit, Update and Delete in Gridview using three tier architecture in Asp.net.
three tier architecture

Description:
I have  a Table USER_REGISTRATION:
ID
int
USERNAME
varchar(50)
PASSWORD
varchar(50)
FIRST_NAME
varchar(50)
LAST_NAME
varchar(50)
SEX
varchar(50)
EMAIL_ID
varchar(50)

Here ID is autoincrement and primary key.

Create a Store Procedure to Delete the Records:

CREATE PROCEDURE [dbo].[DELETE_USER]
(
@ID INT
)
AS
BEGIN
      SET NOCOUNT ON;
   DELETE FROM dbo.USER_REGISTRATION WHERE ID = @ID
END

Create a Store Procedure to Update the Records/Data:

CREATE PROCEDURE [dbo].[UPDATE_USER]
(
@ID INT,
@USERNAME varchar(50),
@FIRST_NAME varchar(50),
@LAST_NAME varchar(50),
@EMAIL_ID varchar(50),
@SEX VARCHAR(50)
)
AS
BEGIN
      SET NOCOUNT ON;

  UPDATE dbo.USER_REGISTRATION SET USERNAME= @USERNAME, FIRST_NAME=@FIRST_NAME, LAST_NAME=@LAST_NAME, EMAIL_ID=@EMAIL_ID, SEX=@SEX
   WHERE ID=@ID
END

Add a new Class  to clsuserRegistartion.cs to Bussiness Layer (DataEntities) of Project:

public class clsuserRegistartion
    {
       public int ID {get;set;}
       public string USERNAME { get; set; }
       public string PASSWORD { get; set; }
       public string FIRST_NAME { get; set; }
       public string LAST_NAME { get; set; }
       public string SEX { get; set; }
       public string EMAIL_ID { get; set; }
    }


To download the SqlHelper.cs CLICK HERE. After download the file put the class in Data Access Layer.
Note : Do not forget to add ConnectionString in web.config file:
<connectionStrings>
    <add name="Con" connectionString="Data Source=VIJAY-PC;Initial Catalog=TEST_APPLICATION;Integrated Security=True"/>
  </connectionStrings>

To call ConectionString in project I cretae class Connection.cs in Data Access layer and write the code as mention below:
using System.Configuration;

public class Connection
    {
       static string str;

       public string Con()
       {
           str = ConfigurationManager.ConnectionStrings["Con"].ToString();
           return str;
       }
    }

Bulid the project. Now add a new class clsRegistartionDal.cs to Data Access layer. Now add the project reference of DataEntites (Bussiness layer) to project , use the namespace of SqlHelper and write the below given code:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
using New.DataEn;

public class clsRegistartionDal
    {
      
       Connection clscon = new Connection();
       DataSet ds = new DataSet();
  //Delete User
       public DataSet Delete_User(int ID)
       {
           SqlParameter[] param = new SqlParameter[1];
           try
           {
               param[0] = new SqlParameter("@ID", ID);
               ds = SqlHelper.ExecuteDataset(clscon.Con(), "DELETE_USER", param);
           }
           catch (Exception ex)
           {
           }
           return ds;
       }
       //Update User
       public DataSet Update_User(string username, string first, string last, string email, string sex, int id)
       {
           SqlParameter[] param = new SqlParameter[6];
           try
           {
               param[0] = new SqlParameter("@ID", id);
               param [1] = new SqlParameter("@USERNAME", username);
               param[2] = new SqlParameter("@FIRST_NAME", first);
               param[3] = new SqlParameter("@LAST_NAME", last);              
               param[4] = new SqlParameter("@EMAIL_ID", email);
               param[5] = new SqlParameter("@SEX", sex);
               ds = SqlHelper.ExecuteDataset(clscon.Con(), "UPDATE_USER", param);

           }
           catch(Exception ex)
           {
           }
           return ds;
       }

    }
}

Here New.DataEn, New is the name of project and DataEn is Bussiness layer (DataEntities).

Add the reference of Data Access Layer and Bussiness Access Layer (DataEntities). After add a new webform to project. Drag and drop the Gridview Data Control from Toolbox and desgin page as shown below:

  <table align="center"><tr><td>
           <asp:GridView ID="grduser" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
               onpageindexchanging="grduser_PageIndexChanging"
               onrowdeleting="grduser_RowDeleting" onrowediting="grduser_RowEditing"
               onrowupdating="grduser_RowUpdating"  ShowHeaderWhenEmpty="True"
               EmptyDataText="No records Found to Display!"
               onrowcancelingedit="grduser_RowCancelingEdit" CssClass="Hover" RowStyle-CssClass="Hover" >
            <Columns>
                <asp:TemplateField HeaderText="USERNAME">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtusername" runat="server" Text='<%# Eval("USERNAME") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblname" runat="server" Text='<%# Eval("USERNAME") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="FIRST NAME">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtfirst" runat="server" Text='<%# Eval("FIRST_NAME") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblfirst" runat="server" Text='<%# Eval("FIRST_NAME") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="LAST NAME">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtlast" runat="server" Text='<%# Eval("LAST_NAME") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lbllast" runat="server" Text='<%# Eval("LAST_NAME") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                 <asp:TemplateField HeaderText="SEX">
                     <EditItemTemplate>
                        <asp:TextBox ID="txtsex" runat="server" Text='<%# Eval("SEX") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblsex" runat="server" Text='<%# Eval("SEX") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                   <asp:TemplateField HeaderText="Email">
                       <EditItemTemplate>
                           <asp:TextBox ID="txtemail" runat="server" Text='<%# Eval("EMAIL_ID") %>'></asp:TextBox>
                       </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblemail" runat="server" Text='<%# Eval("EMAIL_ID") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:CommandField ShowEditButton="True" />
                <asp:CommandField ShowDeleteButton="True" />
            </Columns>
        </asp:GridView></td></tr></table>

Put the style in Head Tag:

<style type="text/css">
        #grduser tr.Hover:hover
    {
                background-color:#2b98d9 ;  
    }
    </style>

Now on Gridview Events write the below given code (.aspx.cs):

using New.DAL;
using New.DataEn;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

clsRegistartionDal clsdal = new clsRegistartionDal();
        clsuserRegistartion clsdata = new clsuserRegistartion();
        DataSet ds = new DataSet();
protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }
        }
//Bind Gridview
private void BindGrid()
        {
            ds = clsdal.Display_Records();
            if (ds.Tables[0].Rows.Count > 0)
            {
                grduser.DataSource = ds;
                grduser.DataBind();
            }
            else
            {
                ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
                grduser.DataSource = ds;
                grduser.DataBind();
                int columnCount = grduser.Rows[0].Cells.Count;
                grduser.Rows[0].Cells.Clear();
                grduser.Rows[0].Cells.Add(new TableCell());
                grduser.Rows[0].Cells[0].ColumnSpan = columnCount;
                grduser.Rows[0].Cells[0].Text = "<font color=Red><b><center>No Data Found !</center></b></font>";
            }
        }

        protected void grduser_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            grduser.PageIndex = e.NewPageIndex;
            BindGrid();
        }

        protected void grduser_RowEditing(object sender, GridViewEditEventArgs e)
        {
            grduser.EditIndex = e.NewEditIndex;
            BindGrid();
        }
        //Delete
        protected void grduser_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            try
            {
                int ID = Convert.ToInt32(grduser.DataKeys[e.RowIndex].Value.ToString());
                ds = clsdal.Delete_User(ID);
                BindGrid();
                Messagebox("Delete Successfully");
            }
            catch (Exception ex)
            {
            }
        }       
            //Update
        protected void grduser_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            try
            {
                int ID = Convert.ToInt32(grduser.DataKeys[e.RowIndex].Value.ToString());
                TextBox txtname = (TextBox)(grduser.Rows[e.RowIndex].FindControl("txtusername"));
                TextBox txtfirst = (TextBox)(grduser.Rows[e.RowIndex].FindControl("txtfirst"));
                TextBox txtlast = (TextBox)(grduser.Rows[e.RowIndex].FindControl("txtlast"));
                TextBox txtemail = (TextBox)(grduser.Rows[e.RowIndex].FindControl("txtemail"));
               TextBox txtsex = (TextBox)(grduser.Rows[e.RowIndex].FindControl("txtsex"));

                string username = ((TextBox)(grduser.Rows[e.RowIndex].FindControl("txtusername"))).Text;
                string first = ((TextBox)(grduser.Rows[e.RowIndex].FindControl("txtfirst"))).Text;
                string last = ((TextBox)(grduser.Rows[e.RowIndex].FindControl("txtlast"))).Text;
                string email = ((TextBox)(grduser.Rows[e.RowIndex].FindControl("txtemail"))).Text;
                string sex = ((TextBox)(grduser.Rows[e.RowIndex].FindControl("txtsex"))).Text;
                 ds = clsdal.Update_User(username, first, last, email,sex, ID);
                 Messagebox("Update User Successfully");
                 grduser.EditIndex = -1;
                 BindGrid();
            }
            catch (Exception ex)
            {
            }
        }
        //To message
        private void Messagebox(string Message)
        {
            Label lblMessageBox = new Label();

            lblMessageBox.Text =
                "<script language='javascript'>" + Environment.NewLine +
                "window.alert('" + Message + "')</script>";

            Page.Controls.Add(lblMessageBox);

        }
        //Cancel the Event
        protected void grduser_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            grduser.EditIndex = -1;
            BindGrid();
        }


Now build the project, run and check the result.

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+

3 comments


EmoticonEmoticon