Selecting, Confirming & Deleting Multiple Checkbox Items In A DataGrid - Part 2: Maintaining CheckBox State Across Pages

sqljunkies.com

Home

Search:

By Dimitrios Markatos
Published: 8/3/2005
Reader Level: Beginner Intermediate
Rated: 4.38 by 8 member(s).

Tell a Friend
Rate this Article
Printable Version
Discuss in the Forums

Introduction

In my previous article Selecting, Confirming & Deleting Multiple Checkbox Items In A DataGrid (i.e. HotMail & Yahoo) that I had written about two years ago, I demonstrated how one could multi-select checkboxes in a DataGrid page, and then cumulatively deleted them all. Initially, I did not make use of any paging with that example, but rather left it rudimentary simply to illustrate how to multi-select a series of checkboxes and then delete them all in one pass. But the limitation there was that this worked on a per-page basis, so once you selected any given checkboxes in that page, then paged to the next, and finally returned to the previous page, all your selections were reset.

Most of the web sites that offer this type of functionality, including AOL Web Mail, display the same abovementioned effect. You're allow only to select and delete whatever checkboxes are presented on that page alone, before proceeding to the next. Having said that, we’ll take care of this in this article, and not only be able to select and delete across pages in one nice batch delete, but also, and here is the cool part, in this article I’ll show you how to repopulate the given page with the precise checkboxes that were selected earlier - even if you go back and check something new in that page.

To make this all come about I utilize Session State to store my checkboxed values that comprise of the DataGrid's DataKeyField and, use its DataKeys properties to sift through and grab the selected checkbox values and repopulate the appropriate checkboxes in the given page. I have also implemented caching using Session State for added performance and scalability, alongside bi-directional column sorting. Therefore, by means of the DataGrid's DataKeys property, your checkbox selections will persist across pages even sorting in any direction as well. Pretty awesome! And furthermore, the use of Session State helps prevent multiple users from getting data crisscrossed.

Typically, there are other methodologies for accomplishing the same end result. One way I've come across was by means of wiring up each CheckBox with an event handler using OnCheckedChanged. Thereby, each time a given checkbox is selected the page get's posted back and stores the appropriate values in any given state capturing method. However, although this technique works, it leaves much to be desired it terms of smoothness and presentation. My method performs all actions on a page by page level, and only when paging occurs is when it'll do what's it supposed to do. In turn, you have a smooth application with non of the constant post back effect for each checkbox selection.

Moving forward, for brevity I won’t reiterate the techniques discussed in Part 1 of this article, but rather I’ll begin by listing the code in its entirety (both the main page and its code-behind), then discuss the logic and code responsible for the effect. Incidentally, as the original code was written in C#, most readers typically requested from me the VB version, so in light of such, the code presented here will all be in VB.

Main Page Code

<%@ Page Language="VB" Strict="True" Explicit="True" Buffer="True" Debug="False" Trace="False" Inherits="MultiDeleteDG.WebForm" Src="mDatagrid.aspx.vb" EnableSessionState="True" %>
<html>
<head>
</head>
<body>

<form runat="server">

<h3>Selecting, Confirming & Deleting Multiple Checkbox Items In A DataGrid (i.e.
HotMail & Yahoo) -<br>Part 2: Maintaining CheckBox State Across Pages with Sorting
</h3>
<br />
Current Page: <%=MyDataGrid.CurrentPageIndex +1%>
<br />

<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="white"
BorderColor="black"
CellPadding="3"
CellSpacing="0"
Font-Size="9pt"
AutoGenerateColumns="False"
HeaderStyle-BackColor="darkred"
HeaderStyle-ForeColor="white"
AllowPaging="True"
AllowCustomPaging="False"
AllowSorting="True"
OnPageIndexChanged="MyDataGrid_Page"
OnSortCommand="MyDataGrid_Sort"
PageSize="10"
PagerStyle-Mode="NumericPages"
PagerStyle-HorizontalAlign="Right"
DataKeyField="ID">

<Columns>

<asp:TemplateColumn>

<HeaderTemplate>
<asp:CheckBox ID="CheckAll" OnClick="javascript: return select_deselectAll (this.checked, this.id);" runat="server" />
<font face="Webdings" color="white" size="4">a</font>
</HeaderTemplate>

<ItemTemplate>
<asp:CheckBox ID="DeleteThis" OnClick="javascript: return select_deselectAll (this.checked, this.id);" runat="server" />
</ItemTemplate>

</asp:TemplateColumn>

