Delete Multiple Rows Records Gridview CheckBox Confirmation

2:24:00 am 0 Comments

Delete Multiple Rows Records Gridview CheckBox Confirmation

Several times while developing web applications we need to implement functionality to delete bulk / multiple rows or records in Gridview using checkbox to select rows / records to be deleted with confirmation dialog box.

In this example i am showing how to achieve it.



You can also read how to Edit or update multiple records/rows in gridview with checkbox using C#
I've put a Delete Button in Footer row of gridview and checkbox columns using ItemTemaplate.

Html markup of the gridview is like

<asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="False" 
              DataSourceID="SqlDataSource1" 
              ShowFooter="true">
     <Columns>
     <asp:TemplateField>
     <ItemTemplate>
     <asp:CheckBox ID="chkSelect" runat="server" />
     </ItemTemplate>
     <FooterTemplate>
     <asp:Button ID="btnDelete" runat="server" Text="Delete" 
              OnClick="btnDelete_Click" 
              OnClientClick="return DeleteConfirmation();"/>
      </FooterTemplate>
      </asp:TemplateField>
      <asp:BoundField DataField="ID" 
                      HeaderText="ID" 
                      SortExpression="ID" />
      <asp:BoundField DataField="Name" 
                      HeaderText="Name" 
                      SortExpression="Name" />
      <asp:BoundField DataField="Location" 
                      HeaderText="Location" 
                      SortExpression="Location" />
      </Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
  ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
     SelectCommand="SELECT [ID], [Name], [Location] 
                    FROM [Details]"></asp:SqlDataSource>

The JavaScript for delete confirmation of records is 
<script type="text/javascript" language="javascript">
function DeleteConfirmation()
{
if (confirm("Are you sure," 
            "you want to delete selected records ?")==true)
   return true;
else
   return false;
}
</script>

Call this javascript by assigning on OnClientClick attribute to delete button in html markup like this 
<FooterTemplate>
<asp:Button ID="btnDelete" runat="server" 
            Text="Delete" 
            OnClick="btnDelete_Click" 
            OnClientClick="return DeleteConfirmation();"/>
</FooterTemplate>

In the code behind i'm using StringCollection to store ID of records to be deleted by looping through gridview rows and checking which row is selected.

C# code behind 
public partial class _Default : System.Web.UI.Page 
{
//Define global Connection String
string strConnection = ConfigurationManager.ConnectionStrings
                       ["ConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnDelete_Click(object sender, EventArgs e)
{
//Create String Collection to store 
//IDs of records to be deleted 
  StringCollection idCollection = new StringCollection();
  string strID = string.Empty;

  //Loop through GridView rows to find checked rows 
   for (int i = 0; i < GridView1.Rows.Count; i++)
   {
    CheckBox chkDelete = (CheckBox)
       GridView1.Rows[i].Cells[0].FindControl("chkSelect");
            if (chkDelete != null)
            {
                if (chkDelete.Checked)
                {
                 strID = GridView1.Rows[i].Cells[1].Text;
                 idCollection.Add(strID);
                }
            }
        }

        //Call the method to Delete records 
        DeleteMultipleRecords(idCollection);

        // rebind the GridView
        GridView1.DataBind();
    }
    /// <summary>
    /// This is to Delete multiple records in gridview
    /// </summary>
    /// <param name="idCollection">stringCollection</param>
private void DeleteMultipleRecords
                            (StringCollection idCollection)
{
//Create sql Connection and Sql Command
 SqlConnection con = new SqlConnection(strConnection);
 SqlCommand cmd = new SqlCommand();
 string IDs = "";

foreach (string id in idCollection)
{
  IDs += id.ToString() + ",";
}
          try
            {
               string strIDs = 
                IDs.Substring(0, IDs.LastIndexOf(","));
                   string strSql = "Delete from Details 
                           WHERE ID in (" + strIDs + ")";
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSql;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                string errorMsg = "Error in Deletion";
                errorMsg += ex.Message;
                throw new Exception(errorMsg);
            }
            finally
            {
                con.Close();
            }
}
    
}

Another Method
We can also use sqlDataSource delete method to delete multiple records in following way
add delete command and delete Parameters in SqlDataSource html markup
Add DataKeyNames in GridView html source
<asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="False" 
              DataSourceID="SqlDataSource1" 
              ShowFooter="true" DataKeyNames="ID">
Now add Delete command 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
  ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
     SelectCommand="SELECT [ID], [Name], [Location] 
                    FROM [Details] 
         DeleteCommand="Delete from [Details] where [ID]=@ID">
<DeleteParameters>
<asp:Parameter Name="ID"/>
</DeleteParameters>">
</asp:SqlDataSource>
Now write this code in click event of Delete button in code behind 
foreach(GridViewRow row in GridView1. Rows)
{
CheckBox chkDelete = (CheckBox)row.FindControl
                                ("chkSelect");
if(chkDelete.Checked)
{
int id = Convert.ToInt32(GridView1.DataKeys
                       [row.RowIndex].Value);
SqlDataSource1.DeleteParameters["ID"]
         .DefaultValue = customerid.ToString();
SqlDataSource1.Delete();
}

}

VB.NET code
Public Partial Class _Default
    Inherits System.Web.UI.Page
    'Define global Connection String
    Private strConnection As String 
                   = ConfigurationManager.ConnectionStrings
        ("ConnectionString").ConnectionString
    Protected Sub Page_Load
(ByVal sender As Object, ByVal e As EventArgs)
        
    End Sub
    Protected Sub btnDelete_Click
(ByVal sender As Object, ByVal e As EventArgs)
        'Create String Collection to store 
        'IDs of records to be deleted 
        Dim idCollection As New StringCollection()
        Dim strID As String = String.Empty
        
        'Loop through GridView rows to find checked rows 
        For i As Integer = 0 To GridView1.Rows.Count - 1
            Dim chkDelete As CheckBox = 
DirectCast(GridView1.Rows(i).Cells(0).FindControl
              ("chkSelect"), CheckBox)
            If chkDelete IsNot Nothing Then
                If chkDelete.Checked Then
                    strID = GridView1.Rows(i).Cells(1).Text
                    idCollection.Add(strID)
                End If
            End If
        Next
        
        'Call the method to Delete records 
        DeleteMultipleRecords(idCollection)
        
        ' rebind the GridView
        GridView1.DataBind()
    End Sub
    ''' <summary>
    ''' This is to Delete multiple records in gridview
    ''' </summary>
    ''' <param name="idCollection">stringCollection</param>
    Private Sub DeleteMultipleRecords
(ByVal idCollection As StringCollection)
        'Create sql Connection and Sql Command
        Dim con As New SqlConnection(strConnection)
        Dim cmd As New SqlCommand()
        Dim IDs As String = ""
        
        'Create string builder to store 
        'delete commands separated by ;
        
        For Each id As String In idCollection
            IDs += id.ToString() & ","
        Next
        
        Try
            Dim strIDs As String = 
IDs.Substring(0, IDs.LastIndexOf(","))
            Dim strSql As String = 
("Delete from Details" & +" WHERE ID in (") + strIDs & ")"
            cmd.CommandType = CommandType.Text
            'cmd.CommandText = sbStrCommand.ToString();
            cmd.CommandText = strSql
            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As SqlException
            Dim errorMsg As String = "Error in Deletion"
            errorMsg += ex.Message
            Throw New Exception(errorMsg)
        Finally
            con.Close()
            
            
        End Try
    End Sub
    
End Class

Hope this helps

Unknown

0 comments:

Subscribe to: Post Comments (Atom)