Web-Enabled Decision Support Systems - PowerPoint PPT Presentation

About This Presentation
Title:

Web-Enabled Decision Support Systems

Description:

15.4 Hands-On Tutorial: Displaying Data with Multiple Parameters ... When enquired, copy the database to the current project folder. Click Next. ... – PowerPoint PPT presentation

Number of Views:34
Avg rating:3.0/5.0
Slides: 81
Provided by: sama49
Category:

less

Transcript and Presenter's Notes

Title: Web-Enabled Decision Support Systems


1
Web-Enabled Decision Support Systems
  • Advance Topics in Database Connectivity

Prof. Name
name_at_email.com Position
(123) 456-7890 University Name
2
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

3
Introduction
  • In the previous chapter, we discussed basic
    database connectivity topics
  • The Data Sources Window
  • Displaying information on Windows forms
  • Displaying data from related tables
  • Building search forms and look-up tables
  • Real life database applications are seldom so
    simple
  • Fortunately, Visual Studio provides a rich set of
    tools to develop professional applications
    efficiently
  • In this chapter, we will study advanced database
    connectivity topics

4
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

5
ADO .NET Architecture
  • ActiveX Data Objects (ADO) .NET is the collection
    of objects that are designed to support data
    access and data manipulation
  • Database connectivity involves the following
    entities
  • Database
  • Stores data on the hard drive (disk)
  • Connection
  • Connects to the database
  • DataAdapter
  • Hosts SQL queries and executes them against the
    database
  • DataSet
  • Stores data in main memory
  • Form
  • Displays data to the user

6
ADO .NET Architecture (cont.)
  • From database to Windows forms
  • A Connection object opens a live connection to
    the database
  • A DataAdapter object executes Select SQL queries
    against the database
  • The DataAdapter object further fills a DataSet
    object with query results
  • The DataSet stores the results in the main memory
  • DataSets are associated with various Windows
    controls on a form
  • This association is referred to as Data Binding
  • Data binding makes it possible for the query
    results in the DataSet to be displayed on a form
    for the user

ADO .NET Architecture
7
ADO .NET Architecture (cont.)
  • From Windows forms to database
  • If a user edits the data in Windows controls, the
    updates are propagated to the DataSet object
  • The DataAdapter object then propagates the
    updates from the DataSet object to the connected
    database by executing Update SQL statements

8
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

9
A Database Connection
  • Creating a database connection is like opening a
    unique session with the database
  • We can access contents of the database using the
    connection object
  • There are two ways to create a database
    connection in Visual Studio
  • Using the Data Source Configuration wizard
  • Using the Server Explorer Window

10
Creating the Project and Data Connection
  • How-to Create a Connection Using the Server
    Explorer Window
  • Create a new Windows Application with the name,
    AdvanceDBConnectivity.
  • Select the View Server Explorer option from the
    Main menu to open the Server Explorer Window.
  • Right-click the Data Connection node in the
    window and choose the Add Connection option.

Adding a Connection in the Server Explorer Window
11
Creating the Project and Data Connection (cont.)
  1. If the Choose Data Source dialog box opens,
    select the Microsoft Access Database File option
    and click Continue. Otherwise, we should directly
    see the Add Connection dialog box. Select the
    University database. Test the connection and
    click OK.

The Add Connection Dialog Box
12
Creating the Project and Data Connection (cont.)
  1. Collapse the Tables node and Views node to
    explore the list of tables and queries from the
    University database.

Server Explorer Window for University Database
13
Creating the Data Source
  1. Choose the Data Show Data Sources option from
    the Main menu.
  2. In the Data Sources Window, click Add New Data
    Source to start the Data Source Configuration
    wizard.
  3. Select the Database icon on the Choose a Data
    Source Type page, and then click Next.
  4. On the Choose Your Data Connection page, accept
    the University database connection we have
    created in the previous step and click Next.

Accepting Connection Created in the Server
Explorer Window
14
Creating the Data Source (cont.)
  1. When enquired, copy the database to the current
    project folder. Click Next.
  2. On the next page of the wizard save the
    connection with the default name
    UniversityConnectionString. Click Next.
  3. Expand the Tables node on the Choose Your
    Database Objects page, and select all the tables
    except the college table and click Finish. The
    Data Sources Window now displays the
    UniversityDataSet tables.

Data Sources Window
Selecting Tables for a Data Source
15
Properties of a Connection Object
  • There are several important properties related to
    Connection objects
  • Name
  • The name given to a Connection object
  • Connection String
  • The string that stores the information required
    to connect to the database
  • A typical ConnectionString property resembles the
    following string
  • Provider
  • Shows the type of the database
  • Jet Engine for MS Access database
  • Data Source
  • Stores the physical path of the database file

