Case Study: Connecting to a Database in ASP.NET

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 presented to the user 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. You can download the examples from www.deitel.com/books/csharpforprogrammers2.) 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 rename 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' captions to Submit and Clear, respectively. We discuss the event handlers for these buttons 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 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 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 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. Change 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 insert new data into 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.

(This item is displayed on page 1114 in the print version)

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.

(This item is displayed on page 1114 in the print version)

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, click the ellipsis button next to the 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 INSERT parameters based on control values.

(This item is displayed on page 1116 in the print version)

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 2058 contain the XHTML and ASP.NET elements that comprise the form that gathers user input. The GridView control appears in lines 6187. The start tag (lines 6165) 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. Lines 6676 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.

Figure 21.40. ASPX file for the guestbook application.

Lines 7786 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.

The messagesSqlDataSource is defined by the markup in lines 89120 in Fig. 21.40. Lines 9091 contain 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 GuestbookConnectionString 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.

Line 92 defines the SqlDataSource's SelectCommand property, which contains the SELECT SQL statement used to retrieve the data from the database. 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 93100 define the DeleteCommand, InsertCommand and UpdateCommand properties, which contain the DELETE, INSERT and UPDATE SQL statements, respectively. These were also 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 101119 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 112 defines the Date parameter of Type String. This corresponds to the @Date parameter in the InsertCommand (line 97). Parameters that obtain their values from controls are defined by ControlParameter elements. Lines 113118 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 to create their corresponding Click event handlers in the Guestbook.aspx.cs 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 4348) 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.

(This item is displayed on page 1121 in the print version)

Lines 1740 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 messagtesSqlDataSource'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 2022 assign a string representation of the current date (e.g., "5/27/05") 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 25 and replace in line 26 by Adding our date parameter. Invoking SqlDataSource method Insert in line 31 executes the INSERT command against the database, thus adding a row to the Messages table. After the data is inserted into the database, lines 3436 clear the TextBoxes, and line 39 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.