SIVANANDA REDDY GANGIREDDY: Create Polls

Sample Screen Shot:
To View the Image Below (IE7):
Go to Internet Options, select Advanced tab. Scroll down until you see "Play animations in webpages" under Multimedia is checked. (You may need to restart your IE)

What is a poll?

A poll can be thought of as a survey. It allows questions to be asked, while generating answers and opinions to be gathered
which can be used for statistics, marketing strategies, as well as gaining user interactivity. It is a great way to retrieve feedback from
users who visit your site.

Why do I need one?

It can be used for your sites to generate statistics for different opinions.
It is a great tool to see what your target audiences want, and need.
It can cut costs if you are doing surveys or polls through paper.

What is needed to create a poll?

Ideally to create a poll, you need a place to store your questions, answers and votes.
We will not concern ourselves with limiting users to only one vote, because I do not think it is right
to only allow registered users to vote. Anyone that comes to your site, should be able to vote, and
mark their opinion.

Well, lets get started then.

Our Database:

Let us first start by creating our database. For this example, we will be using SQL Server 2005.
- Open the SQL Server Management Studio, and connect to the server.
- Next right click on the database, and create a new database (If you have an existing database, feel free to use that one):

- Enter the desired database name (example: PollDB):

- Click on "OK", and you have your database ready.

Our Structure

The following is the database table structure for our poll related tables:
It consists of three normalized tables: Tbl_Questions, Tbl_Answers, and Tbl_Votes.

In order to create the tables, you can use some of the out of the box GUI tools, or run the following scripts below. Make sure you run them in the following order:
1. Script for Tbl_Questions
2. Script for Tbl_Answers
3. Script for Tbl_Votes

You need to run them in this order, because Tbl_Answers has a direct relationship with the primary key of Tbl_Questions, and
Tbl_Votes has direct relationship to the primary key of Tbl_Answers.

SQL Create Statement for Questions Table:

