Microsoft Visual Basic 2010: Reloaded Fourth Edition - PowerPoint PPT Presentation

Loading...

PPT – Microsoft Visual Basic 2010: Reloaded Fourth Edition PowerPoint presentation | free to download - id: 49f1f3-NDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Microsoft Visual Basic 2010: Reloaded Fourth Edition

Description:

Microsoft Visual Basic 2010: Reloaded Fourth Edition Chapter Twelve Access Databases and LINQ * * * * * * * * * * * * Creating the CD Collection application Microsoft ... – PowerPoint PPT presentation

Number of Views:208
Avg rating:3.0/5.0
Slides: 72
Provided by: mywebAsta5
Learn more at: http://myweb.astate.edu
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Microsoft Visual Basic 2010: Reloaded Fourth Edition


1
Microsoft Visual Basic 2010 Reloaded Fourth
Edition
  • Chapter Twelve
  • Access Databases and LINQ

2
Objectives
  • After studying this chapter, you should be able
    to
  • Define the terms used when talking about
    databases
  • Connect an application to a Microsoft Access
    database
  • Bind table and field objects to controls
  • Explain the purpose of the DataSet,
    BindingSource, TableAdapter, TableAdapterManager,
    and BindingNavigator objects

3
Objectives (cont'd.)
  • Customize a DataGridView control
  • Handle errors using the TryCatch statement
  • Position the record pointer in a dataset
  • Access the value stored in a field object
  • Query a dataset using LINQ
  • Customize a BindingNavigator control
  • Use the LINQ aggregate methods

4
Database Terminology
  • Computer database electronic file containing an
    organized collection of related information
  • Relational database database that stores
    information in tables composed of columns and
    rows
  • Field single item of information
  • Record group of related fields
  • Table group of related records
  • Primary key field that uniquely identifies each
    record

5
Database Terminology (cont'd.)
Figure 12-1 Example of a one-table relational
database
6
Database Terminology (cont'd.)
  • Parent table contains a primary key
  • Child table contains a foreign key from the
    parent table to link the tables
  • Foreign key field in a table that contains the
    primary key of another table
  • Relational database advantages
  • Less redundancy
  • Fast retrieval
  • Ability to selectively retrieve data

7
Database Terminology (cont'd.)
Figure 12-2 Example of a two-table relational
database
8
Connecting an Application to a Microsoft Access
Database
  • Microsoft Access database
  • Has a file extension of .accdb
  • Must connect an application to the database
    before the application can access the data
  • Use the Data Source Configuration Wizard to
    connect to a database
  • Dataset copy of the fields and records stored in
    the computers internal memory, which the
    application can access

9
Connecting an Application to a Microsoft Access
Database (cont'd.)
Figure 12-3 Data contained in the tblEmploy table
10
Connecting an Application to a Microsoft Access
Database (cont'd.)
Figure 12-4 How to connect an application to an
Access database
11
Connecting an Application to a Microsoft Access
Database (cont'd.)
Figure 12-5 Result of running the Data Source
Configuration Wizard
12
Connecting an Application to a Microsoft Access
Database (cont'd.)
Figure 12-6 How to preview the contents of a
dataset
13
Figure 12-7 EmployeesDataSet shown in the
Preview Data dialog box
14
Binding the Objects in a Dataset
  • Binding connecting an object in a dataset to a
    control on a form
  • 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

15
Binding the Objects in a Dataset (cont'd.)
Figure 12-8 How to bind an object in a dataset
16
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 column format
  • Use the list arrow next to an objects name to
    change the type of control to be created

17
Having the Computer Create a Bound Control
(cont'd.)
Figure 12-9 Icons in the Data Sources window
18
Having the Computer Create a Bound Control
(cont'd.)
Figure 12-10 Result of clicking the tblEmploy
table objects list arrow
19
Having the Computer Create a Bound Control
(cont'd.)
Figure 12-11 Result of clicking the Last_Name
filed objects list arrow
20
Having the Computer Create a Bound Control
(cont'd.)
  • 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
  • Five objects are placed in the component tray
  • DataSet, BindingSource, TableAdapter,
    TableAdapterManager, BindingNavigator

21
Having the Computer Create a Bound Control
(cont'd.)
Figure 12-12 Result of dragging the table object
to the form
22
Having the Computer Create a Bound Control
(cont'd.)
  • TableAdapter object connects the database to the
    DataSet object
  • DataSet object stores the information to be
    accessed from the database
  • TableAdapterManager object handles saving data
    to multiple tables in the DataSet
  • BindingSource object connects the DataSet object
    to the bound controls on the form

23
Having the Computer Create a Bound Control
(cont'd.)
Figure 12-13 Illustration of the relationships
among the database, the objects in the component
tray, and the bound controls
24
The DataGridView Control
  • DataGridView control displays data in a row and
    column format
  • Each row represents a record
  • Each column represents a field
  • Cell the intersection of a row and column
  • DataGridView has a task list used to control its
    appearance and behavior

25
The DataGridView Control (cont'd.)
Figure 12-14 Task list for a DataGridView control
26
The DataGridView Control (cont'd.)
Figure 12-15 Purpose of each task in the
DataGridViews task list
27
The DataGridView Control (cont'd.)
Figure 12-15 Edit Columns dialog box
28
The DataGridView Control (cont'd.)
  • Many properties of DataGridView are listed only
    in the Properties window
  • AutoSizeColumnsMode property
  • Select Fill setting to automatically adjust
    column widths to exactly fill the display area
  • Select ColumnHeader setting to adjust column
    widths based on the header text

29
The DataGridView Control (cont'd.)
Figure 12-17 DataGridView control after setting
some of its properties
30
Visual Basic Code
  • DataGridView control allows data to be edited
    directly within the control to update the
    database
  • Two event procedures are automatically created in
    Code Editor window when a table or field object
    is dragged to the form
  • MainForm_Load
  • bindingNavigatorSaveItem_Click
  • MainForm_Load event
  • Fill method TableAdapter objects method to
    retrieve data from the database and store it in
    the dataset

31
Visual Basic Code (cont'd.)
  • bindingNavigatorSaveItem_Click event
  • Saves any changes made to the dataset
  • EndEdit method applies pending changes to the
    dataset
  • UpdateAll method commits the dataset changes to
    the database
  • Be sure to use error-handling code

32
Visual Basic Code (cont'd.)
Figure 12-18 Code automatically entered in the
Code Editor window
33
Handling Errors in the Code
  • Exception an error that occurs while an
    application is running
  • VB handles errors if the program does not by
    showing an error message and terminating the
    application
  • TryCatch statement used to trap errors and
    attempt to handle them
  • Place code that could cause an exception within
    the Try block
  • Catch block is executed if an exception occurs

34
Figure 12-19 How to use the TryCatch statement
35
Handling Errors in the Code (contd.)
Figure 12-19 How to use the TryCatch statement
(contd.)
36
Handling Errors in the Code (cont'd.)
Figure 12-20 TryCatch statement entered in the
Save Data buttons Click event procedure
37
Handling Errors in the Code (cont'd.)
Figure 12-21 Sample run of the Morgan Industries
application
38
Handling Errors in the Code (cont'd.)
  • BindingNavigator control provides buttons for
    first, last, previous, and next record and for
    adding records, deleting records, and saving
    changes
  • Can also use the control to access a record by
    its record number

39
The Copy to Output Directory Property
  • Local database file database file contained in a
    project
  • Copy to Output Directory property determines how
    VB saves changes to a local database file
  • Copy always setting database file is copied from
    the project folder to bin\Debug folder each time
    the application is started
  • Changes are made only to the bin\Debug copy
  • Copy if newer setting newer of the two database
    files (project folder and bin\Debug folder) is
    saved in bin\Debug folder

40
The Copy to Output Directory Property (contd.)
Figure 12-22 How to use the Copy to Output
Directory property
41
Binding to an Existing Control
  • Can bind an object in a dataset to an existing
    control in two ways
  • 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,TableAdapter, and
    TableAdapterManager objects are added to the
    component tray
  • BindingNavigator control is NOT added
    automatically

42
Binding to an Existing Control (cont'd.)
Figure 12-23 Result of dragging field objects to
existing label controls
43
Binding to an Existing Control (cont'd.)
Figure 12-24 Sample run of a different version
of the Morgan Industries application
44
Coding the Next Record and Previous Record Buttons
  • BindingSource object uses an invisible record
    pointer to track the current record in the
    dataset
  • Position property stores position of current
    record this position number is zero-relative
  • Move method moves the record pointers position
    to first, last, previous, or next record in the
    dataset

45
Figure 12-23 How to use the BindingSource
objects Position property
46
Figure 12-26 How to use the BindingSource
objects Move methods
47
Coding the Next Record and Previous Record
Buttons (cont'd.)
Figure 12-27 Code entered in the Click event
procedures for the Next Record and Previous
Record buttons
48
Accessing the Value Stored in a Field
Figure 12-28 How to access the value stored in a
field object
49
Creating a Query
  • Can arrange records in a dataset in any order
  • Query specifies the records to select in a
    dataset
  • Language Integrated Query (LINQ) language used
    to create a query
  • Where clause optional, specifies a condition to
    limit which records to view
  • Order By clause optional, specifies whether to
    arrange in descending or ascending order
  • Option Infer On allows computer to infer the
    data type from the variables in the query

50
Creating a Query (contd.)
Figure 12-29 How to use LINQ to select and
arrange records in a dataset
51
Figure 12-29 How to use LINQ to select and
arrange records in a dataset (contd.)
52
Creating a Query (contd.)
Figure 12-30 How to assign a LINQ variables
contents to a BindingSource control
53
Figure 12-31 LINQ code entered in the Find Last
Name buttons Click event procedure
54
Creating a Query (contd.)
Figure 12-32 Employees whose last name begins
with the letter S
55
Personalizing a BindingNavigator Control
  • You can include other items on the
    BindingNavigator control
  • Buttons, text boxes, or drop-down buttons

56
Personalizing a BindingNavigator Control (contd.)
Figure 12-33 How to manipulate the items on a
BindingNavigator control
57
Personalizing a BindingNavigator Control (contd.)
Figure 12-34 Items Collection Editor window
58
Personalizing a BindingNavigator Control (contd.)
Figure 12-35 DropDownButton added to the
BindingNavigator control
59
Using the LINQ Aggregate Operators
  • Aggregate operator returns a single value from a
    group of values
  • Most commonly used aggregate operators
  • Average, Count, Max, Min, and Sum

60
Figure 12-36 How to use the LINQ aggregate
operators
61
Using the LINQ Aggregate Methods (cont'd.)
Figure 12-37 Code associated with the three
items on the DropDownButton
62
Using the LINQ Aggregate Methods (cont'd.)
Figure 12-37 Code associated with the three
items on the DropDownButton (contd.)
63
Using the LINQ Aggregate Methods (cont'd.)
Figure 12-37 Code associated with the three
items on the DropDownButton (contd.)
64
Adding Items to the BindingNavigator Control
(cont'd.)
Figure 12-38 Average pay rate for part-time
employees
65
Programming Tutorial 1
  • Creating the Trivia Game application

Figure 12-40 MainForm for the Trivia Game
application
66
Programming Tutorial 2
  • Creating the CD Collection application

Figure 12-50 Preview Data dialog box showing the
data stored in the CDDataSet
67
Programming Example
  • Cartwright Industries application

Figure 12-55 MainForm in the Cartwright
Industries application
68
Summary
  • Can use Visual Basic to access data stored in
    databases
  • Relational database stores information in tables
    composed of fields and records
  • Primary key field in a database table that
    uniquely identifies each record
  • Data in a relational database can be displayed in
    any order, and you can control the amount of
    information to view

69
Summary (cont'd.)
  • 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
  • DataGridView control displays data in row and
    column format

70
Summary (cont'd.)
  • Use a TryCatch statement to handle exceptions
  • Database files Copy to Output Directory
    property determines when and if the file is
    copied to the projects bin\Debug folder
  • BindingSource objects Position property stores
    the location of the record pointer in a dataset
  • BindingSource objects Move methods used to move
    the record pointer in a dataset
  • Can access the value stored in a field object in
    a dataset

71
Summary (cont'd.)
  • Use LINQ to select and arrange records in a
    dataset
  • LINQ provides Average, Sum, Count, Min, and Max
    aggregate methods
  • Can include additional items such as text boxes
    and drop-down buttons on a BindingNavigator
    control
About PowerShow.com