Microsoft Visual Basic 2005: Reloaded Second Edition

1
Microsoft Visual Basic 2005 Reloaded Second
Edition

• Chapter 11
• Using ADO.NET 2.0

2
Objectives

• After studying this chapter, you should be able
  to
• Define the terms used when talking about
  databases
• Connect a database to an application
• Bind table and field objects to controls
• Explain the purpose of the DataSet,
  BindingSource, TableAdapter, and BindingNavigator
  objects
• Access the records in a dataset
• Write SQL SELECT statements
• Create a query using the Query Configuration
  Wizard
• Associate a ToolStrip control with a query

3
Database Terminology

• Database an organized collection of related
  information stored in a file on a disk
• Relational database a database that stores
  information in tables composed of columns and
  rows
• Field a single item of information
• Record a group of related fields
• Table a group of related records
• Primary key a field that uniquely identifies
  each record

Field names / Columns
Records/ Rows
Table is a container for Records
4
Database Terminology (continued)
Field names / Columns
Primary Key field
Records/ Rows
5
Database Terminology (continued)

• Parent table contains a primary key
• Child table contains a foreign key from the
  parent table to link the tables
• Foreign key a field in a table that contains the
  primary key of another table
• Relational database advantages
• Less redundancy
• Fast retrieval
• Ability to selectively retrieve data

6
Database Terminology (continued)
A Relational Database allows tables to be related
/ connected by a common field in both tables
Parent Table has Primary Key in Relationship
Primary Key
Common field
Foreign Key
Child Table has Foreign Key in Relationship
Child Table has Foreign Key in Relationship
7
ADO.NET 2.0

• ADO.NET 2.0 a technology to connect an
  application to a database
• ADO ActiveX Data Objects
• Dataset a copy of fields and records that the
  application wants to access
• Connection to the database is temporary
• Only connected when retrieving data or making
  changes to data

Allows VB.NET front end .exe to work with a
relational database back end
ADO allows the VB to work with a copy of the
desired data from the database
VB connects with database when retrieving /
updating the data then disconnects
8
ADO.NET 2.0 (continued)
9
Connecting a Database to an Application
10
Connecting a Database to an Application
(continued)
Click Data Click Add New Data Source
11
Connecting a Database to an Application
(continued)
Add new Data Source clicked
Select Database and then Next
Click New Connection
12
Connecting a Database to an Application
(continued)
New Connection clicked
Make Selection (Access) and Continue
13
Connecting a Database to an Application
(continued)
Data source selected and Continue clicked
Browse and select the Access database .mdb to be
used
14
Connecting a Database to an Application
(continued)
Connection between VB and mdb ready
OK clicked after browsing to Access mdb
Click Next
15
Connecting a Database to an Application
(continued)
Data Connection selected, Next clicked
Select desired database object for VB
DataSet name established, click Finish
16
Connecting a Database to an Application
(continued)
Click Data Sources to see dataset
17
Previewing the Data Contained in a Dataset
Server Explorer, right click on the tables name,
then click Show Table Data
18
Previewing the Data Contained in a Dataset
Data Sources, right click on the tables name,
then click Preview Data
Click Preview
12 Fields, 39 Records
19
Binding the Objects in a Dataset

• Binding connecting an object in a dataset to a
  control
• Bound controls controls that are connected to an
  object in a dataset
• Can bind an object to
• An existing control in the interface
• A control the computer creates for you

Dataset objects, (fields)
20
Binding the Objects in a Dataset (continued)
21
Having the Computer Create a Bound Control
(continued)
22
Having the Computer Create a Bound Control
(continued)
23
Having the Computer Create a Bound Control

• To allow the computer to create a bound control
• Drag the object from the dataset to the form
• DataGridView control displays table data in a
  row and columnar format
• Use the list arrow next to an objects name to
  change the type of control to be created

