Project

For this chapter's project, I built a simple multi-page web site that (1) lets the user look up items in the Library database; and (2) duplicates the Library Statistics report created in Chapter 20, but without the RDLC component. I went ahead and included the completed project in your installed source code directory, in the LibraryWebSite subdirectory. You can open it by locating its directory with the File Open Web Site menu command in Visual Studio.

As shown in Figure 22-12, the project includes 11 files and two subdirectories.

Figure 22-12. The Library web site project files

Here's a quick rundown of each file and subdirectory.

• App_Code. The directory for all project code that is not code-behind for an actual ASP.NET web page. All of your general modules and classes for a project appear here.

• General.vb. A code module with six methods that I stole almost verbatim from the main Library Project's General.vb file. I made just a few changes, mostly to deal with not having the LibraryDB global variable sitting around with its connection information.

• BoundSchemas.vb. When we started the checked-out-item report earlier in this chapter, we bound a GridView control to a database query. The GridView control, like most of the Web Forms controls, can also bind to any class that supports the IList interface, including the generic collection classes. The classes in this file will be used for instances within a generic collection in two of the web pages.

• App_Data. This directory normally contains data needed by the application. It was created automatically by Visual Studio. I don't use it in this project.

• Default.aspx and Default.aspx.vb. This is the entry point of the Library web site. The client browser requests the Default.aspx web page to start the library experience. The page includes a link to the statistics report, plus fields that let you look up library items by title or author.

• SearchResults.aspx and SearchResults.aspx.vb. Searches performed from the Default.aspx page end up here, with queries processed in code and then bound to a grid.

• SearchDetail.aspx and SearchDetail.aspx.vb. Each search result includes a link to this page. It provides additional detail for an item, in the same format used in the main Library project's ItemLookup.vb file.

• Statistics.aspx and Statistics.aspx.vb. This page displays the Library Statistics report as a bound web page.

• Web.config. Every ASP.NET project includes a web.config file used to specify project-specific settings in the development and production environments.

Configuring the Database

The web.config file contains a place for database connection strings. I've added an entry within it for the Library database.

[View full width]
<connectionStrings>   <add name="LibraryConnection" connectionString= "Data Source=MYSERVER\SQLEXPRESS;  Initial Catalog=Library;Integrated Security=True" /> </connectionStrings> 

Modify the "MYSERVER\SQLEXPRESS" portion to the name of your SQL Server database instance, and modify the other parts of the connection string as needed. All four of the web pages use the Library database, and they all access the connection string from this entry, via the ConfigurationManager object.

Public LibraryDB As System.Data.SqlClient.SqlConnection ...and later... LibraryDB = New SqlClient.SqlConnection( _    ConfigurationManager.ConnectionStrings( _    "LibraryConnection").ConnectionString) LibraryDB.Open() ...and later still... LibraryDB.Close() 

The Default Page

The Default.aspx page is the starting point for the Library web application, and appears in Figure 22-13.

Figure 22-13. The Library web site's default page

Its code is not much to talk about. It simply fills in the Media Type drop-down list with the available types from the database.

sqlText = "SELECT ID, FullName FROM CodeMediaType " & _    "ORDER BY FullName" dbInfo = CreateReader(sqlText, LibraryDB) Do While dbInfo.Read    SearchMediaType.Items.Add(New WebControls.ListItem( _       CStr(dbInfo!FullName), CStr(dbInfo!ID))) Loop dbInfo.Close() 

The page itself is a little more interesting. When we built the sample ASP.NET web application earlier, each click on the Multiply button sent the page back to itself. It was a one-page application. Most web applications would be useless with only a single page, so button clicks and links need to jump elsewhere in the project. The report link at the bottom of this page is a standard hyperlink to Statistics.aspx, another page within the application. In the search portion of the page, the Search button (ActSearch) also jumps to another project page, SearchResults.aspx. It does this through its PostBackUrl property, which is set to "~/SearchResults.aspx." The new page will have indirect access to all of the field selections on this starting page.

Search Results

The SearchResults.aspx page displays any matching results from the Default.aspx item search section. As shown in Figure 22-14, it includes a GridView control for the listing of results, plus a Label control that shows a count of the matches.

