Chapter 13: Working with Access Databases and LINQ
This presentation is the property of its rightful owner.
Sponsored Links
1 / 52

Chapter 13: Working with Access Databases and LINQ PowerPoint PPT Presentation


  • 149 Views
  • Uploaded on
  • Presentation posted in: General

Chapter 13: Working with Access Databases and LINQ. Displays records in the Books database Allows the store manager to enter an author’s name (or part of a name) Displays only books written by the author Displays the total value of books in the store.

Download Presentation

Chapter 13: Working with Access Databases and LINQ

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Chapter 13 working with access databases and linq

Chapter 13: Working with Access Databases and LINQ


Previewing the paradise bookstore application

Displays records in the Books database

Allows the store manager to enter an author’s name (or part of a name)

Displays only books written by the author

Displays the total value of books in the store

Previewing the Paradise Bookstore Application

Figure 13-1 Books written by Carol Smith

Figure 13-2 Total value of the inventory

Programming with Microsoft Visual Basic 2012


Lesson a objectives

Lesson A Objectives

After studying Lesson A, you should be able to:

  • Define basic database terminology

  • 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

  • Customize a DataGridView control

  • Handle errors using the Try…Catch statement

  • Position the record pointer in a dataset

Programming with Microsoft Visual Basic 2012


Database terminology

Database Terminology

  • Computer database

    • An electronic file containing an organized collection of related information

  • Relational database

    • A database that stores information in tables composed of rows and columns

    • Each column in a table represents a field

    • Each row in a table represents a record

  • Field

    • A single piece of information about a person, place, or thing

Programming with Microsoft Visual Basic 2012


Database terminology cont

Database Terminology (cont.)

  • Record

    • A group of related fields that contain all the necessary data about a specific person, place, or thing

  • Table

    • A group of related records

    • Each record in a table pertains to the same topic and contains the same type of information

  • Primary key

    • A field uniquely identifying a record

  • A two-table database has a parent tableand a child table

  • Foreign key

    • A field that links a child record to a parent record

Programming with Microsoft Visual Basic 2012


Database terminology cont1

Database Terminology (cont.)

Figure 13-3 Example of a one-table relational database

Figure 13-4 Example of a two-table relational database

Programming with Microsoft Visual Basic 2012


Connecting an application to a microsoft access database

Connecting an Application to a Microsoft Access Database

  • Before an application can access the data stored in a database, it needs to be connected to the database

  • The connection is made using the Data Source Configuration Wizard

    • The wizard allows you to specify the data you want to access

    • The computer makes a copy of the specified data and stores the copy in its internal memory

    • The copy of the data you want to access is called a dataset

Programming with Microsoft Visual Basic 2012


Connecting an application to a microsoft access database cont

Connecting an Application to a Microsoft Access Database (cont.)

Figure 13-5 Data contained in the tblEmploy table

Programming with Microsoft Visual Basic 2012


Connecting an application to a microsoft access database cont1

Connecting an Application to a Microsoft Access Database (cont.)

Figure 13-6 Choose a Data Source Type screen

Figure 13-7 Completed Add Connection dialog box

Programming with Microsoft Visual Basic 2012


Connecting an application to a microsoft access database cont2

Connecting an Application to a Microsoft Access Database (cont.)

Figure 13-8 Message regarding copying the database file

Figure 13-9 Objects selected in the Choose Your Database Objects screen

Figure 13-10 Result of running the Data Source Configuration Wizard

Programming with Microsoft Visual Basic 2012


Connecting an application to a microsoft access database cont3

Connecting an Application to a Microsoft Access Database (cont.)

  • Previewing the Contents of a Dataset

  • You can view the fields and records contained in a dataset by right-clicking the dataset’s name in the Data Sources window and then clicking Preview Data

Figure 13-11 Data displayed in the Preview Data dialog box

Programming with Microsoft Visual Basic 2012


Binding the objects in a dataset

Binding the Objects in a Dataset

  • You must bind one or more objects in the dataset to controls in the interface to view dataset contents

  • Binding

    • Connecting an object to a control

  • Bound controls

    • Connected controls

  • Types of controls used to bind dataset objects:

    • Computer-created control

    • Existing control on the form

Figure 13-12 Ways to bind an object in a dataset

