ASP.NET
and the .NET Framework System Classes provide a flexible platform for creating
Web-based applications using Web Forms. This article shows you how to use
ASP.NET in conjunction with ADO.NET to create a robust survey application,
using a surprisingly small amount of code. I recently developed an application
named EasyQuest for creating surveys. This ASP.NET application is based on an
application that appears in my book JScript .NET Programming (Wiley).
EasyQuest's
major features include a browser-based interface for creating new surveys - an
administrator can define the survey question and possible responses. It also
supports survey beginning and end dates, so surveys activate and expire without
an administrator's intervention. It has a graphical results view, it's easy to
deploy, it uses ADO.NET, and it's open-source - you can modify, use, and
distribute EasyQuest's code as you like.
As you
follow along the features of this app, you'll learn to design a flexible,
easy-to-use database structure, use the ASP.NET DataGrid control, and
generate page content with rendering blocks. You'll also customize web.config
to store application-specific settings and optimize the app's performance. You
can download the sample code for this app; this zip file contains a readme.txt
file describing how to set up the application on your system's Web server.
EasyQuest's Interface is ... Easy
You can
access all of EasyQuest's functionality using a browser. EasyQuest's start page
displays a listing of questionnaires to which a visitor can respond (see Figure
1).
Figure 1. The EasyQuest start page shows the
survey question, the date the questionnaire expires, and how many votes the
questionnaire has registered.
All of
EasyQuest's questionnaires are open to voters for a limited time as determined
by an administrator. Only active questionnaires, those with a beginning date
before and an expiration date after the current date, appear in the list of
available questionnaires. Each questionnaire has two links (the underlined
elements in Figure 1). The link in the Question column goes to the voting form,
and the link in the Total Votes column takes you to the results screen.
EasyQuest's
voting form is simple: Simply select your choice and click on the Vote button. EasyQuest records your
vote in its database and takes you to the Results page (see Figure 2).
Figure 2. After voting, readers are taken to
the results screen automatically, where they can see the status of the survey
to date.
EasyQuest's
start screen also has a link to a form that allows an administrator to create a
new EasyQuest survey. Creating a questionnaire is a two-step process: First,
you gather information about the questionnaire, then you acquire the text of
each possible response.
The
EasyQuest database has a logical structure (see Figure 3) that separates
information about questionnaires and questionnaire options. The physical
database design, representing the implementation of the diagram in Figure 3,
mirrors the database's logical structure. The application's OnStart
event triggers EasyQuest to establish a connection to the database through the Application_OnStart
event handler, which resides in the global.asax file. The details of the
connection reside in the web.config file (I'll describe global.asax and
web.config later).
Figure 3. This is the structure of the
EasyQuest database. EasyQuest uses ADO.NET to communicate with the database.
Explore EasyQuest's Start Page
EasyQuest's
start page, default.aspx, uses a database connection stored in an Application
variable to query the database for a list of active questionnaires. The page
displays the list in an ASP.NET DataGrid control. The DataGrid
control makes creating pages that display tabular data easy because the control
handles the details of rendering the data in HTML.
When a
user accesses a page, ASP.NET loads the page and fires a Page_Load event.
The code in default.aspx implements a Page_Load event handler that
initiates page processing:
Sub
Page_Load(sender As Object, e As EventArgs)
listQuestions.DataSource = acquireData()
listQuestions.DataBind()
End Sub
The listQuestions
variable in the listing is the name of the DataGrid control. The Page_Load
event handler calls another function, which acquires the data for the control
and returns a DataView object. The DataGrid control is bound to
the DataView object through the DataGrid's DataBind
method, at which point the control renders itself using HTML. The code in
Figure 4 shows the code that acquires the data from the database.
Function acquireData() As ICollection
Dim startOfDay As String = DateTime.Today
& " 00:00:01" Dim endOfDay
As String = DateTime.Today & " 23:59:59"
dbCmd.Connection = databaseConnection
dbCmd.CommandText = _
"SELECT
questionnaires.questionnaire_id," _
&
"questionnaires.beginDate, " _
&
"questionnaires.expireDate, " _
&
"questionnaires.question, " _
&
"Sum(questionnaire_options.votes) AS SumOfVotes " _
+ "# and
" & "questionnaires.expireDate >= #"+endOfDay+"#
" _
&
"GROUP BY questionnaires.questionnaire_id, " _
&
"questionnaires.beginDate, "
&
"questionnaires.expireDate, " _
&
"questionnaires.question "
Dim dbAdapter As OleDbDataAdapter
Dim dbDataSet As New DataSet()
Dim listQuestions As New DataGrid()
dbAdapter = New OleDbDataAdapter()
dbAdapter.SelectCommand = dbCmd
dbAdapter.Fill(dbDataSet)
dbCmd.Connection.Close()
acquireData = dbDataSet.Tables(0).DefaultView
End Function
Figure
4. The acquireDatafunction gathers information for display in the DataGrid control. The query
is based on the time and date.
Figure 4
shows how default.aspx composes the query based on the current date using the System.DateTime
structure's Today property. The query selects a list of active
questionnaires by comparing the current date with the values of the beginDate
and endDate fields (the current date must be between these values). The query
calculates the total number of votes users have cast for the questionnaire
using the Sum function. The Sum function works in conjunction
with the GROUP BY clause to calculate the total number of votes for each
questionnaire. Using the Sum function to calculate the total number of
votes allows the database to determine the most effective way of calculating
the value and is more effective than maintaining the total programmatically in
another field or calculating it within the application's code.
The
ASP.NET DataGrid control, by default, renders all fields in the result
set as a simple HTML table. You can customize the control's output, however, by
specifying what columns you want to render and how you want the control
to render them. Figure 5 shows the declaration of the listQuestionsDataGrid
control.
Figure
5. This
DataGrid control declares how to display a result set. It specifies the font as
well as how column headers should be rendered.
Figure 5
demonstrates how to use an asp:HyperLinkColumn to generate links with customized
text and references. The links the DataGrid control generates reference the
questionnaire page (which allows users to vote) and the questionnaire's result
page (which displays the survey's results using a chart).
Build the Show Questionnaire Page
The code
in Figure 6 shows how showEasyQuestSurvey.aspx builds the questionnaire page
(which allows users to vote) based on queries that extract information from the
questionnaires and questionnaire_options tables. It uses the OleDbCommand.ExecuteReader
method to return an OleDbDataReader object that fetches data directly
from the database when you call its Read method.
<%
' File: showEasyQuestSurvey.aspx
Dim rst as
OleDbDataReader
Dim dbCmd As
OleDbCommand = New OleDbCommand()
dbCmd.Connection=Application("dbConnection")
if
dbCmd.Connection.State = ConnectionState.Open Then _
Figure
6.
showEasyQuestSurvey.aspx renders a page dynamically based on content from the
EasyQuest database.
The code
in Figure 6 uses rendering blocks - Visual Basic .NET code that's embedded in
HTML code, delimited with <% and %> character sequences - to render the voting form. The code,
although concise, actually does a lot of work. The listing begins by getting
the survey question on which the user wants to vote from the database, based on
the link the user clicked on at the start page (default.aspx). Then, the code
renders each radio button, along with its caption (the option's text), based on
the value of the Rank field. In both cases, the code demonstrates how to use
the OleDbCommand object's ExecuteReader method to access rows,
one by one, through an OleDbDataReader object.
Figure 7 shows the code that executes when a user clicks on the Vote button.
dbCmd.CommandText=
_
"select votes from questionnaire_options
where " _
&" questionnaire_id=" &
Request("survey") _
& " and rank=" &
(Request("surveyOpt"))
dbCmd.Connection.Open()
rst =
dbCmd.ExecuteReader()
rst.Read()
Dim
nextVoteCount As Integer = CInt(rst("votes"))+1
rst.Close()
dbCmd.Connection.Close()
dbCmd.CommandText= _
"UPDATE
questionnaire_options set votes=" _
& nextVoteCount _
& " where questionnaire_id=" & Request("survey") _
& " and rank=" &
(Request("surveyOpt"))
dbCmd.Connection.Open()
dbCmd.ExecuteNonQuery()
dbCmd.Connection.Close()
Figure
7. EasyQuest
records a user's vote. By issuing a select query, the listing retrieves the
total number of votes registered for the option the user selected.
The code
continues by updating the number of votes, using an UPDATE query. Because an UPDATE query
doesn't return any rows, the code uses the OleDbCommand object's ExecuteNonQuery
method, which doesn't store a query's result. This reduces the application's
memory requirements because the application doesn't need to allocate memory for
an empty result set.
Display a Questionnaire's Results
The
showEasyQuestResult.aspx page displays a questionnaire's results to the user
(see Figure 2). Similar to showEasyQuestSurvey.aspx(which displays a
questionnaire to a user), the code in showEasyQuestResult.aspx uses rendering
blocks to generate a chart dynamically that represents the questionnaire's results.
Figure 8 shows the code that acquires a survey's question and the total number
of votes registered for all the question's possible responses.
Dim rst As OleDbDataReader
Dim dbCmd As
OleDbCommand = new OleDbCommand()
dbCmd.Connection=Application("dbConnection")
dbCmd.Connection.Close()
dbCmd.CommandText="select
question from questionnaires " & _
"where questionnaire_id=" & Request("survey")
dbCmd.Connection.Open()
' ExecuteScalar returns the value of the first field
' in the first
row of the result set
Dim
surveyQuestion As String = dbCmd.ExecuteScalar()
dbCmd.Connection.Close()
dbCmd.CommandText="select
sum(votes) from " & _
"questionnaire_options where
questionnaire_id=" & _
Request("survey")
dbCmd.Connection.Open()
Dim totalVotes
As Integer = dbCmd.ExecuteScalar()
dbCmd.Connection.Close()
Figure
8.
showEasyQuestResult.asp gets the survey question and total number of votes from
the database.
Unlike
the code in Figure 7, which reads the value of the first field of the first
record in the result set, the code in Figure 8 uses the ExecuteScalar
method. ExecuteScalar is designed to retrieve the value of the first
field of the first row from a result set and reduce the amount of code you need
to write from three lines (as shown in Figure 7) to a single line (as shown in
Figure 8). ExecuteScalar has the added advantage that it doesn't require
the creation of a DataReader; instead, the scalar value is passed back
in the ExecuteScalar method's return value.
The code
in Figure 9 illustrates how EasyQuest generates the chart that appears after
the survey question dynamically on the Results screen. The code renders a
series of table rows that contain the option's text (rst("text") in
the listing), a bar representing the percentage of total votes the option has
registered for it (based on the pcntVotes variable in the listing), a
textual representation of the percentage of votes, and the actual number of
votes the option has registered for it (rst("votes") in the listing).
<%
Dim pcntVotes As Decimal
dbCmd.CommandText="select
text,votes from " & _
questionnaire_options where
questionnaire_id=" & _
Request("survey") & "
order by rank asc"
dbCmd.Connection.Open()
rst =
dbCmd.ExecuteReader()
While(rst.Read())
Response.Write("
" &
rst("text") & _
"
")
If CInt(rst("votes")) = 0 Then
pcntVotes = 0
Else
pcntVotes=Math.Round((CInt(rst("votes"))/ _
totalVotes)*100)
End If
Response.Write("
" _
& "
& width=" & (pcntVotes*3)
& "> ("& pcntVotes & _
& "%)
")
Response.Write("
" & rst("votes") _
& "
")
End While
rst.Close()
dbCmd.Connection.Close()
%>
Figure
9. EasyQuest
generates the chart that appears after the survey question dynamically on the
Results screen.
As
before, the code resides in a rendering block, uses the ExecuteReader
method to execute the query, and iterates over the result set using the Read
method. As the code iterates over the result set, it calculates the percentage
of total votes an option received. The code uses the Math.Round method
to truncate the percentage to zero decimal places. The resulting value is used
to determine the width of the bar that appears beside each option.
One
thing you need to watch out for when working with the OleDbCommand
object is to make sure you close the connection to the database when you're
finished working with the result set. As soon as you call the ExecuteReader
method, the associated database connection object, OleDbConnection,
becomes unavailable for any other objects until you close it. The code in
Figure 10 closes the underlying database connection explicitly. However, ExecuteReader
has an overloaded implementation that accepts a CommandBehavior value.
Set the value of ExecuteReader's parameter to CommandBehavior.CloseConnection
to have it close the underlying database connection when you close the OleDbDataReader
object, like this:
Two
pages, newEasyQuestSurvey.htm and setEasyQuestSurvOpts.aspx, work together to
gather information about a new questionnaire. The code in newEasyQuestSurvey.htm
is all client-based JavaScript that validates the values the user enters and
the beginDate and expireDate fields. setEasyQuestSurvOpts.aspx renders the text
box fields shown in Figure 5 and forwards all the information to a page that
creates the new survey (see Figure 10).
Dim numberOfOptions As Integer = _
CInt(Request.Form("selOptions"))
Dim
surveyQuestion As String = Request.Form("txtSQ")
Dim
surveyStartDate As String = Request.Form("dtStart")
Dim
surveyExpireDate As String = Request.Form("dtEnd")
Dim dbCmd As
OleDbCommand = New OleDbCommand()
dbCmd.Connection=Application("dbConnection")
' Create the
new questionnaire
dbCmd.CommandText="INSERT
into questionnaires " _
& "(beginDate,expireDate,question)
values('" _
& surveyStartDate & " 00:00:01"
& "','" _
& surveyExpireDate & "
23:59:59" & "','" _
&
surveyQuestion.Replace("'","''") & "')"
dbCmd.Connection.Open()
dbCmd.ExecuteNonQuery()
dbCmd.Connection.Close()
' Get the questionnaire_id of the last (new) questionnaire
dbCmd.CommandText= _
"select
max(questionnaire_id) from questionnaires"
dbCmd.Connection.Open()
Dim surveyID As
String = dbCmd.ExecuteScalar()
dbCmd.Connection.Close()
' Add each option to the questionnaire_options table
Dim nIndex As
Integer
Dim strTmp As
String
For nIndex=1 To
numberOfOptions
strTmp =
Request.Form("txtSurveyOption" & nIndex)
dbCmd.CommandText="insert into
questionnaire_options " & _
Figure
10. Create a new
survey by adding it to the EasyQuest database.
The code
in Figure 10 uses an INSERT query to add the new questionnaire to the
questionnaires table and executes it using the ExecuteNonQuery method.
Before the page adds the survey options (possible responses) to the
questionnaire_options table, the page must determine what the value of the
questionnaire_id field is for the survey it just created to maintain the
relationship between the question and its possible responses. The code
determines the value of the field by executing a select query for the largest value of the
questionnaire_id field (MAX(questionnaire_id) in the listing's select query).
Once the code acquires the value of the new questionnaire_id, it uses the value
to add the possible responses to the questionnaire_options table by using a
series of INSERT queries that execute using the ExecuteNonQuery method.
The code
assumes there aren't any other administrators adding new questionnaires to the
EasyQuest database concurrently. If you want to allow more than one
administrator to create new questionnaires concurrently, create an OleDbTransaction
object to frame all the queries that create the new survey within a
transaction.
Store Application-specific Settings in Web.config
An
ASP.NET application's web.config file uses XML and contains a lot of
information about how ASP.NET should handle your application. You can add
custom, application-specific settings that are exposed to your application
through the System.Configuration.ConfigurationSettings class. The advantage
to storing application-specific settings in the web.config file is you have a
means of providing administrators with a way to change the settings your code
uses without modifying your code. Web.config also is protected from being
downloaded by hackers and thereby offers greater security for sensitive
application settings such as database-connection strings, which often contain
user IDs and passwords. Here's an excerpt from the application's web.config
file that includes the database connection string and the database name:
You get
at the application-specific settings stored in web.config using the ConfigurationSettings
class, as shown here taken from the global.asax's Application_OnStart
event handler:
Here are
three settings you can change in the web.config file that can make a lot of
difference in how your application performs:
The tag disables debugging. Having
debugging enabled for your production application can slow it down a great
deal. The
tag disables trace output, a feature that provides detailed timing information
for every method your pages call.
The tag is possibly the most important
of the three settings because it disables a feature EasyQuest doesn't use and
is normally enabled by default: session-state management. You can write your
ASP.NET applications to store information that lasts for as long as a user's
session is active. Storing per-user session data consumes a lot of memory and,
as a result, affects how many concurrent users the application can service.
Because EasyQuest does not use per-session data, I have disabled the feature,
which greatly reduces EasyQuest's memory requirements.
The files referenced in this
article are available for download.
Essam Ahmed
is an accomplished developer with more than 10 years of professional
programming and design experience. He enjoys writing and is author of JScript .NET Programming (Wiley). He is a book reviewer for CodeGuru.com and has written
articles for ASPToday.com and theCodeChannel.com. He is a practicing member of
the Worldwide Institute of Software Architects (http://www.wwisa.org)
and spoke at VSLive! 2001 in Sydney, Australia. Visit Essam's Web site at http://www.designs2solutions.com.
Tell us what you think! Please send any comments about this
article to [email protected].
Please include the article title and author.
