Click to edit Master title

1
1
2
Objectives

• 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

3
Objectives (continued)

• Create a dataset
• Display a dataset in various controls in an
  interface
• Position the record pointer in a dataset

4
Database 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

5
Database Terminology (continued)
6
Database Terminology (continued)
7
ADO.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

8
ADO.NET (continued)
9
ADO.NET (continued)
10
Creating and Configuring a DataAdapter Object
11
Creating and Configuring a DataAdapter Object
(continued)
12
Creating and Configuring a DataAdapter Object
(continued)
13
Creating and Configuring a DataAdapter Object
(continued)
14
Creating and Configuring a DataAdapter Object
(continued)
15
Creating and Configuring a DataAdapter Object
(continued)
16
SQL

• 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

17
HOW TO
18
HOW TO (continued)
19
Using the Query Builder to Enter a SELECT
Statement
20
Using the Query Builder to Enter a SELECT
Statement (continued)
21
Using the Query Builder to Enter a SELECT
Statement (continued)
22
Using the Query Builder to Enter a SELECT
Statement (continued)
23
Using the Query Builder to Enter a SELECT
Statement (continued)
24
Using the Query Builder to Enter a SELECT
Statement (continued)
25
Using the Query Builder to Enter a SELECT
Statement (continued)
26
Creating a DataSet
27
Creating a DataSet (continued)
28
Creating 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

29
Creating a DataSet (continued)
30
Using the Fill Method

• Method of the DataAdapter object
• Fills a dataset with data while the application
  is running

31
Binding 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

32
HOW TO
33
Binding the Dataset Object to a DataGrid Control
(continued)
34
Reconfiguring the DataAdapter Object
35
Reconfiguring the DataAdapter Object (continued)
36
Reconfiguring the DataAdapter Object (continued)
37
Binding the Dataset Object to a Label Control or
Text box
38
Binding the Dataset Object to a Label Control or
Text box (continued)
39
Binding the Dataset Object to a Label Control or
Text box (continued)
40
Binding the Dataset Object to a Label Control or
Text box (continued)
41
Programming 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

42
Programming Example Cartwright Industries
Application (continued)

• Items.mdb database opened in Microsoft Access.
  Database contains one table tblItems

43
TOE Chart
44
User Interface
45
Objects, Properties, and Settings
46
Tab Order
47
Pseudocode

• 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

48
Code
49
Summary

• 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

50
Summary (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

51
Summary (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

52
Summary (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