Programming with Microsoft Visual Basic 2012


Binding the objects in a dataset cont

Binding the Objects in a Dataset (cont.)

Having the Computer Create a Bound Control

  • When you drag a dataset object onto a form:

    • The computer creates the control (its type is indicated by an icon)

    • The dataset object is automatically bound to the control

  • Example:

    • Drag the tblEmployee table object to the form

    • The DataGridView control is created to display tabular data, with rows representing records and columns representing fields

  • Use the list arrow to change the type of control linked to the object

Figure 13-13 Icons in the Data Sources window

Figure 13-14 Result of clicking the tblEmploy object’s list arrow

Programming with Microsoft Visual Basic 2012


Binding the objects in a dataset cont1

Binding the Objects in a Dataset (cont.)

Having the Computer Create a Bound Control (cont.)

Figure 13-15 Result of clicking the Last_Name object’s list arrow

Figure 13-16 Result of dragging the table object to the form

Programming with Microsoft Visual Basic 2012


Binding the objects in a dataset cont2

Binding the Objects in a Dataset (cont.)

Having the Computer Create a Bound Control (cont.)

  • In addition to a control, the computer adds:

    • BindingNavigator control

      • To move from one record to the next in the dataset

    • TableAdapter object

      • Connects the database to the DataSet object responsible for retrieving data and storing it in the DataSet

    • TableAdapterManager object

      • Handles saving data to multiple tables in the DataSet

    • BindingSource object

      • Provides the connection between the DataSet and the bound controls

Figure 13-17 Illustration of the relationships among the database, the objects in the component tray, and the bound controls

Programming with Microsoft Visual Basic 2012


Binding the objects in a dataset cont3

Binding the Objects in a Dataset (cont.)

The DataGridView Control

  • DataGridView control

    • Displays data in a row and column format

    • Cell

      • Intersection of a row and a column

    • The DataGridView’s task list allows you to add, reorder, and remove columns and set properties of bound columns

    • AutoSizeColumnsMode property

      • Has seven settings that control how the column widths are sized

    • Fill setting

      • Automatically adjusts the column widths so that the display area of the control is filled

Programming with Microsoft Visual Basic 2012


Binding the objects in a dataset cont4

Binding the Objects in a Dataset (cont.)

The DataGridView Control (cont.)

Figure 13-18 DataGridView control’s task list

Programming with Microsoft Visual Basic 2012


Binding the objects in a dataset cont5

Binding the Objects in a Dataset (cont.)

The DataGridView Control (cont.)

Figure 13-19 Edit Columns dialog box

Programming with Microsoft Visual Basic 2012


Binding the objects in a dataset cont6

Binding the Objects in a Dataset (cont.)

Figure 13-21 Completed CellStyle Builder dialog box

Figure 13-20 Completed Format String Dialog box

Programming with Microsoft Visual Basic 2012


Visual basic code

Visual Basic Code

  • When a table or field object is dragged onto a form, the computer also enters code in the Code Editor window

    • Code in the form’s Load event uses the TableAdapter object to retrieve data

    • The BindingNavigator’s save event is also coded

Figure 13-23 Code automatically entered in the Code Editor window

Programming with Microsoft Visual Basic 2012


Visual basic code cont

Visual Basic Code (cont.)

Handling Errors in the Code

  • Exception

    • An error that occurs while an application is running

  • Try…Catch statement

    • Used to intercept exceptions and handle them

  • Try block

    • Contains the statements that might fail

  • Catch block

    • Contains the code to handle the exceptions

Programming with Microsoft Visual Basic 2012


Visual basic code cont1

Visual Basic Code (cont.)

Handling Errors in the Code (cont.)

Figure 13-24 Basic syntax and examples of the Try…Catch statement

Programming with Microsoft Visual Basic 2012


Visual basic code cont2

Visual Basic Code (cont.)

Handling Errors in the Code (cont.)

Figure 13-25 Completed Click event procedure for the Save Data button

Figure 13-27 Tooltip for the Move last button

Figure 13-26 Dataset displayed in the DataGridView control

Programming with Microsoft Visual Basic 2012


The copy to output directory property