16
Methods of a Connection Object
  • There are two primary methods available for
    Connection objects
  • Open
  • This method makes use of the information in the
    ConnectionString to locate the database and open
    a unique session to work with it
  • Close
  • This method shuts the connection down.
  • Closing connections is essential because most
    databases support only a limited number of open
    connections
  • We use a TableAdapter to maintain the Connection
    object
  • Opens and closes the connection as per the
    applications need
  • Makes application development easier, faster, and
    less error-prone

17
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

18
Create a DataGridView Control
  • This application will be based on the faculty
    table
  • The two parameters are the facultys department
    and salary, which are input by the user
  • We display the faculty members who belong to the
    user-specified department and whose salary is at
    least as much as specified by the user
  • How-to Display Data on a Form with Multiple
    Parameters
  • In the Data Sources Window, locate and drag the
    faculty table onto Form1. This creates
  • The data bound DataGridView control
  • A ToolStrip control
  • Related objects in the Component tray

19
Open the DataSet Designer
  • Right-click TblFacultyTableAdapter from the
    Component tray and choose Edit Queries in DataSet
    Designer option to open the DataSet Designer tab.
  • Alternatively
  • Double-click the DataSet (.xsd) file from the
    Solution Explorer Window
  • Click the Edit DataSet with Designer icon from
    the Data Sources Window

Invoking the DataSet Designer to Edit Queries
Opening the DataSet Designer
20
Adding Parameter Queries
  1. Select the TblFacultyTableAdapter in the DataSet
    Designer, right-click Fill, GetData () text,
    and choose the Configure option. This should open
    the TableAdapter Configuration wizard.

Review and Configure Existing Queries in a
TableAdapter
21
Adding Parameter Queries (cont.)
  • In the TableAdapter Configuration wizard, we have
    the Select query based on the faculty table.
  • We can modify the existing query by either
    editing the SQL statement or using the Query
    Builder dialog box.

Edit Existing Query Using TableAdapter
Configuration Wizard
22
Adding Parameter Queries (cont.)
  1. Select the TblFacultyTableAdapter in the DataSet
    Designer and right-click to choose the Add Query
    option to open the Query Configuration wizard.
  2. On the first page of the wizard (Choose a Command
    Type), choose the Use SQL statements option and
    click Next.

Adding a Query to a TableAdapter
Choosing a Command Type
23
Adding Parameter Queries (cont.)
  1. On the next page (Choose a Query Type), choose
    the first option (SELECT which returns rows) and
    click Next.

Choosing the Query Type in TableAdapter Query
Configuration Wizard
24
Adding Parameter Queries (cont.)
  1. On the next page (Specify a SQL SELECT
    statement), create a new query by entering the
    SQL statement on this page.

Writing a SQL Query in TableAdapter Query
Configuration Wizard
25
Adding Parameter Queries (cont.)
  • Finally, name the Fill a DataTable and Return a
    DataTable methods as shown in below and click
    Finish.
  • The faculty TableAdapter now resembles below with
    its additional methods.

Choose and Name the Methods to be Added to the
TableAdapter
Faculty TableAdapter with Parameterized Query
26
Add Existing Query
  1. Right-click the TblFacultyTableAdapter from the
    Component tray and choose the Add Query option to
    open the Search Criteria Builder dialog box.
  2. Choose the Existing query name option. Then,
    choose the FillByDeptSal method we created in the
    previous step from the drop-down list.
  3. Click OK to create two TextBox controls for user
    input in the ToolStrip.

Assigning a Query to a TableAdapter in the
Search Criteria Builder Dialog Box
27
Test the Application
  • Press Ctrl F5 to run and test the application.
  • Enter values for the department and salary
    parameters through the TextBox controls on the
    ToolStrip and check if all the records satisfy
    the specified criteria.

Running Application with Two Input Parameters
28
Code Review
  • Note the code used to assign multiple parameters
    (lines 21-25).

Code Review for the Multiple Parameter Query
29
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

30
Add a New TableAdapter
  • How-to Display Data on a Form from a Join of
    Multiple Tables
  • Add a new form, Form2, to the AdvanceDBConnectivit
    y application.
  • Open the DataSet Designer. Right-click anywhere
    in the designer window and choose the Add
    TableAdapter option to open the TableAdapter
    Configuration wizard.

Adding a New TableAdapter to the Application
31
Add a New TableAdapter (cont.)
  1. On the Choose Your Data Connection page, accept
    the selected University database connection
    string. Click Next.
  2. On the Choose a Command Type page, check Use SQL
    statements option (default) and click Next.

