Transcript Lecture 4.11.2011 - Databases Part 2

Programming in Visual Basic 2010:
The Very Beginner’s Guide
Chapter 14.2
LINQ to SQL
Databases – Part 2
by Jim McKeown
Let’s Review
2
Connecting to a Database Tutorial
What Just Happened?
•
•
•
•
•
•
3
Wizard sets up connection between program and
database
Temporary connection established long enough to
pass the data back and forth when needed
Handles the tricky stuff
Solution Explorer has an icon for the NEC.mdb database
Double click to open the file in Access
(I did not see this)
Connecting to a Database Tutorial
What Just Happened?
•
•
•
•
Select Database Explorer:
 Go to View, Other Windows, Select Database
Explorer
Tabs at the bottom toggle between Toolbox and
Database Explorer
Database Explorer lists the data connections in your
project
Database Explorer
4
•
Window lists the database connections for a project
5
Connecting to a Database Tutorial
Expand the view of the database in DB Explorer
Lists tables and other contents for NEC file
Note: we only have a single table (relation)
No views, stored procedures, or functions
Serves has a handy reference
Remember: DataSet stores data from the database in the computer’s
memory for processing, updating, manipulation, queries, etc.
6
Connecting to a Database Tutorial
NEC
Screen
7
Connecting to a Database Tutorial
Component Tray: Three Items:
NecDataSet
Stores the data from the database into computer’s
memory while program is running.
•
•
•
•
8
Database are usually on a database server on the network
and it’s hard to maintain that connection
By creating a dataset in memory from the database itself, this
frees the server to handle other requests
Faster access when the data is in memory
Changes to the data are stored in the DataSet and then
updated in the database all at once
Connecting to a Database Tutorial
BindingSource
• Stores connection information and
• Provides navigation, filtering, sorting, updating capabilities
• Pulls together data from various tables in a database
•
(We always have more than a single table!!)
TableAdapter
Takes data from the tables and passes them back and forth
between the program and the database.
All three needed to create a connection, get the data and
manage it while the program runs
9
Closer Look: Basic Operations
Established connection to NEC file,
Established DataSet from NEC file.
Look at DataGrideView. (see next slide)
DataSource of this DataGridView is the
NecDataSet
Look at DataGridView: Populate it?
DataGridView populated when app is run
(See two slides down)
Most of the time only some of the data are needed
10
11
12
Closer Look: Editing the View
Click on the DataGridView control
Click Edit Columns Link (bot of Properties window)
We can now change our view! (what we are
interested in)
Can add, remove, change order…. of columns
Can remove a field (remove a few)
13
Can add: select add button and all available fields are
listed so you can add buttons you’ve removed.
Can reorder fields: select and use up / down arrows
Can also see the bound column properties descriptions
on the right.
Closer Look: the HeaderText
This property under Appearance in the Properties
Window contains the column headings;
(next two slides together)
Used it to rename columns in DataGridView
14
•
(Change HeaderText for First to F-Name; press OK
•
Changed it in column names but DataPropertyName is still First)
•
Nice for renaming columns in a report.
•
Does not appear to change the attribute (field) name!!
Connecting to a Database Tutorial
Edit
Columns
Dialog
15
Closer Look: Table Adaptor Properties
Select the NECTableAdapter in component tray.
See links in properties window.
Click on Preview Data... And then Preview
•
Displays all columns / rows currently in DataSet
Note: from this view you may select a Headertext items and the rows
(records) are sorted based on ascending / descending features.
Note Also: number of columns / rows at left bottom; tells number of
records (rows) and fields (attributes).
Note: I changed ID to Identification in HeaderText using Edit Columns
when selecting the Grid. Did not change overall attribute name; did
change headertext in Grid itself when displayed.
16
VB Quiz 01
What is the name of the database used in this example?
How many fields (columns) are in the database?
Does the number of records (rows) in the database in this
example matter?
What is the name of the DataSet?
Why is the connection to the database a temporary one?
Think about how changes to the records would end up in
the database.
Did you move attributes around in the DataSetGrid) Add,
Remove, Change order? What specifically did you do?
Results?
17
Wages Tutorial – Exciting Stuff!!
Now we get to the good stuff: populating controls from the
dataset!
We want to connect controls to our database!
Once done, we can access (for our example coming up)
employee data, calculate wages, and update records
(data from our databases into our programs)!
Want to populate controls (textboxes, labels, …) from our
database.
What a deal!!
18
Tutorial to Populate Controls
Access the Wages database file from my
webpage. Wages.mdb
Open in Microsoft Access to see what it looks like.
Of course, you can view it from VB as well.
Be sure to close it before you start the tutorial.
19
Wages Tutorial
Create a form that looks like the following and name the
controls
Done
Create a new BindingSource from Toolbox and name it
bdsWages
Done; BindingSource then appears in the component tray
Select DataSource property for bdsWages BindingSource
In the dropdown select Add Project Data Source...
Recall: Window opens a wizard to select a database and determine
the settings and connection
20
Wages Tutorial
Wages
Tutorial
Screen
21
Wages Tutorial –
Selecting and Connecting to our Database
Select Database as source type and Next
Select DataSet and Next
(this step and next five are the same as the first tutorial)
Select New Connection... button to create connection to a
database
Select Change... and change Data source option to
Microsoft Access Database File
Click OK to establish Access as the database type for use
in this program
22
Wages Tutorial
(Add Connection dialog set up to work with Access)
Select Browse... and locate the Wages.mdb file
Select it and click Open to add filepath to to Add
Connection Dialog box
Test Connection button;
OK to proceed.
Next
Save Connection:
Next
23
Wages Tutorial
24
Select Tables and Expand Tables
Click on tblWages
Expand the tblWages table for list of fields
Click Finish
Component tray now has a WagesDataSet
DataSource property of the BindingSource bdsWages
shows it’s linked to the WagesDataSet
Please check this out to verify.
Select the DataMember property of the BindingSource and set
it to tblWages
This now adds the tblWages TableAdapter to the
component tray
Note: component tray has the binding source, the data set and
the table adaptor.
Wages Tutorial – Bind Controls to Data
Select each control in turn and bind to a field. (next three
slides after this one. But this is the one…)
Pick a textbox from form (tbxEmpID for me).
Go to Properties Window
Go to DataBindings property (near top of Window); Expand
Select Text property under DataBindings
Select Drop Down box (says none)
Select bdsWages, expand it (double click) and select the
ID property (in this case for me) that I am trying to bind to
the control.
25
Be sure to use the Text property in the DataBindings and not the regular
Text property Should say bdsWages - ID
Regular Text property has a canister in it to indicate the property is bound to
a data field
Wages Tutorial
Wages
Tutorial
Component
Tray
26
Wages Tutorial
Wages
Select ID
Popup
27
Wages Tutorial
Repeat this for other TextBoxes to bind them to the
appropriate data fields
Do This.
Save your project
Database connection established and control
are bound to the DataSet
It works!!!!
Program “works” but only displays the first record
No way to navigate through the records
28 Program needs a BindingNavigator
Wages Tutorial – Binding Navagator
The BindingNavigator is the user interface to
move through the records in a DataSet
Go to your Toolbox and Add a BindingNavigator
to top of the form; Drag from Toolbox
Name it bdnWages (Name property)
(Adds bdnWages to Component Tray)
BindingNavigator
provides a user interface for navigation and
29
manipulation of data bound to controls on a form
Wages Tutorial- Appearance
Wages
Tutorial
Toolbar
30
Wages Tutorial – go through dataset
Set BindingSource property for BindingNavigator to
bdsWages to link it to data
Run program.
Move through records using BindingNavigator
Each record shows up in the TextBoxes
Go to Source Code Window: One line of code in the Load event
Me.TblWagesTableAdapter.Fill(Me.WagesDataSet.tblWages)
This statement fills the TableAdapter with data when the
program starts
31
Wages Tutorial - Navigator Options.
Some buttons on BindingNavigator aren’t needed
Right click on bdnWages BindingNavigator and
select Insert Standard Items from drop down.
Adds many standard buttons including Cut, Copy,
Paste, New, Open, Save, Print and Help
Remove all except the Save button
Right click on a button and select Delete to
remove it
32
Wages Tutorial – Adding Options
Insert a new Button
Right Click on the bdnWages BindingNavigator to get a
dropdown
Select Button from the list
Add Button
Right click on Button
Select Set Image...
(or select Image from the Properties)
Browse until you find the Exit.gif file
Replace the image with it
Double Click on Exit button
Key in End
33
Wages Tutorial – Save Button
Save Button must be coded
Click on the Save Button and insert this code.
Try
bdsWages.EndEdit()
‘
attempts to end the editing
TblWagesTableAdapter.Update(WagesDataSet.tblWages)
‘ needed to update the database
.
Catch ex As Exception
MessageBox.Show("An error has occurred.", "Save",
MessageBoxButtons.OK, MessageBoxIcon.Error)
34
End Try
Wages Tutorial
Try/Catch prevents a crash if there’s an error and
databases are finicky
Two lines in Try
first line says to EndEdit for the data
second line updates the DataSet and the file
Until the update, changes to a record are not sent
to the file
35
Wages Tutorial – Delete Button
Code the Delete Button
Try
WagesDataSet.tblWages.Rows(bdsWages.Position).Delete()
‘ This line deletes current row in Dataset
‘ current row determined by position in the
‘ binding source.
TblWagesTableAdapter.Update(WagesDataSet.tblWages)
‘ Updates the dataset and file.
Catch ex As Exception
MessageBox.Show("An error has occurred.", "Save",
MessageBoxButtons.OK, MessageBoxIcon.Error)
36
End Try
Wages Tutorial – Clearing Add Button
Add this line to the Add Button
lblWages.Text = ""
It clears the text from the Label so there’s no
confusion when a new record is added
37
Wages Tutorial – Adding Wages
Add this code to the Wages Button
Dim decWages As Decimal
Dim sngHours As Single
Dim decPayRate As Decimal
sngHours =
WagesDataSet.tblWages.Item(bdsWages.Position)(5)
decPayRate =
WagesDataSet.tblWages.Item(bdsWages.Position)(4)
decWages = sngHours * decPayRate
38
lblWages.Text = decWages.ToString("c")
Wages Tutorial – Calculating Results
First two lines get values from DataSet based on Position
Position is the current record in a DataSet
Last number is a field number
Similar to how fields were split and assigned to variables using file I/O
Field 4 is the PayRate field
Field 5 is the number of Hours
Next line calculates their wages
Last line displays their wages in the Label
39 Position: the current record in a DataSet
Wages Tutorial
Finished
Wages
Tutorial
40
Wages Tutorial
Wrap-up
•
•
•
•
•
•
41
Connecting a control to a field from a database is an easy
way to display and manipulate data
BindingNavigator makes navigation easier
Little code is needed to add, delete and update records
VB interface makes it easy to work with data
Interfaces for most databases are hard to work with
Easy to customize the interface and make it appealing to
users
Potential Problems
Be sure to select the correct connection type
Check DataSet to ensure it includes correct data
Be sure to bind proper fields with correct control
Be sure to use Text property under DataBindings
for your connections
42
Potential Problems
Be sure to add the code to the Load event to fill the
DataSet
Never try to open a database in two programs at
once
Always keep a backup of your data
43