Transcript Visual Basic PP Slides Ch 10

Chapter 10
Database Applications
McGraw-Hill
Copyright © 2011 by The McGraw-Hill Companies, Inc. All Rights Reserved.
Objectives
•
•
•
•
•
Use database terminology correctly.
Create Windows and Web projects that display
database data.
Display data in a DataGridView control.
Bind data to text boxes and labels.
Allow the user to select from a combo box or list
box and display the corresponding record in databound controls.
10-2
Visual Basic and Database Files
•
•
•
•
VB projects can display and update the data from
database files.
VB .NET uses ADO.NET for database access.
ADO.NET is the next generation of database
technology, based on Microsoft's previous version,
ActiveX Data Objects (ADO).
An advantage of ADO.Net is that information is
stored and transferred in Extensivle Markup
Language (XML).
10-3
Database Terminology
•
•
Databases
Tables—can be viewed like a spreadsheet
• Records (rows) — data for one item, person,
transaction
• Fields (columns) — store a different element of
data
•
• Key field (unique to each record)
Relational database
• Multiple tables
• Relationships between the tables
10-4
Database Table Example
10-5
XML Data (1 of 2)
•
•
•
•
•
Industry-standard format for storing and
transferring data
The XML needed for accessing databases will be
automatically generated in Visual Basic.
Data stored in XML is text, identified by tags —
similar to HTML tags that can be edited by any text
editor program.
Tags in XML are not predefined as they are in
HTML.
Tags can identify fields by name.
10-6
XML Data (2 of 2)
•
In addition to an XML data file, there is normally an
XML schema file.
• Schema describes the fields, data types, and any
•
constraints, such as required fields.
ADO.NET validates the data against the schema and
checks for constraint violations.
• Schema is defined with XML tags and can be
•
viewed/edited in a text editor.
ADO.NET can treat the XML data as objects, allowing
the IntelliSense feature of the VS.NET environment to
provide information to the programmer.
• Data executes faster.
10-7
Using ADO.NET and Visual Basic
•
•
Can display data from a database on
• Windows Form
• Web Form
Add controls to form and bind data to them.
• Label, TextBox
• Special controls designed just for data such
as DataGrid, DataList
10-8
The DataGrid View Control
The DataGridView control is bound to a table in a
dataset. The data fields display automatically in
the cells of the grid.
10-9
Data Access in Visual Studio
•
•
•
•
•
Allows an easy way to create data-bound controls on a form
Can drag tables and fields from the window onto a form to
automatically create controls that are bound to the data
Data can be displayed in grids or individual fields,referred to
as details.
Can drag a field from the Data Sources window and drop it on
an existing control — causes data binding to be set up
automatically.
An .xsd file and the schema is added to the Server Explorer
window; Binding Source, Table Adapter, and Data Set
objects are added to the form’s component tray.
10-10
Overview of Database Objects
•
•
•
Binding Source
• Establishes a link to the actual data; a specific file
and/or server
Table Adapter
• Handles retrieving and updating the data
• Generates SQL statements that are used to access
or update data
Dataset
• Contains actual data — may come from multiple
connections and/or multiple data adapters
10-11
Steps to Display Data in Bound Controls on a
Form
10-12
Binding Sources
•
•
•
Object establishes a link from a specific file or
database to the program.
Use a wizard to automatically create Binding Source
objects –OR—
Add new Binding Source objects using the Data
Sources window or the Data menu.
10-13
Table Adapters
•
•
•
•
Does all the work of passing data back and forth
between a data source (the binding source) and
a program (the dataset)
Data does not have to be from a database.
Data can be text file, object, or an array.
Transfers data to and from via XML
10-14
Datasets
•
•
•
•
Temporary set of data stored in memory
In ADO.NET datasets are disconnected; the
copy of data kept in memory does not keep an
active connection to the data source.
Dataset may contain multiple tables and
relationships.
Any controls bound to the dataset will
automatically fill with data.
10-15
The Smart Tag
•
•
•
Smart Tags are used for many functions including:
• Selecting a data source
• Editing properties of grid columns
• AutoFormating the grid
• Previewing data
Preview helps users do a better job of designing
output.
Selecting AutoFormat allows the user to choose
from many predefined formats.
10-16
Grid with Pre-Defined AutoFormat
Predefined formats can be applied from the
AutoFormat item on the smart tag.
10-17
The Database Schema File
•
•
•
•
When a new data source is added to a project, a file
.xsd is added to the Solution Explorer — the XML
schema definition.
XML file contains the description and properties of the
data.
The schema shows the names of the table(s) and fields,
the primary keys for each table, and the relationships
among the tables.
The TableAdapter for the table, which handles different
methods for the table, appears at the bottom of the
schema.
10-18
.XSD (Schema) File
The .xsd file holds the
schema of the
database, where table
elements, relationships,
and keys can be viewed
and modified.
10-19
Binding Individual Data Fields
•
•
•
Table fields from the dataset can be bound to many
types of controls such as labels, text boxes, combo
boxes, and check boxes.
Controls that are connected to fields are bound
controls or data-bound controls.
Easiest way to create bound controls is to use the
automatic binding feature of the Data Source
window.
• Creates individual text box controls for each field of data
and navigation control allowing the user to more from one
record to another
10-20
Data-Bound Text Boxes — Example
Each text box or check
box is bound to one
field from the table. As
the user clicks the
navigation buttons, all
controls change to
display the data for
the next record.
10-21
The Data Sources Window
•
•
•
This window is used to add
a new data source by
using the Data Source
Configuration Wizard.
Click the table name to
make a drop-down list
available to view Details.
The table’s icon changes
to match the view.
10-22
Selecting Records from a List
• Allows user to select records to display from a
list
• Fill a ListBox or ComboBox with values from the
•
database –OR—
Display the information in a drop-down list and
allow the user to make a selection; the
corresponding data elements fill remaining
fields.
10-23
Selecting Fields from the Table
•
•
•
Select individual fields when creating the new data
source –OR– after creating the data source.
When creating the data source, choose the Add
New Data Source option and follow the
Configuration Wizard; expand the Tables node and
place a check mark on the desired fields.
To modify after creating, select the dataset and use
the Configuration Wizard and make the field
selections from the Wizard.
10-24
Data Source Configuration Wizard
10-25
Sorting the List Data
•
•
•
•
•
Sort records in a query that selects data for the
dataset.
SQL SELECT statement is generated
automatically by the designer; can be modified.
Use the Solutions Explorer and the schema file
(.xsd extension) and the Property button (…).
Choose “Ascending” in the Sort Type cell of the
Query Builder.
Execute the query to preview the output.
10-26
Sorting Data
Click the Fill,GetData() entry
in the dataset schema to
display the properties of the
Fill command in the Properties
window.
Select Ascending for the Sort
Type to sort the data by the Title
field. The SQL statement changes
to include an ORDER BY clause.
10-27
Choosing the Control Type for Fields
•
•
Dragging a Details view
to a form displays text
boxes for the default text
fields.
Click a field name and
choose a control type in
the Data Sources
window.
10-28
Selecting Records Using Web Forms
•
•
•
•
•
Web controls are much different from Windows controls.
Additional security issues exist for Web-based database
applications.
Web database applications operate differently than
Windows applications due to Web pages in a client/server
environment.
Web pages are stateless—each time a page displays it is
a new page.
Each time the user makes a selection from a list, a
postback occurs and the Web page redisplays with only
the selected data.
10-29
Security in Web Database Applications
•
•
•
Security is much tighter for Web database
applications than for Windows applications.
User authentication is required if setting up a Web
application that displays or allows modification to
data.
Visual Studio integrates security features that are
generally strict by default.
10-30
Moving Database Projects
•
•
•
•
Improvement from previous versions of VB.NET
Project needs to be closed in the IDE.
Copy the complete folder from one computer or
location to another.
For Web projects, copy the solution folder to the
desired location, using Windows Explorer.
10-31
LINQ
•Language Integrated Query (LINQ), is a general
purpose query language that can ask a question of any
data that is defined as an object, a database, or as XML.
•The source of the data may be a database, but could
also be any collection, such as the collection for a list box.
•VB contains a LINQ to SQL component to convert
database items to objects and a LINQ to XML component
for converting XML document collections to objects.
10-32
Querying Operating System Data
•The key operators in the query are From, In, and
Select.
•The LINQ Query—General Form
Dim VariableName = From ItemName In Object
Select ListOfFields/Items
•The LINQ Query—Example
Dim MyQuery = From AnItem in
Process.GetProcesses_
Select AnItem.ProcessName, AnItem.StartTime
10-33
Creating a LINQ Project
•
•
•
•
Open a new Windows
application project.
Add a DataGridView
control, naming it
ProcessesDataGridView.
Double-click on the form to
access the Form_Load
event.
Type in the code.
10-34