objectives
DESCRIPTION
1. 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. Objectives ( continued ). Create a dataset - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/1.jpg)
Using ADO.NET
Chapter Microsoft Visual Basic .NET: Reloaded
1
![Page 2: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/2.jpg)
2Microsoft Visual Basic .NET: Reloaded
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
![Page 3: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/3.jpg)
3Microsoft Visual Basic .NET: Reloaded
Objectives (continued)
• Create a dataset
• Display a dataset in various controls in an interface
• Position the record pointer in a dataset
![Page 4: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/4.jpg)
4Microsoft Visual Basic .NET: Reloaded
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
![Page 5: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/5.jpg)
5Microsoft Visual Basic .NET: Reloaded
Database Terminology (continued)
![Page 6: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/6.jpg)
6Microsoft Visual Basic .NET: Reloaded
Database Terminology (continued)
![Page 7: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/7.jpg)
7Microsoft Visual Basic .NET: Reloaded
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
![Page 8: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/8.jpg)
8Microsoft Visual Basic .NET: Reloaded
ADO.NET (continued)
![Page 9: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/9.jpg)
9Microsoft Visual Basic .NET: Reloaded
ADO.NET (continued)
![Page 10: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/10.jpg)
10Microsoft Visual Basic .NET: Reloaded
Creating and Configuring a DataAdapter Object
![Page 11: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/11.jpg)
11Microsoft Visual Basic .NET: Reloaded
Creating and Configuring a DataAdapter Object (continued)
![Page 12: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/12.jpg)
12Microsoft Visual Basic .NET: Reloaded
Creating and Configuring a DataAdapter Object (continued)
![Page 13: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/13.jpg)
13Microsoft Visual Basic .NET: Reloaded
Creating and Configuring a DataAdapter Object (continued)
![Page 14: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/14.jpg)
14Microsoft Visual Basic .NET: Reloaded
Creating and Configuring a DataAdapter Object (continued)
![Page 15: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/15.jpg)
15Microsoft Visual Basic .NET: Reloaded
Creating and Configuring a DataAdapter Object (continued)
![Page 16: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/16.jpg)
16Microsoft Visual Basic .NET: Reloaded
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
![Page 17: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/17.jpg)
17Microsoft Visual Basic .NET: Reloaded
HOW TO…
![Page 18: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/18.jpg)
18Microsoft Visual Basic .NET: Reloaded
HOW TO… (continued)
![Page 19: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/19.jpg)
19Microsoft Visual Basic .NET: Reloaded
Using the Query Builder to Enter a SELECT Statement
![Page 20: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/20.jpg)
20Microsoft Visual Basic .NET: Reloaded
Using the Query Builder to Enter a SELECT Statement (continued)
![Page 21: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/21.jpg)
21Microsoft Visual Basic .NET: Reloaded
Using the Query Builder to Enter a SELECT Statement (continued)
![Page 22: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/22.jpg)
22Microsoft Visual Basic .NET: Reloaded
Using the Query Builder to Enter a SELECT Statement (continued)
![Page 23: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/23.jpg)
23Microsoft Visual Basic .NET: Reloaded
Using the Query Builder to Enter a SELECT Statement (continued)
![Page 24: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/24.jpg)
24Microsoft Visual Basic .NET: Reloaded
Using the Query Builder to Enter a SELECT Statement (continued)
![Page 25: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/25.jpg)
25Microsoft Visual Basic .NET: Reloaded
Using the Query Builder to Enter a SELECT Statement (continued)
![Page 26: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/26.jpg)
26Microsoft Visual Basic .NET: Reloaded
Creating a DataSet
![Page 27: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/27.jpg)
27Microsoft Visual Basic .NET: Reloaded
Creating a DataSet (continued)
![Page 28: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/28.jpg)
28Microsoft Visual Basic .NET: Reloaded
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
![Page 29: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/29.jpg)
29Microsoft Visual Basic .NET: Reloaded
Creating a DataSet (continued)
![Page 30: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/30.jpg)
30Microsoft Visual Basic .NET: Reloaded
Using the Fill Method
• Method of the DataAdapter object• Fills a dataset with data while the application
is running
![Page 31: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/31.jpg)
31Microsoft Visual Basic .NET: Reloaded
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”
![Page 32: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/32.jpg)
32Microsoft Visual Basic .NET: Reloaded
HOW TO…
![Page 33: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/33.jpg)
33Microsoft Visual Basic .NET: Reloaded
Binding the Dataset Object to a DataGrid Control (continued)
![Page 34: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/34.jpg)
34Microsoft Visual Basic .NET: Reloaded
Reconfiguring the DataAdapter Object
![Page 35: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/35.jpg)
35Microsoft Visual Basic .NET: Reloaded
Reconfiguring the DataAdapter Object (continued)
![Page 36: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/36.jpg)
36Microsoft Visual Basic .NET: Reloaded
Reconfiguring the DataAdapter Object (continued)
![Page 37: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/37.jpg)
37Microsoft Visual Basic .NET: Reloaded
Binding the Dataset Object to a Label Control or Text box
![Page 38: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/38.jpg)
38Microsoft Visual Basic .NET: Reloaded
Binding the Dataset Object to a Label Control or Text box (continued)
![Page 39: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/39.jpg)
39Microsoft Visual Basic .NET: Reloaded
Binding the Dataset Object to a Label Control or Text box (continued)
![Page 40: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/40.jpg)
40Microsoft Visual Basic .NET: Reloaded
Binding the Dataset Object to a Label Control or Text box (continued)
![Page 41: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/41.jpg)
41Microsoft Visual Basic .NET: Reloaded
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 item’s price
![Page 42: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/42.jpg)
42Microsoft Visual Basic .NET: Reloaded
Programming Example – Cartwright Industries Application (continued)
• Items.mdb database opened in Microsoft Access. Database contains one table tblItems
![Page 43: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/43.jpg)
43Microsoft Visual Basic .NET: Reloaded
TOE Chart
![Page 44: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/44.jpg)
44Microsoft Visual Basic .NET: Reloaded
User Interface
![Page 45: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/45.jpg)
45Microsoft Visual Basic .NET: Reloaded
Objects, Properties, and Settings
![Page 46: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/46.jpg)
46Microsoft Visual Basic .NET: Reloaded
Tab Order
![Page 47: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/47.jpg)
47Microsoft Visual Basic .NET: Reloaded
Pseudocode
btnExit Click event procedure1. close application
frmCartwright Load event procedure1. fill the dataset with the item numbers and prices from the Items.mdb database
![Page 48: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/48.jpg)
48Microsoft Visual Basic .NET: Reloaded
Code
![Page 49: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/49.jpg)
49Microsoft Visual Basic .NET: Reloaded
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
![Page 50: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/50.jpg)
50Microsoft Visual Basic .NET: Reloaded
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
![Page 51: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/51.jpg)
51Microsoft Visual Basic .NET: Reloaded
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
![Page 52: Objectives](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681678a550346895ddca4aa/html5/thumbnails/52.jpg)
52Microsoft Visual Basic .NET: Reloaded
Summary (continued)
• Use DataAdapter object’s 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