<asp:BoundColumn HeaderText="StoreID" SortExpression="ID asc" Datafield="ID" runat="server" />
<asp:BoundColumn HeaderText="Store" SortExpression="Company asc" Datafield="Company" runat="server" />
<asp:BoundColumn HeaderText="Address" SortExpression="Address asc" Datafield="Address" runat="server" />
<asp:BoundColumn HeaderText="City" SortExpression="City asc" Datafield="City" runat="server" />
<asp:BoundColumn HeaderText="State" SortExpression="State asc" Datafield="State" runat="server" />
<asp:BoundColumn HeaderText="Zip" SortExpression="Zip asc" Datafield="Zip" runat="server" />

</Columns>

</ASP:DataGrid>

<br />
<asp:Button id="Confirm" onclick="DeleteAllIds" runat="server" Text="Delete Items" />
<asp:Button id="ClearAll" onclick="ClearDataGrid" runat="server" Text="Clear All" />
<span id="OutputMsg" runat="server" EnableViewState="false" />

</form>

</body>
</html>

The Code-Behind

Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SQLClient
Imports System.Text
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports Microsoft.VisualBasic

Namespace MultiDeleteDG

Public Class WebForm
Inherits System.Web.UI.Page

Protected MyDataGrid As System.Web.UI.WebControls.DataGrid
Protected CheckBox As System.Web.UI.WebControls.CheckBox
Protected OutputMsg As System.Web.UI.HtmlControls.HtmlGenericControl
Protected objConnect As SqlConnection
Protected myDataAdapter As SqlDataAdapter
Protected myCommand As SqlCommand
Protected DS as Dataset
Protected dgItem As DataGridItem

Public deletedIds As String = ""
Public ChkdItems As String = ""
Public SortField As String = ""
Public ChkBxIndex As String = ""
Public BxChkd As Boolean = False
Public CheckedItems As ArrayList
Public Results() As String

Sub Page_PreRender (ByVal Sender As Object, ByVal E As EventArgs)

Dim nl As String = Environment.NewLine

Dim jsScript As New StringBuilder ()

With jsScript

.Append ("<script language=JavaScript>" & nl)
.Append (" " & nl & nl)
.Append ("</scr" & "ipt>")

End With

RegisterClientScriptBlock ("clientScript", jsScript.ToString())

jsScript = Nothing

Dim button As WebControl = CType(Page.FindControl("Confirm"), WebControl)
button.Attributes.Add("onclick", "return confirmDelete (this.form);")

End Sub

Sub Page_Load (ByVal Sender As Object, ByVal E As EventArgs)

objConnect = New SqlConnection("server=(local);database=Northwind;uid=sa;pwd=;")

If Not IsPostBack Then

Session.Clear()

'Set up default column sorting
If IsNothing(Session ("SortOrder")) Then

BindData ("ID asc")

Else

BindData (Session ("SortOrder"))

End If

End If

End Sub

Sub MyDataGrid_Page (sender As Object, e As DataGridPageChangedEventArgs)

'Get CheckBoxValues before paging occurs
GetCheckBoxValues()

MyDataGrid.CurrentPageIndex = e.NewPageIndex

BindData(Session ("SortOrder"))

'Populate current DataGrid page with the current page items from Session after databind
RePopulateCheckBoxes ()

End Sub

Sub GetCheckBoxValues() 'As paging occurs store checkbox values

CheckedItems = New ArrayList

'Loop through DataGrid Items
For Each dgItem In MyDataGrid.Items

'Retrieve key value of each record based on DataGrids DataKeyField property
ChkBxIndex = MyDataGrid.DataKeys(dgItem.ItemIndex)

CheckBox = dgItem.FindControl("DeleteThis")

'Add ArrayList to Session if it doesnt exist
If Not IsNothing(Session ("CheckedItems")) Then

CheckedItems = Session ("CheckedItems")

End If

If CheckBox.Checked Then

BxChkd = True

'Add to Session if it doesnt already exist
If Not CheckedItems.Contains(ChkBxIndex) Then

CheckedItems.Add(ChkBxIndex.ToString())

End If

Else

'Remove value from Session when unchecked
CheckedItems.Remove(ChkBxIndex.ToString())

End If

Next

'Update Session with the list of checked items
Session ("CheckedItems") = CheckedItems

End Sub

Sub BindData (SortField As String)

'Setup Session Cache for different users
Dim Source As DataView = Session ("dgCache")

If (IsNothing (Source)) Then

Dim sqlQuery As String = "Select OrderId As Id, ShipName As Company, ShipAddress As Address, ShipCity As City, ShipCountry As State, ShipPostalCode As Zip from Orders"

