Connecting ASP.NET pages to a database, especially if you use some of the wizard-style features of Visual Studio, is extremely easy. That's because many of the controls included with ASP.NET are specifically designed to display and interact with data from tabular data sources. We'll try out a quick wizard example here, and do a lot more database integration in this chapter's "Project" section.
In Chapter 20, the first of the five built-in reports we created for the Library system displayed a list of all checked-out items. We designed an RDLC report for it, and because ASP.NET includes an RDLC Report Viewer control, we could reuse that for a Web-based report. But instead we'll display the report using one of the Web Forms controls, GridView. Here's the query that retrieves the checked-out items.
SELECT PA.LastName + ', ' + PA.FirstName AS PatronName, PA.Barcode AS PatronBarcode, PC.DueDate, IC.CopyNumber, IC.Barcode AS ItemBarcode, NI.Title, CMT.FullName AS MediaName FROM Patron AS PA INNER JOIN PatronCopy AS PC ON PA.ID = PC.Patron INNER JOIN ItemCopy AS IC ON PC.ItemCopy = IC.ID INNER JOIN NamedItem AS NI ON IC.ItemID = NI.ID INNER JOIN CodeMediaType AS CMT ON NI.MediaType = CMT.ID WHERE PC.Returned = 0 AND PC.Missing = 0 AND IC.Missing = 0 ORDER BY NI.Title, IC.CopyNumber, PA.LastName, PA.FirstName
That should look familiar. Create a new ASP.NET web site through Visual Studio. Type the following line at the top of the content page.
ACME Library Checked Out Items
Feel free to embellish it to make it look nicer. I added <h1> tags around it in the markup to make it stand out. Below that title line, add a new GridView control to the page. I found it in the Data section of my Visual Studio Toolbox. The control's smart tag opens and shows a panel of GridView Tasks, as shown in Figure 22-9.
Figure 22-9. A short list of tasks for the GridView control
If you want to click on the "Auto Format" task and change the look of the grid, you can, but the important task for now is "Choose Data Source." Select "<Add New Source>" from the list. Our old friend the Data Source Configuration Wizard appears again, although with some changes specific to ASP.NET. Select "Database" for the data source type and click the OK button. When prompted for the connection, you should already have a Library database connection in the list. Select it (or create a new connection) and click Next.
You'll be asked to save the connection string in the application configuration file. If you do, it will add an entry to the <connectionStrings> section of the web.config file created for the ASP.NET application. If you like to play power games with your system administrator, leave the field unchecked. But if you want an easy way to modify the connection information later, you had better leave the field as it is, giving the entry a reasonable name. Then click Next.
The wizard prompts you for table and field details. Select Specify a custom SQL statement or stored procedure, click Next, and type in the checked-out-item query. Click Next again. The wizard gives you one last chance to test the query before you click the Finish button.
Now here's the simple part. Visual Studio connects to the database, reads the schema, and creates columns in the grid perfectly designed for the query. Your application is complete. Press F5 to run it.
We're going to stop there for now and pick this up in the "Project" section.