Transcript Data Grid - Professor Jerry Post

.NET Web Forms
DataGrid
© 2002 by Jerry Post
1
Data Grid Has Many Uses
 The grid uses HTML tables to display multiple rows of data. It is
flexible and powerful, but requires some careful work.
 It can be used to edit all of the rows in a table, which is really only useful
for small tables; such as setup tables of list data.
 It can be used as the subgrid in a Main/Sub approach; such as a standard
Order/Order Item linkage.
 The grid is very dangerous in terms of performance.
 Using the grid requires holding at least some data in a dataset that is held
as Session data.
 In business, subform data tables can be huge. If you are sloppy in design
or coding, you may tell the system to load and save hundreds of
thousands of rows of data in memory.
 The internal tools of the grid (sorting, filtering, and especially pagination),
are supposed to minimize these problems.
 The default pagination options are useless at reducing the amount of data
retrieved. And require complex SQL to function safely.
 As much as possible, never edit a dataset with more than a couple
hundred rows. Find a user-interface method to reduce the number early.
2
Data Grid Example: Country List
 Note the Edit and Delete
columns. They are somewhat
sloppy user interface
(especially the delete), but
line-editing returns data to the
server earlier, and minimizes
some problems.
 Clicking Edit provides text
boxes and Update/Cancel
buttons.
 The Update link also makes
the Page Save button visible,
which needs to be clicked to
send the changes to the
database.
3
DataGrid Basics




Create a data adapter and dataset in the DB component
Add a new Web form
Add the dataset to the page
Create a DataView from the DataSource





Views can be sorted and filtered
Drag a DataView icon onto the form
Name: (dsServiceList1)
Table: select a table from the DataSource (ServiceList1)
Apply Default Sort: true
 Data Grid Properties
 ID: dgService (whatever)
 Property Builder
 DataSource: dvServiceList1
 Data key field: Service ID (*** warning: can only be single column)
 Allow sorting
 Advanced--Leap ahead
 Basic grid templates with Edit, Update, Sort, Filter, Concurrency
 Grid templates with Drop Down Lists
4
Data Component: Data Adapter
 Add Data Component
 Drag A Data Adapter
onto page
 Follow the wizard
 Create a connection if
needed
 Use SQL or stored
procedures
 Advanced Options
 Generate Insert,
Update and Delete
statements
 Optimistic
Concurrency
5
Data Component: Data Set
 Right-click the data adapter
and generate a data set
 Select New and enter a name
 Select the table
 Click the Add to designer box
 When done, View + Code and
add a routine to fill the
dataset.
Public Sub FillServiceOffered(ByVal dSet
As DsServiceList)
adpServiceList.Fill(dSet)
End Sub
6
Data Grid: Basics
 Select the
DataSource (usually
a dataview)
 Select the key
column (only one
column allowed)
 Click the Allow
sorting checkbox
7
DataGrid: Columns
 Uncheck: Create
columns automatically
at run time
 Move columns to
right-hand side in the
order you want.
8
DataGrid: Basics – Link Columns
 Add a ButtonColumn or HyperLinkColumn
 HyperLink is more flexible in that is supports frames, including inline
frames to display more data on the same page.
 DataNavigateUrlField
reference ID column name
 DataNavigateUrlFormatString
Page to open +
 DataTextField
reference name column
 DataTextFormatString
Text to display +
 Target
Frame/HTTP Target
<asp:HyperLinkColumn
runat="server"
DataNavigateUrlField ="StudentID“
DataNavigateUrlFormatString="EditStudent.aspx?id={0}"
DataTextField =“LastName”
DataTextFormatString ="More on {0}"
>
</asp:HyperLinkColumn>
Session("StudentID") = Request("id").Trim
cmpStu.SetStudentSelect(cmpStu.strSelStudent & " WHERE StudentID='" &
Session(“StudentID”) & "'")
9
DataGrid: Edit Columns
 Expand the Button
Group
 Add Edit, Update,
Cancel
 Add Delete
 Set the appropriate
column names
10
DataGrid Basics: Page_Init
 You need code to even see anything!
 Page_Init, add the following to read the data from the database into the
dataset, and then display the dataset in the grid:
If (Not Page.IsPostBack) Then
Dim cmpMain As DBMain = New DBMain()
cmpMain.FillServiceOffered(DsServiceList1)
DataBind()
Session(“dsServiceList1”) = DsServiceList1
End If
 Code assumes you have a data fill routine in the main data component to
isolate the DB-specific adapter:
Public Sub FillServiceOffered(ByVal dSet As DsServiceList)
adpServiceList.Fill(dSet)
End Sub
 Warning: Most books use Page_Load, but the DataGrid work is all done at
Page_Init, so you should start off with putting everything there.
11
DataGrid: Sorting (easy version)
 Properties: Allow sorting
 View + Code
 Select data grid in left select box: dgService
 Select SortCommand in right select box
 Add code (it must be a data view to have .Sort):