Figure 22-14. The Library web site's search results page

Unlike the GridView populated earlier, this one does not connect directly to a database query. Instead, I hand-build instances of the BoundSchemaSearchResults class (from BoundSchemas.vb), collect them into a generic List, and bind them to the fields in the GridView. Actually, binding in this way is a snap. Each column I configured in the GridView control looks for a property in the incoming records that matches a specific field name. These columns are defined through the Column Editor (see Figure 22-15), accessed via the control's Columns property.

Figure 22-15. Editing columns in a GridView control

Figure 22-15 shows the properties for the first bound data column, "Item Name." It's bound to a field in the data named ItemData via the DataField property. The next two columns are configured similarly, but use the incoming data fields AuthorName and MediaType. The fourth column provides a hyperlink to the SearchDetail.aspx for each matching record. To build this column, I added it as a HyperLinkField column instead of a BoundField column. I set its Text property to "Detail," which will appear on every record. Clicking on the link will pass the ID of the matching item (I set the DataNavigateUrlFields to ID) to the target page via a query string. The DataNavigateUrlFormatString property contains a string that will be sent to the String.Format method, along with the fields listed in DataNavigateUrlFields. Here is the format string.

SearchDetail.aspx?ID={0} 

The "{0}" part gets replaced with the value of each record's ID field.

This page's Load event handler is triggered by a call from the Search button on the Default.aspx page. When a Web Forms page called itself, it can directly examine the values in its controls. But the controls on the Default.aspx page don't exist here in the SearchResults.aspx page. Fortunately, the previous page's controls are sent as data to the new page. You can access them through the PreviousPage object. The following code extracts the values from each of the search fields.

' ----- Get the title search text. sourceTextBox = CType(PreviousPage.FindControl( _    "SearchTitle"), TextBox) If (sourceTextBox IsNot Nothing) Then _    useTitle = Trim(sourceTextBox.Text) ' ----- Get the last name search text. sourceTextBox = CType(PreviousPage.FindControl( _    "SearchLastName"), TextBox) If (sourceTextBox IsNot Nothing) Then _    useLastName = Trim(sourceTextBox.Text) ' ----- Get the first name search text. sourceTextBox = CType(PreviousPage.FindControl( _    "SearchFirstName"), TextBox) If (sourceTextBox IsNot Nothing) Then _    useFirstName = Trim(sourceTextBox.Text) ' ----- Get the media type value. sourceMediaType = CType(PreviousPage.FindControl( _    "SearchMediaType"), DropDownList) If (sourceMediaType IsNot Nothing) Then _    useMediaType = sourceMediaType.SelectedValue 

Amazingly, the previous page didn't just send its fields as string values. Instead, they retained their existence as true objects. Using the CType function to convert them to TextBox and DropDownList controls was enough to access their control properties.

I use the user-supplied values to build a SQL statement and query the database for results. If there are any, the resulting data is massaged into a list of objects.

Dim oneEntry As BoundSchemaSearchResults Dim reportData As Collections.Generic.List( _    Of BoundSchemaSearchResults) Do While dbInfo.Read    ' ----- Add it to the report data.    oneEntry = New BoundSchemaSearchResults    oneEntry.ID = CInt(dbInfo!ID)    oneEntry.ItemName = CStr(dbInfo!Title)    If (IsDBNull(dbInfo!LastName) = True) Then _       useLastName = "" Else _       useLastName = CStr(dbInfo!LastName)    If (IsDBNull(dbInfo!FirstName) = True) Then _       useFirstName = "" Else _       useFirstName = CStr(dbInfo!FirstName)    If (useFirstName <> "") Then       If (useLastName <> "") Then useLastName &= ", "       useLastName &= useFirstName    End If    oneEntry.AuthorName = useLastName    oneEntry.MediaType = CStr(dbInfo!MediaName)    reportData.Add(oneEntry) Loop 

The results are bound to the grid, and a count is displayed to the user.

ResultsGrid.DataSource = reportData ResultsGrid.DataBind() MatchCount.Text = reportData.Count & " matching items." 

You must call the GridView control's DataBind method or you won't see any results.

Search Detail