myDataAdapter = New SqlDataAdapter(sqlQuery, objConnect)
DS = New Dataset()

myDataAdapter.Fill(DS, "MyDataGrid")

'Assign sort expression to Session
Session ("SortOrder") = SortField

'Setup DataView for Sorting
Source = DS.Tables(0).DefaultView

'Insert DataView into Session
Session ("dgCache") = Source

End If

Source.Sort = SortField

MyDataGrid.DataSource = Source

MyDataGrid.DataBind ()

'Close connection
objConnect.Close

End Sub

Sub RePopulateCheckBoxes ()

CheckedItems = New ArrayList

CheckedItems = Session ("CheckedItems")

If Not IsNothing(CheckedItems) Then

'Loop through DataGrid Items
For Each dgItem in MyDataGrid.Items

ChkBxIndex = MyDataGrid.DataKeys(dgItem.ItemIndex)

'Repopulate DataGrid with items found in Session
If CheckedItems.Contains(ChkBxIndex) Then

CheckBox = CType(dgItem.FindControl("DeleteThis"), CheckBox)
CheckBox.Checked = True

End If

Next

End If

'Copy ArrayList to a new array
Results = CheckedItems.ToArray(GetType(String))

'Concatenate ArrayList with comma to properly send for deletion
deletedIds = String.Join(",", Results)

End Sub

Sub DeleteAllIds (ByVal sender As Object, ByVal e As EventArgs)

'Regrab values in case the deletion occurs on the given page and any checkboxes were unchecked on the current page without any postback to correct the values in Session
GetCheckBoxValues ()

If BxChkd = True Then

RePopulateCheckBoxes ()

'Delete the rows of data containing the checkbox values
Dim deleteSQL As String = "DELETE from Orders WHERE OrderId IN (" + deletedIds + ");"

myCommand = New SqlCommand (deleteSQL, objConnect)

With myCommand

.Connection.Open()
.ExecuteNonQuery()

End With

'Close connection
objConnect.Close()

OutputMsg.InnerHtml += "<font size=4><b>Store information has been deleted.</b></font>"
OutputMsg.Style("color") = "green"

'Clear all Session values
Session.Clear()

'Reset DataGrid to top
MyDataGrid.CurrentPageIndex = 0

BindData (Session ("SortOrder"))

End If

End Sub

Function SortOrder (Field As String) As String

Dim so As String = Session ("SortOrder")

If Field = so Then

SortOrder = Replace (Field,"asc","desc")

ElseIf Field <> so Then

SortOrder = Replace (Field,"desc","asc")

Else

SortOrder = Replace (Field,"asc","desc")

End If

'Maintain persistent sort order
Session ("SortOrder") = SortOrder

End Function

Sub MyDataGrid_Sort (Sender As Object, E As DataGridSortCommandEventArgs)

'To retain checkbox on sorting
GetCheckBoxValues ()

MyDataGrid.CurrentPageIndex = 0 'To sort from top

BindData (SortOrder (E.SortExpression).ToString()) 'Rebind our DataGrid

'To retain checkbox on sorting
RePopulateCheckBoxes ()

End Sub

Sub ClearDataGrid (ByVal sender As Object, ByVal e As EventArgs)

'Clear All Session Values
Session.Clear()

'Reset DataGrid to top
MyDataGrid.CurrentPageIndex = 0

BindData ("ID asc") 'Rebind our DataGrid

End Sub

End Class

End Namespace

Our Main Page

Our main page has pretty much remained the same, the only thing you should notice is simply because we need more data to page through to make this thing work, I have changed the database we're using, field names, SQL query and connection string used in the code from the Pubs database to Northwind. So that being said, let’s now get to the next section where there obviously have been more apparent changes and tweaks, and we’ll now explore these.

Storing and Maintaining DataGrid CheckBox Values

Now, the logic behind maintaining current pages values and recalling them in the current page all takes place for the most part in the DataGrid Paging function, where in between paging and databinding two things occur. One, is to find out which checkboxes were selected prior the DataGrid being paged - this being done by the GetCheckBoxValues() method listed below. And the other, is to store them by means of Session State.

The code presented below is what parses the DataGrid's checkboxes and determines which ones are selected, if any, using the FindControl method. It then holds the selected values via the DataGrid's DataKeyField property for each checkbox selected and adds them all into an ArrayList, which in turn is added to Session State.

Sub GetCheckBoxValues() 'As paging occurs store checkbox values

CheckedItems = New ArrayList

'Loop through DataGrid Items
For Each dgItem In MyDataGrid.Items

