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

3
Objectives (continued)

• Write SQL SELECT statements
• Create a query using the Query Configuration
  Wizard
• Associate a ToolStrip control with a query

4
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

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

8
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

9
ADO.NET 2.0 (continued)
10
Connecting a Database to an Application
11
Connecting a Database to an Application
(continued)
12
Connecting a Database to an Application
(continued)
13
Previewing the Data Contained in a Dataset
14
Previewing the Data Contained in a Dataset
(continued)
15
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

16
Binding the Objects in a Dataset (continued)
17
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

18
Having the Computer Create a Bound Control
(continued)
19
Having the Computer Create a Bound Control
(continued)
20
Having the Computer Create a Bound Control
(continued)

• DataGridView control
• AutoSizeColumnsMode property select Fill to
  automatically adjust column widths to exactly
  fill the display area
• BindingNavigator control allows you to move from
  one record to the next in a dataset
• Four objects are placed in the component tray
• DataSet
• BindingSource
• TableAdapter
• BindingNavigator

21
Having the Computer Create a Bound Control
(continued)
22
Having the Computer Create a Bound Control
(continued)

• TableAdapter object connects the database to the
  DataSet object
• DataSet object stores the information to be
  accessed from the database
• BindingSource object connects the DataSet object
  to the bound controls on the form

23
Having the Computer Create a Bound Control
(continued)
24
Having the Computer Create a Bound Control
(continued)
25
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

26
Having the Computer Create a Bound Control
(continued)

• MainForm_Load event
• Fill method TableAdapter objects method to
  retrieve data from the database and store it in
  the dataset
• DataGridView control allows data to be edited
  directly within the control to update the database

27
Having the Computer Create a Bound Control
(continued)
28
Having the Computer Create a Bound Control
(continued)

• BindingNavigator control
• Allows movement to first, last, next, or previous
  record
• Allows direct selection of record by number
• Allows you to add or delete a record
• Allows you to save changes made to the dataset

29
Having the Computer Create a Bound Control
(continued)
30
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

31
Binding to an Existing Control (continued)
32
Binding to an Existing Control (continued)
33
Binding to an Existing Control (continued)
34
Accessing the Records in a Dataset

• BindingSource properties
• Position stores position of current record
  position number is zero-relative
• Move moves the record pointers position

35
Accessing the Records in a Dataset (continued)
36
Accessing the Records in a Dataset (continued)
37
Accessing the Records in a Dataset (continued)
38
Accessing the Records in a Dataset (continued)
39
DataSet Designer

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

40
DataSet Designer (continued)
41
DataSet Designer (continued)
42
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

43
DataSet Designer (continued)
44
DataSet Designer (continued)

• 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

45
DataSet Designer (continued)
46
Creating a New Query
47
Creating a New Query (continued)
48
Creating a New Query (continued)
49
Creating a New Query (continued)
50
Creating a New Query (continued)
51
Using the Query Builder Dialog Box
52
Allowing the User to Run a Query
53
Allowing the User to Run a Query (continued)
54
Allowing the User to Run a Query (continued)
55
Allowing the User to Run a Query (continued)
56
Programming Tutorial
57
Programming Example
58
Summary

• 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
• Primary key a field in a database table that
  uniquely identifies each record
• ADO.NET 2.0 a technology to access data in a
  database

59
Summary (continued)

• You must connect the application to a database to
  create a dataset
• Display dataset information by binding controls
  to dataset objects
• TableAdapter connects a database to a DataSet
  object
• BindingSource object connects a DataSet object
  to bound controls on a form
• BindingSource objects Position property stores
  the location of the record pointer in a dataset

60
Summary (continued)

• BindingSource objects Move methods used to move
  the record pointer in a dataset
• SQL SELECT statement specifies the fields and
  records to include in a dataset
• Query Configuration Wizard creates queries
• Query Builder dialog box provides a convenient
  way to create a SELECT statement
• Associate a query with a ToolStrip control on a
  form