The Copy to Output Directory Property

  • Copy to Output Directory property

    • Determines the way Visual Basic saves changes to a local file

    • Copy always (default setting)

      • The database file is copied to the project’s bin\Debug folder each time the application starts

      • Result: The database file appears in two different folders

      • Changes to the file in the bin\Debug folder are overwritten

    • Copy if newer

      • Preserves run-time changes

      • Copies over the file in bin\Debug only if it is not current

Programming with Microsoft Visual Basic 2012


The copy to output directory property cont

The Copy to Output Directory Property (cont.)

Figure 13-28 Settings for the Copy to Output Directory property

Programming with Microsoft Visual Basic 2012


Binding to an existing control

Binding to an Existing Control

  • You can bind an object in a dataset to an existing control on the form in two ways:

    • Drag the object from the Data Sources window to the control

    • Set one or more of the control’s properties in the Properties window

  • Properties to set depend on the type of control being bound:

    • DataGridView: Set the DataSource property

    • ListBox: Set the DataSource and DisplayMember properties

    • Label or text box: Set the DataBindings /Text property

Programming with Microsoft Visual Basic 2012


Binding to an existing control cont

Binding to an Existing Control (cont.)

Figure 13-30 Result of binding a field to an existing control

Figure 13-29 A different version of the Morgan Industries application

Figure 13-31 First record displayed in the interface

Programming with Microsoft Visual Basic 2012


Coding the next record and previous record buttons

Coding the Next Record and Previous Record Buttons

  • BindingSource object’s Position property

    • Stores an invisible record pointer

    • Positions are integer values  0

    • First record is at position 0

  • BindingSource object’s Move methods

    • Can be used to move the record pointer in a dataset to the first, last, next, or previous record in the dataset

Programming with Microsoft Visual Basic 2012


Coding the next record and previous record buttons cont

Coding the Next Record and Previous Record Buttons (cont.)

Figure 13-32 Syntax and examples of the BindingSource object’s Position property

Figure 13-33 Syntax and examples of the BindingSource object’s Move methods

Programming with Microsoft Visual Basic 2012


Coding the next record and previous record buttons cont1

Coding the Next Record and Previous Record Buttons (cont.)

Figure 13-34 btnNext_Click and btnPrevious_Click procedures

Programming with Microsoft Visual Basic 2012


Lesson a summary

Lesson A Summary

  • Use the Data Source Configuration Wizard to connect an application to a database

  • Use the Preview Data command on the Data menu to preview the data in a dataset

  • Bind an object in a dataset by dragging it to the form and letting the computer create a control, or by dragging it onto an existing control

  • Use the Fill setting of the DataGridView’s AutoSizeColumnsMode property to have columns fill the display area

Programming with Microsoft Visual Basic 2012


Lesson a summary cont

Lesson A Summary (cont.)

  • Use the Dock property of DataGridView to anchor it to the borders of a form

  • Use the Try…Catch statement to handle exceptions (errors) occurring during run time

  • Use the BindingSource object’s Position property or its Move methods to move the record pointer while the application is running

Programming with Microsoft Visual Basic 2012


Lesson b objectives

Lesson B Objectives

After studying Lesson B, you should be able to:

  • Query a dataset using LINQ

  • Customize a BindingNavigator control

  • Use the LINQ aggregate operators

Programming with Microsoft Visual Basic 2012


Creating a query

Creating a Query

  • Query

    • Specifies records to select from a dataset and the order in which to arrange them

  • Language Integrated Query (LINQ)

    • Used to create queries in Visual Basic

  • Where clause

    • Contains a condition, or conditions, to limit the records to be selected

  • Order By clause

    • Used to arrange the records in ascending or descending order by one or more fields

Programming with Microsoft Visual Basic 2012


Creating a query cont

Creating a Query (cont.)

Figure 13-35 Basic LINQ syntax and examples for selecting and arranging records in a dataset

Programming with Microsoft Visual Basic 2012


Creating a query cont1

Creating a Query (cont.)

Figure 13-36 Syntax and an example of assigning a LINQ variable’s contents to a BindingSource object

Programming with Microsoft Visual Basic 2012


Creating a query cont2

Creating a Query (cont.)

Figure 13-37 Code entered in the General Declarations section and btnFind Click event procedure

Programming with Microsoft Visual Basic 2012


Creating a query cont3

Creating a Query (cont.)

Figure 13-38 Employees whose last name begins with the letter S