USE [PollDB]
GO
/****** Object: Table [dbo].[Tbl_Questions] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tbl_Questions](
       [QuestionID] [int] IDENTITY(1,1) NOT NULL,
       [Question] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [Enable] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [QuestionCreatedDate] [datetime] NULL,
CONSTRAINT [PK_Tbl_Questions] PRIMARY KEY CLUSTERED
(
       [QuestionID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

SQL Create Statement for Answers Table:

USE [PollDB]
GO
/****** Object: Table [dbo].[Tbl_Answers] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tbl_Answers](
       [AnswerID] [int] IDENTITY(1,1) NOT NULL,
       [Answer] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [Enable] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [AnswerCreatedDate] [datetime] NULL,
       [QuestionID] [int] NOT NULL,
CONSTRAINT [PK_Tbl_Answers] PRIMARY KEY CLUSTERED
(
       [AnswerID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Tbl_Answers] WITH CHECK ADD CONSTRAINT [FK_Tbl_Answers_Tbl_Questions] FOREIGN KEY([QuestionID])
REFERENCES [dbo].[Tbl_Questions] ([QuestionID])
GO
ALTER TABLE [dbo].[Tbl_Answers] CHECK CONSTRAINT [FK_Tbl_Answers_Tbl_Questions]

SQL Create Statement for Votes Table:

USE [PollDB]
GO
/****** Object: Table [dbo].[Tbl_Votes] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl_Votes](
       [VotesID] [int] IDENTITY(1,1) NOT NULL,
       [Votes] [int] NULL,
       [AnswerID] [int] NULL,
CONSTRAINT [PK_Tbl_Votes] PRIMARY KEY CLUSTERED
(
       [VotesID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tbl_Votes] WITH CHECK ADD CONSTRAINT [FK_Tbl_Votes_Tbl_Answers] FOREIGN KEY([AnswerID])
REFERENCES [dbo].[Tbl_Answers] ([AnswerID])
GO
ALTER TABLE [dbo].[Tbl_Votes] CHECK CONSTRAINT [FK_Tbl_Votes_Tbl_Answers]

Information about the database and tables

Once you have run the script, or generated your tables, we can proceed to the next step.
The database structure and tables we created are normalized, and have primary keys which maintains good practice strategies.
Here is a brief description of what each table represents:
- Tbl_Questions will be responsible for holding the Poll Questions.
A sample row in the table can be the following: question can be: "Have you visited www.asp.net?"

QuestionID	Question	Enable	QuestionCreatedDate
1	Have you visited www.asp.net?	Y	03/03/2008 10:00:00

- Tbl_Answers will be responsible for all the choices that are available for a specific question
      Some sample answers could be:
         - Yes
         - No
         - Never heard of it
         - I go there every day

AnswerID	Answer	Enable	AnswerCreatedDate	QuestionID
1	Yes	Y	03/03/2008 10:00:00	1
2	No	Y	03/03/2008 10:00:01	1
3	Never heard of it	Y	03/03/2008 10:00:02	1
4	I go there every day	Y	03/03/2008 10:00:03	1

- Tbl_Votes will hold which answer had how many votes.
Below is an example based on the answer choices above.

VotesID	Votes	AnswerID
1	39839	1
2	387	2
3	12	3
4	93983	4

The Code

/* The following is the code that will handle events to load all the poll questions, their answers. We also have
the event for the vote, and how to update the record in the database.
The code is heavily commented, and self explanatory.
*/

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Controls_PollControl : System.Web.UI.UserControl
{

protected void Page_Load(object sender, EventArgs e)
{

        if (IsPostBack == false)
        {
            //Add attributes to the View Results hyperlink so it shows
            //the hand cursor
            HLViewResults.Attributes["onmouseover"] = "this.style.cursor = 'pointer';";
            HLViewResults.Attributes["onmouseout"] = "this.style.cursor = 'pointer';";

            //Add attributes to add collapse/expand features

HLViewResults.Attributes["onclick"] = "ShowHideDiv('" + this.DivResults.ClientID + "');";
//Set the Poll Title

            this.LblPollTitle.Text = "MY SITE POLL";
            //Get the max available poll question from our database,
            //and populate the answers, and results.

            GetQuestion();
        }
    }

    private void GetQuestion()
    {
        //Here we will get the maximum question.
        DataSet QuestionsDS = new DataSet();

        DB.Tbl_Questions Questions = new DB.Tbl_Questions();
        //Get the max question that is enabled.
        QuestionsDS = Questions.Tbl_Questions_DS_Select("WHERE QuestionID = " +
            "(SELECT MAX(QuestionID) AS QID " +
            "FROM Tbl_Questions AS TblQ1 " +
            "Where Enable = 'Y')");
        //check if database returned any records.
        if (QuestionsDS != null && QuestionsDS.Tables.Count > 0 && QuestionsDS.Tables[0].Rows.Count > 0)
        {
            //Display the Question from our database
            LblPollQuestion.Text = QuestionsDS.Tables[0].Rows[0]["Question"].ToString();
            //Get the question ID to fetch all the answer choices.
            int QuestionID = int.Parse(QuestionsDS.Tables[0].Rows[0]["QuestionID"].ToString());
            //Get all the answer choices for this question.
            GetAnswers(QuestionID);

            //Get all the votes for the results section.
            GetVotes(QuestionID);
        }
        else
        {
            //No Question Found. Default Question will be shown:
            LblPollQuestion.Text = "What is your favorite car?";
        }
    }

    private void GetAnswers(int QuestionID)
    {
        //Get all the Answers for this question
        DataSet AnswersDS = new DataSet();
        DB.Tbl_Answers Answers = new DB.Tbl_Answers();
        AnswersDS = Answers.Tbl_Answers_DS_Select("Where QuestionID = '" +
            QuestionID + "' And Enable = 'Y' Order By Answer ASC");
        //Make sure the database returned some answers
        if (AnswersDS != null && AnswersDS.Tables.Count > 0 && AnswersDS.Tables[0].Rows.Count > 0)
        {
            //clear our items in the answer list
            AnswerList.Items.Clear();
            //add each item to the list box
            foreach (DataRow dr in AnswersDS.Tables[0].Rows)
            {
                ListItem itm = new ListItem();
                //set the text display for this item
                itm.Text = dr["Answer"].ToString();
                //keep the database id of this item
                itm.Value = dr["AnswerID"].ToString();
                //add to the listbox
                AnswerList.Items.Add(itm);
            }
        }
        else
        {
            //Didn't find any answers.
            //block user from choosing anything, and/or voting.
            AnswerList.Items.Clear();
            AnswerList.Items.Add("Unable to load information");
            AnswerList.Enabled = false;
            BtnVote.Enabled = false;
        }
    }

    private void GetVotes(int QuestionID)
    {
        //Do an inner join to get all the answers, and how many votes are for each
        string sql = "SELECT Tbl_Answers.Answer, Tbl_Votes.Votes FROM Tbl_Answers " +
            "INNER JOIN Tbl_Votes ON " +
            "Tbl_Answers.AnswerID = Tbl_Votes.AnswerID " +
            "Where Tbl_Answers.QuestionID = '" + QuestionID.ToString() + "'";
        //run the query and bind the data.
        Functions fn = new Functions();
        DataSet Results = new DataSet();
        Results = fn.RunDSQuery(sql);
        //Make sure we have results, and bind to the gridview.
        if (Results != null)
        {
            GridViewResults.DataSource = Results;
            GridViewResults.DataBind();
        }
    }

    protected void BtnVote_Click(object sender, EventArgs e)
    {
        if (AnswerList.SelectedIndex == -1)
        {
            //user did not select a choice
            return;
        }
        else
        {
            //Get the id of the database answer which the user chose.
            int AnswerChoice = int.Parse(AnswerList.SelectedValue);
            //Get the current amounnt of votes, so we can add one to it
            //and update the database with the new value.
            DataSet CurrentVotesDS = new DataSet();
            DB.Tbl_Votes V = new DB.Tbl_Votes();
            CurrentVotesDS = V.Tbl_Votes_DS_Select("Where AnswerID = '" + AnswerChoice + "'");
            //if the current votes dataset is empty
            //then we may not have this in the database
            if (CurrentVotesDS != null && CurrentVotesDS.Tables.Count > 0 && CurrentVotesDS.Tables[0].Rows.Count > 0)
            {
                //get the votesid from the database
                int VotesID = int.Parse(CurrentVotesDS.Tables[0].Rows[0]["VotesID"].ToString());
                //get the current vote cout.
                int VoteCount = int.Parse(CurrentVotesDS.Tables[0].Rows[0]["Votes"].ToString());
                //add an extra vote, because the user just clicked to vote for this item.
                VoteCount = VoteCount + 1;
                //update the database with the new total
                V.Tbl_Votes_Update(VotesID.ToString(),
                    VoteCount.ToString(),
                    AnswerChoice.ToString(),
                    "Where VotesID = '" + VotesID.ToString() + "'");

                //Rebind information
                GetQuestion();
            }
        }
    }
}

Our Project Classes

The following are the classes used by poll web user control. They consist of methods to insert, update, select, and delete items from our database. They are all very similar, except for the different paramaters being used for each table. I will not go into the details of the classes, because they just handle the queries to the database.

Functions Class - used to call the database, connect, perform queries

using System;
using System.Data;
using System.Data.SqlClient;

public class Functions
{
    private string strcnn = System.Configuration.ConfigurationManager.AppSettings["cnString"].ToString();
    public bool ExecuteSQLStatement(string SQLString)
{
        SqlConnection cnn = new SqlConnection(strcnn);
        try
        {
            if (cnn.State != ConnectionState.Open)
            {
                cnn.Open();
            }
            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = cnn;
            myCommand.CommandText = SQLString;
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (cnn.State == ConnectionState.Open)
            {
                cnn.Close();
            }
        }
    }