Specifying Connection for a New TableAdapter
32
Add a New TableAdapter (cont.)
  1. On the Enter a SQL Statement page, click on the
    QueryBuilder button. In the Add Table dialog box
    that opens, select the student and transcript
    tables and click Add.

Adding Query Tables in the Query Builder Dialog
Box
33
Add a New TableAdapter (cont.)
  1. Note that the two selected tables appear in the
    table pane of the Query Builder dialog box. Click
    Close on the Add Table dialog box.

Query Builder Dialog Box Showing the Query
Design
34
Add a New TableAdapter (cont.)
  • Select the fields to be displayed from the
    student and transcript tables (StudentID, DeptID,
    and Name from student table and Grade field from
    the transcript table).
  • Notice that the corresponding SQL Select query
    (performing the join of the two tables) is
    automatically built in the SQL pane as we select
    the fields.
  • Now add the DISTINCT keyword after the SELECT
    keyword in the SQL pane.
  • Add the query parameters (DeptID and Grade) in
    the WHERE clause.

35
Add a New TableAdapter (cont.)
  1. Click OK on the Query Builder dialog box. Click
    Next on the Table Adapter Configuration wizard,
    accept the default method names Fill and GetData,
    and click Finish.

Newly Added TableAdapter in the DataSet Designer
Newly Added DataTable in the Data Sources Window
36
Create a DataGridView Control and Test
  1. From the Data Sources Window, drag-and-drop the
    DataTable1 table on Form2 to create a
    DataGridView control.
  2. Set Form2 as the start-up object. Press Ctrl F5
    to run the application. Test the application by
    entering different department IDs through the
    ToolStrip.

Data from the Join of Two Tables
37
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

38
Add Access Query
  • How-to Display Data Using Existing MS Access
    Query
  • Add a Form3 to the AdvanceDBConnectivity
    application.
  • In the Data Sources Window, click the Configure
    DataSet with Wizard icon to open the Choose
    DataSet Editor dialog box.

Using Data Sources Window to Invoke the Dataset
Configuration Wizard
39
Add Access Query (cont.)
  1. Choose the Continue with wizard option to open
    the Data Source Configuration wizard.

Invoking Data Sources Configuration Wizard
40
Add Access Query (cont.)
  1. On the last page of the wizard, Choose Your
    Database Objects, collapse the Views node and
    choose qryGPA, and click Finish. This adds the
    qryGPA as a DataTable in the Data Sources Window.

Access Query as a DataTable in the Data Sources
Window
Choosing the Query
41
Create a DataGridView and Test
  1. From the Data Sources Window, drag-and-drop the
    qryGPA data table on Form3 to create a
    DataGridView control.
  2. Set Form3 as the start-up object. Press Ctrl F5
    to run the application. Navigate through the
    student records and notice that the calculated
    GPA is displayed in column 5.

Running Application with Student GPAs
42
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

43
Passing Data Between Forms
Application Flow Passing Data between Two
Windows Forms
44
Create the First (Faculty) Form
  • How-to Pass Data between Two Windows Forms
  • Add Form4 to the AdvanceDBConnectivity
    application.
  • Drag-and-drop the faculty table from the Data
    Sources Window on Form4.
  • This should create the faculty DataGridView and
    related objects in the Component tray.

45
Create the Second (Course) Form
  1. Add Form5 to the same application.
  2. Switch to the DataSet Designer by double-clicking
    on UniversityDataSet.xsd.
  3. Right-click in the designer window to add a new
    TableAdapter.
  4. Follow the TableAdapter Configuration wizard as
    described in the previous hands-on tutorial. On
    the Enter a SQL Statement page of the wizard,
    click on the Query Builder button and add
    tblSection and tblCourse tables to the query
    design. Design the SQL statement shown below
    using the Query Builder dialog box.

Course Information Query for the Course Form
46
Create the Second (Course) Form (cont.)
  • Rename the Fill method as FillFaculty before
    finishing the configuration wizard.
  • Name the new table adapter as qryFacultyCoursesTab
    leAdapter and the new data table as
    qryFacultyCourses.
  • Drag-and-drop the qryFacultyCourses data table
    from the Data Sources Window on Form5.
  • This should create the courses DataGridView and
    related objects in the Component tray.

47
Write a Subroutine
  • In the Form5.vb file, write a new subroutine
    LoadCourses as shown below.
  • Takes in the FacultyID as an argument and passes
    it as a parameter to the FillFaculty method.

