html5-img
1 / 80

Web-Enabled Decision Support Systems

Web-Enabled Decision Support Systems. Advance Topics in Database Connectivity. Prof. Name name@email.com Position (123) 456-7890 University Name. Overview. 15.1 Introduction 15.2 ADO .NET Architecture 15.3 A Database Connection

ciel
Télécharger la présentation

Web-Enabled Decision Support Systems

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Web-Enabled Decision Support Systems Advance Topics in Database Connectivity Prof. Name name@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 DataConnection node in the window and choose the AddConnection option. Adding a Connection in the Server Explorer Window

  11. Creating the Project and Data Connection (cont.) • 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.) • 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 • Choose the Data | Show Data Sources option from the Main menu. • In the Data Sources Window, click Add New Data Source to start the Data Source Configuration wizard. • Select the Database icon on the Choose a Data Source Type page, and then click Next. • 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.) • When enquired, copy the database to the current project folder. Click Next. • On the next page of the wizard save the connection with the default name UniversityConnectionString. Click Next. • Expand the Tables node on the ChooseYourDatabaseObjects 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 • DataSource: • 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 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 • 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 EditQueriesinDataSetDesigner option to open the DataSetDesigner tab. • Alternatively: • Double-click the DataSet (.xsd) file from the Solution Explorer Window • Click the EditDataSetwithDesigner icon from the Data Sources Window Invoking the DataSet Designer to Edit Queries Opening the DataSetDesigner

  20. Adding Parameter Queries • Select the TblFacultyTableAdapter in the DataSet Designer, right-click “Fill, GetData ()” text, and choose the Configure option. This should open the TableAdapterConfiguration wizard. Review and Configure Existing Queries in a TableAdapter

  21. Adding Parameter Queries (cont.) • In the TableAdapterConfiguration 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 QueryBuilder dialog box. Edit Existing Query Using TableAdapter Configuration Wizard

  22. Adding Parameter Queries (cont.) • Select the TblFacultyTableAdapter in the DataSetDesigner and right-click to choose the AddQuery option to open the QueryConfiguration wizard. • 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.) • On the next page (Choose a Query Type), choose the first option (SELECT which returns rows) and click Next. Choosing the Query Type in TableAdapterQueryConfiguration Wizard

  24. Adding Parameter Queries (cont.) • 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 TableAdapterQueryConfiguration 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 • Right-click the TblFacultyTableAdapter from the Component tray and choose the AddQuery option to open the SearchCriteriaBuilder dialog box. • Choose the Existingqueryname option. Then, choose the FillByDeptSal method we created in the previous step from the drop-down list. • 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 AdvanceDBConnectivity application. • Open the DataSetDesigner. Right-click anywhere in the designer window and choose the Add | TableAdapter option to open the TableAdapterConfiguration wizard. Adding a New TableAdapter to the Application

  31. Add a New TableAdapter (cont.) • On the Choose Your Data Connection page, accept the selected University database connection string. Click Next. • 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.) • 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.) • Note that the two selected tables appear in the table pane of the QueryBuilder dialog box. Click Close on the AddTable 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.) • Click OK on the Query Builder dialog box. Click Next on the TableAdapterConfiguration 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 • From the Data Sources Window, drag-and-drop the DataTable1 table on Form2 to create a DataGridView control. • 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 ConfigureDataSetwithWizard icon to open the Choose DataSet Editor dialog box. Using Data Sources Window to Invoke the Dataset Configuration Wizard

  39. Add Access Query (cont.) • Choose the Continue with wizard option to open the Data Source Configuration wizard. Invoking Data Sources Configuration Wizard

  40. Add Access Query (cont.) • 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 • From the Data Sources Window, drag-and-drop the qryGPA data table on Form3 to create a DataGridView control. • 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 • Add Form5 to the same application. • Switch to the DataSetDesigner by double-clicking on UniversityDataSet.xsd. • Right-click in the designer window to add a new TableAdapter. • Follow the TableAdapterConfiguration 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 qryFacultyCoursesTableAdapter 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 • In the faculty form, Form4.vb, write the code for DataGridView’sDoubleClick 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 • 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

  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

More Related