'Retrieve key value of each record based on DataGrids DataKeyField property
ChkBxIndex = MyDataGrid.DataKeys(dgItem.ItemIndex)

CheckBox = dgItem.FindControl("DeleteThis")

'Add ArrayList to Session if it doesnt exist
If Not IsNothing(Session ("CheckedItems")) Then

CheckedItems = Session ("CheckedItems")

End If

If CheckBox.Checked Then

BxChkd = True

'Add to Session if it doesnt already exist
If Not CheckedItems.Contains(ChkBxIndex) Then

CheckedItems.Add(ChkBxIndex.ToString())

End If

Else

'Remove value from Session when unchecked
CheckedItems.Remove(ChkBxIndex.ToString())

End If

Next

'Update Session with the list of checked items
Session ("CheckedItems") = CheckedItems

End Sub

Now that we've discussed the methodology for storing the selected boxes. What about paging back and forth and checking boxes arbitrarily and sorting, won't this whack the order and such out of place? Nope. For instance, if you select any two checkboxes on the first page, then page to the next, the second you page back your page is repopulated with your previously selected values. So if you remove them all, then page again, the aforementioned method deletes the values in the Session State, so when you page back, they're gone.

But say you didn't uncheck any, but rather checked off a couple more, then paged again. Well, same the logic applies here as well, we easily determine upon paging, one, that you already have existing values in Session State for the given page, which in truth is the ID field and not necessarily the page itself. And two, all that is now left to do is update Session State with the new ID values.

The function responsible for delegating the actions at the appropriate time resides in the DataGrid paging event handler method. Here it where it determines which checkboxes were selected, then it does it thing. After Databind has occurred we call the RePopulateCheckBoxes() method to tell us if the given page we've paged to has checkboxes it needs to repopulate.

Sub MyDataGrid_Page (sender As Object, e As DataGridPageChangedEventArgs)

'Get CheckBoxValues before paging occurs
GetCheckBoxValues()

MyDataGrid.CurrentPageIndex = e.NewPageIndex

BindData(Session ("SortOrder"))

'Populate current DataGrid page with the current page items from Session after databind
RePopulateCheckBoxes ()

End Sub

Once all this happens you have each DataGrid page filled with the correct boxes checked or empty. Pretty cool! Now, how does one repopulate the checkboxes on any page?

Repopulate our DataGrid

This is all brought about in a more or less similar manner as storing the values. The RePopulateCheckBoxes() method listed below starts of by setting up an ArrayList based on the values stored in Session State. Next, as before, it loops through the current page DataGrids items, and if Session State contains any matching values that happen to be on the current page, it'll then proceed and set the appropriate checkbox to true.

Sub RePopulateCheckBoxes ()

CheckedItems = New ArrayList

CheckedItems = Session ("CheckedItems")

If Not IsNothing(CheckedItems) Then

'Loop through DataGrid Items
For Each dgItem in MyDataGrid.Items

ChkBxIndex = MyDataGrid.DataKeys(dgItem.ItemIndex)

'Repopulate DataGrid with items found in Session
If CheckedItems.Contains(ChkBxIndex) Then

CheckBox = CType(dgItem.FindControl("DeleteThis"), CheckBox)
CheckBox.Checked = True

End If

Next

End If

'Copy ArrayList to a new array
Results = CheckedItems.ToArray(GetType(String))

'Concatenate ArrayList with comma to properly send for deletion
deletedIds = String.Join(",", Results)

End Sub

Additionally, the aforesaid method at the end also copies the ArrayList into a new array from which the values get cleanly concatenated with commas, so they can be batch deleted.

The rest of our code is pretty undemanding. The remaining functions are all self explanatory. For one, you have the SortOrder() function, which properly sorts the order based on the values passed to it, in turn sorting the DataGrid accordingly. The other notable function is the DeleteAllIds() method that deletes all the values that were setup in the method listed above. Moreover, since one might uncheck any checkbox on the given current page, then proceed to delete their selections, we need to support this action by including the GetCheckBoxValues() and RePopulateCheckBoxes() methods, whereby it'll regrab the values and update Session State accordingly, so the final delete reflect this change.

Finally, you have the ClearDataGrid() method, that willclear all Session State values and rebinds our DataGrid from the top. And that all she wrote.

Conclusion

Well, I hope this was interesting and fun as well. In turn, you have now ended up with one really cool way to not only select, confirm & delete multiple checkbox items in a DataGrid page, but furthermore, actually page, sort, check some more, takes some away, and then delete them. I knew that was worth the read. :-)

Until next time. Happy .NETing </>

Marketplace
(Sponsored Links)

What are the green links?