Web-Enabled Decision Support Systems

Web-Enabled Decision Support Systems Database Connectivity in Web Applications Prof. Name name@email.com Position (123) 456-7890 University Name

Overview • 19.1 Introduction • 19.2 Connecting to the Database Using the Server Explorer Window • 19.3 Hands-On Tutorial: Displaying Data on a Web Page • 19.4 Reconfiguring SQL Query in an AccessDataSource • 19.5 Paging, Sorting, and Data Manipulation in a GridView Control • 19.6 Hands-On Tutorial: Displaying Data on a Web Page from a Query • 19.7 Hands-On Tutorial: Displaying Images in a GridView Control • 19.8 Hands-On Tutorial: Adding Look-up and Filter Functionalities • 19.9 Hands-On Tutorial: Displaying Related Data in a DetailsView Control • 19.10 Hands-On Tutorial: Working with the Repeater Control • 19.11 Web-based Crystal Reports • 19.12 Programmatically Accessing the Database at Run-Time • 19.13 In-Class Assignment • 19.14 Summary

Introduction • A Web-based, data-driven application is a program that allows users to manipulate data in a DBMS through a Web interface • Databases are typically stored on a Web server • Accessed from a Web client using a Web browser program • Examples: • Online shopping sites: Amazon.com, Buy.com • Driving-direction providers: Mapquest.com, Yahoo Maps • Movie information portals: Imdb.com • In this chapter, we will learn how to use the Visual Studio environment to develop Web-based, data-driven applications using ASP .NET and an MS Access database

Connecting to the Access Database • How-to: Add a Database Connection Using the Server Explorer Window • Create a new ASP .NET website named WebDBConnectivity. • Open the Server Explorer Window by choosing the View | Server Explorer option from the Main menu. • Right-click on the Data Connections icon, and select the Add Connection option to open the Add Connection dialog box. Invoking the Add Connection Dialog Box

Adding and Testing a Connection • In the Add Connection dialog box, click the Browse button, and locate and select the “University.mdb” database file for Chapter 19 (see book Web site to download related files). • Click on the Test Connection button to verify the database connection. Click the OK button to add the connection. Connecting to the University Database

Exploring Database Tables and Views • How-to: View and Modify the Data in an Access Database • In the Server Explorer Window for the WebDBConnectivity project, open the newly added connection node. • To access the database tables and queries, open the Tables and Views nodes. Server Explorer Window Showing Tables and Queries

Showing Table Data • To view a database table, right-click on the listed table and select the Show Table Data option from the short-cut menu. • Opens the table in a separate tab in the Design Window of Visual Studio IDE. • Presented as a grid-like structure, very much like in Access’ Datasheet View. • We can view and edit columns’ values using this grid interface. Viewing Table Data in the Visual Studio Environment

Displaying Data on a Web Page • How-to: Display Contents of a Database Table on a Web Page • Add a Web page named “Page1.aspx” to the WebDBConnectivity project. • Drag and drop the tblStudent table from the Server Explorer Window onto Page1.aspx in the Design Window. • Automatically creates an AccessDataSource control and a GridView control. • Configures the GridView control and sets its DataSourceID property to the AccessDataSource control. Adding an AccessDataSource Control and Data-Bind GridView Control

Testing and Formatting the GridView • Run the application (Ctrl + F5) to view the student table on a Web page. • Select the GridView control and click on its smart tag, which is located on the top-right corner of the control. Select the AutoFormat option. The Student Table Displayed on a Web Page Formatting a GridView Control Using its AutoFormat Feature

Formatting the GridView (cont.) • This should pop up an AutoFormat dialog box. Select from the available pre-defined formatting schemes, and click the OK button. Selecting a Pre-Defined Formatting Scheme for a GridView Control ASP Tag for Data-Bind GridView Control

Reconfiguring a Query in an AccessDataSource • How-to: Edit a SQL Query Associated with an AccessDataSource • Open the Web page, Page1.aspx, created in the previous section. Select the auto-generated AccessDataSource1 control below the GridView control. • Click on the smart tag of AccessDataSource1 to view its Tasks list. Select the Configure Data Source option to open a Configure Data Source wizard. Configuring an AccessDataSource Using its Tasks List

Configuring the Data Source • On the first page of the Configure Data Source wizard, accept the default path. Click the Next button. • Since we have created the database connection before, the wizard page displays the path of a connected database. Choose a Database Page of the Wizard

Configuring the Select Statement • On the Configure the Select Statement page, select the tblStudent table from the Name drop-down box. Select the table columns from the Columns area as shown below. Reconfiguring a Select Query Statement