    public DataSet RunDSQuery(string QueryString)
    {
        SqlConnection cnn = new SqlConnection(strcnn);
        try
        {
            if (cnn.State != System.Data.ConnectionState.Open)
            {
                cnn.Open();
            }
            SqlDataAdapter adapter = new SqlDataAdapter(QueryString, cnn);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }
        catch
        {
            return null;
        }
        finally
        {
            if (cnn.State == ConnectionState.Open)
            {
                cnn.Close();
            }
        }
    }
}

The Questions Class - Insert, Update, Delete, Get Dataset from our Questions Table

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace DB
{
       public class Tbl_Questions
       {
              public string Tbl_Questions_Insert (System.String Question, System.String Enable, System.DateTime
                    QuestionCreatedDate )
              {
                     string insertstatement = "INSERT INTO Tbl_Questions ( [Question], [Enable], [QuestionCreatedDate] )
                        VALUES ( '" + Question.ToString().Replace("'", "''") + "', '" +
                        Enable.ToString().Replace("'", "''")
                        + "', '" + QuestionCreatedDate.ToString().Replace("'", "''") + "') ";

                     Functions fn = new Functions();
                     fn.ExecuteSQLStatement(insertstatement);
                     return "";
              }
              public string Tbl_Questions_Update (System.String Question, System.String Enable, System.String
                    QuestionCreatedDate, string WhereClause )
              {
                     string updatestatement = "Update Tbl_Questions SET [Question] = '" +
                           Question.ToString().Replace("'","''") +
                           "' , [Enable] = '" + Enable.ToString().Replace("'", "''") +
                           "' , [QuestionCreatedDate] = '" + QuestionCreatedDate.ToString().Replace("'", "''") +
                           "' " + " " + WhereClause;

                     Functions fn = new Functions();
                     fn.ExecuteSQLStatement(updatestatement);
                     return "";
              }

              public DataSet Tbl_Questions_DS_Select ( string WhereClause )
              {
                     string select = "Select * from Tbl_Questions " + WhereClause;
                     DataSet ds = new DataSet();
                     Functions fn = new Functions();
                     ds = fn.RunDSQuery(select);
                     return ds;
              }

              public void Tbl_Questions_Delete ( string WhereClause )
              {
                     string deletestatement = "Delete from Tbl_Questions " + " " + WhereClause;
                     Functions fn = new Functions();
                     fn.ExecuteSQLStatement(deletestatement);
              }
       }
}

The Answers Class - Insert, Update, Delete, Get Dataset from our Answers Table

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace DB
{
       public class Tbl_Answers
       {
              public string Tbl_Answers_Insert (System.String Answer, System.String Enable, System.DateTime
                AnswerCreatedDate, System.Int32 QuestionID )
              {
                     string insertstatement = "INSERT INTO Tbl_Answers ( [Answer], [Enable], [AnswerCreatedDate],
                            [QuestionID] ) VALUES ( '" + Answer.ToString().Replace("'", "''") + "', '" +
                            Enable.ToString().Replace("'", "''") + "', '" +
                            AnswerCreatedDate.ToString().Replace("'", "''") +
                            "', '" + QuestionID.ToString().Replace("'", "''") + "') ";

                     Functions fn = new Functions();
                     fn.ExecuteSQLStatement(insertstatement);
                     return "";
              }

              public string Tbl_Answers_Update (System.String AnswerID, System.String Answer, System.String Enable,
                    System.String AnswerCreatedDate, System.String QuestionID, string WhereClause )
              {
                     string updatestatement = "Update Tbl_Answers SET [Answer] = '" +
                        Answer.ToString().Replace("'", "''") +
                        "' , [Enable] = '" + Enable.ToString().Replace("'", "''") +
                        "' , [AnswerCreatedDate] = '" +
                        AnswerCreatedDate.ToString().Replace("'", "''") +
                        "' , [QuestionID] = '" +
                        QuestionID.ToString().Replace("'", "''") + "' " + " " + WhereClause;

                     Functions fn = new Functions();
                     fn.ExecuteSQLStatement(updatestatement);
                     return "";
              }

              public DataSet Tbl_Answers_DS_Select ( string WhereClause )
              {
                     string select = "Select * from Tbl_Answers " + WhereClause;
                     DataSet ds = new DataSet();
                     Functions fn = new Functions();
                     ds = fn.RunDSQuery(select);
                     return ds;
              }
              public void Tbl_Answers_Delete ( string WhereClause )
              {
                     string deletestatement = "Delete from Tbl_Answers " + " " + WhereClause;
                     Functions fn = new Functions();
                     fn.ExecuteSQLStatement(deletestatement);
              }
       }
}

The Votes Class - Insert, Update, Delete, Get Dataset from our Votes Table

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace DB
{
       public class Tbl_Votes
       {
              public string Tbl_Votes_Insert (System.Int32 Votes, System.Int32 AnswerID )
              {
                     string insertstatement = "INSERT INTO Tbl_Votes ( [Votes], [AnswerID] ) VALUES ( '" +
                        Votes.ToString().Replace("'", "''") + "', '" +
                        AnswerID.ToString().Replace("'", "''") + "') ";

                     Functions fn = new Functions();
                     fn.ExecuteSQLStatement(insertstatement);
                     return "";
              }
              public string Tbl_Votes_Update (System.String VotesID, System.String Votes, System.String AnswerID,
                    string WhereClause )

{

                     string updatestatement = "Update Tbl_Votes SET [Votes] = '" +
                            Votes.ToString().Replace("'", "''") +
                            "' , [AnswerID] = '" + AnswerID.ToString().Replace("'", "''") +
                            "' " + " " + WhereClause;
                     Functions fn = new Functions();
                     fn.ExecuteSQLStatement(updatestatement);
                     return "";
              }

              public DataSet Tbl_Votes_DS_Select ( string WhereClause )
              {
                     string select = "Select * from Tbl_Votes " + WhereClause;
                     DataSet ds = new DataSet();
                     Functions fn = new Functions();
                     ds = fn.RunDSQuery(select);
                     return ds;
              }

              public void Tbl_Votes_Delete ( string WhereClause )
              {
                     string deletestatement = "Delete from Tbl_Votes " + " " + WhereClause;
                     Functions fn = new Functions();
                     fn.ExecuteSQLStatement(deletestatement);
              }
       }
}

How to use this with my project

To use this control with your project, just add this control to your project.
Next, just drag and drop the control onto your page. Make sure you
reference the following javascript method on your main page to be able to
collapse and expand the results. You can also put this into a js file if you wish:

To Create A New Poll Question

I am currently working on a Poll Administration control which will allow you to add new poll questions.
Look for it in the next couple of days.
You can easily also do this just by adding a record to the questions table, then adding the answers onto the answers table with the proper QuestionID.
Next in the Votes table just add the AnswerID's of your answers, and default the votes to zero.

A common question I am asked
Why didn't we use stored procedures?
- For demo's and tutorials I prefer not to use stored procedures. It makes it difficult for users to debug and understand the concepts of the demo's quickly.
Please feel free to modify this code, and use stored procedures if you desire.

IMPORTANT
Make sure to change the connections settings on the web.config file. This should have your database connection string.

What else is awaiting?
I am also working on a poll that is 100% xml based. This way even people that don't have a backend database can use a poll. Coming soon...

Notes

Make sure to change the connections settings on the web.config file.

Coding4fun

Add a Quick Poll to Your Web Site

Duncan Mackenzie
Microsoft Developer Network

August 3, 2004

Applies to:
Microsoft Visual Basic .NET

Summary: Duncan Mackenzie describes his process to build a "Quick Poll" using Visual Basic and ASP.NET. (16 printed pages)

Download the source code for this article.

Time for Something Different...

Just the other day, at exactly 10 PM, I was doing what I am always doing at 10 PM, sitting on the couch with my wife, Laura. That night had been like every other. We started dinner at 6:33 PM and finished at 7 PM. Dinner was the usual: fish sticks and fries, just like every night. So there we were, each with our tea, like we always do: mine in the blue cup, Laura's in the white cup. She was on the right side of the couch, I was on the left, just like every night. Suddenly, unexpectedly, Laura lets out a sigh and says, "We should do something different one of these days."

Well, I knew exactly what she meant... I leapt up from the couch and rushed off telling Laura not to worry, something different would be in the works very soon. Yep, it was certainly time for a change...

So I decided to write a Web application. I bet she'll be ecstatic.

I almost decided to write it in C#, but just venturing outside of Microsoft Windows Forms is enough for now. If you hadn't noticed, almost every article—okay, every article—in the Coding4Fun series has been about Windows Forms. Now Laura doesn't read these articles, so I'm a little mystified how she knew I had been sticking to a standard routine, but a good husband knows when to take a hint, regardless of the context.

Deciding what to build took very little time at all. I wanted to be able to host "Quick Polls" on both my personal Web site and hopefully on MSDN. A Quick Poll, if the term isn't already familiar to you, is a simple multiple choice question (see Figure 1) that is often used on Web sites.

Figure 1. Quick Polls are usually multiple-choice and are often used for casual surveys.

People vote (select an option) and then they get to see the tabulated results of all the other votes that have been cast, including their own (see Figure 2).

Figure 2. The real fun of a quick-poll is seeing how everyone else voted

For various reasons, this is not the best way to conduct a "real" survey, and the results obtained are at least slightly questionable, but it is a fun and interesting bit of interactive content to offer on your Web site. I won't go into detail here about why this method isn't perfect, but you can read about it on your own by searching the web for "SLOPS", "Self-Selected Surveys," or read a brief description in What Type of Survey is Best? by Carolyn Browne.

Starting with Someone Else's Code

As a programmer, I generally want to build the cool stuff, but you should always at least consider buying (or downloading) part or all of the code you need to accomplish a project. Price out a few commercial options, investigate any free examples available and then determine if any of these resources meet your specific requirements and fall within your budget. I looked around a bit and found that Rob Howard had already written a simple polling component for use on the Microsoft ASP.NET Web site. This component, available from here, was very easy to use and the code was very clear. It was the perfect place to start experimenting.

Within a couple of hours, I had figured out how to access the Microsoft SQL Server that my Web host provides me with, set up the one table (see the SQL statement below) that Rob's component uses, and create the poll.xml file that described the poll I wanted to run.

Copy

CREATE TABLE [Poll] (

[PollName] [nvarchar] (128) NOT NULL ,

[Vote] [char] (1) NOT NULL ,

[VoteCount] [int] NOT NULL

) ON [PRIMARY]

I set everything up on my laptop first, creating a mirror of my public site and the SQL database, tested it in the most casual fashion, then I copied the component up to my public site and modified the code of my default.aspx page to display it right near the top of the page on http://www.duncanmackenzie.net.

This seemed to work well, but very quickly I realized I wanted to add a feature. Wouldn't it be useful if people could choose to view the results without actually voting? I would hate to imagine people voting, perhaps without giving their answer much thought, just because they are interested in seeing how the poll is going. Going into Rob's C# code, I added a new "Show Results" button and a little bit of code to make it switch views without voting. I uploaded the modified control and almost instantly received the feedback that once a user picked "Show Results," there was no way to get back to the voting view without closing and reopening your browser. Okay, so I added a "Go Back" button to the view of the page that shows the Results. It was relatively easy to implement.

Note I will be going through the code of the final control in more detail later in this article, including the new features I just described. And, of course, all of the code is available for download, so anything left out of the article will be there in the source.

That was the last of the UI modifications I wanted to make—the control was now behaving exactly as I had hoped—but I started to realize that a much larger rewrite was coming. The data structure of the control, with the poll details stored in an XML file and the votes themselves stored in a database table, left a bit to be desired (a comment that I will explain in just a moment). I also had a set of additional requirements in mind that would necessitate even more changes.

Back to the Drawing Board

Any project, even an informal one like this, can benefit from writing down the requirements for the finished work. In this case, I wanted a Quick Poll component that would allow me to:

Schedule polls for different locations on a Web site.
Retrieve results for any poll (whether it was still active or not).
View results broken down by poll location and date range.

The original design of the component didn't allow for any of these advanced features. In particular, since the only place the poll's choices were stored was the poll.xml file, once a new poll was running, their was no structured way to link people's votes with the choices they were voting for. I decided to remove the xml file from the system, move everything into the database, and add my additional functionality as I designed that new data storage system. I started with an empty database and created the following tables:

Poll—To store the main details, including the question, an ID, a Name, and the date the Poll was created).
PollDates—For scheduling polls into a specific location between two dates.
Location—List all valid locations for scheduling.
PollOptions—The list of choices for a specific poll.
Vote—Contains the actual vote submitted by a user, including their IP address, the poll they were voting in, the choice they made, and the location from which the poll was accessed.

