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. 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 guest-book 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 guest-book: formatted as an h2 header. As discussed in Section 25.3.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. 25.30) in the top three cells in the table’s left column. Then place TextBoxes named nameTextBox, emailTextBox and message-TextBox 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 speci-fied through the Auto Format... link in the GridView Tasks smart tag menu that opens when Auto Format open with several choices. In this example, we chose Simple. We soon show how to set the GridView’s data source (i.e., where it gets the data to display in its rows and columns).
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, it is easiest to first add it to the project’s App_Data folder. Right click this folder in the Solution Ex-plorer and select Add Existing Item…. Locate the Guestbook.mdf file in the exampleData-
bases 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 applica-tion to interact with the Guestbook database. Set the ID of the data source to messagesSql-DataSource 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. 25.31), then click Next > twice to continue to the Configure the Select Statement screen.
The Configure the Select Statement screen (Fig. 25.32) 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 col-umns in the Message table. Click the Advanced button, then check the box next to Gen
erate 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 data-base. We discuss inserting new guestbook entries based on users’ form submissions shortly. Click OK, then click Next > to continue the Configure Data Source wizard.
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. 25.33).
Finally, click Finish to complete the wizard. Notice that a control named messages-SqlDataSource now appears on the Web Form directly below the GridView (Fig. 25.34). This control is represented in Design mode as a gray box containing its type and name. This control will not appear on the web page—the 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.
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 guest-book entries—this 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 re-sulting Fields dialog (Fig. 25.35), 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. 25.30.
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 date—we 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 messagesSql-DataSource 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. 25.36) that appears displays the INSERT command used by the Sql-DataSource 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 Param-eter 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.
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. 25.37. This file contains a large amount of generated markup. We discuss only those parts that are new or noteworthy for the current example. Lines 19–58 contain the XHTML and ASP.NET elements that comprise the form that gathers user input. The GridView control appears in lines 60–85. The <asp:GridView> start tag (lines 60–63) 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 66–75 define the Columns that appear in the GridView. Each column is repre-sented 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 76–84 contain nested elements that define the styles used to format the GridView’s rows. The IDE configured these styles based on your selec-tion of the Simple style in the Auto Format dialog for the GridView.
The messagesSqlDataSource is defined by the markup in lines 86–115 in Fig. 25.37. 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.Lines 88–95 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.
1 <%-- Fig. 25.37: Guestbook.aspx --%>
2 <%-- Guestbook Web application with a form for users to submit --%>
3 <%-- guestbook entries and a GridView to view existing entries. --%>
4 <%@ Page Language="VB" AutoEventWireup="false"
5 CodeFile="Guestbook.aspx.vb" Inherits="Guestbook" %>
7 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
10 <html xmlns="http://www.w3.org/1999/xhtml" >
11 <head runat="server">
15 <form id="form1" runat="server">
18 Please leave a message in our guestbook:</h2>
21 <td style="width: 130px; height: 21px" valign="top">
22 Your name:<br />
24 <td style="width: 300px; height: 21px" valign="top">
25 <asp:TextBox ID="nameTextBox" runat="server"
30 <td style="width: 130px" valign="top">
31 Your e-mail address:<br />
33 <td style="width: 300px" valign="top">
34 <asp:TextBox ID="emailTextBox" runat="server"
39 <td style="width: 130px" valign="top">
40 Tell the world:<br />
42 <td style="width: 300px" valign="top">
43 <asp:TextBox ID="messageTextBox" runat="server"
44 Height="100px" Rows="8" Width="300px">
49 <td style="width: 130px" valign="top">
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" />
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" />
<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" />
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" />
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">
97 <asp:Parameter Name="MessageID" Type="Int32" />
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" />
<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" />
Fig. 25.37 | ASPX file for the guestbook application.
Notice that the SQL commands used by the SqlDataSource contain several parame-ters (prefixed with @). Lines 96–114 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 108–113 contain markup that sets up the relationships between the INSERT parameters and the Web Form’s TextBoxes. We estab-lished these relationships in the Command and Parameter Editor (Fig. 25.36). 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.
Copyright © 2018-2020 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.