See Esposito, p. 59+ for more detailed sort interface
Private Sub dgService_SortCommand(ByVal source As Object, ByVal e
As System.Web.UI.WebControls.DataGridSortCommandEventArgs)
Handles dgService.SortCommand
dvServiceList1.Sort= e.SortExpression
End Sub
12
Sorting: Complications
 Eventually we need to keep the Dataset between calls
 So we also need to keep the sort list
 Page_Init
If (Not Page.IsPostBack) Then
Dim cmpMain As DBMain = New DBMain()
cmpMain.FillServiceOffered(DsServiceList1)
DataBind()
Session("dsServiceList1") = DsServiceList1
Session("sSort") = ""
End If
 Sort
Private Sub dgService_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
dgService.SortCommand
Session("sSort") = e.SortExpression
UpdateGridView()
End Sub
13
DataGrid: UpdateGridView
 After every PostBack, you need to redraw/rebind the grid
 That means you have to reset the sort condition
Private Sub UpdateGridView()
DsServiceList1 = CType(Session("dsServiceList1"), DsServiceList)
dvServiceList1.Table = DsServiceList1.Tables("ServiceOffered")
dvServiceList1.Sort = CType(Session("sSort"), String)
dgService.DataBind()
End Sub
14
DataGrid: View with Sort
 Right-click file name in Solution Explorer and select
 Build and Browse
 Sort works, but Edit and Delete do nothing when clicked.
15
DataGrid: Register Event Handlers
 Go to main forms page, click on HTML, add event handlers to process
Edit, Update, Cancel
<asp: DataGrid id=“grid”
…
OnEditComamnd=“EditCommand”
OnUpdateCommand=“UpdateCommand”
OnCancelCommand=“CancelCommand
OnDeleteCommand=“DeleteCommand”
>
 You can also assign them by selecting Grid in the top-left select box, and
then each command in the top-right select box.
 It is possible to assign these handlers dynamically by code, but the code
must be in Page_Init (not Page_Load).
C#:
dgVisit.UpdateCommand += new
DataGridCommandEventHandler(dgVisit_Update);
VB:
AddHandler dgVisit.UpdateCommand, AddressOf dgVisit_Update
16
DataGrid: Event Handler for Edit
 You simply have to set the EditIndex to the proper row and refresh
 Visual Basic