Adding a Where Clause • On the same wizard page, click the WHERE button to add a WHERE clause to the query statement. • This should pop up the Add WHERE Clause dialog box. Adding a WHERE Clause to the Query

The Add Where Clause Dialog Box • Select the Class column from the Column drop-down box. Enter the criteria “= Graduate” using the Operator and Value input areas. Click the Add button to add the selection criterion to the query. Similarly, add the criterion “Class = Full Time”. Click OK to return to the wizard page. Selection Criteria for the WHERE Clause

Configuring the Select Statement • On the Configure the Select Statement page, click the ORDER BY button to add an ORDER BY clause to the query and open the Add ORDER BY Clause dialog box. Select the Name column from the Sort by drop-down list, and specify ascending sorting order. Click OK to return to the wizard page. Adding an ORDER BY Clause to the Query

Advanced Configuration and Testing • Click on the Advanced button on the Configure the Select Statement page to open the Advanced SQL Generation Options dialog box. Select the generate CheckBox control, and click the OK button to return to the wizard page. • Click the Next button on the Configure the Select Statement page to open the Test Query wizard page. Use the Test Query button to test the query. Generating INSERT, UPDATE, and DELETE Queries for a Data Source

Testing the Application • Click the Finish button. Confirm GridView column changes by clicking the Yes button to refresh the columns and the key of the GridView control. • Test the application to verify the change. • Note that the records are now sorted in ascending order of the Name column. Student Page with Reconfigured Query AccessDataSource ASP Tag

Paging and Sorting • If a data source has hundreds of records to display, it may clutter a Web page • The GridView control provides a well-structured and well-formatted solution • Through paging, the control allows us to display a large number of records on multiple pages • Users can view a fixed number of records at a time • Navigate through multiple pages to view all the records • When the data-bind GridView control’s data source supports a sorting operation, extending this functionality to a Web interface is easy • In this section we will: • Enable paging and sorting features for a GridView control • See how to select, edit and delete a row in a GridView control

Using GridView’s Tasks List • How-to: Enable Paging, Sorting, and Data Manipulation for a GridView • Continue with Page1.aspx from the previous sections. Click on the smart tag of the GridView control to view its Tasks list. • Check the Enable Paging, EnableSorting, EnableEditing, EnableDeleting, and EnableSelection options from the Tasks list as shown below. Using GridView’s Tasks List

Testing the Application • Run and test the application. Selecting a Student Record Updating a Student Record Paging Functionality

Adding an AccessDataSource Control • How-to: Display Data on a Web Page from a Multiple Table Query • Add a new Web page, Page2.aspx, to the WebDBConnectivity project. • Open the Toolbox window by choosing the View | Toolbox option from the Main menu. • Drag and drop the AccessDataSource control under the Data category in the Toolbox onto Page2.aspx. Adding an AccessDataSource from the Toolbox onto a Page

Selecting a Database • Click on the smart tag of the AccessDataSource control to view its Tasks list. Select Configure Data Source to open the Configure Data Source wizard. • Select the University.mdb database file, and click on the Next button. • On the Configure the Select Statement page, choose the Specify a custom SQL statement or stored procedure option, and click the Next button. Selecting the Existing Database for an AccessDataSource

The Query Builder Dialog Box • Click the Query Builder button on the Define Custom Statements or Stored Procedures page to open the Add Table dialog box. Select the tblFaculty and tblDepartment tables and click the Add button. • Query: “Display the details of faculty members who joined the university after 1995 and have a salary of more than $50,000.” Designing a Multi-Table Faculty Query in the Query Builder Dialog Box

Query Building and Adding a GridView • In the Query Builder dialog box, select the appropriate columns. In the Filter column of the grid, enter the comparison values for the Salary and JoiningDate fields. Click the Execute Query button test the query. Click OK. • Click the Finish button to close the Configure Data Source wizard. • Drag and drop a GridView control onto Page2.aspx. • Use the Choose Data Source drop-down list of the GridView control’s Tasks list to select the AccessDataSource1 data source created in steps 1-9. Binding a GridView Control to an AccessDataSource

Formatting GridView and Testing • Select the AutoFormat option, and choose the RainyDay template. • Select the EnablePaging, EnableSorting, and EnableSelection options. • Set Page2.aspx as the start page for the application. • Run and test the application. List of Faculty Members on a Web Page AccessDataSource ASP Tag

Reconfigure the SQL Query • How-to: Display Data-Bind Images in a GridView Control • Click on the smart tag of the AccessDataSource1 control to view its Tasks list. Choose the Configure Data Source option to open the Configure Data Source wizard. • On the Define Custom Statements or Stored Procedures page of the wizard, modify the existing SQL query to add the Picture field from the faculty table. Adding the Picture Field from the Faculty Table to the SQL Query

