Chapter 12: ADO.NET and ASP.NET

1
Chapter 12 ADO.NET and ASP.NET
Programming with Microsoft Visual Basic .NET,
Second Edition
2
Database TerminologyLesson A 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

3
Database TerminologyLesson A Objectives
(continued)

• Write SQL SELECT statements
• Create a dataset
• Display a dataset in a DataGrid control

4
Database Terminology (continued)

• Database organized collection of related
  information stored in a file on a disk
• Relational database database that stores
  information in tables
• Each column represents a field
• Each row represents a record

5
Database Terminology (continued)

• Table a group of related records
• Each record in a table pertains to the same
  topic, and each contains the same type of
  information
• Relational database contains one or more tables
• Primary key a field that uniquely identifies
  each record in a table

6
Database Terminology (continued)
Figure 12-3 Example of a two-table relational
database
7
Database Terminology (continued)

• In Figure 12-3
• The first table is called the parent table
• The second table is called the child table
• In the parent table, the Number field is the
  primary key
• In the child table, the Number field is called
  the foreign key

8
ADO.NET

• To connect an application to a database, Visual
  Basic .NET uses ADO.NET
• With ADO.NET, the connection between an
  application and a database is a temporary one
• Use three ADO.NET objects and a provider to
  access a database from a Visual Basic .NET
  application
• DataAdapter, Connection, and DataSet

9
ADO.NET (continued)
Figure 12-4 Illustration of the relationships
among an application, the ADO.NET objects, a
provider, and a database
10
Creating and Configuring a DataAdapter Object

• The DataAdapter object is the link between the
  application and the Connection object
• DataAdapter object contacts the Connection object
  whenever the application needs to read data from
  or write data to a database

11
Creating and Configuring a DataAdapter Object
(continued)
Figure 12-6 Procedure for creating and
configuring a DataAdapter object for a Microsoft
Access database
12
Creating and Configuring a DataAdapter Object
(continued)
Figure 12-6 Procedure for creating and
configuring a DataAdapter object for a Microsoft
Access database (continued)
13
SQL

• SQL (Structured Query Language) a set of
  commands to access and manipulate the data stored
  in many database management systems
• SQL commands perform database tasks such as
  storing, retrieving, updating, deleting, and
  sorting

14
SQL (continued)

• SELECT statement allows you to
• Specify the fields and records you want to view
• Control the order in which the fields and records
  appear when displayed

15
SQL (continued)
Figure 12-13 Syntax and examples of the SELECT
statement
16
SQL (continued)
Figure 12-13 Syntax and examples of the SELECT
statement (continued)
17
Using the Query Builder to Enter a SELECT
Statement

• When you click the Next gt button on the Choose a
  Query Type screen, the Generate the SQL
  statements screen appears
• You can enter the SELECT statement yourself, or
  you can have the Query Builder enter it for you
• To use the Query Builder, click the Query Builder
  button
• The Query Builder and Add Table dialog boxes open

18
Using the Query Builder to Enter a SELECT
Statement (continued)
Figure 12-15 Query Builder and Add Table dialog
boxes
19
Using the Query Builder to Enter a SELECT
Statement (continued)
Figure 12-18 SELECT statement entered in the
Generate the SQL statements screen
20
Creating a Dataset

• A dataset contains the data you want to access
  from the database, as specified in the SELECT
  statement associated with the DataAdapter object
• XML (Extensible Markup Language) a text-based
  language used to store and share data between
  applications and across networks and the Internet
• An XML schema definition file defines the tables
  and fields that make up the dataset

21
Creating a Dataset (continued)
Figure 12-21 Procedure for creating a dataset
22
Using the Fill Method

• Use the DataAdapter objects Fill method to fill
  a dataset with data while an application is
  running

Figure 12-25 Syntax and an example of the Fill
method
23
Binding the DataSet Object to a DataGrid Control

• View the data contained in a dataset by
  connecting its DataSet object to one or more
  controls in the interface
• Binding Connecting a DataSet object to a control
• Bound controls the connected controls
• Bind a control using one or more properties
  listed in the Properties window

24
Binding the DataSet Object to a DataGrid Control
(continued)
Figure 12-26 Procedure for binding a DataSet
object to a DataGrid control
25
Binding the DataSet Object to a DataGrid Control
(continued)

• When bound to a DataSet object, the DataGrid
  control displays the data from the dataset in a
  row and column format
• Each field in the dataset appears in a column in
  the DataGrid control
• Each record in the dataset appears in a row in
  the DataGrid control

26
Reconfiguring the DataAdapter Object
Figure 12-30 Procedure for reconfiguring an
existing DataAdapter object
27
More on Binding ControlsLesson B Objectives

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