24
Binding the Objects in a Dataset (continued)
BindingNavigator control
Click tblCust arrow to select the bound control
to be created. This is an example of the DataGrid
option which creates a spreadsheet type control
when dragged onto the form
25
Having the Computer Create a Bound Control
(continued)
26
Having the Computer Create a Bound Control
(continued)

• Four objects are placed in the component tray
• DataSet Stores the information accessed from the
  database
• BindingSource
• Provides the connection between the DataSet
  object and the controls on the form
• TableAdapter connects the database to the
  DataSet object
• BindingNavigator allows user to navigate the
  dataset

27
Binding the Objects in a Dataset (continued)
AutoSizeColumnsMode Fill will adjust column
widths so that all columns exactly display the
display area
28
Application with DataGrid control
29
Binding to an Existing Control

• Two ways to bind to an existing control
• Drag an object in the dataset to a control on the
  form
• Select the control and set properties
• Properties to bind the control are specific to
  the control
• DataSet, BindingSource, and TableAdapter objects
  are added to the component tray
• BindingNavigator control is NOT added
  automatically

30
Binding the Objects in a Dataset (continued)
Click tblCust arrow to select the bound control
to be created. This is an example of the Details
option which creates a separate control for each
field object when dragged onto the form
31
Having the Computer Create a Bound Control
(continued)
32
Having the Computer Create a Bound Control
(continued)

• Two event procedures are automatically created in
  Code Editor window
• bindingNavigatorSaveItem_Click
• MainForm_Load
• bindingNavigatorSaveItem_Click event
• Saves any changes made to the dataset
• EndEdit method applies pending changes
• Update method commits the changes to the database

33
Having the Computer Create a Bound Control
(continued)
EndEdit() is created to apply any pending changes
to the dataset
Update() is created to commit the changes to the
dataset to the source database
Created to retrieve data from the database and
store it in the dataset
34
Application with Details and DataGrid
Both DataGrid and Details controls are displayed.
BindingNavigator control works for both.
35
Accessing the Records in a Dataset (continued)
36
Navigating with Buttons
37
Navigating with Buttons
38
Accessing the Records in a Dataset (continued)
39
Navigating with Buttons
40
Navigating with Buttons
41
DataSet Designer

• DataSet Designer
• Indicates the order in which to display the data
• Specifies fields and records to be viewed

42
DataSet Designer (continued)
43
DataSet Designer (continued)
Right click Data Sources window, click Edit
dataset with Designer
44
DataSet Designer (continued)

• Extensible Markup Language (XML) a text-based
  language used to store and share data between
  applications and across networks
• XML schema definition file
• Defines the tables and fields that make up the
  dataset
• Has extension of .xsd
• Query
• Specifies the fields and records to retrieve from
  the database
• Specifies the field order

45
DataSet Designer (continued)
Right click query area, then click Configure
Query created by VB based upon original dataset
specified
46

• Structured Query Language (SQL) a set of
  commands to access and manipulate database data
• SELECT statement
• Used to specify the fields and records to
  retrieve, as well as the order of display
• WHERE clause limit the records to be selected
• ORDER BY clause control the order of display

47
Creating a New Query
48
DataSet Designer (continued)
Right click query area, then click Add Query
Wizard to help create the query
49
Creating a New Query (continued)
50
Creating a New Query (continued)
Current dataset query
51
Creating a New Query (continued)
Click Query Builder for help if you do not know
SQL
52
Creating a New Query (continued)
Use Query Builder to get only CA customers
Click OK
53
Creating a New Query (continued)
New query displayed in SQL
Click Finish
54
Creating a New Query (continued)
New query methods are now available
55
Allowing the User to Run a Query
56
Allowing the User to Run a Query
Right click Adapter object and click Add Query
57
Allowing the User to Run a Query (continued)
Existing query name Select query for CA
58
Allowing the User to Run a Query (continued)
Dataset has default all records
59
Allowing the User to Run a Query (continued)
Dataset has CA only records from FillBy