Testing the Application • Run and test the application. • We should see the path of the image file rather than the image itself. • This is because the table column actually has image paths stored. • Actual images are stored externally to the database. Faculty Information with the New Picture Column

Editing GridView Columns • Open the GridView control’s Tasks list, and select the Edit Columns option to open the Fields dialog box. • Delete the existing Picture column. Select the Picture column from the list under the Selected fields area, and click the Delete button. • Note that removing the Picture field from the GridView control does not remove it from the AccessDataSource. Accessing the Edit Columns Option Removing the Picture Field

Configuring the ImageField • From the Available fields area, select the ImageField entry and click Add. • Select the newly added ImageField, and view its properties in the ImageField Properties pane. Set the HeaderText property to the text “Picture” and the DataImageUrlField property to the data source column, Picture. Click OK. Adding an ImageField Binding the ImageField to the Picture Column

Testing the Application • Run and test the application. Running Application with an ImageField The ImageField ASP Tag

Adding a Page and AccessDataSource • How-to: Use Parameterized Queries and Provide Look-Up and Filter Functionalities on a Web Page • Add another page, Page3.aspx, to the WebDBConnectivity project. • Add an AccessDataSource from the Toolbox Window onto the Web page. • Click the smart tag of the AccessDataSource, and choose the Configure Data Source option to open the Configure Data Source wizard. • Select the University.mdb database file, and click the Next button.

Configuring the Select Statement • On the Configure the Select Statement page, select tblDepartment from the Name drop-down list, and select the DeptID and Name columns as shown. • Drag and drop the DropDownList control from the Toolbox onto page. Configuring an AccessDataSource to Query the tblDepartment Table

Configuring the DropDownList • Click the smart tag of the DropDownList control to open its Tasks list. Select the Choose Data Source option to open the Data Source Configuration wizard with the Choose Data Source page on top. Adding a DropDownList Control to a Web Page

Configuring the DropDownList (cont.) • Select the AccessDataSource1 control from the Select a data source drop-down list. Select the Name field for the field to display and DeptID field for the value field. Click OK. • Check the Enable AutoPostBack option of the DropDownLists’s Tasks list. • Drag and drop a Label control just above the DropDownList control, and set its Text property to “Select a Department:”. Specifying Data Source and the Display and Value Fields

Setting the Start Page and Testing • Set Page3.aspx as the start page of the application. • Run and test the application. Running Application DropDownList and AccessDataSource Tags

Configuring the AccessDataSource • Drag and drop the AccessDataSource control onto the page. Use the control’s Tasks list to open the Configure Data Source wizard. • On the Configure the Select Statement page, select the tblStudent table and its columns as shown below. Selecting Fields for the Student Query

Adding a Where Clause and a GridView • Click the WHERE button to add the WHERE clause to the query design. Set the Add WHERE Clause dialog box as shown. Click the Add button. • Test the query and click Finish to close the Configure Data Source wizard. • Drag and drop a GridView control onto the page. Use the control’s Tasks list to set its data source to the AccessDataSource2 control created in step 13. Also, choose the Enable Paging option. Adding a WHERE Clause; Specifying a Parameter and its Value Source

Testing the Application • Run and test the application. Application Output: Department Lookup and Filtered Student Records AccessDataSource ASP Tag

Enabling Selection and Adding Controls • How-to: Use the DetailsView Control to Show Details about a Record • Open the Page3.aspx file of the WebDBConnectivity project. • Click on the smart tag of the GridView control to open its Tasks list. Check the Enable Selection option from the list. • When the user clicks this Select link, we display detailed information about the selected student. • Add another AccessDataSource control, AccessDataSource3, to the Web page. • We will fetch the student’s details using this data source. • Use AccessDataSource control’s Tasks list to invoke the Configure Data Source wizard.

Configuring the Select Statement • Choose the University database on the first page of the wizard. On the second page, Configure the Select Statement, select the tblStudent table and its columns as shown. Configuring a Select Query for the DetailsView Control’s Data Source

The Where Clause • Click on the WHERE button to add the WHERE clause to the select query. Follow below to set the AddWHEREClause dialog box. Click the Add button. Click the OK button to return to the Configure Data Source wizard. • On the same page of the wizard, click the Advanced button, and select the Generate INSERT, UPDATE, and DELETE statements option. Specifying the WHERE Clause and Linking its Value Source

Web-Enabled Decision Support Systems