Figure 3. Database Diagram for the new polling system

I chose to use integer IDs for those items that I will need to specify in the properties of the control, as no one likes to copy and paste GUIDs around any more than they have to. I did use a GUID for the Vote records though, since these will represent all votes over the entire lifetime of this system. With the tables in place, I created four stored procedures that would represent the core of my interaction with the system:

GetPollsByLocation
GetPollDetails
GetVoteCount
IncrementPoll

Of course, I will need to add to this list of procedures to support reporting and poll administration (creation/editing), but I'm not going to build that until the component is up and running.

To keep my database work independent from my ASP.NET-related code, I created some simple classes to represent the Poll and its related options.

http://i.msdn.microsoft.com/dynimg/IC107470.gif

Figure 4. Simple classes were used to slightly abstract the control from the database

I also used CodeSmith to create three collection classes—Polls, PollOptions and PollResults—all used to hold instances of the appropriate entity. These classes are not really worth detailing in the text of the article (although they are included in the source download) as they are nothing more than storage devices with simple property routines and the appropriate internal variables. I also created two classes to encapsulate all of my database work:

Loader, which provides access to GetPollsByLocation, GetPollDetails, and GetVoteCount.
Voter (cheesy name I know), which wraps the IncrementPoll stored procedure.

Here is the code for LoadPoll (which calls GetPollDetails) to illustrate the basic structure of all of these database calls.

