Transcript Accessing Database Files

aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf
aslkjdhfalskhjfgalsdkfhalskdhjfglaskdhjflaskdhjfglaksjdhflakshflaksdhjfglaksjhflaksjhf 10-1
10
Chapter
Accessing Database Files
McGraw-Hill
© 2006 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 data-bound
controls
10-3
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 Extensive Markup Language (XML)
10-4
Database Terminology
 Databases
 Tables – can be viewed like a spreadsheet
 Records (rows) – data for one item, person,
transaction
 Fields (columns) – stores a different element of
data
 Key field (unique to each record)
 Relational database
 Multiple tables
 Relationships between the tables
10-5
Database Table Example
10-6
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 and 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-7
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-allows
the IntelliSense feature of the VS.NET environment to
provide information to the programmer
 Data executes faster
10-8
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-9
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 statement that are used to access
or update data
 Dataset
 Contains actual data, may come from multiple
connections and/or multiple data adapters
10-10
Steps to Display Data in Bound Controls
on a Form
10-11
Binding Sources
 Object establishes a link from a specific file or
database to the program
 Can you a wizard to automatically create Binding
Source objects –OR—
 Add new Binding Source objects using the Data
Sources window or the Data menu
10-12
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-13
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-14
Data Access in Visual Studio 2005
 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-15
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-16
The Smart Tag
 Smart Tags are used for many functions including:
 Selecting a data source
 Editing properties of grid columns
 AutoFormat the grid
 Preview data
 Preview helps users do a better job of designing
output
 Selecting AutoFormat allows the user to choose from
many predefined format
10-17
Grid with Pre-Defined AutoFormat
Predefined formats can be applied from the
AutoFormat item on the smart tag.
10-18
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
 At the bottom of the schema appears the TableAdapter
for the table which handles different methods for the
table
10-19
.XSD File
The .xsd file holds the
schema of the
database, where table
elements, relationships,
and keys can be viewed
and modified
10-20
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-21
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-22
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-23
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-24
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
AddNewData Source option and follow the
Configuration Wizard; expand the Tables node and
palce 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-25
Data Source Configuration Wizard
10-26
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-27
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-28
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-29
Selecting Records Using Web Forms
 Web controls are much different from Windows controls
 Additional security issues 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-30
Security in Web Database Applications
 Security is much tighter for Web database applications
than for Windows applications
 Require user authentication if setting up a Web
application that displays or allows modification to data
 Visual Studio 2005 integrates security features which
are general strict by default
10-31
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, open the IDE, select Open Web Site
and browse to the folder
10-32