DataGrid, DataView, and ListView

1
DataGrid, DataView, and ListView
2
4.1 DataGrid Control

• Spreadsheet-like view of a dataset
• Simple to usedefaults work fairly well
• Allows user to
• edit data in place
• delete rows
• insert rows

3
DataGrid Properties

• Used most often

4
Rental Inventory DataGrid

• Hands-on tutorial
• Program RentalInventory
• Displays Items table from SportsRentals database
  in a DataGrid

5
Rental Inventory Output
6
Examples
7
DataGrid Events

• Navigate
• fires when user moves between related tables
• Click
• fires when user clicks on row header button
• use CurrentRowIndex to obtain dataset row
• Dim drItem As DataRow _
• DsItems1.Items(dgdInventory.CurrentRowIndex)

8
Updating a DataGrid

• Deleting a row
• obtain the row (previous slide)
• call row.Delete( )
• Updating a row
• obtain the row
• assign value to one or more columns
• row("Amount") 35.50
• Always use exception handling!

9
Karate Updates Example

• Program KarateUpdates
• Actions
• displays the Payments table
• user can delete a payment
• user can select a payment and modify the amount
• underlying database can be updated

10
4.2 DataViews and ListViews
11
DataView Control

• Sorts and filters a dataset
• Data section of the Visual Studio Toolbox
• Important properties
• Table dataset table providing the data
• Sort, RowFilter
• Sort expression uses ORDER BY clause from SQL
• ex LastName ASC, FirstName DESC
• RowFilter uses WHERE clause from SQL
• ex Salary gt 3000

12
Accessing a DataRow

• Each row is a DataRowView object
• Example
• Get row 0 from the DataView
• Dim row As DataRowView dvMembers(0)
• Get contents of the LastName column
• txtName.Text row("LastName")

13
Filling a ListView Control

• Excellent for displaying database tables
• Very flexible
• Looks like a DataGrid
• No data binding
• Fill from either dataset or DataView
• Example

14
ListView's Structure

• Items collection holds the rows
• Each row is a ListViewItem object
• In each row
• Text property holds first column
• SubItems collection holds the other columns

15
Example Filling a ListView

• lvwPayments.Items.Clear()
• Dim count As Integer dvPayments.Count
• Dim i As Integer
• Dim item As ListViewItem
• For i 0 To count - 1
• With dvPayments(i)
• item New ListViewItem(Format(.Item("PaymentDa
  te"), _
• " MM/dd/yyyy"))
• lvwPayments.Items.Add(item)
• item.SubItems.Add(.Item("MemberId"))
• item.SubItems.Add(FormatNumber(.Item("Amount"))
  )
• End With
• Next i

16
Karate School Payments

• Hands-on tutorial
• Program KaratePayments
• Fills a ListView from a DataView
• Sorts on any column, both directions, when user
  clicks on column header

17
4.3 Three-Tier Sports Rental Income

• Hands-on tutorial
• Program RentalIncome
• Displays and calculates from the Items table
• estimates income based on rental rates and
  inventory

18
Class Design

• Implements a Three-Tier model (see Chapter 1)
• Data tier SportsRental class
• Business tier Items class
• application-specific calculations
• Presentation tier frmRentalIncome class

19
4.4 Sports Rental Checkout Example

• Program RentalCheckout
• Emphasis on object-oriented design
• Checks out rental items from a Sports Rental
  store
• Design tool Use-case scenario
• describes interaction between user and program
• lists anticipated inputs and outcomes

20
Subtasks

• User enters a username and password
• Clerk creates a Rental Invoice

21
Subtask Clerk enters username, password

• Program displays a dialog window containing a
  list of employee names.
• The clerk selects a name from the list and inputs
  a corresponding user password.
• The program checks the password against the
  database and does one of the following
• If the password is correct, the program closes
  the login window and proceeds to the next
  subtask.
• If the password is incorrect, the program
  displays an error message and asks the clerk to
  reenter the password. The clerk is given three
  tries before the program ends.

22
4.5 Command Objects

• Visual Studio creates four commands for each data
  adapter
• SelectCommand
• InsertCommand
• UpdateCommand
• DeleteCommand
• Primary key required for all but SelectCommand

23
Parameters Collection

• Each Command object has a Parameters collection
• Examples
• Reference the collection
• daPayments.InsertCommand.Parameters
• Assign value to a parameter
• With daPayments.InsertCommand
• .Parameters("Date").Value txtDate.Text
• End With
• Get parameter's name
• Dim param As OleDbParameter
• param daPayments.InsertCommand.Parameters(0)
• param.ParameterName

24
Direct Update Approach

• Updates database directly, bypassing dataset
• Careful there is no "undo"
• Other database users get the very latest data
• Potential for more network traffic
• when multiple operations execute

25
Command Object
26
Inserting Table Rows

• General format
• INSERT INTO target
• (field1,field2,...)
• VALUES(value1,,value2,...)
• Example
• INSERT INTO Payroll (SSN, PaymentDate,
• HoursWorked, HourlyRate)
• VALUES('400-33-2555', 1/15/1998, 47.5, 17.50)

optional
27
Query Parameters

• Make queries more flexible
• Avoid messy embedding of variable names inside
  SQL code
• Microsoft Access Example
• INSERT INTO Payments( Amount, MemberId,
  PaymentDate )
• VALUES (?, ?, ?)
• SQL Server Example
• INSERT INTO Payments( Amount, MemberId,
  PaymentDate )
• VALUES (_at_Amount, _at_MemberId, _at_PaymentDate)

28
Executing Commands

• Open the database connection
• Assign value to each parameters
• Call ExecuteNonQuery
• Close the connection

29
InsertCommand Example
30
Updating Table Rows

• Use the WHERE clause, or all rows will be
  updated!
• General format
• UPDATE table
• SET fieldname newvalue
• SET fieldname newvalue ...
• WHERE criteria
• Example
• UPDATE Payroll
• SET HourlyRate HourlyRate 1.05
• WHERE PaymentDate gt 05/01/1999

31
Deleting Table Rows

• Use the WHERE clause, or all rows will be
  deleted!
• General format
• DELETE
• FROM table
• WHERE criteria
• Example
• DELETE FROM Payroll
• WHERE PaymentDate lt 1/1/1998

32
Inserting Karate Payments

• Example program KarateInsertPayments
• Uses a Command object to execute an INSERT query