Gridview Delete with Confirm Dialog

Gridview Delete Operation

For Complete gridview design please follow earlier code about gridview edit operation here. 

Only we add following in gridview design

AutoGenerateDeleteButton="True" OnRowDeleting="gridData_RowDeleting"

 

 Stored Procedure for  Delete Operation

 CREATE PROCEDURE SP_Delete_User

-- Add the parameters for the stored procedure here

@userID int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

--1. REMOVE RECORDS FROM DEPENDANT TABLE

DELETE FROM [dbo].[User_In_Org_Role]

WHERE User_ID = @userID

--2. REMOVE RECORD FROM ACTUAL TABLE

DELETE FROM [dbo].[User]

WHERE User_ID=@userID

 END

GO

  

Note: In stored procedure above, we perform delete operation on [User_In_Org_Role] table first because it has foreign key reference to table [User].

Here is code for delete operation :

1. Confirm delete operation

protected void gridData_RowDataBound(object sender, GridViewRowEventArgs e)

{ 

if (e.Row.RowType == DataControlRowType.DataRow)

 {

Try

{

              LinkButton lbDelete = (LinkButton)e.Row.Cells[0].Controls[2];

if (lbDelete.Text.Trim().Equals("Delete"))

{

lbDelete.OnClientClick = "return confirm('confirm delete action?');";

 }

}

catch (Exception)

{ 

// throw

       }

}

}

2. Delete Event

protected void gridData_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

 

if (e.RowIndex != -1 && gridData.Rows[e.RowIndex].RowType == DataControlRowType.DataRow)

            {

              try

              { 

//1. get PKey field value

Label lblDataUser_ID = (Label)gridData.Rows[e.RowIndex].FindControl("lblDataUser_ID");

int User_ID = Convert.ToInt32(lblDataUser_ID.Text);

//method to delete

clsConnect cc = new clsConnect();

cc.DeleteData(User_ID);

}

              catch (Exception)

              {}

}

gridData.EditIndex = -1;

GetDt();

} 

Note : For GetDt() details follow details in Gridview Edit Post

 

3. Method to execute Stored Procedure

public void DeleteData(int UserID)

{ 

//SP_Delete_User

//@userID int

SqlConnection con = new SqlConnection(connectionString);

con.Open();

try

{

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

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter spuserID = new SqlParameter();

spuserID.ParameterName = "@userID";

spuserID.DbType = DbType.Int32;

       spuserID.Value = UserID;

cmd.Parameters.Add(spuserID);

cmd.ExecuteNonQuery();

}

catch (Exception)

{

}

con.Close();

}

 

 

 

 

 

 

Gridview Operations - Display, Edit, Update

Stored Procedure to perform Update operation

CREATE PROCEDURE SP_Update_User

       -- Add the parameters for the stored procedure here

@User_ID int,@Fname varchar(200),@Lname varchar(200),@Email varchar(500),@ActiveDate datetime,@gender varchar(50),@city varchar(200)

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

 

    -- Insert statements for procedure here

      

       update [dbo].[User]

set First_Name=@Fname, Last_Name=@Lname, Email=@Email, Active_Date=@ActiveDate,Gender=@gender,City=@city

       where User_ID=@User_ID

 

 

END

GO

 

 

 

Gridview design for update operations