Subroutine to Load Course Information for a Given
FacultyID
48
Write Code to Handle Events
  1. In the faculty form, Form4.vb, write the code for
    DataGridViews DoubleClick event. Select the
    TblFacultyDataGridView from the left drop-down
    list and its DoubleClick event from the right
    drop-down list at the top of the Code Window.
    Associate the code below with this event.

DoubleClick Event of a DataGridView for the
Faculty Form
49
Test the Application
  1. Set Form4 as the applications start-up object.
    Press Ctrl F5 to run the application. Test the
    application by double-clicking any faculty record
    from the DataGridView and viewing the related
    course data.

Running Application Showing Related Information
50
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

51
DataSets
  • A DataSet is an in-memory relational database
  • As relational databases have tables, DataSets
    also have DataTables
  • As database tables have rows and columns,
    datasets DataTables also have DataRows and
    DataColumns
  • As database tables are related to each other
    through relationships, DataTables are linked
    together through DataRelations object

The Architecture of a DataSet Object
52
How-to Create a DataSet and Add a DataTable
  • Creating a DataSet
  • We can add a DataSet to an application using the
    Data Source Configuration wizard.
  • Adding a DataTable
  • We can add a DataTable to a DataSet using the
    Data Sources Configuration wizard.

53
How-to Add a DataRow to a DataTable
  • In order to add a new record into a DataTable
  • First create a new DataRow (lines 6-8)
  • Assign values to the DataColumns (lines 11-13)
  • Add a new row to the Rows collection of a
    DataTable (line 16)

Adding a DataRow to a DataTable
54
How-to Edit a DataRow in a DataTable
  • In order to edit an existing row in a DataTable,
    we should first locate the DataRow, and then
    update its one or more columns.
  • Locate the DataRow using the DataTables
    FindByltPrimaryKeygt method or by utilizing the row
    index.

Editing a DataRow in a DataTable
Editing a DataRow in a DataTable Using a Row
Index
55
How-to Delete a DataRow in a DataTable
  • We can make use of the Delete method of a DataRow
    to delete a row
  • Again, we must find the row before we can delete
    it.

Deleting a DataRow in a DataTable
Deleting a DataRow from a DataTable Using a Row
Index
56
How-to Commit Changes in a DataSet
  • The changes to DataSet are like changes in a Word
    document.
  • We must save the changes to update the Dataset
  • Initially, we check if the DataSet has been
    changed (line 55)
  • Then use its AcceptChanges method to commit
    insertions, edits, and deletions (line 57)

Committing the Changes in a DataSet
57
How-to Filter and Sort Data
  • We can filter data by using the Filter property
    of a BindingSource
  • We can sort data using the Sort property of the
    BindingSource on the column name which we want to
    sort
  • Sort property supports DESC and ASC sort ordering

Filtering and Sorting Data Using the Properties
of a BindingSource Object
58
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

59
Data Flow from Windows Forms to a Database
  • It is vital for an application to enable users to
    insert new data and edit, update, or delete
    existing data and then propagate those changes to
    the database
  • Two-stage propagating process
  • Commit changes to DataSet
  • Commit changes to Database

Two-Stage Propagating Process
60
Maintaining Information About Changes
  • As a user makes changes in data-bind controls,
    the DataSets are automatically updated with the
    changes.
  • The DataRow object keeps track of these changes
    and can be accessed using its properties.

61
Retrieving Changed Rows
  • It is also important that we are able to retrieve
    the changes made by a user.
  • Easily review the changes made by a user and
    possibly validate those changes before committing
    them to a database.
  • Efficiently handle and work with only the changed
    portion of the DataSet rather than the entire
    DataSet.

62
Committing Changes in the DataSet
  • We can commit changes in the DataSet at
  • The DataRow level
  • The DataTable level
  • The DataSet level

63
Build the Application
  • How-to Update (Save) Changes to the Database
  • Add a form (Form6) to the AdvanceDBConnectivity
    application.
  • Drag-and-drop the student DataTable on the form
    from the Data Sources Window.
  • Double-click the Save button on the ToolStrip to
    open the Code Window.

64
Build the Application and Test
  • Use the code below to complete the
    TblStudentBindingNavigatorSaveItem_Click event.
  • Set Form6 as the start-up object of the
    application. Press Ctrl F5 to run the
    application. Test the application by changing
    values in the DataGridView and by saving the
    changes using the ToolStrips Save button.

Saving Changes to the Database
65
Build the Application
  • How-to Insert and Delete Records in the Database
  • Add two command buttons named cmdInsert and
    cmdDelete to Form6, as shown below.