Copy

VB.NET:

Public Shared Function LoadPoll(ByVal pollID As Integer, _

ByVal connectionString As String) As Poll

Dim result As New Poll

Dim conn As New SqlConnection(connectionString)

conn.Open()

Dim cmdGetPollDetails As New SqlCommand("GetPollDetails", conn)

cmdGetPollDetails.CommandType = CommandType.StoredProcedure

Dim paramPollID As SqlParameter _

= cmdGetPollDetails.CreateParameter()

With paramPollID

.SqlDbType = SqlDbType.Int

.ParameterName = "@PollID"

.Direction = ParameterDirection.Input

.Value = pollID

End With

Dim paramPollName As SqlParameter _

= cmdGetPollDetails.CreateParameter()

With paramPollName

.ParameterName = "@PollName"

.Direction = ParameterDirection.Output

.SqlDbType = SqlDbType.NVarChar

.Size = 50

End With

Dim paramPollQuestion As SqlParameter _

= cmdGetPollDetails.CreateParameter()

With paramPollQuestion

.ParameterName = "@PollQuestion"

.Direction = ParameterDirection.Output

.SqlDbType = SqlDbType.NVarChar

.Size = 100

End With

cmdGetPollDetails.Parameters.Add(paramPollID)

cmdGetPollDetails.Parameters.Add(paramPollName)

cmdGetPollDetails.Parameters.Add(paramPollQuestion)

Dim dr As SqlDataReader = _

cmdGetPollDetails.ExecuteReader( _

CommandBehavior.CloseConnection)

If dr.HasRows Then

Dim po As PollOption

Do While dr.Read

po = New PollOption

po.OptionID = dr.GetInt32(0)

po.OptionText = dr.GetString(1)

result.Options.Add(po)

Loop

result.ID = pollID

result.Name = CStr( _

cmdGetPollDetails.Parameters("@PollName").Value)

result.Question = CStr( _

cmdGetPollDetails.Parameters("@PollQuestion").Value)

dr.Close()

Return result

Else

dr.Close()

Return Nothing

End If

End Function

public static Poll LoadPoll(int pollID, string connectionString)