<asp:GridView ID="gridData" runat="server" AutoGenerateColumns="False" Width="100%" CellPadding="4"

             ForeColor="#333333" GridLines="None" AutoGenerateEditButton="True"

             OnRowCancelingEdit="gridData_RowCancelingEdit"

            OnRowEditing="gridData_RowEditing"

            OnRowUpdating="gridData_RowUpdating"

            OnRowDataBound="gridData_RowDataBound" >

            <AlternatingRowStyle BackColor="White" />

            <Columns>

                 <asp:TemplateField >

                     <HeaderTemplate>

                         <asp:Label ID="lblUser_ID" runat="server" Text="User ID"

                         ></asp:Label>

                     </HeaderTemplate>

                   <ItemTemplate>

                       <asp:Label ID="lblDataUser_ID" runat="server"

                       Text='<%#Eval("User_ID") %>' ></asp:Label>

                   </ItemTemplate>

                   

                </asp:TemplateField>

                 <asp:TemplateField >

                      <HeaderTemplate>

                         <asp:Label ID="lblFirst_Name" runat="server" Text="First Name"

                         ></asp:Label>

                     </HeaderTemplate>

                   <ItemTemplate>

                       <asp:Label ID="lblDataFirst_Name" runat="server"

                       Text='<%#Eval("First_Name") %>' ></asp:Label>

                   </ItemTemplate>

                     <EditItemTemplate>

                        <asp:TextBox ID="txtDataFirst_Name" runat="server"

                        Text='<%#Eval("First_Name") %>'></asp:TextBox>

                    </EditItemTemplate>

                </asp:TemplateField>

                 <asp:TemplateField >

                      <HeaderTemplate>

                         <asp:Label ID="lblLast_Name" runat="server" Text="Last Name"

                         ></asp:Label>

                     </HeaderTemplate>

                   <ItemTemplate>

                       <asp:Label ID="lblDataLast_Name" runat="server"

                       Text='<%#Eval("Last_Name") %>'  ></asp:Label>

                   </ItemTemplate>

                     <EditItemTemplate>

                        <asp:TextBox ID="txtDataLast_Name" runat="server"

                        Text='<%#Eval("Last_Name") %>'></asp:TextBox>

                    </EditItemTemplate>

                </asp:TemplateField>

                 <asp:TemplateField >

                      <HeaderTemplate>

                         <asp:Label ID="lblEmail" runat="server" Text="Email"

                         ></asp:Label>

                     </HeaderTemplate>

                   <ItemTemplate>

                       <asp:Label ID="lblDataEmail" runat="server" Text='<%#Eval("Email")

                        %>'    ></asp:Label>

                   </ItemTemplate>

                     <EditItemTemplate>

                        <asp:TextBox ID="txtDataEmail" runat="server"

    Text='<%#Eval("Email") %>'></asp:TextBox>

                    </EditItemTemplate>

 

                </asp:TemplateField>

                 <asp:TemplateField >

 

                      <HeaderTemplate>

                         <asp:Label ID="lblActive_Date" runat="server" Text="Active Date"

                         ></asp:Label>

                     </HeaderTemplate>

                   <ItemTemplate>

                       <asp:Label ID="lblDataActive_Date" runat="server" Text=

  '<%#Eval("Active_Date") %>'  ></asp:Label>

                   </ItemTemplate>

                     <EditItemTemplate>

                        <asp:TextBox ID="txtDataActive_Date" runat="server"

                        Text='<%#Eval("Active_Date") %>'></asp:TextBox>

                         <asp:CalendarExtender ID="CalendarExtender1" runat="server"

                             TargetControlID="txtDataActive_Date"

                             SelectedDate='<%#Eval("Active_Date") %>'>

                         </asp:CalendarExtender>

                    </EditItemTemplate>

                </asp:TemplateField>

                 <asp:TemplateField >

                      <HeaderTemplate>

                         <asp:Label ID="lblGender" runat="server" Text="Gender"

                         ></asp:Label>

                     </HeaderTemplate>

                   <ItemTemplate>

                          <asp:Label ID="lblDataGender" runat="server"

                          Text='<%#Eval("Gender") %>' ></asp:Label>

                   </ItemTemplate>

                     <EditItemTemplate>

                          <asp:DropDownList ID="ddlGender" runat="server" >

                          </asp:DropDownList>

                    </EditItemTemplate>

                </asp:TemplateField>

                 <asp:TemplateField >

                       <HeaderTemplate>

                         <asp:Label ID="lblCity" runat="server" Text="City" ></asp:Label>

                     </HeaderTemplate>

                   <ItemTemplate>

                         <asp:Label ID="lblDataCity" runat="server" Text='<%#Eval("City")

     %>'  ></asp:Label>

                   </ItemTemplate>

                     <EditItemTemplate>

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

                    </EditItemTemplate>

                </asp:TemplateField>

                </Columns>

 

            <EditRowStyle BackColor="#2461BF" />

            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

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

            <RowStyle BackColor="#EFF3FB" />

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

            <SortedAscendingCellStyle BackColor="#F5F7FB" />

            <SortedAscendingHeaderStyle BackColor="#6D95E1" />

            <SortedDescendingCellStyle BackColor="#E9EBEF" />

            <SortedDescendingHeaderStyle BackColor="#4870BE" />

 

        </asp:GridView>

 

 

Code Behind

1.       Show data on button click event

   protected void btnGetData_Click(object sender, EventArgs e)

    {

        GetDt();

    }

       We have to bind gridview several times, so create a method for binding data in gridview i.e. GetDt()

               

 

private void GetDt()

       {

 

        clsConnect connect = new clsConnect();

        DataSet ds = connect.getUserData();

        gridData.DataSource = ds.Tables[0];

        gridData.DataBind();

  

 }

 

Details of Class Method getUserData()

 

public DataSet getUserData()

    {

        ds = new DataSet();

        try

        {

           

            SqlConnection con=new SqlConnection(connectionString);

            con.Open();

            SqlDataAdapter da = new SqlDataAdapter("select * from [dbo].[User]", con);

            da.Fill(ds);

            con.Close();

 

 

        }

        catch (Exception ex)

        {

           

            //throw;

        }

        return ds;

   

    }

For Connection details visit earlier posts

 

2.       Gridview Editing event

We have maintain earlier Gender field value to be show in update method as previous value.

Hence Session["genderVal"] in code below

protected void gridData_RowEditing(object sender, GridViewEditEventArgs e)

    {

        gridData.EditIndex = e.NewEditIndex;

        //gender

        Label thisGender = (Label)gridData.Rows[e.NewEditIndex].FindControl("lblDataGender");

        string genderVal = thisGender.Text;

        Session["genderVal"] = genderVal;

        GetDt();

    }

 

3.       RowDataBound event: This event is used to bind fields for controls available in update mode of gridview.