Form6 with Command Buttons to Insert and Delete
Records
66
Build the Application (cont.)
  1. Replace the Click event code of the cmdInsert
    button with the code shown.

67
Build the Application (cont.)
  • Replace the Click event code of the cmdDelete
    button with the code shown.

68
Test the Application
  1. Ensure that Form6 is set as the start-up form and
    press Ctrl F5 to run the application.
  2. Click the Insert button and insert the record
    99999999, James Adams.
  3. Now click the Delete button and delete the record
    inserted in previous step, by giving the
    StudentID 99999999 as input.

Adding a New Record to the Database
MessageBox Showing the Record Deleted from the
Database
69
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

70
Build the Application
  • How-to Update Changes to Multiple Tables in the
    Database
  • Drag the related transcript table (under the
    student node) from the Data Sources Window on
    Form6.
  • Add a command button named cmdSave to the form.
  • Replace the Click event of the Save button with
    code shown below.

Update Code Part 1
71
Build the Application (cont.)
  • (cont.) Replace the Click event of the Save
    button with code shown below.

Update Code Part 2
72
Build the Application (cont.)
  • (cont.) Replace the Click event of the Save
    button with code shown below.

Update Code Part 3
73
Test the Application
  1. Press Ctrl F5 to run the application.
  2. Change values in both the data grids and click
    Save. Update the record with the StudentID
    10100118 so that DeptID CISE and Grade 3.6.

Running Application Updating Multiple Tables
in the Database
74
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

75
In-Class Assignment
  • Build an application with two forms
  • The first form should display the output of
    Query1
  • List all the courses from the University
    database, where the course belongs to College
    name and has name LIKE Course name.
  • Show the Query 1 output on a DataGridView on
    Form1.
  • When a user double clicks any course entry in the
    DataGridView, we should open the second form
    which displays the output of the following query
  • List all the sections that belongs to the course
    selected on the first form and have capacity of
    at least 40 students.

76
Overview
  • 15.1 Introduction
  • 15.2 ADO .NET Architecture
  • 15.3 A Database Connection
  • 15.4 Hands-On Tutorial Displaying Data with
    Multiple Parameters
  • 15.5 Hands-On Tutorial Displaying Data from Join
    of Tables
  • 15.6 Hands-On Tutorial Displaying Data Using
    Access Query
  • 15.7 Hands-On Tutorial Passing Data between
    Forms
  • 15.8 DataSets
  • 15.9 Inserts, Updates, and Deletes in a Windows
    Application
  • 15.10 Hands-On Tutorial Updating Changes to
    Multiple Tables
  • 15.11 In-Class Assignment
  • 15.12 Summary

77
Summary
  • ActiveX Data Objects (ADO) .NET is the collection
    of objects that are designed to support data
    access and data manipulation
  • Database connectivity involves the following
    entities
  • Database
  • Stores data on the hard drive (disk)
  • Connection
  • Connects to the database
  • DataAdapter
  • Hosts SQL queries and executes them against the
    database
  • DataSet
  • Stores data in main memory
  • Form
  • Displays data to the user

78
Summary (cont.)
  • ADO .NET objects interact from database to
    Windows forms
  • A Connection object opens a live connection to
    the database.
  • A DataAdapter object executes Select SQL queries
    against the database.
  • The DataAdapter object further fills a DataSet
    object with query results.
  • The DataSet stores the results in the main
    memory.
  • ADO .NET objects interact from Windows forms to
    database
  • If a user edits the data in Windows controls, the
    updates are propagated to the DataSet object.
  • The DataAdapter object then propagates the
    updates from the DataSet object to the connected
    database by executing Update SQL statements.

79
Summary (cont.)
  • Creating a database connection is like opening a
    unique session with the database.
  • Once opened, we can access contents of the
    database using the Connection object.
  • We considered some advanced topics in displaying
    data on Windows forms.
  • How to display data from a query with multiple
    parameters.
  • How to display data from a join of two or more
    tables.
  • How to make use of existing MS Access queries to
    display data on forms.
  • How to pass data between two Windows forms.

80
Summary (cont.)
  • A DataSet is essentially an in-memory relational
    database.
  • A DataSet may contain one or more DataTables.
  • Each DataTable can have multiple DataRows and
    DataColumns.
  • DataSets act as a cache for a Windows
    application.
  • We considered topics like how to insert, update,
    and delete rows from the database with the
    two-stage updates strategy.
  • Finally, to conclude the chapter, we showed how
    to handle propagation of changes to the database
    for related or multiple tables.
Write a Comment
User Comments (0)
About PowerShow.com