Public Sub EditCommand(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
grid.EditItemIndex = e.Item.ItemIndex
‘ Set the edit row
UpdateGridView()
‘ Redraw/refresh
End Sub
 C#
public void EditCommand(object sender,
DataGridCommandEventArgs e)
{
grid.EditItemIndex = e.Item.ItemIndex
UpdateGridView()
}
17
DataGrid: Event Handler for Cancel
 You simply remove the EditIndex and refresh
 Visual Basic
Public Sub EditCommand(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
grid.EditItemIndex = -1
‘ Clear the edit row
UpdateGridView()
‘ Redraw/refresh
End Sub
 C#
public void EditCommand(object sender,
DataGridCommandEventArgs e)
{
grid.EditItemIndex = -1
UpdateGridView()
}
18
DataGrid: Edit Mode
 Clicking Edit on a row
 Sets up text boxes for editing
 Displays Update and Cancel commands
 The width of the edit text boxes can only be controlled with
templates
 You also need templates to use Select boxes or Check boxes
 But the entire column must be a template column to enable
templates in edit mode
19
DataGrid: Save Button






The Save Button is invisible until changes are made.
Note the use of the tool tip to provide more detail.
Changes are made to the “memory” DataSet.
Which is maintained in a Session variable between pages.
Clicking the button saves all changes in a batch mode.
You can set Optimistic locking or simple overwrite when you
create the Data Adapter. But optimistic locking simply generates
row errors and does not save anything. It requires additional code.
20
DataGrid: Code-Setup
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
If (Not Page.IsPostBack) Then
Dim cmpMain As DBMain = New DBMain()
cmpMain.FillServiceOffered(DsServiceList1)
DataBind()
Session("dsServiceList1") = DsServiceList1
Session("sSort") = ""
Else
DsServiceList1 = CType(Session("dsServiceList1"), DsServiceList)
dvServiceList1.Table = DsServiceList1.Tables("ServiceOffered")
dvServiceList1.Sort = CType(Session("sSort"), String)
End If
End Sub
Private Sub UpdateGridView()
DsServiceList1 = CType(Session("dsServiceList1"), DsServiceList)
dvServiceList1.Table = DsServiceList1.Tables("ServiceOffered")
Me.dvServiceList1.Sort = CType(Session("sSort"), String)
dgService.DataBind()
End Sub
21
DataGrid: Code-Easy Edits
Private Sub dgService_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
dgService.SortCommand
Session("sSort") = e.SortExpression
UpdateGridView()
lblMessage.Text = ""
End Sub
Public Sub EditServiceGrid(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
dgService.EditItemIndex = e.Item.ItemIndex
UpdateGridView()
lblMessage.Text = ""
End Sub
Public Sub CancelServiceGrid(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
dgService.EditItemIndex = -1
UpdateGridView()
lblMessage.Text = ""
End Sub
22
DataGrid: Code-Update
Public Sub UpdateServiceGrid(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
Dim row As DataRowView = dvServiceList1(e.Item.ItemIndex)
row.BeginEdit()
Dim txt As TextBox = CType(e.Item.Cells(0).Controls(0), TextBox)
row("ServiceID") = txt.Text
txt = CType(e.Item.Cells(1).Controls(0), TextBox)
: row("SortOrder") = txt.Text
txt = CType(e.Item.Cells(2).Controls(0), TextBox)
: row("Abbreviation") = txt.Text
txt = CType(e.Item.Cells(3).Controls(0), TextBox)
: row("ShortName") = txt.Text
txt = CType(e.Item.Cells(4).Controls(0), TextBox)
: row("Description") = txt.Text
row.EndEdit()
Session("dsServiceList1") = DsServiceList1
dgService.EditItemIndex = -1
:
UpdateGridView()
btnSave.Visible = True
:
lblMessage.Text = ""
End Sub
23
DataGrid: Code-Delete
Public Sub DeleteServiceGrid(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
dvServiceList1.Delete(e.Item.ItemIndex)
Session("dsServiceList1") = DsServiceList1
dgService.EditItemIndex = -1
UpdateGridView()
btnSave.Visible = True ' We are using batch update
lblMessage.Text = ""
End Sub
24
DataGrid: Code-Add
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
' Want to find the currently largest ServiceID, since we are not using AutoNumber
Dim row1 As DataRowView = dvServiceList1(dvServiceList1.Count - 1)
Dim n As Integer = row1("ServiceID")
' Reset the sort
dvServiceList1.Sort = CType(Session("sSort"), String)
Dim row As DataRowView = dvServiceList1.AddNew()
row("ServiceID") = n + 1
:
row("SortOrder") = n + 1
row("Abbreviation") = "“
:
row("ShortName") = ""
row("Description") = "“
:
row.EndEdit()
Session("dsServiceList1") = DsServiceList1
dgService.EditItemIndex = dvServiceList1.Count - 1
UpdateGridView()
btnSave.Visible = True
:
lblMessage.Text = ""
End Sub
25
DataGrid: Code-Save
Private Sub btnSave_Click (ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click
Dim cmpMain As DBMain = New DBMain()
DsServiceList1 = CType (Session("dsServiceList1"), DsServiceList)
lblMessage.Text = cmpMain.UpdateServiceListBatch(DsServiceList1)
btnSave.Visible = False
End Sub
DBMain
Public Function UpdateServiceListBatch (ByVal ds As DataSet) As String
Dim iChanges As Integer = 0, str As String
Try
iChanges = adpServiceList.Update(ds, "ServiceOffered")
str = iChanges.ToString & " changes were saved to the database."
Catch e As Exception
str = e.Message
End Try
Return str
End Function
26
DataGrid: Concurrency




Batch updates should always check for concurrency.
The challenge lies in notifying the user,
And in updating the data so that changes can be saved.
This approach




Returns an error message.
And color highlights the data that was not updated.
It also reloads the entire grid from the database.
And redisplays the changes made by this user.
 But it does not show the user the new value currently in the database.
27
DataGrid: Cell Highlight Code
 You can alter the display (or edit) of individual cells on the fly.
 Use grid.OnItemCreated, but note that it is fired for each row.
 To alter individual cells, you have to examine each one.
Public Sub ItemCreatedGrid(ByVal sender As Object, ByVal e As DataGridItemEventArgs)
Dim lit As ListItemType = e.Item.ItemType
If (lit = ListItemType.Item OrElse lit = ListItemType.AlternatingItem) Then
Dim row As DataRowView = dvServiceList1(e.Item.ItemIndex)
Dim id As Integer = row("ServiceID")
' Find row in underlying dataset, data view does not provide access to the Original value
Dim dsRow As DataRow = DsServiceList1.Tables("ServiceOffered").Rows.Find(id)
' Iterate through columns (they are in the constant array declared at the start to simplify)
Dim iCol As Integer
For iCol = 0 To GridCols.GetUpperBound(0) - 1
If dsRow(GridCols(iCol)) <> dsRow(GridCols(iCol), DataRowVersion.Original) Then
e.Item.Cells(iCol).CssClass = "CellChanged"
End If
Next
End If
End Sub
28
DataGrid: Save
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click
Dim cmpMain As DBMain = New DBMain()
lblMessage.Text = cmpMain.UpdateServiceListBatch(DsServiceList1)
' Need to resave Session because concurrency code and re-read changed it
Session("dsServiceList1") = DsServiceList1
btnSave.Visible = False
UpdateGridView()
End Sub
 Only one new line in the save button routine:
 After data has been updated, save the session, because the
dataset was probably modified during the update.
29
DataGrid: Concurrency Test
Public Function UpdateServiceListBatch(ByRef ds As DataSet) As String
Dim iChanges As Integer = 0, str As String
adpServiceList.ContinueUpdateOnError = True ' With default False, it will raise an error
but then stop. We need to process as many changes as possible first.
Try
iChanges = adpServiceList.Update(ds, "ServiceOffered")
If (iChanges = 1) Then
str = "One row change was saved to the database."
Else
str = iChanges.ToString & " row changes were saved to the database."
End If
Catch e As Exception
str = e.Message
End Try
 Only minor change to the actual save: ContinueUpdateOnEntry
 Default is False
 So, only rows before the concurrency problem would be updated.
 This change enables all modifications to be attempted
30
DataGrid: Concurrency Code2
' Add the rows with concurrency errors to a new table
Dim tb As DataTable = ds.Tables("ServiceOffered")
Dim tbChg As DataTable = tb.Clone()
Dim dr As DataRow
If tb.HasErrors Then
For Each dr In tb.Rows
If dr.HasErrors Then
tbChg.ImportRow(dr)
End If
Next dr
End If
 The trick now is to identify the rows that had concurrency problems.
 Then save those rows in a temporary table,
 So we keep the user changes.
31
DataGrid: Concurrency Code3
' Reload the main dataset so that it resets the Original
values (and picks up all database changes)
Me.FillServiceOffered(ds)
' Need to merge the changed values back
in to the dataset--if the row still exists
If (tbChg.Rows.Count > 0) Then
 Now reload the base dataset
Dim drNew As DataRow, i As Integer
with the current database
For Each dr In tbChg.Rows
drNew = tb.Rows.Find(dr("ServiceID")) contents.
 That updates all rows
If (Not drNew Is Nothing) Then
For i = 0 To tbChg.Columns.Count - 1  And sets the underlying
drNew(i) = dr(i)
Original value correctly so
Next i
future changes can be
End If
made.
Next dr
 Then take the temporarily
End If
saved user changes and
Return str
reapply them
End Function
32
DataGrid: Concurrency
 Compensate for concurrency:
 Rows might be changed
 Rows might be deleted
 Rows might be added
 Painful to do by hand, easier as follows:
 Stash the modified rows not saved because of concurrency
 Reread the dataset
 Merge the stashed rows, but watch for deleted
 Remaining problem:
 How do we show the user what the new value is?
 It is stored in row(“ColumnName”, DataRowVersion.Original)
 But we need an easy and unobtrusive way to display it, and
probably let the user select/restore it so it is not overwritten.
33
DataGrid: Basic Review
 Add a Component page
 Add a Data Adapter, Data
Connection, and Data Set
 Add a Web form page
 Drag the Data Set to the page
 Drag and create a Data View
from the Data Set (sorted)
 Drag a Data Grid to the page
 Set the ID
 Property Builder





DataSource = Data View
Data key
Allow sorting
Select columns to display
Add Edit and Delete columns
 Write Code to run the grid
 Page_Init: Fill and DataBind
 Sorting
 UpdateGridView
 Load from Session
 Resort
 DataBind
 Edit Event Handlers





Register them
OnEditCommand (easy)
OnCancelCommand (easy)
OnUpdateCommand (use sample)
OnDeleteCommand (use sample)
 Button Add row code (use sample)
 Button Save batch code (use sample)
 Concurrency
 OnItemCreated code (use sample)
 Modify save code
 Add data component Update code
34
DataGrid: TemplateColumn
 You almost always need TemplateColumns in your DataGrid




ItemTemplate to control the display within each cell
EditItemTemplate to control the editing
FooterTemplate for buttons
If you want one of these, you have to implement all of them (except
Footer is optional)
 See Esposito, p. 79 and 117
 Setup code to mimic BoundColumn but set the edit box size
goes into the .aspx file, HTML view:
<asp:TemplateColumn SortExpression="ServiceID" HeaderText="ServiceID">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem,"ServiceID") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox Runat="server" Width="5em" ID="txtServiceID"
Text='<%# DataBinder.Eval(Container.DataItem, "ServiceID") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
35
DataGrid: Edit Template-Update
 BoundColumn and EditTemplate refer to the edited data differently:
 BoundColumn
Dim txt1 As TextBox = CType(e.Item.Cells(nCol).Controls(0), TextBox)
 TemplateColumn
Dim txt1 As TextBox = CType(e.Item.FindControl(“txtServiceID”)
Public Sub UpdateServiceGrid(…)
…
' Use the predefined array to convert column index into column name
because the grid list is not in the same order as the dataset
Dim iCol As Integer
For iCol = 0 To GridCols.GetUpperBound(0) - 1
txt = CType(e.Item.FindControl(GridEdits(iCol)), TextBox)
row(GridCols(iCol)) = txt.Text
Next
…
End Sub
36
DataGrid: EditTemplate Effect
 In this case, the primary effect is that we can control the width
of the text input box.
 Templates are considerably more powerful
 You can put multiple items within a cell and control formatting.
 You can include images, links and multiple rows within a cell.
 You can edit with Check Boxes and Select Boxes.
37
DataGrid: Template and Concurrency
 Now it is possible to add a control to cells that have experienced
concurrency problems or have been changed.
 The Add Row button has also been moved to a Footer Template.
 A swap/undo button is added that displays the underlying value on rollover,
and places it into the dataset when clicked.





The value was changed via this Web form (Int4).
It was altered directly in the database (Int8).
An attempt was made to save the changes.
Clicking Edit + Update + Save Changes will put Int4 into the database.
Clicking the swap button or closing the form will leave Int8 there.
38
DataGrid: Swap Button
<asp:TemplateColumn SortExpression="Abbreviation“ HeaderText="Abbreviation">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem,"Abbreviation") %>
<asp:ImageButton Runat="server" ID="btnChgAbbreviation“
AlternateText="" ImageUrl="images/swap1.gif" Visible="False“
OnClick="SwapButtonClick" />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox Runat="server" Width="5em" ID="txtAbbreviation"
Text='<%# DataBinder.Eval(Container.DataItem, "Abbreviation") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
 The image button is added directly to each Template Column.
 Adding it to the ItemTemplate instead of the EditItemTemplate
makes it possible to replace the data with one click instead of
three.
39
DataGrid: ItemCreated-2
Public Sub ItemCreatedGrid(ByVal sender As Object, ByVal e As DataGridItemEventArgs)
Dim lit As ListItemType = e.Item.ItemType
If (lit = ListItemType.Item OrElse lit = ListItemType.AlternatingItem) Then
Dim row As DataRowView = dvServiceList1(e.Item.ItemIndex),
Dim id As Integer = row("ServiceID"), iCol As Integer
Dim dsRow As DataRow = DsServiceList1.Tables("ServiceOffered").Rows.Find(id)
For iCol = 0 To GridCols.GetUpperBound(0) - 1
Try
If (dsRow(GridCols(iCol)) <> dsRow(GridCols(iCol), DataRowVersion.Original)) Then
e.Item.Cells(iCol).CssClass = "CellChanged"
Dim imgBtn As ImageButton = e.Item.Cells(iCol).FindControl("btnChg“ &
GridCols(iCol))
imgBtn.Visible = True
imgBtn.AlternateText = dsRow(GridCols(iCol), DataRowVersion.Original)
imgBtn.CommandName = id
‘ These two identify the row and column
imgBtn.CommandArgument = iCol
End If
Catch e1 As Exception
End Try
Next
End If
End Sub
40
DataGrid: Concurrency Swap Button
Public Sub SwapButtonClick(ByVal sender As Object, ByVal e As
ImageClickEventArgs)
Dim imgBtn As ImageButton = CType(sender, ImageButton)
Dim ServiceID As Integer = CType(imgBtn.CommandName, Integer)
Dim iCol As Integer = CType(imgBtn.CommandArgument, Integer)
Dim dsRow As DataRow =
DsServiceList1.Tables("ServiceOffered").Rows.Find(ServiceID)
Dim str As String = imgBtn.AlternateText
imgBtn.AlternateText = dsRow(GridCols(iCol)) ' Note: this does not work
dsRow(GridCols(iCol)) = str
Session("dsServiceList1") = DsServiceList1
UpdateGridView()
End Sub
 The button code puts the Alternate text into the dataset.
 It also sets up a potential swap by putting the .Current value back
into the Alternate text.
 But that swap does not work because the Alternate text does not
persist. You would need to save those values in a Session array.
41
DataGrid: Building Your Own
 I tried to make the code generic, so that you can build your own grid with
the same features.




Templates
DataGrid Code
Data Component Update Code
GenKey and BatchUpdate
 First, create the data adaptor and data set and the grid.
 Then copy and paste the Column descriptors and templates into the
Design view. And alter them to match your own columns.
 Then copy and paste the code into your Code view.
 In several places, you have to change the .Tables(“ServiceOffered”) into your
table. It would be better to make it a global constant, so you can change it in
one location.
 In several places, you have to change the data view name (dvServiceList) and
data set name (dsServiceList). Use search and replace to change them all.
 Do the same thing to change the grid ID (dgService).
 btnAdd_Click needs to initialize your columns.
 Change the cmpMain reference and cmpMain.FillService.
 Change GridCols() and GridEdits() at the top of the code.
 Make minor changes to UpdateServiceListBatch in DBMain.
42
DataGrid: Pagination Questions
 The goal of pagination is to reduce the transfer of data.
 For example, users would see only 10-20 items on a page, and could click link
buttons to select pages.
 Data grid sort of has a built-in paging mechanism. It is only useful for tables
with less than about 200 rows.
 Data grid has a paging mechanism that you can customize.
 It is better, and can be made acceptable.
 But, it is difficult to use—particularly with Oracle.
 See Esposito, pp. 43-59 and Sceppa, pp. 623-629
 The problem/challenge:
 You do not want to hold a huge data set in Session/memory. (That’s the problem
with the default approach.)
 So you need a method to retrieve rows from the database by sequence position.
 But, relational databases do not work that way. (The samples give some complex
queries to work around this.)
 And database updates alter the data which destroys your sequence. (None of the
samples take this into account.)
 Whenever possible, make people enter selection criteria.
 Limit large dataset paging to a few users (administrators?)
43
DataGrid: Pagination
 To use the semi-automatic paging system:
 Set paging properties in the Property Builder.
 Allow paging (use custom if you want more control).
 Page size (e.g., 15).
 Show navigation buttons (Page numbers might be best).
 Add code to handle the page change event.
 Make sure the entire dataset is held in Session.
 But your update code must be changed. See next page.
Private Sub dgCountry_PageIndexChanged(ByVal source As Object, ByVal e
As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles
dgCountry.PageIndexChanged
dgCountry.CurrentPageIndex = e.NewPageIndex
UpdateGridView()
End Sub
44
DataGrid: Pagination-Update
 You must be careful about which row you are editing.
 The common Update code for a grid uses:
Dim row As DataRowView = dvCounry1(e.Item.ItemIndex)
 But, you need to compensate for the page, since the ItemIndex
only counts from the top of the current page.
Dim idx As Integer = dgCountry.CurrentPageIndex * dgCountry.PageSize
+ e.Item.ItemIndex
Dim row As DataRowView = dvCountry1(idx)
45
DataGrid: Pagination Output
46
DataGrid: Filter
 A DataView can be
filtered to display a
limited number of rows.
 The filter syntax is
similar to the SQL
Where syntax.
 But you can make it
easier for users by
having them enter a
partial value, and then
building the filter
yourself.
47
DataGrid: Filter Button HTML
<FooterTemplate>
<asp:button id="btnAdd" accessKey="A" runat="server“
Text="Add Row"
ToolTip="Add a new row to the list“
OnClick="btnAdd_Click">
</asp:button>
<br>
<asp:Label ID="lblFilter" Runat="server">Search/Filter:
</asp:Label>
<asp:TextBox ID="txtFilter" Runat="server" />
<asp:Button ID="btnFilter" AccessKey="F“
Runat="server" Text="Find"
ToolTip="Find matching entry.“
OnClick="btnFilter_Click">
</asp:Button>
</FooterTemplate>
48
DataGrid: Filter Code1
 Like the Sort clause, the Filter statement has to be
stored in a Session variable between page calls.
Sub Page_Init …
If (Session("DsCountry1") Is Nothing) Then
Dim cmpMain As DBMain = New DBMain()
cmpMain.FillCountry(DsCountry1)
DataBind()
Session("DsCountry1") = DsCountry1
Session("sSortCountry") = ""
Session("FilterCountry") = ""
Else
DsCountry1 = CType(Session("DsCountry1"), DsCountry)
Me.dvCountry1.Table = Me.DsCountry1.Tables(0)
Me.dvCountry1.Sort = CType(Session("sSortCountry"), String)
Me.dvCountry1.RowFilter = Session("FilterCountry")
End If
End Sub
49
DataGrid: Filter Code2
 Again, like Sort, the RowFilter must be reapplied when
the grid display is refreshed.
Private Sub UpdateGridView()
Me.DsCountry1 = CType(Session("DsCountry1"), DsCountry)
dvCountry1.Table = DsCountry1.Tables(0)
Me.dvCountry1.Sort = CType(Session("sSortCountry"), String)
Me.dvCountry1.RowFilter = CType(Session("FilterCountry"), String)
dgCountry.DataBind()
End Sub
50
DataGrid: Filter Button Code 1
Public Sub btnFilter_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs)
Dim cell As TableCell = CType(sender.parent, TableCell)
Dim txtFilter As TextBox = CType(cell.FindControl("txtFilter"), TextBox)
Dim sbFilter As System.Text.StringBuilder = New System.Text.StringBuilder()
Dim sFilter As String = txtFilter.Text.Trim.ToLower
lblMessage.Text = ""
Dim i As Integer = sFilter.IndexOf("<")
If (i < 0) Then i = sFilter.IndexOf(">")
If (i < 0) Then i = sFilter.IndexOf("=")
If (i < 0) Then i = sFilter.IndexOf("like")
If (i < 0) Then ' user probably just entered part of the name
Dim j1 As Integer = sFilter.IndexOf("'")
Dim j2 As Integer = sFilter.IndexOf("'", j1 + 1)
If (j2 < 0) Then j2 = sFilter.Length
sbFilter.Append("Country LIKE '")
sbFilter.Append(txtFilter.Text.Substring(j1 + 1, j2 - j1 - 1))
sbFilter.Append("%'")
Session("FilterCountry") = sbFilter.ToString
UpdateGridView()
51
DataGrid: Filter Button Code 2
Else
‘ User entered own search condition
Dim j1 As Integer = sFilter.IndexOf("'")
Dim j2 As Integer = sFilter.IndexOf("country")
If ((j1 < 0) OrElse (j2 < 0)) Then ' Does not meet minimum format
lblMessage.Text = "Filter should be a partial country name,
or of the form: Country > 'A' or Country Like 'B%'"
lblMessage.Visible = True
Else
Session("FilterCountry") = txtFilter.Text
Try
UpdateGridView()
Catch e1 As Exception
lblMessage.Text = "Filter error: " & e1.Message
lblMessage.Visible = True
Session("FilterCountry") = ""
' Clear the bad filter
End Try
End If
End If
End Sub
52
DataGrid: Source Code
 This example (edit the country list) handles
 Edit/Cancel/Delete/Update
 Add new row
 Batch update
 Concurrency
 Pagination (but performance only for about 200 rows)
 Sort (not real useful with only one column but code is the same)
 Filter
 Code sections (rtf format)
 HTML (buttons and grid)
 Page code (handles page events)
 Data code (transfer dataset to the database)
 Update and GenKey (common code for DB component)
53
DataGrid: Your Own Forms
 You need a fast way to build a Web form that edits a single table in
grid view. And handle Sort, Filter, Edit, Update, and Concurrency.
 A better wizard would be nice, otherwise follow these steps:
(1) Use Windows to copy and rename three files





EditCountry.aspx
EditCountry.aspx.resx
EditCountry.aspx.vb
Change notes
DBMain.vb
(HTML file)
(Resource file)
(Code file)
(rtf file that is easier to use)
(Common update code)
(2) In the main database component
 Add a new DataAdapter
 Generate a new Dataset
(3) Use WordPad to edit and modify the three files
(4) Add three lines of code to the main database component
(5) Add all three edited files to Visual Studio
(6) Link the new page into your main form, check security.
(7) Add the TMF component to your system, and Build everything.
54
DataGrid: Edit .aspx.resx (Resource)
 Search and Replace
 DsCountry
 dvCountry
Ds[DataSet Name]
dv[DataView Name]
55
DataGrid: Edit .aspx (HTML)
 Search and Replace





EditCountry
dvCountry
Standard Country List
DataKeyField=“Country”
id=dgCountry
[FileName]
dv[ViewName]
[Your Page Title]
DataKeyField=“[IDColumn]”
id=dg[GridName]
 Copy and edit the columns, use <FooterTemplate> in only 1 column.
 Edit the columns








SortExpression=“[Column Name]”
HeaderText=“[Column Name]”
DataBinder…“[Column Name]”
ID=“btnChg[Column Name]”
<Edit Item Template> (remove to disable editing of a column)
ID=“txt[Column Name]”
DataBinder…”[Column Name]”
Set the Width=“#em”
56
DataGrid: Edit .aspx.vb (Code) - 1
 Search and Replace
 EditCountry
 DsCountry
[Your FileName]
Ds[DataSet Name]







dv[DataView Name]
dg[Grid Name]
.Sort = “[Primary Key Column]”
.[Table Name]
.Fill[Table Name]
(in DBMain)
sSort[Table Name]
sFilter[Table Name]
dvCountry
dgCountry
.Sort = “Country”
.Country
.FillCountry
sSortCountry
sFilterCountry
(without the 1)
(without the 1)
 Edit Column arrays:
 Line 11: Private GridCols() As String = {“Country”}
 Line 12: Private GridEdits() As String = {“txtCountry”}
 These are comma-separated lists of (a) the table columns, and (b) the
IDs in the edit templates. But even if you do not edit a column, you
must include it in the edit list—to keep the sequence right.
57
DataGrid: Edit .aspx.vb (Code) - Save
 Edit Sub btnSave_Click routine:
lblMessage.Text = tmf.OleBatchUpdate(DsCountry1, cmpMain.adpCountry,
"Country")
 DsCountry1
 .adpCountry
 “Country”
Ds[DataSet Name]1
.adp[Adapter Name]
“[Table Name]”
58
DataGrid: Edit .aspx.vb (Code) - Add
 Edit Sub btnAdd_Click
 If your primary key is a string and you set the default value to “”,
leave the first If test and End If to prevent errors.
 If your primary key is numeric or you set a new value each
time, comment out the first If and matching End If statements.
 If you want to generate a new ID from the AutoNumber table:
 Remove the comments from the three lines marked
 In GenKey, change “Country” to “[Table Name]”
 Comment out the fourth line: row(“Country”) = “”
 At the end of the routine, uncomment the Page locator that jumps
to the end of the list, and comment the one that goes to the top.
 Set other default values as desired.
 If you want to let the user pick an ID, but suggest a value:
dvServiceList1.Sort = "ServiceID“, n As Integer = row1("ServiceID")
Dim row1 As DataRowView = dvServiceList1(dvServiceList1.Count - 1)
dvServiceList1.Sort = CType(Session("sSortService"), String)
Dim row As DataRowView = dvServiceList1.AddNew()
row("ServiceID") = n + 1
row("SortOrder") = n + 1
59
DataGrid: Edit .aspx.vb (Code) - ItemCreated
 Edit Sub dg[Grid Name]_ItemCreated
 Change: (datagrid currently only supports 1 column key)
 Dim id As String = row(“Country”) to your column and data type
 Dim id As Integer = row(“[Primary Key]”)
 Edit Sub btnFilter_Click
 Many possible changes here, depending on user interface
 Important: Change the word Country to the name of the column
you want people to search on by default (several places).
 Be careful with IndexOf(“[column name]”). Must be lower case.
 When you think you are done, search for: Country
 If you find it, change it. It should not be in your final code
60
DataGrid: Edit/Finalize
 In database component (DBMain), add fill:
Public Sub Fill[Table Name](ByVal ds As DataSet)
adp[Adapter Name].Fill(ds)
End Sub
 Add all three files to Visual Studio (plus tmf).
 Solution Explorer, right-click, Add Existing Files
 Add link from your main page/menu to new form
 Edit (or remove?) security check in Page_Init
 If (Not CType(Session(“IsValidLogin”), Boolean)) Then…
 If (CType(Session(“User”), UserData).UserLevel = “Student”)…
 Build everything and test.
 Create a wizard to do this all automatically.
61
DataGrid: Drop Down List/Foreign Keys
 Relational database tables often have foreign keys
 Table with a column that is a primary key in a second table.
 Example:
 Order(OrderID, OrderDate, CustomerID)
 Customer(CustomerID, LastName, FirstName, …)
 CustomerID is a foreign key in the Order table.
 It is an efficient storage method, but causes data entry
problems, because you do not want the user to have to look up
every CustomerID.
 Solution: use a Drop Down List (ddl) that displays a userfriendly list of sorted names. When a name is selected, the
matching Customer ID is inserted into the Order table.
 Challenge: Drop Down Lists are somewhat challenging to
incorporate into a data grid, because you need to display the
correct text item whenever you change the underlying key. It is
even more difficult because update only works with tables, not
with database queries.
62
DataGrid: DDL Example
63
DataGrid: DDL Structure
Database:
tables and queries
SchoolID
Major
School
MajorID, SchoolID, Major
.NET DataSets
School
SchoolID
DataSet
For Table
School
qryMajorSchool
SchoolID
MajorID School Major
Data Grid Display
DDL
Drop Down List
64
DataGrid: DDL Overview
 The main display is based on a query that contains all of the
columns from the main table, and the display column from the
lookup table.
 A separate dataset provides data to fill the drop down list.
 A drop down list needs to know six things:




DataSource
DataMember
DataTextField
DataValueField
the data set (or array or reader) to fill it
the table or query in the DataSet
the text for each row to display to users
the hidden ID value that uniquely identifies a row
 [ColumnLookUpText]
the name of the column in the underlying
dataset that displays the current value for one row.
 [ColumnLookUpValue]
the name of the column in the underlying
dataset that holds the chosen ID value
 With the query, displaying data is relatively easy.
65
DataGrid: DDL Update
 First update problem: when the user makes a selection you must:
 Store the ID value for the selected item into the underlying dataset.
 Store the display text for the selected item into the underlying dataset
(the query only works when you pull data from the DBMS, so you have
to manually keep the display accurate).
 Second update problem: writing the changes back to the database.
 ADO .NET Update only works on single tables.
 Oracle is notoriously unreliable at updating queries.
 Solution:
 Copy the underlying dataset
 Drop the query-added display columns.
 Call ADO Update using the temporary dataset.
 Requery the database and merge changes into temporary dataset.
 Reload the query dataset and merge changes from temp dataset.
66
DataGrid: DDL Template Steps
 Example is from EditMajor. Use the rtf files and search/replace for
the items listed in the notes file.










GridBase2Notes.rtf
GridBase2Res.rtf
GridBase2HTML.rtf
GridBase2Code.rtf
GridBase2DB.rtf
Notes, substitution lists
EditMajor.aspx.resx
EditMajor.aspx
EditMajor.aspx.vb
Add to DBMain.vb
You will probably need to add columns in the HTML file.
You will need to hand edit a few lines in the Code file.
You need to build the data adapters and datasets.
You also need the tmf.vb file.
All structure and code are in the templates.





Create the queries and datasets for your problem.
Add the columns to the grid (HTML file).
Substitute the names in the code file (VB).
Make a couple of decisions on code options.
The templates work for Check Boxes and read-only columns.
67
DataGrid: Main and Sub Form
 You can build a Main/Sub form using the main form
template and then adding a data grid template. But you
have to be careful with the BindDataForm—it is
responsible for updating the data grid as well.
 An additional template is included for a fairly standard
main/sub form pair. It requires that the main form use
only a single column primary key, and the subform also
uses a single primary key.
 RTF files are as follows:
 Notes: GridSub2Notes.rtf
 Resx:
GridSub2Res.rtf
 HTML: GridSub2HTML.rtf
 Code:
GridSub2Code.rtf
 DB code: GridSub2DB.rtf
68
DataGrid: Main/Sub Sample
Note that the grid is probably too wide. Better to reduce the
columns and add a More… link to a full-page editor.
69
DataGrid: Navigation Example
 Non-student users can see and search a list of all students. Note that
this list would be too large if all students at a university are included;
so you might want to set default filters before opening the form.
 Clicking on the More… link opens the standard student edit page.
 The grid provides a fast method to see and search the table.
 The separate editing page can be used for several purposes (edits by
staff and by students).
70