Transcript Table - Wiley

Chapter 9
Working with
Databases in VB .NET
9-1
Learning Objectives



9-2
Understand how databases are used to store
business data and how they differ from
arrays.
Describe the parts of a database and
understand database operations including
working with ADO.NET and the Dataset.
Add oleDBDataAdapter and DataGrid
controls to a form and configure them to
display records from a database table.
Learning Objectives (continued)



9-3
Add the necessary code to find and display
records that match some criteria.
Add programming instructions and controls
to edit database records, add new records,
delete existing records, and print records in
the Dataset.
Use VB .NET and an ADO.NET database to
carry out the rental of a DVD.
Database concepts

Database
–

The storage of different types of data in a such a way that
the data can be easily manipulated and retrieved by an end
user.
Field
–
–
A single fact or data item, the smallest unit of named data
that has meaning in a database.
Examples


–
9-4
A name, address, or phone number on a membership list;
A product number in an inventory list; or a price in a price list.
Fields are given field names that are used to identify them.
More Database Concepts

Record
–
–

Table
–
–

9-5
A collection of related data that is treated as a unit
For example, a membership list record that contains
numerous fields, including name, phone number, street
address, city, state, Zip code
A related collection of records, all having the same fields
A table for the Vintage DVDs membership list would have a
record for each person on the list
In a table, the records are commonly referred to as
the rows of the table and the fields are the columns
of the table.
Relational Databases


A database with multiple tables that are
related is known as a relational database.
The most common type of database used
today
–
–
9-6
MS Access
MS SQL Server
Relational Database Example
9-7
Primary Key and Foreign Key

Primary Key
–
–
–

Foreign Key
–
–
–
9-8
A field in a table that uniquely identifies a record in that
same table
In a table containing customers, the Social Security Number
field could be the primary key
No two individuals have the same Social Security Number
A field in a table that uniquely identifies a record in another
table
In a table containing Payments information, a single field
would be used to keep track of who made the payment.
That field (say Social Security Number) is a foreign key
Database Operations

The primary use of a database
–
–

9-9
To enable the user to obtain information from it in a usable
form by constructing queries
For a relational database, these queries are written in a
special language known as SQL (for Structured Query
Language),
Queries enable database users to find records from
a table that meet some stated criterion or to create
composite records from multiple tables that meet the
criterion.
Databases vs Arrays




9-10
A database table can store
multiple types of data in the
same table
Any changes to the
database are immediately
saved to disk as they occur.
We can use a sophisticated
database engine to handle
the processing.
It is very easy to connect
multiple computers to the
same database, so that any
changes are immediately
known to all computers.

Arrays are restricted to a
single data type.

Arrays are inherently
volatile, you need to write
code to “persist” changes
Array operations all require
customized code


No such immediate update,
still requires code
Databases in VB .NET

ADO.Net
–
–


9-11
Active Data Object .Net
The data access framework used in VB .Net
We use Access as database
Similar coding approach would work with any
database providing an OLEDB interface
Step-by-step 9-1:
Starting database project

9-12
Demo
VB .Net Data Objects and Wizards

Data Adapter object
–

DataSet
–

can be made up of one or more database tables
along with their relations
DataTable
–
9-13
Acts as intermediary between the database on
disk and disconnected in-memory representations
of the database called DataSets and DataTables
One table from the database
ADO.Net’s way: Disconnected Views




9-14
In ADO.Net typical programming, DataSet’s
and DataTable’s are disconnected
They are created from the database in
computer memory
They are manipulated in memory
The results are used to update the original
database on disk when desired
Data Adapters in VB .Net

Two types of data adapters in VB .NET
–
oleDbDataAdapter


–
sqlDbDataAdapter


9-15
Designed to work with a number of types of databases
including Access databases
The most generic adapter
Specifically designed to work with Microsoft SQL Server
databases
The most specific adapter
Data Objects in the Toolbox
9-16
Configuring a Data Adapter
1.
2.
3.
9-17
Set up database connection
Generate SQL to display fields from a table
of the database
Generate a Dataset
Setting up a database Connection
9-18
Setting up a database connection
9-19
Creating the SQL to display fields
9-20
The Query Builder
9-21
Generating a dataset

Two ways to generate a dataset
–
–

What it does
–
–
9-22
Click the “Generate Dataset” hyperlink below the
Property window when either the form or data
adapter control is selected
Select the Data|Generate Dataset menu option
Creates a class from which a DataSet object can
be instantiated
Instantiates an object from the class
Dataset Generation Dialog Box
9-23
Step-by-Step 9-2:
Connecting to the database

9-24
Demo
Displaying records in DataGrid


Add a DataGrid control to project
Set the DataSource property of the control
–
–

Fill the DataGrid by calling the Fill method of
the DataAdapter to which it is “bound”
–
9-25
Use the properties window
At this point, the grid knows the name of the
Fields but not the values of the records
oleDBDataAdapter1.Fill(DataSetName,TableName)
Searching for records
Need an SQL Query
–
General form
SELECT fieldnames
FROM tables
WHERE query
ORDER BY fieldname;
–
Use the * character to indicate all fields

–
9-26
SELECT * FROM MyTable
Not all parts of the general form are necessary
More SQL

SELECT * FROM Members WHERE Late_Fees > 10
–

SELECT * FROM Members ORDER By
Phone_Number
–
–

Order (ascending) the members by their phone number
To obtain a descending order, append DESC at the end
SELECT * FROM Members WHERE Phone_Number
= ‘706-555-1234’
–
–
9-27
Selects only those members with more than $10 in late fees
Failure to include the apostrophes will lead to an error
The apostrophes indicate a literal string value in SQL
Implementing a Search