Programming with Microsoft Visual Basic 2012


Customizing a bindingnavigator control

You can add additional items to a BindingNavigator control to personalize it

Button, Textbox, Drop-down button

Customizing a BindingNavigator Control

Figure 13-39 Instructions for customizing a BindingNavigator control

Programming with Microsoft Visual Basic 2012


Customizing a bindingnavigator control cont

Customizing a BindingNavigator Control (cont.)

Figure 13-40 Items Collection Editor dialog box

Programming with Microsoft Visual Basic 2012


Customizing a bindingnavigator control cont1

Customizing a BindingNavigator Control (cont.)

Figure 13-41 DropDownItems property in the Items Collection Editor dialog box

Programming with Microsoft Visual Basic 2012


Customizing a bindingnavigator control cont2

Customizing a BindingNavigator Control (cont.)

Figure 13-42 DropDownButton added to the TblEmployBindingNavigator control

Programming with Microsoft Visual Basic 2012


Using the linq aggregate operators

Using the LINQ Aggregate Operators

  • Aggregate operator

    • Returns a single value from a group of values

  • LINQ provides several aggregate operators:

    • Average

    • Count

    • Max

    • Min

    • Sum

Figure 13-43 Syntax and examples of the LINQ aggregate operators

Programming with Microsoft Visual Basic 2012


Using the linq aggregate operators cont

Using the LINQ Aggregate Operators (cont.)

Figure 13-45 Message box showing the average pay rate for all employees

Figure 13-44 Code entered in each menu item’s Click event procedure

Programming with Microsoft Visual Basic 2012


Lesson b summary

Lesson B Summary

  • Use LINQ to select and arrange records in a dataset

  • You can customize the BindingNavigator control by adding additional items to it

  • LINQ provides aggregate operators that return a single value from a group of values

Programming with Microsoft Visual Basic 2012


Lesson c objectives

Lesson C Objectives

After studying Lesson C, you should be able to:

  • Prevent the user from adding and deleting records

  • Remove buttons from a BindingNavigator control

  • Add a label, a text box, and a button to a BindingNavigator control

Programming with Microsoft Visual Basic 2012


Completing the paradise bookstore application

Requirements for the Paradise Bookstore application:

Display records from the Books database

Allow the store manager to enter an author’s name (or part of a name) to display books by that author

The database contains one table: tblBooks

It has five fields and 11 records

The user should not be allowed to add or delete records

You must modify the BindingNavigatorControl to remove the add and delete buttons

Completing the Paradise Bookstore Application

Programming with Microsoft Visual Basic 2012


Completing the paradise bookstore application cont

Completing the Paradise Bookstore Application (cont.)

Figure 13-46 tblBooks table in the Books database

Figure 13-47 Completed Items Collection Editor dialog box

Figure 13-48 Completed TblBooksBindingNavigator control

Programming with Microsoft Visual Basic 2012


Coding the paradise bookstore application

Go button’s Click event procedure

Displays only records whose Author field starts with the characters entered in the text box

If the text box is empty, it displays all records

Use the LINQ LIKE operator

Total Value button’s Click event procedure

Displays the total value of all books in the store

Use the LINQ aggregate function SUM

Coding the Paradise Bookstore Application

Programming with Microsoft Visual Basic 2012


Coding the paradise bookstore application cont

Coding the Paradise Bookstore Application (cont.)

Figure 13-49 Books written by authors whose names begin with s

Figure 13-50 Message box showing the total value of the inventory

Programming with Microsoft Visual Basic 2012


Coding the paradise bookstore application cont1

Coding the Paradise Bookstore Application (cont.)

Figure 13-51 btnGo_Click and btnTotal_Click procedures

Programming with Microsoft Visual Basic 2012


Lesson c summary

Lesson C Summary

  • Use the DataGridView control’s task box to prevent the user from adding or deleting records in the control

  • To delete items from a BindingNavigator control:

    • Click the BindingNavigator control’s task box and then click Edit Items

    • In the Members list, click the item you want to remove

  • To add controls to a BindingNavigator control:

    • Click the BindingNavigator control’s task box and then click Edit Items

    • Use the “Select item and add to list below” box and the Add button to add the appropriate control

Programming with Microsoft Visual Basic 2012


  • Login