When the user clicks on one of the Detail links in the search results, it sends the ID of the selected NamedItem record to the SearchDetail.aspx page as a query string. The page itself, which I won't show here, includes many Label controls that attempt to mimic the output on the detail panel of the ItemLookup.vb form in the main Library application. I even use almost the same Cascading Style Sheet instructions in this page that I use in the application.

When the page's Load event handler fires, it first examines the query string to extract the supplied NamedItem ID. A missing ID results in a return to the main search form.

itemID = Val(Page.Request.QueryString("ID")) If (itemID <= 0) Then    Response.Redirect("Default.aspx")    Return End If 

Most of the formatting code for this page comes from the ItemLookup.vb file in the main application. It queries the database for details of the specified NamedItem record, and updates each label using these values. The only thing that is interestingbesides the fact that this seems all too easy for web-page developmentis the creation of the table of item copies near the bottom of the page. In the ItemLookup.vb version of the code, I hand-crafted an HTML <table> set, and filled in its columns with the status of each available copy of the named library item. I thought it was a shame to ignore all of that great code, so I just copied it nearly unchanged into the code for SearchDetail.aspx.vb. So far, I haven't had to do anything with HTML itself, except when I wanted to add <h1> tags around the page titles. But because I had written the HTML-generating code, and because ASP.NET applications target HTML, I thought I could use it.

And I can. One of the Web Forms controls is Literal, a control that exists only so you can set its Text property to properly formatted HTML content. After building up the table structure in a StringBuilder object named copyTable, I assign that HTML content to the Literal control.

' ----- Add the table to the output. PutTableHere.Text = copyTable.ToString() 

Statistics Report

The Statistics.aspx page displays the same summary information included in one of the reports from Chapter 20. In the original Statistics report, I displayed record counts from six different tables, and presented them as a list in an RDLC report format. In this web page, I do those same six queries, build a generic list of the results, and bind that list tosurprisea GridView control, which is quickly becoming our favorite. Here's the code for the page in its entirety.

Imports System.Data Partial Class Statistics    Inherits System.Web.UI.Page    Public LibraryDB As System.Data.SqlClient.SqlConnection    Protected Sub Page_Load(ByVal sender As Object, _          ByVal e As System.EventArgs) Handles Me.Load       ' ----- Prepare the data for the report.       Dim sqlText As String       Dim reportData As Collections.Generic.List( _          Of BoundSchemaStatistics)       Dim oneEntry As BoundSchemaStatistics       Dim resultValue As Integer       Dim counter As Integer       Dim tableSets() As String = {"Author", "Publisher", _          "Subject", "NamedItem", "ItemCopy", "Patron"}       Dim tableTitles() As String = {"Authors", "Publishers", _          "Subject Headings", "Items", "Item Copies", "Patrons"}       ' ----- Connect to the database.       LibraryDB = New SqlClient.SqlConnection( _          ConfigurationManager.ConnectionStrings( _          "LibraryConnection").ConnectionString)       LibraryDB.Open()       ' ----- Build the report data. It's all counts from       '       different tables.       reportData = New Collections.Generic.List( _          Of BoundSchemaStatistics)       For counter = 0 To UBound(tableSets)          ' ----- Process one table.          sqlText = "SELECT COUNT(*) FROM " & _             tableSets(counter)          resultValue = CInt(ExecuteSQLReturn(sqlText, _             LibraryDB))          ' ----- Add it to the report data.          oneEntry = New BoundSchemaStatistics          oneEntry.EntryName = tableTitles(counter)          oneEntry.EntryValue = CStr(resultValue)          reportData.Add(oneEntry)       Next counter       ' ----- Finished with the connection.       LibraryDB.Close()       StatisticsGrid.DataSource = reportData       StatisticsGrid.DataBind()    End Sub End Class 

I only included a minimum set of features in this web site, and don't start cracking jokes about my web page design skills. If I were planning to deploy this web site, I would certainly enable some links on the SearchDetail.aspx page so that the user could click to search for other items by the same author, publisher, series, and so on. I would also add patron-specific features that would let them check their current checked-out items and any fines due. Another great feature to add would be online help content that told the patron or administrator how to use the system. And that just happens to be the topic for the next chapter. Lucky you.