Clear the Dataset
Create the appropriate query string
–

9-28
Usually on the fly according to some user input
Modify the data adapter to use the new query
string rather than the one created with the
Query Builder wizard during the data adapter
configuration process.
Code for the Search
Sub procedure to search for late fees
Sub FindLateFees(ByVal decAmount As Decimal)
Dim SelString As String
DsVintage1.Clear()
SelString =
"SELECT * “ & _
“FROM Members “ & _
“WHERE Late_Fees > Cstr(decAmount)”
MembersAdapter.SelectCommand.CommandText = SelString
MembersAdapter.Fill(DsVintage1, "Members")
End Sub
9-29
The LIKE operator

The general form of the query using the LIKE
Operator is:
–
–
–


9-30
SELECT * FROM
Table WHERE FieldName
LIKE ’%SearchString%’
% is the wild card character for strings
% can be replaced by any number of other
characters
Step-by-Step 9-3:
Displaying records in a DataGrid

9-31
Demo
Record Operations




9-32
Editing a record in the DataGrid by changing
one or more fields and saving the results
back to the database
Adding a new record to the database
Deleting an existing record
Reading records
Editing records

Editing a record that appears in the
DataGrid and saving the changes back to
the underlying database table is a two-step
process:
1.
2.
9-33
Make changes directly into the cell in the
DataGrid
Update these changes to modify the database
Adding a new record
1.
2.
Use a dialog form like that discussed in Chapter 8
to input multiple items of data and transfer them to
variables in the btnAdd_Click event procedure in
the frmMembers code window
Create a new row of the Dataset table composed of
the variable contents from Step 1

3.
9-34
Make sure no row already exists with same primary key
Add the new row to the Dataset table and update
the database.
Adding a Row to DataSet
Sub AddMembers(ByVal strName As String, ByVal strPhoneNumber As String)
‘Declare a DataRow variable
Dim Member_Row As DataRow
‘Create a new DataRow and assign to the DataRow variable
Member_Row = DsVintage1.Tables("Members").NewRow
‘Assign values to each of the fields
Member_Row("Name") = strName
Member_Row("Phone_Number") = strPhoneNumber
Member_Row("Late_Fees") = 0
‘Add the DataRow to the Rows collection of the table
DsVintage1.Tables("Members").Rows.Add(Member_Row)
‘Update the database with the changes made to it
MembersAdapter.Update(DsVintage1, "Members")
End Sub
9-35
Error Handling:
The Try-Catch structure
The complete form of the Try-Catch structure is:
Try
Programming statement to be tested
Catch variable As exception error
Programming statement to be executed
if error is caught
Finally
Programming statements to
be executed in all cases
End Try
9-36
Deleting a record
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnDelete.Click
Const strDelete As String = "Are you sure you want to delete this record?"
Dim strPhoneNumber As String, intNumRows, intResponse As Integer
strPhoneNumber = InputBox("Input phone number of person to be deleted.")
FindPhoneNumber(strPhoneNumber)
intNumRows = DsVintage1.Tables("Members").Rows.Count
If intNumRows = 0 Then
MsgBox("Nobody with that telephone number!")
Exit Sub
End If
intResponse = MsgBox(strDelete, vbYesNoCancel + vbCritical + vbDefaultButton2, "Delete Record")
9-37
If intResponse = vbYes Then DsVintage1.Tables("Members").Rows(0).Delete()
MembersAdapter.Update(DsVintage1, "members")
Else
MsgBox("Record not deleted", vbInformation, "Vintage")
End If
End Sub
FindPhoneNumber
Sub FindPhoneNumber(ByVal strPhNum As String)
Dim strSelectString As String
DsVintage1.Clear()
strSelectString =
“SELECT * “ & _
“FROM Members “ &
“WHERE " & _
"Phone_Number = '" & strPhNum & "'“
MembersAdapter.SelectCommand.CommandText = _
strSelectString
MembersAdapter.Fill(DsVintage1, "Members")
End Sub
9-38
Reading Matching Records

Need two new objects
–
–



9-39
oleDBCommand
oleDBDataReader
The oleDBCommand control can be dragged from
the toolbox
Use the oleDBCommand objet to generate the
oleDBDataReader
The oleDBDataReader is used to simply read
through a Dataset without making any changes
Code to read matching records
Private Sub btnPrint_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnPrint.Click
Dim datReader As OleDb.OleDbDataReader, strOutput As String
MembersConnection.Open()
‘MembersCommand is an OleDBCommand object
datReader = MembersCommand.ExecuteReader
While datReader.Read()
strOutput = datReader("Name").ToString() _
& vbTab & datReader("Phone_Number").ToString() _
& vbTab & _
Format(datReader("late_fees").ToString, "currency")
Debug.WriteLine(strOutput)
End While
End Sub
9-40
Step-by-Step 9-4:
Record Operations

9-41
Demo
Step-by-Step 9-5:
Renting a DVD

9-42
Demo
Copyright 2004 John Wiley & Sons, Inc.
All rights reserved. Reproduction or translation of this
work beyond that permitted in section 117 of the
1976 United States Copyright Act without express
permission of the copyright owner is unlawful.
Request for further information should be addressed
to the Permissions Department, John Wiley & Sons,
Inc. The purchaser may make back-up copies for
his/her own use only and not for distribution or
resale. The Publisher assumes no responsibility for
errors, omissions, or damages caused by the use of
these programs or from the use of the information
herein
9-43