Introduction
Here I show a simple way to develop a Polling/Survey System. It shows results with percentage of votes in a graphical manner. I have used RadioButtonList to show poll options to
select one option.
DataBase Diagram
I have three tables one for question, second for offered answers and third one
is to store answer selected by user.
Insert Poll and Options
SP for insert a Poll and various options/Answers.
CREATE PROCEDURE [dbo].[insQues]
@ques varchar(350),
@other varchar(250)
as
BEGIN
SET NOCOUNT ON;
update tblQues set status=0;
insert into tblQues (qText,other) values(@ques,@other);
END
CREATE PROCEDURE [dbo].[insAnswers]
@anstext varchar(450)
as
BEGIN
SET NOCOUNT ON;
declare @qid int
select @qid=MAX(qid) from tblQues;
insert into tblQuesAns (qID,anstext) values(@qid,@anstext);
END
Here I input options/answers in one text box and split it by ';' to seperate various options:
SqlCommand cmdins = ff.getCommand("insQues");
cmdins.Parameters.AddWithValue("@ques", txtQues.Text);
cmdins.Parameters.AddWithValue("@other", txtOther.Text);
cmdins.ExecuteNonQuery();
//we have to insert each answer so split it by ; to seperate answer
String[] ans = txtAns.Text.Split(new String[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string ss in ans)
{
cmdins = ff.getCommand("insAnswers");
cmdins.Parameters.AddWithValue("@anstext", ss);
cmdins.ExecuteNonQuery();
}
cmdins.Connection.Close();
Showing graphical Results
To show results graphically for quick analysis,I have used
StringBuilder and calculate percentage as
no. of votes of respective answers*100/total no of votes
and now I set an image within td and width of this image is this percentage.
See above Image 2
Point of interest
Here I want to fetch answers and the total number of answers count from the tblQuesAns table. So I use an output parameter:
CREATE PROC [dbo].[selectResults]
(
@qid int,
@b int output
)
AS
BEGIN
select anstext,anscount from tblQuesAns where qID=@qid;
set @b=(select sum(anscount) from tblQuesAns where qID=@qid);
END
Now how to fetch value of @b in C#:
SqlCommand cmd = ff.getCommand("selectResults");
cmd.Parameters.AddWithValue("@qid", qid);
SqlParameter sp = cmd.Parameters.Add("@b", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int total = int.Parse(cmd.Parameters["@b"].Value.ToString());
SqlDataReader dr = cmd.ExecuteReader();
Download the source file to see the detailed description of this.
Future Scope
This has limitation to select only one option So I will modify this with option
of RadioButtonList and CheckBoxList both, and change to this in Custom Control
to make it useful.
Thank You for Reading.