Transcript VB Advanced Database Connectivity

Web-Enabled Decision Support Systems
Advance Topics in Database Connectivity
Don McLaughlin
IE 423
West Virginia University
[email protected]
(304) 293-0388
1
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
2
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
3
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
4
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
5
ADO .NET Architecture (cont.)

From database to Windows forms:
1. A Connection object opens a live connection to the database
2. A DataAdapter object executes Select SQL queries against the database
3. The DataAdapter object further fills a DataSet object with query results

The DataSet stores the results in the main memory
4. 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
6
ADO .NET Architecture (cont.)

From Windows forms to database:
1. If a user edits the data in Windows controls, the updates are propagated to
the DataSet object
2. The DataAdapter object then propagates the updates from the DataSet
object to the connected database by executing Update SQL statements
7
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
8
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
9
Creating the Project and Data Connection

How-to: Create a Connection Using the Server Explorer Window
1. Create a new Windows Application with the name, AdvanceDBConnectivity.
2. Select the View | Server Explorer option from the Main menu to open the
Server Explorer Window.
3. Right-click the Data Connection node in the window and choose the Add
Connection option.
Adding a Connection in the Server Explorer Window
10
Creating the Project and Data Connection (cont.)
4. 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
11
Creating the Project and Data Connection (cont.)
5. 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
12
Creating the Data Source
6. Choose the Data | Show Data Sources option from the Main menu.
7. In the Data Sources Window, click Add New Data Source to start the Data
Source Configuration wizard.
8. Select the Database icon on the Choose a Data Source Type page, and then
click Next.
9. 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
13
Creating the Data Source (cont.)
10. When enquired, copy the database to the current project folder. Click Next.
11. On the next page of the wizard save the connection with the default name
UniversityConnectionString. Click Next.
12. 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
14
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=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\University.mdb
– Provider:
 Shows the type of the database
 Jet Engine for MS Access database
– Data Source:
 Stores the physical path of the database file
15
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
16
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
17
Create a DataGridView Control

This application will be based on the faculty table
– The two parameters are the faculty’s 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
1. 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
18
Open the DataSet Designer
2. Right-click TblFacultyTableAdapter from the Component tray and choose
Edit Queries in DataSet Designer option to open the DataSet Designer tab.
Invoking the DataSet
Designer to Edit Queries
– Alternatively:


Double-click the DataSet (.xsd) file from the Solution Explorer Window
Click the Edit DataSet with Designer icon from the Data Sources Window
Opening the
DataSet Designer
19
Adding Parameter Queries
3. 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
20
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
21
Adding Parameter Queries (cont.)
4. Select the TblFacultyTableAdapter in the DataSet Designer and right-click to
choose the Add Query option to open the Query Configuration wizard.
Adding a Query
to a TableAdapter
4. On the first page of the wizard (Choose a Command Type), choose the Use
SQL statements option and click Next.
Choosing a
Command Type
22
Adding Parameter Queries (cont.)
6. 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
23
Adding Parameter Queries (cont.)
7. 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
24
Adding Parameter Queries (cont.)
8. Finally, name the Fill a DataTable and Return a DataTable methods as
shown in below and click Finish.
Choose and Name
the Methods to be
Added to the TableAdapter
– The faculty TableAdapter now resembles below with its additional methods.
Faculty TableAdapter
with Parameterized Query
25
Add Existing Query
9. Right-click the TblFacultyTableAdapter from the Component tray and choose
the Add Query option to open the Search Criteria Builder dialog box.
10. Choose the Existing query name option. Then, choose the FillByDeptSal
method we created in the previous step from the drop-down list.
Assigning a Query to
a TableAdapter in the
Search Criteria Builder
Dialog Box
11. Click OK to create two TextBox controls for user input in the ToolStrip.
26
Test the Application
12. 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
27
Code Review

Note the code used to assign multiple parameters (lines 21-25).
Code Review for the Multiple Parameter Query
28
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
29
Add a New TableAdapter

How-to: Display Data on a Form from a Join of Multiple Tables
1. Add a new form, Form2, to the AdvanceDBConnectivity application.
2. 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
30
Add a New TableAdapter (cont.)
3. On the Choose Your Data Connection page, accept the selected University
database connection string. Click Next.
Specifying Connection for a New TableAdapter
4. On the Choose a Command Type page, check Use SQL statements option
(default) and click Next.
31
Add a New TableAdapter (cont.)
5. 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
32
Add a New TableAdapter (cont.)
6. 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
33
Add a New TableAdapter (cont.)
7. 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.
8. Now add the DISTINCT keyword after the SELECT keyword in the SQL
pane.
9. Add the query parameters (DeptID and Grade) in the WHERE clause.
34
Add a New TableAdapter (cont.)
10. 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
35
Create a DataGridView Control and Test
11. From the Data Sources Window, drag-and-drop the DataTable1 table on
Form2 to create a DataGridView control.
12. 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
36
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
37
Add Access Query

