Modifying the Wrox Survey Engine Although the Wrox Survey Engine is a great starter application for utilizing ASP.NET 2.0, it likely does not contain all the features you might want to see. Some possible enhancements to the Wrox Survey Engine could include: * Survey Reviews: Let users make comments on what they think about the survey. * Number Counts: The ability to view the number of responses to each of the surveys from the Administration section. * Charting: The ability to view a pie chart or a bar chart next to the percentages of the survey results pages. * Email Invitations: An e-mail message-generation tool for sending an e-mail invitation to take the survey. * Final Report: A request area to view final survey results once a specified number of responses are received. * Reporting: Some prepared reports about surveys, their responses, and percentages for each response. If you want to implement number counts, the following steps would be required: 1. In Visual Web Developer or Visual Studio 2005, open the website from its extracted location at C:\inetpub\wwwroot\SurveyEngine. Open the Database Explorer by selecting the View@@>Database Explorer menu selection. Find your database under the Database Connections node of the tree view. If your database is not listed, right click the Data Connections node in the tree view, select the Add Connection option, select the Microsoft SQL Server File option, and browse to the PhotoDB.mdf file in the App_Data folder of the site. This should bring up your connection within the Database Explorer window. Now that you have a valid database tree, expand it to see the Views node, and drill down to the viewResponseCountBySurvey view. Right click the view and select the Open View Definition option. 2. The query used for the view should be the following: SELECT TOP (100) PERCENT dbo.viewResponseCountBySurvey.SurveyID, dbo.viewResponseCountBySurvey.NumberResponses / dbo.viewQuestionCountBySurvey.NumQuestions AS Responses FROM dbo.viewQuestionCountBySurvey INNER JOIN dbo.viewResponseCountBySurvey ON dbo.viewQuestionCountBySurvey.SurveyID = dbo.viewResponseCountBySurvey.SurveyID ORDER BY dbo.viewResponseCountBySurvey.SurveyID * The table design area, which corresponds to the SQL query, should appear similar to Figure 4-1. Figure 4-1 * In this view, the count for the survey responses is returned with the SurveyID. To obtain the survey records and the count of responses, you have to add the Survey table to the view designer, joining on the SurveyID. So in chronological steps, the query can be modified to provide all of the fields you need for the survey grid, along with its number of user responses. * You can modify this query by right clicking near the tables in the design area, and selecting the Add Table option. 3. On the Tables tab of the pop-up window, select the Survey table. Click OK to see the Survey table added to the designer window. 4. Then, click the ID column in the Survey table, and drag to the viewQuestionCountBySurvey table and release over the SurveyID field. The relationship of inner join between the surveyID and ID fields. Then check the box next to all of the column names (ID, Name, Description, Date, and IsCurrentSurvey). Next, uncheck the SurveyID column from the viewResponseCountBySurvey view within the same designer window. The results of this query will return the fields of the Survey table, and count the number of responses that the survey generated. Figure 4-2 displays the view design with the added Survey table. Figure 4-2 * The new Transact-SQL code for the query is below: SELECT TOP (100) PERCENT dbo.viewResponseCountBySurvey.NumberResponses / dbo.viewQuestionCountBySurvey.NumQuestions AS Responses, dbo.Survey.Name, dbo.Survey.Description, dbo.Survey.Date, dbo.Survey.IsCurrentSurvey, dbo.Survey.ID FROM dbo.viewQuestionCountBySurvey INNER JOIN dbo.viewResponseCountBySurvey ON dbo.viewQuestionCountBySurvey.SurveyID = dbo.viewResponseCountBySurvey.SurveyID INNER JOIN dbo.Survey ON dbo.viewQuestionCountBySurvey.SurveyID = dbo.Survey.ID ORDER BY dbo.viewResponseCountBySurvey.SurveyID * Running the SQL query produces the results shown in Figure 4-3. Figure 4-3 5. The resulting data provide a better view of a survey, and now you can provide this view to the user through modifying the stored procedure that is used to get the records for the user. Do this by opening the stored procedure entitled sprocSurveySelectList: ALTER PROCEDURE dbo.sprocSurveySelectList /* '=============================================================== ' NAME: sprocSurveySelectList ' DATE CREATED: October 5, 2005 ' CREATED BY: Shawn Livermore (shawnlivermore.blogspot.com) ' CREATED FOR: ASP.NET 2.0 - Instant Results ' FUNCTION: Returns a list of surveys from the database. '=============================================================== */ as select * from Survey * By modifying this stored procedure to select from the modified view, rather than the Survey table directly, you can obtain the fields needed to display the number of responses for the surveys. 6. Change the select statement to read, Select * from viewNumberResponsesBySurvey and click Save. * The new stored procedure should look exactly like the code excerpt below: ALTER PROCEDURE dbo.sprocSurveySelectList /* '=============================================================== ' NAME: sprocSurveySelectList ' DATE CREATED: October 5, 2005 ' CREATED BY: Shawn Livermore (shawnlivermore.blogspot.com) ' CREATED FOR: ASP.NET 2.0 - Instant Results ' FUNCTION: Returns a list of surveys from the database. '=============================================================== */ as select * from viewNumberResponsesBySurvey 7. Next, modify the user interface to provide the visibility to the new information in the GridView. Double-click the webform Admin.aspx to open the form in Design View. Then, right click the GridView control and select Edit Columns. In the Edit Columns screen, add a bound column, with the HeaderText value of # Columns and the DataField property of Responses. Click OK, and save the form. 8. Run the project, log in to the site, and view the Administration grid. You should see the # Responses column with the number of responses listed for each survey, as displayed in Figure 4- 4. Figure 4-4 Now that you have walked through an example enhancement, there are plenty more ideas that may flow from this direction in conducting surveys online.
Pages to are hidden for
"Bonus"Please download to view full document