21.7. Case Study: Connecting to a Database in ASP.NET Many Web sites allow users to provide feedback about the Web site in a guestbook. Typically, users click a link on the Web site's home page to request the guestbook page. This page usually consists of an XHTML form that contains fields for the user's name, e-mail address, message/feedback and so on. Data submitted on the guestbook form is then stored in a database located on the Web server's machine. In this section, we create a guestbook Web Form application. This example's GUI is slightly more complex than that of earlier examples. It contains a GridView ASP.NET data control, as shown in Fig. 21.33, which displays all the entries in the guestbook in tabular format. We explain how to create and configure this data control shortly. Note that the GridView displays abc in Design mode to indicate string data that will be retrieved from a data source at runtime. Figure 21.33. Guestbook application GUI in Design mode. 
The XHTML form consists of a name field, an e-mail address field and a message field. The form also contains a Submit button to send the data to the server and a Clear button to reset each of the fields on the form. The application stores the guestbook information in a SQL Server database called Guestbook.mdf located on the Web server. (We provide this database in the examples directory for this chapter, which you can download from www.deitel.com/books/vbforprogrammers2.) Below the XHTML form, the GridView displays the data (i.e., guestbook entries) in the database's Messages table. 21.7.1. Building a Web Form That Displays Data from a Database We now explain how to build this GUI and set up the data binding between the GridView control and the database. Many of these steps are similar to those performed in Chapter 20 to access and interact with a database in a Windows application. We present the ASPX file generated from the GUI later in the section, and we discuss the related code-behind file in the next section. To build the guestbook application, perform the following steps: Step 1. | Creating the Project
Create an ASP.NET Web Site named Guestbook and name the ASPX file Guestbook.aspx. Rename the class in the code-behind file Guestbook, and update the Page directive in the ASPX file accordingly.
| Step 2. | Creating the Form for User Input
In Design mode for the ASPX file, add the text Please leave a message in our guestbook: formatted as a navy blue H2 header. As discussed in Section 21.5.1, insert an XHTML table with two columns and four rows, configured so that the text in each cell aligns with the top of the cell. Place the appropriate text (see Fig. 21.33) in the top three cells in the table's left column. Then place TextBoxes named nameTextBox, emailTextBox and messageTextBox in the top three table cells in the right column. Set messageTextBox to be a multiline TextBox. Finally, add Buttons named submitButton and clearButton to the bottom-right table cell. Set the buttons' Text properties to Submit and Clear, respectively. We discuss the buttons' event handlers when we present the code-behind file.
| | | Step 3. | Adding a GridView Control to the Web Form
Add a GridView named messagesGridView that will display the guestbook entries. This control appears in the Data section of the Toolbox. The colors for the GridView are specified through the Auto Format... link in the GridView Tasks smart tag menu that opens when you place the GridView on the page. Clicking this link causes an Auto Format dialog to open with several choices. In this example, we chose Simple. We show how to set the GridView's data source (i.e., where it gets the data to display in its rows and columns) shortly.
| Step 4. | Adding a Database to an ASP.NET Web Application
To use a SQL Server 2005 Express database in an ASP.NET Web application, you must first add it to the project's App_Data folder. Right click this folder in the Solution Explorer and select Add Existing Item…. Locate the Guestbook.mdf file in the exampleDatabases subdirectory of the chapter's examples directory, then click Add.
| | | Step 5. | Binding the GridView to the Messages Table of the Guestbook Database
Now that the database is part of the project, we can configure the GridView to display its data. Open the GridView Tasks smart tag menu, then select <New data source...> from the Choose Data Source drop-down list. In the Data Source Configuration Wizard that appears, select Database. In this example, we use a SqlDataSource control that allows the application to interact with the Guestbook database. Set the ID of the data source to messagesSqlDataSource and click OK to begin the Configure Data Source wizard. In the Choose Your Data Connection screen, select Guestbook.mdf from the drop-down list (Fig. 21.34), then click Next> twice to continue to the Configure the Select Statement screen.
Figure 21.34. Configure Data Source dialog in Visual Web Developer. 
The Configure the Select Statement screen (Fig. 21.35) allows you to specify which data the SqlDataSource should retrieve from the database. Your choices on this page design a SELECT statement, shown in the bottom pane of the dialog. The Name drop-down list identifies a table in the database. The Guestbook database contains only one table named Messages, which is selected by default. In the Columns pane, click the checkbox marked with an asterisk (*) to indicate that you want to retrieve the data from all the columns in the Message table. Click the Advanced button, then check the box next to Generate UPDATE, INSERT and DELETE statements. This configures the SqlDataSource control to allow us to change data in, insert new data into and delete data from the database. We discuss inserting new guestbook entries based on users' form submissions shortly. Click OK, then click Next> to continue the Configure Data Source wizard.
Figure 21.35. Configuring the SELECT statement used by the SqlDataSource to retrieve data. 
The next screen of the wizard allows you to test the query that you just designed. Click Test Query to preview the data that will be retrieved by the SqlDataSource (shown in Fig. 21.36).
Figure 21.36. Previewing the data retrieved by the SqlDataSource . 
Finally, click Finish to complete the wizard. Notice that a control named messagesSqlDataSource now appears on the Web Form directly below the GridView (Fig. 21.37). This control is represented in Design mode as a gray box containing its type and name. This control will not appear on the Web pagethe gray box simply provides a way to manipulate the control visually through Design mode. Also notice that the GridView now has column headers that correspond to the columns in the Messages table and that the rows each contain either a number (which signifies an autoincremented column) or abc (which indicates string data). The actual data from the Guestbook database file will appear in these rows when the ASPX file is executed and viewed in a Web browser.
Figure 21.37. Design mode displaying SqlDataSource control for a GridView . 
| | | Step 6. | Modifying the Columns of the Data Source Displayed in the GridView
It is not necessary for site visitors to see the MessageID column when viewing past guestbook entriesthis column is merely a unique primary key required by the Messages table within the database. Thus, we modify the GridView so that this column does not display on the Web Form. In the GridView Tasks smart tag menu, click Edit Columns. In the resulting Fields dialog (Fig. 21.38), select MessageID in the Selected fields pane, then click the X. This removes the MessageID column from the GridView. Click OK to return to the main IDE window. The GridView should now appear as in Fig. 21.33.
Figure 21.38. Removing the MessageID column from the GridView. 
| Step 7. | Modifying the Way the SqlDataSource Control Inserts Data
When you create a SqlDataSource in the manner described here, it is configured to permit INSERT SQL operations against the database table from which it gathers data. You must specify the values to insert either programmatically or through other controls on the Web Form. In this example, we wish to insert the data entered by the user in the nameTextBox, emailTextBox and messageTextBox controls. We also want to insert the current datewe will specify the date to insert programmatically in the code-behind file, which we present shortly.
|
To configure the SqlDataSource to allow such an insertion, select the messagesSqlDataSource control then click the ellipsis button next to the control's InsertQuery property of the messagesSqlDataSource control in the Properties window. The Command and Parameter Editor (Fig. 21.39) that appears displays the INSERT command used by the SqlDataSource control. This command contains parameters @Date, @Name, @Email and @Message. You must provide values for these parameters before they are inserted into the database. Each parameter is listed in the Parameters section of the Command and Parameter Editor. Because we will set the Date parameter programmatically, we do not modify it here. For each of the remaining three parameters, select the parameter, then select Control from the Parameter source drop-down list. This indicates that the value of the parameter should be taken from a control. The ControlID drop-down list contains all the controls on the Web Form. Select the appropriate control for each parameter, then click OK. Now the SqlDataSource is configured to insert the user's name, e-mail address and message in the Messages table of the Guestbook database. We show how to set the date parameter and initiate the insert operation when the user clicks Submit shortly. Figure 21.39. Setting up INSERTg parameters based on control values. 
ASPX File for a Web Form That Interacts with a Database The ASPX file generated by the guestbook GUI (and messagesSqlDataSource control) is shown in Fig. 21.40. This file contains a large amount of generated markup. We discuss only those parts that are new or noteworthy for the current example. Lines 1958 contain the XHTML and ASP.NET elements that comprise the form that gathers user input. The GridView control appears in lines 6085. The <asp:GridView> start tag (lines 6063) contains properties that set various aspects of the GridView's appearance and behavior, such as whether grid lines should be displayed between rows and columns. The DataSourceID property identifies the data source that is used to fill the GridView with data at runtime. Figure 21.40. ASPX file for the guestbook application. 1 <%-- Fig. 21.40: Guestbook.aspx --%> 2 <%-- Guestbook Web application with a form for users to submit --%> 3 <%-- guestbook entires and a GridView to view existing entries. --%> 4 <%@ Page Language="VB" AutoEventWireup="false" 5 CodeFile="Guestbook.aspx.vb" Inherits="Guestbook" %> 6 7 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 8 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 9 10 <html xmlns="http://www.w3.org/1999/xhtml" > 11 <head runat="server"> 12 <title>Guestbook</title> 13 </head> 14 <body> 15 <form id="form1" runat="server"> 16 <div> 17 <h2> 18 Please leave a message in our guestbook: </h2> 19 <table> 20 <tr> 21 <td style="width: 130px; height: 21px" valign="top"> 22 Your name:<br /> 23 </td> 24 <td style="width: 300px; height: 21px" valign="top"> 25 <asp:TextBox ID="nameTextBox" runat="server" 26 Width="300px"></asp:TextBox> 27 </td> 28 </tr> 29 <tr> 30 <td style="width: 130px" valign="top"> 31 Your e-mail address: <br /> 32 </td> 33 <td style="width: 300px" valign="top"> 34 <asp:TextBox ID="emailTextBox" runat="server" 35 Width="300px"></asp:TextBox> 36 </td> 37 </tr> 38 <tr> 39 <td style="width: 130px" valign="top"> 40 Tell the world: <br /> 41 </td> 42 <td style="width: 300px" valign="top"> 43 <asp:TextBox ID="messageTextBox" runat="server" 44 Height="100px" Rows="8" Width="300px"> 45 </asp:TextBox> 46 </td> 47 </tr> 48 <tr> 49 <td style="width: 130px" valign="top"> 50 </td> 51 <td style="width: 300px" valign="top"> 52 <asp:Button ID="submitButton" runat="server" 53 Text="Submit" /> 54 <asp:Button ID="clearButton" runat="server" 55 Text="Clear" /> 56 </td> 57 </tr> 58 </table> 59 <br /> 60 <asp:GridView ID="messagesGridView" runat="server" 61 AutoGenerateColumns="False" CellPadding="4" 62 DataKeyNames="MessageID" DataSourceID="messagesSqlDataSource" 63 ForeColor="#333333" GridLines="None" Width="600px" > 64 <FooterStyle BackColor="#1C5E55" Font-Bold="True" 65 ForeColor="White" /> 66 <Columns> 67 <asp:BoundField DataField="Date" HeaderText="Date" 68 SortExpression="Date" /> 69 <asp:BoundField DataField="Name" HeaderText="Name" 70 SortExpression="Name" /> 71 <asp:BoundField DataField="Email" HeaderText="Email" 72 SortExpression="Email" /> 73 <asp:BoundField DataField="Message" HeaderText="Message" 74 SortExpression="Message" /> 75 </Columns> 76 <RowStyle BackColor="#E3EAEB" /> 77 <EditRowStyle BackColor="#7C6F57" /> 78 <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" 79 ForeColor="#333333" /> 80 <PagerStyle BackColor="#666666" ForeColor="White" 81 HorizontalAlign="Center" /> 82 <HeaderStyle BackColor="#1C5E55" Font-Bold="True" 83 ForeColor="White" /> 84 <AlternatingRowStyle BackColor="White" /> 85 </asp:GridView> 86 <asp:SqlDataSource ID="messagesSqlDataSource" runat="server" 87 ConnectionString=" <%$ ConnectionStrings:ConnectionString %>" 88 DeleteCommand="DELETE FROM [Messages] WHERE [MessageID] = 89 @MessageID" InsertCommand="INSERT INTO [Messages] 90 ([Date], [Name], [Email], [Message]) 91 VALUES (@Date, @Name, @Email, @Message)" 92 SelectCommand="SELECT * FROM [Messages]" UpdateCommand= 93 "UPDATE [Messages] SET [Date] = @Date, [Name] = @Name, 94 [Email] = @Email, [Message] = @Message 95 WHERE [MessageID] = @MessageID"> 96 <DeleteParameters> 97 <asp:Parameter Name="MessageID" Type="Int32" /> 98 </DeleteParameters> 99 <UpdateParameters> 100 <asp:Parameter Name="Date" Type="String" /> 101 <asp:Parameter Name="Name" Type="String" /> 102 <asp:Parameter Name="Email" Type="String" /> 103 <asp:Parameter Name="Message" Type="String" /> 104 <asp:Parameter Name="MessageID" Type="Int32" /> 105 </UpdateParameters> 106 <InsertParameters> 107 <asp:Parameter Name="Date" Type="String" /> 108 <asp:ControlParameter ControlID="nameTextBox" Name="Name" 109 PropertyName="Text" Type="String" /> 110 <asp:ControlParameter ControlID="emailTextBox" Name="Email" 111 PropertyName="Text" Type="String" /> 112 <asp:ControlParameter ControlID="messageTextBox" 113 Name="Message" PropertyName="Text" Type="String" /> 114 </InsertParameters> 115 </asp:SqlDataSource> 116 </div> 117 </form> 118 </body> 119 </html>
(a) 
(b)  |
Lines 6675 define the Columns that appear in the GridView. Each column is represented as a BoundField, because the values in the columns are bound to values retrieved from the data source (i.e., the Messages table of the Guestbook database). The DataField property of each BoundField identifies the column in the data source to which the column in the GridView is bound. The HeaderText property indicates the text that appears as the column header. By default, this is the name of the column in the data source, but you can change this property as desired. Lines 7684 contain nested elements that define the styles used to format the GridView's rows. The IDE configured these styles based on your selection of the Simple style in the Auto Format dialog for the GridView. The messagesSqlDataSource is defined by the markup in lines 86115 in Fig. 21.40. Line 87 contains a ConnectionString property, which indicates the connection through which the SqlDataSource control interacts with the database. The value of this property uses an ASP.NET expression, delimited by <%$ and %>, to access the Guestbook-ConnectionString stored in the ConnectionStrings section of the application's Web.config configuration file. Recall that we created this connection string earlier in this section using the Configure Data Source wizard. As determined by our actions in the Configure Data Source wizard, this statement retrieves the data in all the columns in all the rows of the Messages table. Lines 8895 define the DeleteCommand, InsertCommand, SelectCommand and UpdateCommand properties, which contain the DELETE, INSERT, SELECT and UPDATE SQL statements, respectively. These were generated by the Configure Data Source wizard. In this example, we use only the InsertCommand. We discuss invoking this command shortly. Notice that the SQL commands used by the SqlDataSource contain several parameters (prefixed with @). Lines 96114 contain elements that define the name, the type and, for some parameters, the source of the parameter. Parameters that are set programmatically are defined by Parameter elements containing Name and Type properties. For example, line 107 defines the Date parameter of Type String. This corresponds to the @Date parameter in the InsertCommand (line 91). Parameters that obtain their values from controls are defined by ControlParameter elements. Lines 108113 contain markup that sets up the relationships between the INSERT parameters and the Web Form's TextBoxes. We established these relationships in the Command and Parameter Editor (Fig. 21.39). Each ControlParameter contains a ControlID property indicating the control from which the parameter gets its value. The PropertyName specifies the property that contains the actual value to be used as the parameter value. The IDE sets the PropertyName based on the type of control specified by the ControlID (indirectly via the Command and Parameter Editor). In this case, we use only TextBoxes, so the PropertyName of each ControlParameter is Text (e.g., the value of parameter @Name comes from nameTextBox.Text). However, if we were using a DropDownList, for example, the PropertyName would be SelectedValue. 21.7.2. Modifying the Code-Behind File for the Guestbook Application After building the Web Form and configuring the data controls used in this example, double click the Submit and Clear buttons in Design view to create their corresponding Click event handlers in the Guestbook.aspx.vb code-behind file (Fig. 21.41). The IDE generates empty event handlers, so we must add the appropriate code to make these buttons work properly. The event handler for clearButton (lines 3338) clears each TextBox by setting its Text property to an empty string. This resets the form for a new guestbook submission. Figure 21.41. Code-behind file for the guestbook application. 1 ' Fig. 21.41: Guestbook.aspx.vb 2 ' Code-behind file that defines event handlers for the guestbook. 3 Partial Class Guestbook 4 Inherits System.Web.UI.Page 5 6 ' Submit Button adds a new guestbook entry to the database, 7 ' clears the form and displays the updated list of guestbook entries 8 Protected Sub submitButton_Click(ByVal sender As Object, _ 9 ByVal e As System.EventArgs) Handles submitButton.Click 10 ' create a date parameter to store the current date 11 Dim currentDate As New System.Web.UI.WebControls.Parameter( _ 12 "Date", TypeCode.String, DateTime.Now.ToShortDateString()) 13 14 ' set the @Date parameter to the date parameter 15 messagesSqlDataSource.InsertParameters.RemoveAt( 0 ) 16 messagesSqlDataSource.InsertParameters.Add(currentDate) 17 18 ' execute an INSERT SQL statement to add a new row to the 19 ' Messages table in the Guestbook database that contains the 20 ' current date and the user's name, e-mail address and message 21 messagesSqlDataSource.Insert() 22 23 ' clear the TextBoxes 24 nameTextBox.Text = "" 25 emailTextBox.Text = "" 26 messageTextBox.Text = "" 27 28 ' update the GridView with the new database table contents 29 messagesGridView.DataBind() 30 End Sub ' submitButton_Click 31 32 ' Clear Button clears the Web Form's TextBoxes 33 Protected Sub clearButton_Click(ByVal sender As Object, _ 34 ByVal e As System.EventArgs) Handles clearButton.Click 35 nameTextBox.Text = "" 36 emailTextBox.Text = "" 37 messageTextBox.Text = "" 38 End Sub ' clearButton_Click 39 End Class ' Guestbook
|
Lines 830 contain the event-handling code for submitButton, which adds the user's information to the Messages table of the Guestbook database. Recall that we configured messagesSqlDataSource's INSERT command to use the values of the TextBoxes on the Web Form as the parameter values inserted into the database. We have not yet specified the date value to be inserted, though. Lines 1112 assign a String representation of the current date (e.g., "3/27/06") to a new object of type Parameter. This Parameter object is identified as "Date" and is given the current date as a default value. The SqlDataSource's InsertParameters collection contains an item named Date, which we Remove in line 15 and replace in line 16 by Adding our currentDate parameter. Invoking SqlDataSource method Insert in line 21 executes the INSERT command against the database, thus adding a row to the Messages table. After the data is inserted into the database, lines 2426 clear the TextBoxes, and line 29 invokes messagesGridView's DataBind method to refresh the data that the GridView displays. This causes messagesSqlDataSource (the data source of the GridView) to execute its SELECT command to obtain the Messages table's newly updated data. |