{

Poll result = new Poll();

SqlConnection conn = new SqlConnection(connectionString);

conn.Open();

SqlCommand cmdGetPollDetails = new SqlCommand("GetPollDetails", conn);

cmdGetPollDetails.CommandType = CommandType.StoredProcedure;

SqlParameter paramPollID = cmdGetPollDetails.CreateParameter();

{

paramPollID.SqlDbType = SqlDbType.Int;

paramPollID.ParameterName = "@PollID";

paramPollID.Direction = ParameterDirection.Input;

paramPollID.Value = pollID;

}

SqlParameter paramPollName = cmdGetPollDetails.CreateParameter();

{

paramPollName.ParameterName = "@PollName";

paramPollName.Direction = ParameterDirection.Output;

paramPollName.SqlDbType = SqlDbType.NVarChar;

paramPollName.Size = 50;

}

SqlParameter paramPollQuestion = cmdGetPollDetails.CreateParameter();

{

paramPollQuestion.ParameterName = "@PollQuestion";

paramPollQuestion.Direction = ParameterDirection.Output;

paramPollQuestion.SqlDbType = SqlDbType.NVarChar;

paramPollQuestion.Size = 100;

}

cmdGetPollDetails.Parameters.Add(paramPollID);

cmdGetPollDetails.Parameters.Add(paramPollName);

cmdGetPollDetails.Parameters.Add(paramPollQuestion);

SqlDataReader dr = cmdGetPollDetails.ExecuteReader(CommandBehavior.CloseConnection);

if (dr.HasRows) {

PollOption po = default(PollOption);

while (dr.Read) {

po = new PollOption();

po.OptionID = dr.GetInt32(0);

po.OptionText = dr.GetString(1);

result.Options.Add(po);

}

result.ID = pollID;

result.Name = Convert.ToString(cmdGetPollDetails.Parameters("@PollName").Value);

result.Question = Convert.ToString(cmdGetPollDetails.Parameters("@PollQuestion").Value);

dr.Close();

return result;

} else {

dr.Close();

return null;

}

The other routines follow the same basic structure: calling the stored procedure, using a DataReader to return the results, and then turning those results into one of my project's custom object classes. Voting is a bit different, of course, since it doesn't need to return anything, but it is just doing an insert into the Vote table with 4 values through the IncrementPoll stored procedure shown below.

Copy

CREATE PROCEDURE dbo.IncrementPoll

(

@PollID int,

@Vote int,

@LocationID int,

@IPAddress nvarchar(50)

)

INSERT INTO

Vote

(PollID, LocationID, OptionID, IPAddress)

VALUES

(

@PollID,

@LocationID,

@Vote,

@IPAddress

)

With my database ready, and with all of my data access code created, it is time to try my hand at building an ASP.NET control.

Building My First ASP.NET Control

There is a first time for everything, and you get to see my first entry into the ASP.NET control world. The one control I had read through in any detail was Rob Howard's Poll component, so the structure and code of my control is based on that example. I started off using the WebControl library project template, which does not allow the creation of .ascx style controls, and created my control as a simple class that inherited from System.Web.UI.WebControls.WebControl. I added the desired properties to my class (for the Poll ID, Location ID, Connection String Key, and three properties for style attributes), and then I started working on the flow for the control.

The control will have two states, either it will be showing the user an option button list (also known as a radio button list) of choices (to see the "choices" layout, see Figure 1), or it will be showing a bar graph of the results (the "results" layout, as in Figure 2). When the control is not being rendered in response to one of its own button clicks, it decides which view to show based on the presence of a cookie. This cookie is set when you vote. It serves two purposes:

To improve the user experience by showing them the results immediately if they have voted on a previous visit.
To reduce the amount of ballot-stuffing (one person submitting multiple votes).

In the OnInit routine of my control, I check for the cookie and then call either ShowResults or ShowChoices as appropriate

VB.NET:

Protected Overrides Sub OnInit(ByVal e As System.EventArgs)

Dim myCookie As Web.HttpCookie

myCookie = GetCookie()

If Me.PollID <= 0 And Me.LocationID > 0 Then

Me.PollID = Me.GetPollID(Me.LocationID)

End If

If myCookie Is Nothing OrElse _

myCookie.Value = String.Empty Then

Me.ShowChoices()

Else

Me.ShowResults(myCookie.Value)

End If

End Sub

Private Function GetCookieName() As String

If Me.PollID > 0 Then

Return "Poll_" & Me.PollID

Else

Return Nothing

End If

End Function

Private Function GetCookie() As Web.HttpCookie

Dim myCookie As Web.HttpCookie = _

Me.Context.Request.Cookies(Me.GetCookieName)

Return myCookie

End Function

protected override void OnInit(System.EventArgs e)

{

Web.HttpCookie myCookie = default(Web.HttpCookie);

myCookie = GetCookie();

if (this.PollID <= 0 & this.LocationID > 0) {

this.PollID = this.GetPollID(this.LocationID);

}

if (myCookie == null || myCookie.Value == string.Empty) {

this.ShowChoices();

} else {

this.ShowResults(myCookie.Value);

}

private string GetCookieName()

{

if (this.PollID > 0) {

return "Poll_" + this.PollID;

} else {

return null;

}

private Web.HttpCookie GetCookie()

{

Web.HttpCookie myCookie = this.Context.Request.Cookies(this.GetCookieName);

return myCookie;

}

In the actual rendering routines, I am creating my controls and layout manually, as opposed to laying them out visually, as one would when working with an .ascx, which seems very old school. Everything works, though, and the layout is simple enough that it doesn't take that much code to create. To create the "choices" layout, I pull back the poll information, and use data binding to display a list of option buttons with the appropriate text.

Copy

Private Sub ShowChoices()

Me.Controls.Clear()

Dim myPoll As PollInformation.Poll

myPoll = GetPoll(Me.PollID)

If myPoll Is Nothing Then

Dim noPoll As New WebControls.Label

noPoll.Text = "No Poll Found"

noPoll.ApplyStyle(Me.ItemStyle)

Me.Controls.Add(noPoll)

Else

Dim questionLabel As New WebControls.Label

questionLabel.Text = myPoll.Question

questionLabel.ApplyStyle(Me.QuestionStyle)

myRB.DataSource = myPoll.Options

myRB.DataValueField = "OptionID"

myRB.DataTextField = "OptionText"

myRB.DataBind()

myRB.ApplyStyle(Me.ItemStyle)

Me.Controls.Add(questionLabel)

Me.Controls.Add(myRB)

voteButton.Text = "Vote"

showResultsButton.Text = "Show Results"

voteButton.ApplyStyle(Me.ButtonStyle)

showResultsButton.ApplyStyle(Me.ButtonStyle)

Me.Controls.Add(voteButton)

Me.Controls.Add(showResultsButton)

End If

End Sub

private void ShowChoices()

{

this.Controls.Clear();

PollInformation.Poll myPoll = default(PollInformation.Poll);

myPoll = GetPoll(this.PollID);

if (myPoll == null) {

WebControls.Label noPoll = new WebControls.Label();

noPoll.Text = "No Poll Found";

noPoll.ApplyStyle(this.ItemStyle);

this.Controls.Add(noPoll);

} else {

WebControls.Label questionLabel = new WebControls.Label();

questionLabel.Text = myPoll.Question;

questionLabel.ApplyStyle(this.QuestionStyle);

myRB.DataSource = myPoll.Options;

myRB.DataValueField = "OptionID";

myRB.DataTextField = "OptionText";

myRB.DataBind();

myRB.ApplyStyle(this.ItemStyle);

this.Controls.Add(questionLabel);

this.Controls.Add(myRB);

voteButton.Text = "Vote";

showResultsButton.Text = "Show Results";

voteButton.ApplyStyle(this.ButtonStyle);

showResultsButton.ApplyStyle(this.ButtonStyle);

this.Controls.Add(voteButton);

this.Controls.Add(showResultsButton);

}

Instead of calling into my data routines (LoadPoll in this case, described earlier) directly, I call them through some little routines that handle caching this data.

Copy

Private Function GetPoll(ByVal id As Integer) _

As PollInformation.Poll

Dim cacheKey As String = String.Format("Poll_{0}", id)

Dim obj As Object

Dim myPoll As PollInformation.Poll

obj = Me.Context.Cache.Get(cacheKey)

If obj Is Nothing Then

myPoll = PollInformation.Loader.LoadPoll( _

id, Me.GetConnectionString)

Me.Context.Cache.Add(cacheKey, myPoll, _

Nothing, Now.AddHours(1), _

Web.Caching.Cache.NoSlidingExpiration, _

Web.Caching.CacheItemPriority.Normal, Nothing)

Else

myPoll = DirectCast(obj, PollInformation.Poll)

End If

Return myPoll

End Function

private PollInformation.Poll GetPoll(int id)

{

string cacheKey = string.Format("Poll_{0}", id);

object obj = null;

PollInformation.Poll myPoll = default(PollInformation.Poll);

obj = this.Context.Cache.Get(cacheKey);

if (obj == null) {

myPoll = PollInformation.Loader.LoadPoll(id, this.GetConnectionString);

this.Context.Cache.Add(cacheKey, myPoll, null, Now.AddHours(1), Web.Caching.Cache.NoSlidingExpiration, Web.Caching.CacheItemPriority.Normal, null);

} else {

myPoll = (PollInformation.Poll)obj;

}

return myPoll;

}

Note The length of time to cache the data is hard coded at the moment (please restrain your shock), but it would be a good candidate for moving into the configuration or properties of the control in the future.

The final choices layout (see Figure 5) includes two buttons, Vote and Show Results. Show Results just calls the ShowResults procedure without a selected item parameter (which I'll cover in just a bit), and Vote calls back to my database routines to cast the user's vote for a specific option.

http://i.msdn.microsoft.com/dynimg/IC106658.gif

Figure 5. The Show Results button was added to allow people to see the current voting data without actually casting a vote themselves.

Copy

Private Sub voteButton_Click(ByVal sender As Object, _

ByVal e As System.EventArgs) Handles voteButton.Click

If Not myRB.SelectedItem Is Nothing Then

Dim ip As String = context.Request.UserHostAddress

Dim myPoll As Poll

myPoll = Me.GetPoll(Me.PollID)

If ChoiceInOptions(myPoll.Options, myRB.SelectedItem.Text, _

CInt(myRB.SelectedValue)) Then

Me.SetCookie(myRB.SelectedItem.Text)

PollInformation.Voter.Vote(Me.PollID, _

CInt(myRB.SelectedValue), _

0, ip, Me.GetConnectionString)

Me.ShowResults(myRB.SelectedItem.Text)

End If

End Sub

private void // ERROR: Handles clauses are not supported in C#

voteButton_Click(object sender, System.EventArgs e)

{

if ((myRB.SelectedItem != null)) {

string ip = context.Request.UserHostAddress;

Poll myPoll = default(Poll);

myPoll = this.GetPoll(this.PollID);

if (ChoiceInOptions(myPoll.Options, myRB.SelectedItem.Text, Convert.ToInt32(myRB.SelectedValue))) {

this.SetCookie(myRB.SelectedItem.Text);

PollInformation.Voter.Vote(this.PollID, Convert.ToInt32(myRB.SelectedValue), 0, ip, this.GetConnectionString);

this.ShowResults(myRB.SelectedItem.Text);

}

Note The ChoiceInOptions function just ensures that the vote ID and text is equal to one of the choices I have in the database for the current Poll. Checking those values may be overkill, but it makes it a bit harder to post invalid data into the system through the control.

The results layout displays a bar chart (see Figure 6) using a combination of a table and another ASP.NET control based on Rob Howard's Progress Bar control (included as part of his original Poll component). If the results layout is displayed after the user has voted, the user's choice is passed in as a string (validated against the list of valid options using the ChoiceInOptions function). In the case of a user that has just clicked on the Show Results button (and therefore hasn't voted), an empty string is passed in (and a Go Vote button is shown).

Figure 6. The bar chart is constructed by dividing the area into a bunch of small cells and then setting the back color of the appropriate number of cells.

Copy

Private Sub ShowResults(ByVal choice As String)

Me.Controls.Clear()

Dim lbl As New WebControls.Label

lbl.ApplyStyle(Me.QuestionStyle)

Dim myPoll As PollInformation.Poll

myPoll = Me.GetPoll(Me.PollID)

Dim results As PollInformation.PollResults

If choice = String.Empty Then

results = Me.GetPollResults(Me.PollID)

Else

results = Me.GetPollResults(Me.PollID, True)

End If

If myPoll Is Nothing OrElse results Is Nothing Then

Dim noPoll As New WebControls.Label

noPoll.Text = "No Poll Found"

noPoll.ApplyStyle(Me.ItemStyle)

Me.Controls.Add(noPoll)

Else

lbl.Text = myPoll.Question

Me.Controls.Add(lbl)

Dim resultsTable As New WebControls.Table

resultsTable.CellSpacing = 2

resultsTable.Width = WebControls.Unit.Percentage(1)

Dim totalVotes As Integer = 0

If results.Count > 0 Then

totalVotes = results(0).TotalVotes

End If

For Each pr As PollInformation.PollResult In results

Dim tr As New WebControls.TableRow

Dim td As New WebControls.TableCell

td.Text = pr.OptionText

td.ApplyStyle(Me.ItemStyle)

td.Wrap = False

tr.Cells.Add(td)

td = New WebControls.TableCell

Dim pb As New ProgressBar

pb.Percentage = (pr.Result / pr.TotalVotes)

pb.Cols = 20

pb.BackColor = System.Drawing.Color.LightGray

pb.ForeColor = System.Drawing.Color.Blue

td.Controls.Add(pb)

tr.Cells.Add(td)

td = New WebControls.TableCell

td.ApplyStyle(Me.ItemStyle)

td.Text = CInt(pb.Percentage * 100) & "%"

'td.Width = WebControls.Unit.Percentage(0.2)

tr.Cells.Add(td)

resultsTable.Rows.Add(tr)

Dim Summary As New WebControls.TableRow

Dim yourVote As New WebControls.TableCell

yourVote.HorizontalAlign = WebControls.HorizontalAlign.Right

yourVote.ApplyStyle(Me.ItemStyle)

yourVote.ColumnSpan = 3

Me.goBackButton.ApplyStyle(Me.ButtonStyle)

Me.goBackButton.Text = "Go Vote"

Summary.Cells.Add(yourVote)

resultsTable.Rows.Add(Summary)

Me.Controls.Add(resultsTable)

If (Not choice = String.Empty) AndAlso _

ChoiceInOptions(myPoll.Options, choice) Then

yourVote.Text = _

String.Format( _

"Your Vote: {0}<br />Total Votes: {1}", _

choice, totalVotes)

Else

yourVote.Text = _

String.Format( _

"Total Votes: {0}", _

totalVotes)

Me.Controls.Add(Me.goBackButton)

End If

End Sub

C#:

private void ShowResults(string choice)

{

this.Controls.Clear();

WebControls.Label lbl = new WebControls.Label();

lbl.ApplyStyle(this.QuestionStyle);

PollInformation.Poll myPoll = default(PollInformation.Poll);

myPoll = this.GetPoll(this.PollID);

PollInformation.PollResults results = default(PollInformation.PollResults);

if (choice == string.Empty) {

results = this.GetPollResults(this.PollID);

} else {

results = this.GetPollResults(this.PollID, true);

}

if (myPoll == null || results == null) {

WebControls.Label noPoll = new WebControls.Label();

noPoll.Text = "No Poll Found";

noPoll.ApplyStyle(this.ItemStyle);

this.Controls.Add(noPoll);

} else {

lbl.Text = myPoll.Question;

this.Controls.Add(lbl);

WebControls.Table resultsTable = new WebControls.Table();

resultsTable.CellSpacing = 2;

resultsTable.Width = WebControls.Unit.Percentage(1);

int totalVotes = 0;

if (results.Count > 0) {

totalVotes = results(0).TotalVotes;

}

foreach (PollInformation.PollResult pr in results) {

WebControls.TableRow tr = new WebControls.TableRow();

WebControls.TableCell td = new WebControls.TableCell();

td.Text = pr.OptionText;

td.ApplyStyle(this.ItemStyle);

td.Wrap = false;

tr.Cells.Add(td);

td = new WebControls.TableCell();

ProgressBar pb = new ProgressBar();

pb.Percentage = (pr.Result / pr.TotalVotes);

pb.Cols = 20;

pb.BackColor = System.Drawing.Color.LightGray;

pb.ForeColor = System.Drawing.Color.Blue;

td.Controls.Add(pb);

tr.Cells.Add(td);

td = new WebControls.TableCell();

td.ApplyStyle(this.ItemStyle);

td.Text = Convert.ToInt32(pb.Percentage * 100) + "%";

//td.Width = WebControls.Unit.Percentage(0.2)

tr.Cells.Add(td);

resultsTable.Rows.Add(tr);

}

WebControls.TableRow Summary = new WebControls.TableRow();

WebControls.TableCell yourVote = new WebControls.TableCell();

yourVote.HorizontalAlign = WebControls.HorizontalAlign.Right;

yourVote.ApplyStyle(this.ItemStyle);

yourVote.ColumnSpan = 3;

this.goBackButton.ApplyStyle(this.ButtonStyle);

this.goBackButton.Text = "Go Vote";

Summary.Cells.Add(yourVote);

resultsTable.Rows.Add(Summary);

this.Controls.Add(resultsTable);

if ((!(choice == string.Empty)) && ChoiceInOptions(myPoll.Options, choice)) {

yourVote.Text = string.Format("Your Vote: {0}<br />Total Votes: {1}", choice, totalVotes);

} else {

yourVote.Text = string.Format("Total Votes: {0}", totalVotes);

this.Controls.Add(this.goBackButton);

}

There is more code in this project than I wanted to list directly in the article, so you will want to pull down the code and check it out for yourself. Included in the .msi is a SQL Script for the database, including the tables, the stored procedures, and several triggers. A test ASP.NET page is also included, along with a sample web.config file, but you will need to customize it to include your own connection string information. Adding this control to a page involves only two things, a Register statement at the top of the page, and the control itself:

Copy

<%@ Register TagPrefix="cc1"

Namespace="QuickVote"

Assembly="QuickVote" %>

<cc1:vote id="Vote2" runat="server"

PollID="-1"

Height="96px"

Width="376px"

LocationID="1">

</cc1:vote>

</form>

In a future article, I hope to extend this sample by moving the database access into a set of Web services. This design change would allow the control to be hosted on other Web sites, and to be extended to rich-client applications. Of course, adding administrative functionality and reporting would also be useful, so perhaps you will see more than one update to this system.

Coding Challenge

At the end of some of my Coding4Fun columns, I include a little coding challenge—something for you to work on if you are interested. For this article, the challenge is to create a Microsoft .NET application around the topic of polling, surveying, or something else related to my sample. Just post whatever you produce to GotDotNet and send me an e-mail message (at duncanma@microsoft.com) with an explanation of what you have done and why you feel it is interesting. You can send me your ideas whenever you like, but please just send me links to code samples, not the samples themselves (my inbox thanks you in advance).

Have your own ideas for hobbyist content? Let me know at duncanma@microsoft.com, and happy coding!

SIVANANDA REDDY GANGIREDDY

Pages

Tuesday, January 10, 2012

Create Polls

No comments:

Post a Comment