How-to: Display Data Using Existing MS Access Query
1. Add a Form3 to the AdvanceDBConnectivity application.
2. 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
38
Add Access Query (cont.)
3. Choose the Continue with wizard option to open the Data Source
Configuration wizard.
Invoking Data Sources Configuration Wizard
39
Add Access Query (cont.)
4. 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
40
Create a DataGridView and Test
5. From the Data Sources Window, drag-and-drop the qryGPA data table on
Form3 to create a DataGridView control.
6. 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
41
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
42
Passing Data Between Forms
Application Flow: Passing Data between Two Windows Forms
43
Create the First (Faculty) Form

How-to: Pass Data between Two Windows Forms
1. Add Form4 to the AdvanceDBConnectivity application.
2. 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.
44
Create the Second (Course) Form
3.
4.
5.
6.
Add Form5 to the same application.
Switch to the DataSet Designer by double-clicking on UniversityDataSet.xsd.
Right-click in the designer window to add a new TableAdapter.
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
45
Create the Second (Course) Form (cont.)
7. Rename the Fill method as FillFaculty before finishing the configuration
wizard.
8. Name the new table adapter as qryFacultyCoursesTableAdapter and the
new data table as qryFacultyCourses.
9. 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.
46
Write a Subroutine
10. 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
47
Write Code to Handle Events
11. In the faculty form, Form4.vb, write the code for DataGridView’s 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
48
Test the Application
12. Set Form4 as the application’s 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
49
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
50
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, dataset’s 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
51
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.
52
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
53
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 DataTable’s FindBy<PrimaryKey> method or
by utilizing the row index.
Editing a DataRow in a DataTable
Editing a DataRow in a DataTable Using a Row Index
54
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
55
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
56
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
57
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
58
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
59
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.
DataRow State
Description
Added
The row that has been added to the Rows collection.
Deleted
The row that has been deleted.
Modified
A column of the row that has been changed.
Unchanged
Whether the row has been changed since the last call to AcceptChanges.
60
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.
Usage Example
Description
DataSet1.GetChanges()
Returns new DataSet with only changed records.
DataTable1.GetChanges()
Returns new DataTable with only changed records.
DataSet1.GetChanges(DataRowState.Added)
Returns new DataSet with only newly added records.
DataTable1.GetChanges(DataRowState.Added)
Returns new DataTable with only newly added records.
DataSet1.GetChanges(DataRowState.Deleted)
Returns new DataSet with only deleted records.
DataTable1.GetChanges(DataRowState.Deleted)
Returns new DataTable with only deleted records.
61
Committing Changes in the DataSet

We can commit changes in the DataSet at:
– The DataRow level
– The DataTable level
– The DataSet level
Methods
Results
DataRow.AcceptChanges()
Changes are committed only on the specific row
DataTable.AcceptChanges()
Changes are committed on all rows in a specific table
DataSet.AcceptChanges()
Changes are committed on all rows in all the tables of the DataSet
62
Build the Application

How-to: Update (Save) Changes to the Database
1. Add a form (Form6) to the AdvanceDBConnectivity application.
2. Drag-and-drop the student DataTable on the form from the Data Sources
Window.
3. Double-click the Save button on the ToolStrip to open the Code Window.
63
Build the Application and Test
4. Use the code below to complete the
TblStudentBindingNavigatorSaveItem_Click event.
Saving Changes
to the Database
5. 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 ToolStrip’s Save button.
64
Build the Application

How-to: Insert and Delete Records in the Database
1. Add two command buttons named cmdInsert and cmdDelete to Form6, as
shown below.
Form6 with Command Buttons to Insert and Delete Records
65
Build the Application (cont.)
2. Replace the Click event code of the cmdInsert button with the code shown.
66
Build the Application (cont.)
– Replace the Click event code of the cmdDelete button with the code shown.
67
Test the Application
3. Ensure that Form6 is set as the start-up form and press Ctrl + F5 to run the
application.
4. Click the Insert button and insert the record [99999999, “James Adams”].
Adding a New Record
to the Database
5. Now click the Delete button and delete the record inserted in previous step,
by giving the StudentID = 99999999 as input.
MessageBox Showing the Record
Deleted from the Database
68
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
69
Build the Application

How-to: Update Changes to Multiple Tables in the Database
1. Drag the related transcript table (under the student node) from the Data
Sources Window on Form6.
2. Add a command button named cmdSave to the form.
3. Replace the Click event of the Save button with code shown below.
Update Code:
Part 1
70
Build the Application (cont.)
3. (cont.) Replace the Click event of the Save button with code shown below.
Update Code:
Part 2
71
Build the Application (cont.)
3. (cont.) Replace the Click event of the Save button with code shown below.
Update Code:
Part 3
72
Test the Application
4. Press Ctrl + F5 to run the application.
5. 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
73
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
74
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.”
75
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
76
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
77
Summary (cont.)

ADO .NET objects interact from database to Windows forms:
1. A Connection object opens a live connection to the database.
2. A DataAdapter object executes Select SQL queries against the database.
3. 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:
1. If a user edits the data in Windows controls, the updates are propagated to
the DataSet object.
2. The DataAdapter object then propagates the updates from the DataSet
object to the connected database by executing Update SQL statements.
78
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.
79
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.
80