Bookstore Web Application: Information Tier Examining the Database and Creating Database Components

1. 30 • Bookstore WebApplication:Information Tier • Examining the Database andCreating Database Components

2. Outline • 30.1 Reviewing the Bookstore Web Application • 30.2 Information Tier: Database • 30.3 Connecting to the Database and Retrieving Information

3. In this tutorial you will learn: Connect to a database in Visual Web Developer. Create LINQ to SQL classes in a web application. Create a LinqDataSource. Create a data-bound ListBox using a LinqDataSource. Objectives

4. 30.1 Reviewing the Bookstore Web Application When the Books page is requested Retrieve the book titles from the database Display book titles in a ListBox When the user selects a book title from the ListBox Display the book’s cover image below the ListBox in an Image When the user clicks the View Information Button If the user did not select a book from the ListBox Display an error message Otherwise Store the selected book’s product ID Redirect the browser to the BookInformation page

5. 30.1 Reviewing the BookstoreWeb Application (Cont.) When the BookInformation page is requested Retrieve the selected book’s information from the database Display the book title in a Label Display the authors in a Label Display the cover art in an Image Display the remaining information in a DetailsView When the user clicks the Return to Book List Button on the BookInformation page Redirect the browser back to the Books page

6. Action/Control/Event (ACE) Table forthe Bookstore Web Application • Review the ACE table (Fig. 30.1) for this application: Figure 30.1| ACE table for the Bookstore web application. (Part 1 of 2.)

7. Action/Control/Event (ACE) Table forthe Bookstore Web Application (Cont.) Figure 30.1| ACE table for the Bookstore web application. (Part 2 of 2.)

8. Action/Control/Event (ACE) Table forthe Bookstore Web Application (Cont.) Figure 30.1| ACE table for the Bookstore web application. (Part 3 of 2.)

9. 30.2 Information Tier: Database • The information tier maintains all the data needed for an application. • Figure 30.2 displays the Products table of Bookstore.mdf. Products table of the Bookstore.mdf database Figure 30.2|Products table of the Bookstore.mdf database.

10. Adding the Bookstore.mdf Databaseto the Bookstore Web Application • Re-open the Bookstore web application. • Select Tools > Connect to Database… (Fig. 30.3). • Make sure the Data source: is set to Microsoft SQL Server Database File (SqlClient). • Next, click Browse..., and select Bookstore.mdf, then click OK.

11. Adding the Bookstore.mdf Databaseto the Bookstore Web Application (Cont.) Figure 30.3|Adding theBookstore.mdf database to the web application.

12. Creating LINQ to SQL Classes fromthe Bookstore.mdf Database • Right click the project in the Solution Explorerand select Add > New Item • Select LINQ to SQL Classes and create BookInformationDB.dbml (Fig. 30.4) Select LINQtoSQLClassesin Templates:pane Rename the LINQ to SQL classes Figure 30.4|Adding LINQ to SQL classes.

13. Creating LINQ to SQL Classes fromthe Bookstore.mdf Database (Cont.) • Click Yes on the dialog that appears (Fig. 30.5). • The App_Code folder is used to store your web application’s code files on the server. Figure 30.5| Confirming that the LINQ to SQL classes will beadded to the App_Code folder.

14. Creating LINQ to SQL Classes fromthe Bookstore.mdf Database (Cont.) • Double click BookInformationDB.dbml inthe Solution Explorer. • Drag the Bookstore.mdf database’s Productstable from the Database Explorer onto the Object Relational Designer (Fig. 30.6). • Click Yes on the dialog that appears. Add the Products table tothe Object Relational Designer Figure 30.6|Adding the Products table to the LINQ to SQL classes.

15. Creating a LinqDataSource • Select the bookTitlesListBox in the Books.aspx page and click the small black arrow in the upper-right corner (Fig. 30.7). • Select Choose Data Source…. Figure 30.7| Choosing the ListBox’s data source.

16. Creating a LinqDataSource (Cont.) • Select <New data source…> from the Selecta data source:ComboBox (Fig. 30.8). Create a new data source Figure 30.8|Creating a new data source.

17. Creating a LinqDataSource (Cont.) • Select LINQ and name the data source linqDataSource (Fig. 30.9). • Click OK to proceed. The LINQ data source type Name the data source Figure 30.9| Creating a LinqDataSource.

18. Creating a LinqDataSource (Cont.) • The LINQ to SQL classes include a DataContext object used to access the database tables. • Select the BookInformationDBDataContext object from the ComboBox and click Next > (Fig. 30.10). BookInformationDBDataContext object selected as the datasource’s context Figure 30.10| Selecting a DataContext object for the data source.

19. Creating a LinqDataSource (Cont.) • By default, the LinqDataSource retrieves all columns from the specified table—indicated by the *CheckBox (Fig. 30.11). Products table selected Select table columnscontained in the LinqDataSource Figure 30.11|Configuring the information contained in the data source.

20. Creating a LinqDataSource (Cont.) • Click the Order By… Button to determine how the information in the data source is sorted. • Select Title from the ComboBox in the Sort byGroupBox (Fig. 30.12), then click OK. • Click Finish. Figure 30.12|Determine the order in which information is stored in the data source.

21. Creating a LinqDataSource (Cont.) • Select Title from the Select a data field to display in the ListBox:ComboBox(Fig. 30.13). • Select ProductID from the Select a data field for the value of the ListBox: ComboBox (Fig. 30.13). • This will be used to uniquely identify the selected book. • Click OK to close the Data Source Configuration Wizard.

22. Creating a LinqDataSource (Cont.) Select the data displayed Select the data stored in the SelectedValue property Figure 30.13| Setting the display and value fields for the ListBox.

23. Creating a LinqDataSource (Cont.) • Run the application (Fig. 30.14). Figure 30.14|Books.aspx displaying book titles in data-bound ListBox.