1 / 76

Web-Enabled Decision Support Systems

Web-Enabled Decision Support Systems. Database Connectivity with ADO .NET. Don McLaughlin Don.McLaughlin@mail.wvu.edu West Virginia University (304) 293-0405x4258. Overview. 14.1 Introduction 14.2 Database Applications Overview

sirvat
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 Database Connectivity with ADO .NET Don McLaughlin Don.McLaughlin@mail.wvu.edu West Virginia University (304) 293-0405x4258

  2. Overview • 14.1 Introduction • 14.2 Database Applications Overview • 14.3 Hands-On Tutorial: Creating a Simple Database Application • 14.4 Auto-Created Objects in the Component Tray • 14.5 Hands-On Tutorial: Displaying Data in Individual Windows Controls • 14.6 Hands-On Tutorial: Binding Data to Existing Controls • 14.7 Hands-On Tutorial: Displaying Related Data on a Windows Form • 14.8 DataGridView Control • 14.9 Hands-On Tutorial: Creating a Search Form • 14.10 The Query Builder • 14.11 Hands-On Tutorial: Creating a Look-up Table • 14.12 Data Binding Properties of a ComboBox Control • 14.13 In-Class Assignment • 14.14 Summary

  3. Introduction • A database application is a computer program that allow users to manipulate data in a DBMS through a user-friendly interface • Examples: • Amazon.com • Online shopping • Mapquest.com • Driving directions • University portals • Transcripts and tuition payment information

  4. Overview • 14.1 Introduction • 14.2 Database Applications Overview • 14.3 Hands-On Tutorial: Creating a Simple Database Application • 14.4 Auto-Created Objects in the Component Tray • 14.5 Hands-On Tutorial: Displaying Data in Individual Windows Controls • 14.6 Hands-On Tutorial: Binding Data to Existing Controls • 14.7 Hands-On Tutorial: Displaying Related Data on a Windows Form • 14.8 DataGridView Control • 14.9 Hands-On Tutorial: Creating a Search Form • 14.10 The Query Builder • 14.11 Hands-On Tutorial: Creating a Look-up Table • 14.12 Data Binding Properties of a ComboBox Control • 14.13 In-Class Assignment • 14.14 Summary

  5. Database Application Types • We classify database applications into the following three categories: • Display-oriented applications • Display data retrieved from a relational database on forms or web pages • Examples: Online newspapers, portal to view student transcript and grades, mapping websites • Transaction-oriented applications • Involve frequent transfer of data to and from a database • Examples: Online shopping, portal for university faculty to enter student grades • Communication-oriented applications • Communicate with other applications or processes • Examples: Application with underlying optimization software, Crystal Reports, Map Point

  6. Database Application Overview • A VB .NET database application involves connectivity between a database and a graphical user interface (GUI) • Achieved through ActiveX Data Objects (ADO) .NET • A collection of objects (classes) designed to support data access and manipulation • The ADO .NET architecture forms the basis of VB .NET database applications • ADO .NET object examples: • Connection object • TableAdapter object • DataSet object

  7. Database Application Processes • Connecting to the database • Establish a two-way communication channel via Connection object • Fetching data using database queries • Bring data into an application in the desired format (filtered, sorted, etc.) via TableAdapter object • Temporarily storing the result somewhere • Via DataSet object • Displaying data on Windows forms • Data binding • Editing data in the application • Saving updated data back in the database

  8. Database Application Data Cycle Database Application Data Cycle

  9. Overview • 14.1 Introduction • 14.2 Database Applications Overview • 14.3 Hands-On Tutorial: Creating a Simple Database Application • 14.4 Auto-Created Objects in the Component Tray • 14.5 Hands-On Tutorial: Displaying Data in Individual Windows Controls • 14.6 Hands-On Tutorial: Binding Data to Existing Controls • 14.7 Hands-On Tutorial: Displaying Related Data on a Windows Form • 14.8 DataGridView Control • 14.9 Hands-On Tutorial: Creating a Search Form • 14.10 The Query Builder • 14.11 Hands-On Tutorial: Creating a Look-up Table • 14.12 Data Binding Properties of a ComboBox Control • 14.13 In-Class Assignment • 14.14 Summary

  10. Create the Project • How-to: Create a Simple Database Application • In Visual Studio, choose the File | New Project option from the Main menu to open the NewProject dialog box. • Make sure to select the WindowsApplication icon in the Templates area. • Name the project DatabaseConnectivity and click OK.

  11. Create a 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 DataSourceConfigurationWizard. Opening the Data Sources Window Invoking the Data Sources Configuration Wizard

  12. Create a Data Source (cont.) • Select the Database icon on the ChooseaDataSourceType page of the wizard. Click Next. Choosing a Data Source Type

  13. Create a Data Source (cont.) • On the ChooseYourDataConnection page, click NewConnection to open the ChooseDataSource dialog box. Specifying the Data Connection

  14. Create a Data Source (cont.) • Select the Microsoft Access Database File in the Data Source list box. Click on Continue. • In the AddConnection dialog box that opens, select the University database file. Click TestConnection to verify the connection. Click OK. Creating the Database Connection

  15. Create a Data Source (cont.) • Click Yes to copy the database to the current project folder. Click Next. • On the Save the Connection String to the Application Configuration File page, save the connection string with its default name. Click Next. Saving the Connection String

  16. Create a Data Source (cont.) • Expand the Tables node on the Choose your Database Objects page, and select the student, department, transcript, and faculty tables. Click Finish. Selecting Database Tables for a Data Source

  17. Using a DataGridView Control • In the Data Sources Window, drag the student table onto Form1 to create the DataGridView control along with a ToolStrip at the top of the form. • The ToolStrip provides controls for navigation, adding/deleting records, and saving data Drag-and-Drop to Create DataGridView Control Snapshot of the Component Tray

  18. Test the Application • Press Ctrl + F5 to run the application. • Test the ToolStrip functionality by navigating through the student records. • Alter values in the DataGridView control, and click on the Save button in the ToolStrip to modify the data. Application Output

  19. Property Window Review • Click the smart tag on the DataGridView control to view its Tasks list. • Note that the DataSource property is set to the TblStudentBindingSource. • Also note the automatic enabling of various options. Tasks List of a DataGridView Control

  20. Code Review • Double click anywhere on the form to open the code behind window. • Review the auto-generated code that was created when we dragged the student table from the Data Sources Window. Form1_Load Event Populating the Student DataTable

  21. Overview • 14.1 Introduction • 14.2 Database Applications Overview • 14.3 Hands-On Tutorial: Creating a Simple Database Application • 14.4 Auto-Created Objects in the Component Tray • 14.5 Hands-On Tutorial: Displaying Data in Individual Windows Controls • 14.6 Hands-On Tutorial: Binding Data to Existing Controls • 14.7 Hands-On Tutorial: Displaying Related Data on a Windows Form • 14.8 DataGridView Control • 14.9 Hands-On Tutorial: Creating a Search Form • 14.10 The Query Builder • 14.11 Hands-On Tutorial: Creating a Look-up Table • 14.12 Data Binding Properties of a ComboBox Control • 14.13 In-Class Assignment • 14.14 Summary

  22. Auto-Created Objects in the Component Tray • Four objects were added to the Component tray when we dragged the student table node from the Data Sources Window onto Form1: • TableAdapter object • Host for SQL queries • Corresponding to each query, has a Fill method that executes a SQL query • DataSet object • Acts as an in-memory data repository • Can have any number of DataTables • DataTables are populated using a TableAdapter, by means of results of SQL queries

  23. Auto-Created Objects in the Component Tray (cont.) • BindingSource object • Mediator between a Windows control and a DataSet • BindingNavigator object • Enables users to navigate through and manipulate data on Windows forms with the help of a ToolStrip control BindingSource Object Work Diagram

  24. Overview • 14.1 Introduction • 14.2 Database Applications Overview • 14.3 Hands-On Tutorial: Creating a Simple Database Application • 14.4 Auto-Created Objects in the Component Tray • 14.5 Hands-On Tutorial: Displaying Data in Individual Windows Controls • 14.6 Hands-On Tutorial: Binding Data to Existing Controls • 14.7 Hands-On Tutorial: Displaying Related Data on a Windows Form • 14.8 DataGridView Control • 14.9 Hands-On Tutorial: Creating a Search Form • 14.10 The Query Builder • 14.11 Hands-On Tutorial: Creating a Look-up Table • 14.12 Data Binding Properties of a ComboBox Control • 14.13 In-Class Assignment • 14.14 Summary

  25. Setting the Controls to be Created • How-to: Display Data in Individual Windows Controls • Add a new form, Form2, to the DatabaseConnectivity application. • In the Data Sources Window, select the student table node, and click the drop-down arrow to select the Details option. The default is the DataGridView control. Selecting the Details Option for a Table Display

  26. Setting the Controls to be Created (cont.) • Expand the tblStudent node in the Data Sources Window. • Choose the controls for all the columns as shown below. Individual Displaying Controls for the Columns of the Student Table

  27. Setting the Controls to be Created (cont.) • Drag the tblStudent node from the Data Sources Window onto Form2 to create individual data-bind controls for each selected column. • These controls are accompanied by appropriately titled Label controls. Individual Controls Shown in Form’s Design View

  28. Test the Application • Set Form2 as the start-up form. Press Ctrl + F5 to run the application. The first record displayed is shown below. • Use the ToolStrip on top of the form to navigate through the records. Edit the value of any field, and click Save to test its functionality. Student Table Displayed One Record at a Time

  29. Property Window Review • Select the TextBox control for the Name field, and navigate to its DataBindings property in the Property Window. • Note that the Text property of the NameTextBox is associated with the Name column from the TblStudentBindingSource. Data Binding Properties for the Name Column’s TextBox Control

  30. Overview • 14.1 Introduction • 14.2 Database Applications Overview • 14.3 Hands-On Tutorial: Creating a Simple Database Application • 14.4 Auto-Created Objects in the Component Tray • 14.5 Hands-On Tutorial: Displaying Data in Individual Windows Controls • 14.6 Hands-On Tutorial: Binding Data to Existing Controls • 14.7 Hands-On Tutorial: Displaying Related Data on a Windows Form • 14.8 DataGridView Control • 14.9 Hands-On Tutorial: Creating a Search Form • 14.10 The Query Builder • 14.11 Hands-On Tutorial: Creating a Look-up Table • 14.12 Data Binding Properties of a ComboBox Control • 14.13 In-Class Assignment • 14.14 Summary

  31. Adding a PictureBox Control • How-to: Bind Data to Existing Controls • Continue with the Windows form created in the previous hands-on tutorial. Drag-and-drop a PictureBox control onto the right of the existing controls. • Verify that the Image property of the PictureBox control has the default value None. Running Application with Data-bind PictureBox Control

  32. Associating the Picture Column and Testing • Drag-and-drop the Picture column under the student table node from the Data Sources Window onto the PictureBox control. • Press Ctrl + F5 to run the application. Test the data binding for PictureBox by navigating through student records using the ToolStrip. Running Application with Data-bind PictureBox Control

  33. Property Window Review • Re-open the Property Window for the PictureBox control and review its Image property. • It is now associated with the Picture column from the TblStudentBindingSource. Data-bind Image Property for the PictureBox Control

  34. Overview • 14.1 Introduction • 14.2 Database Applications Overview • 14.3 Hands-On Tutorial: Creating a Simple Database Application • 14.4 Auto-Created Objects in the Component Tray • 14.5 Hands-On Tutorial: Displaying Data in Individual Windows Controls • 14.6 Hands-On Tutorial: Binding Data to Existing Controls • 14.7 Hands-On Tutorial: Displaying Related Data on a Windows Form • 14.8 DataGridView Control • 14.9 Hands-On Tutorial: Creating a Search Form • 14.10 The Query Builder • 14.11 Hands-On Tutorial: Creating a Look-up Table • 14.12 Data Binding Properties of a ComboBox Control • 14.13 In-Class Assignment • 14.14 Summary

  35. Adding the Related Transcript Table • How-to: Display Related Data on a Windows Form • In the Data Sources Window, expand the student table node and select the transcript table node. Drag-and-drop it onto the bottom of Form2. Adding Related Tables from the Data Sources Window Component Tray

  36. Editing the Transcript DataGridView • Select the transcript DataGridView and click its smart tag to view the Tasks list. Select the EditColumn option from the list to open the Edit Columns dialog box. Invoking DataGridView’sEdit Columns Dialog Box

  37. Editing the Transcript DataGridView (cont.) • Select the StudentID column and click Remove to delete the column. • In general, we can use the Edit Columns dialog box to edit properties of existing columns, such as column heading, width, and more. Removing StudentID Column from the DataGridView Control

  38. Editing the Transcript DataGridView (cont.) • Select the DataGridView and navigate to the Property Window. • Select the AlternatingRowsDefaultCellStyle property and click the Build button (…) to edit the alternating cell style. Styling Alternate Rows of a DataGridView Control

  39. Editing the Transcript DataGridView (cont.) • Choose the BackColor and ForeColor properties in the CellStyle Builder dialog box as shown below. Setting BackColor and ForeColor Properties

  40. Testing the Application • Press Ctrl + F5 to run the application. Test the parent-child relationship by navigating through student records using the ToolStrip. Running Application with Parent-Child Relationship

  41. Property Window Review • Select the TblStudentBindingSource object (parent) from the Component tray and open the Property Window. • Note that the DataSource property of this object is set to the UniversityDataSet. • Also, the DataMember property is set to the tblStudent data table. DataSource Property of Parent BindingSource

  42. Property Window Review (cont.) • Now select the TblTranscriptBindingSource object (child) from the Component tray and open the Property Window. • Note that the DataSource property of this object is set to the TblStudentBindingSource. • Also, the DataMember property is set to tblStudenttblTranscript, which is the name of the DataRelation object that relates parent and child tables. DataSource Property of Child BindingSource

  43. Code Review • Since we added one more table on the form, Visual Studio added one more line of code. • Executes the appropriate SQL query to fill the tblTranscript data table. Form2_Load Event Populating the Transcript and Student Tables

  44. Overview • 14.1 Introduction • 14.2 Database Applications Overview • 14.3 Hands-On Tutorial: Creating a Simple Database Application • 14.4 Auto-Created Objects in the Component Tray • 14.5 Hands-On Tutorial: Displaying Data in Individual Windows Controls • 14.6 Hands-On Tutorial: Binding Data to Existing Controls • 14.7 Hands-On Tutorial: Displaying Related Data on a Windows Form • 14.8 DataGridView Control • 14.9 Hands-On Tutorial: Creating a Search Form • 14.10 The Query Builder • 14.11 Hands-On Tutorial: Creating a Look-up Table • 14.12 Data Binding Properties of a ComboBox Control • 14.13 In-Class Assignment • 14.14 Summary

  45. DataGridView Control • The DataGridView control is one of the most used data controls • We can use it to: • Display data • Format data • Sort data • Update and delete data • Select and navigate through data records • DataGridView Tasks: • We can use the smart tag to: • Access the DataSource property of the control • Add a new column to the column collection • Enable or disable edit, delete, and insertion operations

  46. Sorting with DataGridView • DataGridView controls may be sorted by clicking any column heading in the grid • Click repeatedly to toggle sorting order (ascending/descending) • A small triangular icon appears in the column when sorted Sorting Data by DeptID Column in Descending Order

  47. Data Binding • Binding data to the DataGridView control is intuitive and straightforward • Specify data source by setting the DataSource property to a BindingSource • Set DataMember property for specific tables/queries DataSource and DataMember Properties of a DataGridView Control

  48. DataGridView: In Depth • Additional properties: • BackColor, BackgroundColor, ColumnCount, Font, ForeColor, Name, ReadOnly, RowCount, Visible, Width

  49. Overview • 14.1 Introduction • 14.2 Database Applications Overview • 14.3 Hands-On Tutorial: Creating a Simple Database Application • 14.4 Auto-Created Objects in the Component Tray • 14.5 Hands-On Tutorial: Displaying Data in Individual Windows Controls • 14.6 Hands-On Tutorial: Binding Data to Existing Controls • 14.7 Hands-On Tutorial: Displaying Related Data on a Windows Form • 14.8 DataGridView Control • 14.9 Hands-On Tutorial: Creating a Search Form • 14.10 The Query Builder • 14.11 Hands-On Tutorial: Creating a Look-up Table • 14.12 Data Binding Properties of a ComboBox Control • 14.13 In-Class Assignment • 14.14 Summary

  50. Hands-On Tutorial: Creating a Search Form • Often we are not interested in viewing an entire table of data • Prefer to display only the data that satisfies some specified criteria • We can do this using search forms in a database application • A parameterized query takes in some criteria as parameters and retrieves records that satisfy the criteria • Improve the efficiency of database applications • Fetch only the portion of the data that interests us

More Related