28
Binding the DataSet Object to a Label Control or
a Text Box
Figure 12-33 Procedure for binding a DataSet
object to a label control or text box
29
Coding the 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
• Items.mdb database
• Contains one table named tblItems
• The Number and Name fields contain text, and the
  Price field contains numbers

30
Coding the Cartwright Industries Application
(continued)
Figure 12-37 Items.mdb database opened in
Microsoft Access
31
Coding the Cartwright Industries Application
(continued)
Figure 12-39 TOE chart for the Cartwright
Industries application
32
Creating Web Applications Using ASP.NETLesson C
Objectives

• Define the terms used when talking about the Web
• Create a Web application
• Add controls to a Web form
• Start a Web application

33
Creating Web Applications Using ASP.NET Lesson C
Objectives (continued)

• Use the validator controls
• Include a list box on a Web form
• Determine whether a postback has occurred
• Include a DataGrid control on a Web form

34
Web Terminology

• The Internet is the worlds largest computer
  network, connecting millions of computers located
  all around the world
• World Wide Web (WWW or the Web)
• Part of the Internet
• Consists of documents called Web pages that are
  stored on Web servers

35
Web Terminology (continued)

• Web server computer that contains software that
  serves up Web pages in response to requests
  from clients
• A client is a computer that requests information
  from a Web server
• The information is requested and subsequently
  viewed through the use of a program called a Web
  browser (or simply, a browser)

36
Web Terminology (continued)
Figure 12-49 Illustration of the relationship
between a client, a browser, and a Web server
37
Web Terminology (continued)

• Static Web page document whose purpose is merely
  to display information to the viewer
• Dynamic Web page is interactive it can accept
  information from the user and also retrieve
  information for the user
• Every Web page has a unique address called a URL
  (Uniform Resource Locator) that indicates its
  location on the Web

38
Creating Web Applications

• Use a Web form to create a Web page in Visual
  Basic .NET
• Create (or design) the Web page in the Web Form
  Designer window

39
Creating Web Applications (continued)
Figure 12-52 Procedure for creating a Web
application
40
Adding Controls to a Web Form

• Use the tools contained in the Toolbox window to
  add controls to a Web form
• The tools for a Web form are located on the Web
  Forms tab in the toolbox
• Can add a control to a Web form by simply
  dragging the corresponding tool from the Web
  Forms tab to the form

41
Using the Web Validator Tools

• These tools allow you to validate user input

Figure 12-65 The Web validator tools
42
Including a List Box on a Web Form

• Use the ListBox tool on the Web Forms tab to add
  a list box to a Web form
• Can use a list box on a Web form to display a
  list of choices from which the user can select
  one or more choices

43
Including a List Box on a Web Form (continued)

• A postback refers to the client requesting data
  from the server, and the server responding
• Each time a postback occurs, the Web page is
  redisplayed on the clients screen
• Can use the Web forms IsPostBack property to
  determine if the Web form is being displayed for
  the first time or as a result of a postback

44
Including a DataGrid Control on a Web Form

• DataGrid control displays the data from a dataset
  in a row and column format
• Each field in the dataset appears in a column in
  the DataGrid control, and each record appears in
  a row
• The DataGrid controls DataBind method is used to
  bind the control to the dataset

45
Including a DataGrid Control on a Web Form
(continued)
Figure 12-72 Web form for the Fairview
application
46
Including a DataGrid Control on a Web Form
(continued)

• DataGrid control provides many properties to
  control the appearance of its output
• DataGrid control provides an Auto Format dialog
  box that allows selection from a list of
  predefined formats, or schemes, for displaying
  data

47
Summary

• To access a database from a Visual Basic .NET
  application, use the DataAdapter, Connection, and
  DataSet objects
• Use SQL SELECT to specify the fields and records
  to view, and to control the order in which the
  fields and records appear when displayed
• Use the Fill method to fill a dataset with data
  while an application is running

48
Summary (continued)

• To bind a DataSet object to a DataGrid control
• Set DataGrid controls DataSource property to the
  name of the DataSet object
• Set DataMember property to the name of a table
• To bind a DataSet object to a list box
• Set the list boxs DataSource property to the
  name of the DataSet object
• Set its DisplayMember property to the name of the
  table and field

49
Summary (continued)

• To add a control to a Web form, use the tools on
  the Web Forms tab in the toolbox
• To validate user input on a Web page, use one or
  more Web validator tools
• To determine whether a postback has occurred, use
  the Web forms IsPostBack property
• To bind a Web DataGrid control to a dataset, use
  the DataBind method