Monday, September 2, 2013

Home / Asp.Net / C#.Net / Three Tier Architecture / Edit, Update and Delete in Gridview using three tier architecture in Asp.net using SqlHelper

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

by on in , ,
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:
In the last I have explained How to insert Data intoDatabase using three tier architecture in Asp.net and How to bind data to Gridview in Three-Tier Architecture in asp.net using SqlHelper.
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.
You May Also Like:
By at
Tags: , ,

3 comments:

Subscribe to: Post Comments (Atom)