Title: Click to edit Master title
11
2Objectives
- Define the terms used when talking about
databases - Explain the purpose of the DataAdapter,
Connection, and DataSet objects - Explain the role of the provider
- Create and configure an OleDbDataAdapter object
- Write SQL SELECT statements
3Objectives (continued)
- Create a dataset
- Display a dataset in various controls in an
interface - Position the record pointer in a dataset
4Database Terminology
- Database
- Organized collection of related information
stored in a file on a disk - Relational database stores information in tables
composed of columns and rows - Table is a group of related records
- Each row represents a record
- Each column represents a row
- Primary key is a field that uniquely identities a
record in a table - Foreign key is a field in one table used as the
primary key in another table - Tables are related by a common field or fields
5Database Terminology (continued)
6Database Terminology (continued)
7ADO.NET
- A Connection object is used to establish a
temporary connection to a database - Requests for information are then submitted using
a DataAdapter object - The DataAdapter uses a provider which translates
the request into a language the database
understands - A DataSet stores a copy of the records and
fields requested in a internal memory location
8ADO.NET (continued)
9ADO.NET (continued)
10Creating and Configuring a DataAdapter Object
11Creating and Configuring a DataAdapter Object
(continued)
12Creating and Configuring a DataAdapter Object
(continued)
13Creating and Configuring a DataAdapter Object
(continued)
14Creating and Configuring a DataAdapter Object
(continued)
15Creating and Configuring a DataAdapter Object
(continued)
16SQL
- Structured Query Language (SQL)
- A set of commands that allows you to access and
manipulate the data stored in many database
management systems on computers of all sizes - SELECT statement allow you to specify which
fields and records you want to view - WHERE clause limits records to be selected
- ORDER BY clause sorts record display order
17HOW TO
18HOW TO (continued)
19Using the Query Builder to Enter a SELECT
Statement
20Using the Query Builder to Enter a SELECT
Statement (continued)
21Using the Query Builder to Enter a SELECT
Statement (continued)
22Using the Query Builder to Enter a SELECT
Statement (continued)
23Using the Query Builder to Enter a SELECT
Statement (continued)
24Using the Query Builder to Enter a SELECT
Statement (continued)
25Using the Query Builder to Enter a SELECT
Statement (continued)
26Creating a DataSet
27Creating a DataSet (continued)
28Creating a DataSet (continued)
- XML (Extensible Markup Language)
- XML schema definition file defines the tables and
fields that make up the data set - Schema file is automatically generated by VB.NET
- Contents of file may be viewed in IDE
- Contents of dataset can be preview by right
clicking the DataAdapter object in the component
tray and clicking Preview Data
29Creating a DataSet (continued)
30Using the Fill Method
- Method of the DataAdapter object
- Fills a dataset with data while the application
is running
31Binding the Dataset Object to a DataGrid Control
- Connecting a DataSet object to a control is
called binding - Connected controls are referred to as bound
controls
32HOW TO
33Binding the Dataset Object to a DataGrid Control
(continued)
34Reconfiguring the DataAdapter Object
35Reconfiguring the DataAdapter Object (continued)
36Reconfiguring the DataAdapter Object (continued)
37Binding the Dataset Object to a Label Control or
Text box
38Binding the Dataset Object to a Label Control or
Text box (continued)
39Binding the Dataset Object to a Label Control or
Text box (continued)
40Binding the Dataset Object to a Label Control or
Text box (continued)
41Programming Example Cartwright Industries
Application
- Carl Simons, the sales manager at Cartwright
Industries, records the item number, name, and
price of each product the company sells in a
database named Items.mdb - The database is contained in the
VbDotNet\Chap12\Databases folder. - Mr. Simons wants an application that the sales
clerks can use to enter an item number and then
display the items price
42Programming Example Cartwright Industries
Application (continued)
- Items.mdb database opened in Microsoft Access.
Database contains one table tblItems
43TOE Chart
44User Interface
45Objects, Properties, and Settings
46Tab Order
47Pseudocode
- btnExit Click event procedure
- 1. close application
- frmCartwright Load event procedure
- 1. fill the dataset with the item numbers and
prices from the Items.mdb database
48Code
49Summary
- Databases created by Microsoft Access are
relational databases - Can contain one or more tables
- Most tables contain a primary key that uniquely
identifies each record - Data in relational database can be displayed in
any order, and you can control the amount of
information you want to view - Visual Basic .NET uses a technology called
ADO.NET to access the data stored in a database
50Summary (continued)
- Connection between a database and an application
that uses ADO.NET is only temporary - To access data stored in a database, first create
and configure a DataAdapter object, which is the
link between the application and the Connection
object - Use an OleDbDataAdapter object and an
OleDbConnection object in applications that
access Microsoft Access databases
51Summary (continued)
- DataAdapter, Connection, and DataSet objects are
stored in component tray - Use SQL SELECT statement to specify fields and
records for a dataset - Query Builder provides a convenient way to create
a SELECT statement - Dataset contains data you want to access from the
database - Data specified in SELECT statement associated
with DataAdapter object
52Summary (continued)
- Use DataAdapter objects Fill method to fill a
dataset with data while an application is running - Users view data stored in a dataset through
controls that are bound to DataSet object - Use DataGrid control to display records contained
in a dataset - When you change the SELECT statement associated
with a DataAdapter object, you must regenerate
the dataset