protected void gridData_RowDataBound(object sender, GridViewRowEventArgs e)

    {

            if (gridData.EditIndex == e.Row.RowIndex && e.Row.RowType ==  

            DataControlRowType.DataRow)

        {

            try

            {

                DropDownList ddlGender = (DropDownList)e.Row.FindControl("ddlGender");

                if (ddlGender != null)

                {

                    ddlGender.Items.Add("M");

                    ddlGender.Items.Add("F");

 

                }

                //set selected field

                if (Session["genderVal"] != null)

                {

                    for (int i = 0; i < ddlGender.Items.Count; i++)

                    {

                        if (ddlGender.Items[i].Text.Trim().Equals(Session["genderVal"].ToString()))

                        {

                            ddlGender.Items[i].Selected = true;

                            break;

                       

                        }

                    }

                }

            }

            catch (Exception ex)

            {

               

                //throw;

            }

        }

    }

4.       Update Event

  protected void gridData_RowUpdating(object sender, GridViewUpdateEventArgs e)

    {

        if (e.RowIndex!=-1 && gridData.Rows[e.RowIndex].RowType==DataControlRowType.DataRow )

        {

           

            try

            {

                //1. get PKey field value

                Label lblDataUser_ID = (Label)gridData.Rows[e.RowIndex].FindControl("lblDataUser_ID");

                int User_ID = Convert.ToInt32(lblDataUser_ID.Text);

 

                //2. get new values for First_Name Last_Name Email Active_Date Gender City

                TextBox txtDataFirst_Name = (TextBox)gridData.Rows[e.RowIndex].FindControl("txtDataFirst_Name");

                TextBox txtDataLast_Name = (TextBox)gridData.Rows[e.RowIndex].FindControl("txtDataLast_Name");

                TextBox txtDataEmail = (TextBox)gridData.Rows[e.RowIndex].FindControl("txtDataEmail");

                TextBox txtDataActive_Date = (TextBox)gridData.Rows[e.RowIndex].FindControl("txtDataActive_Date");

                DropDownList ddlGender = (DropDownList)gridData.Rows[e.RowIndex].FindControl("ddlGender");

                TextBox txtDataCity = (TextBox)gridData.Rows[e.RowIndex].FindControl("txtDataCity");

 

                //update Method

                clsConnect cc = new clsConnect();

              cc.UpdateData(User_ID, txtDataFirst_Name.Text, txtDataLast_Name.Text, txtDataEmail.Text,

                    Convert.ToDateTime(txtDataActive_Date.Text), ddlGender.SelectedItem.Text, txtDataCity.Text);

            }

            catch (Exception)

            {

               

                //throw;

            }

        }   

        gridData.EditIndex = -1;

        GetDt();

    }

5.       Update Method

public void UpdateData(int User_ID,string First_Name,string Last_Name,string Email,DateTime Active_Date,string Gender,string City)

    {

        ds = new DataSet();

        try

        {

            //SP_Update_User has following parameters

 //@User_ID int,@Fname varchar(200),@Lname varchar(200),@Email   

 // varchar(500),

            //@ActiveDate datetime,@gender varchar(50),@city varchar(200)

 

            SqlConnection con = new SqlConnection(connectionString);

            con.Open();

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

            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

 

            SqlParameter UserID = new SqlParameter();

            UserID.ParameterName = "@User_ID";

            UserID.DbType = DbType.Int32;

            UserID.Value = User_ID;

            cmd.Parameters.Add(UserID);

 

            SqlParameter Fname = new SqlParameter();

            Fname.ParameterName = "@Fname";

            Fname.DbType = DbType.String;

            Fname.Value = First_Name;

            cmd.Parameters.Add(Fname);

 

            SqlParameter Lname = new SqlParameter();

            Lname.ParameterName = "@Lname";

            Lname.DbType = DbType.String;

            Lname.Value = Last_Name;

            cmd.Parameters.Add(Lname);

 

            SqlParameter spEmail = new SqlParameter();

            spEmail.ParameterName = "@Email";

            spEmail.DbType = DbType.String;

            spEmail.Value = Email;

            cmd.Parameters.Add(spEmail);

 

            SqlParameter ActiveDate = new SqlParameter();

            ActiveDate.ParameterName = "@ActiveDate";

            ActiveDate.DbType = DbType.DateTime;

            ActiveDate.Value = Active_Date;

            cmd.Parameters.Add(ActiveDate);

 

            SqlParameter gender = new SqlParameter();

            gender.ParameterName = "@gender";

            gender.DbType = DbType.String;

            gender.Value = Gender;

            cmd.Parameters.Add(gender);

 

            SqlParameter city = new SqlParameter();

            city.ParameterName = "@city";

            city.DbType = DbType.String;

            city.Value = City;

            cmd.Parameters.Add(city);

 

            cmd.ExecuteNonQuery();

            con.Close();

                     

 

 

 

        }

        catch (Exception ex)

        {

           

           // throw;

           // return ds;

        }

 

    }

6. Cancel Edit Event

protected void gridData_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

    {

        gridData.EditIndex = -1;

     

        GetDt();

    }