Creating a Poll System in C#

Introduction

Here I show a simple way to develop a Polling/Survey System. It shows results with percentage of votes in a graphical manner. have used RadioButtonList to show poll options to select one option.

Database Diagram

I have three tables one for questions, the second for offered answers, and the third one to store answers selected by the user.

Insert Poll and Options

SP for inserting a Poll and various options/Answers.

CREATE PROCEDURE [dbo].[insQues]
    @ques varchar(350),
    @other varchar(250)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        -- Disable status for all questions
        update tblQues set status = 0;
        
        -- Insert new question
        insert into tblQues (qText, other) values (@ques, @other);
    END TRY
    BEGIN CATCH
        -- Handle error (e.g., logging, raising an error, etc.)
    END CATCH;
END
CREATE PROCEDURE [dbo].[insAnswers]
    @anstext varchar(450)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        -- Insert new answer for the latest question
        declare @qid int;
        select @qid = IDENT_CURRENT('tblQues'); -- Use IDENT_CURRENT to get the latest identity value
        insert into tblQuesAns (qID, anstext) values (@qid, @anstext);
    END TRY
    BEGIN CATCH
        -- Handle error (e.g., logging, raising an error, etc.)
    END CATCH;
END

Here I input options/answers in one text box and split it by  to separate various options.

using System.Data.SqlClient;
// Assuming 'ff' is an instance of your custom class handling database connections and commands

try
{
    // Insert the question
    SqlCommand cmdins = ff.getCommand("insQues");
    cmdins.Parameters.AddWithValue("@ques", txtQues.Text);
    cmdins.Parameters.AddWithValue("@other", txtOther.Text);
    cmdins.ExecuteNonQuery();

    // Insert each answer separately
    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();
    }
}
catch (Exception ex)
{
    // Handle exceptions (e.g., logging, displaying error to the user, etc.)
}
finally
{
    // Close the database connection
    if (cmdins != null && cmdins.Connection != null)
    {
        cmdins.Connection.Close();
    }
}

Showing Graphical Results

To show results graphically for quick analysis, I have used StringBuilder and calculated percentages as.

no. of votes of respective answers*100/total no of votes, and now I set an image within td, and the width of this image is this percentage.

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 PROCEDURE [dbo].[selectResults]
(
    @qid int,
    @b int OUTPUT
)
AS
BEGIN
    -- Retrieve answer information for the given question ID
    SELECT anstext, anscount
    FROM tblQuesAns
    WHERE qID = @qid;

    -- Calculate the sum of anscount for the given question ID
    SET @b = (SELECT SUM(anscount) FROM tblQuesAns WHERE qID = @qid);
END

Now how to fetch the value of @b in C#.

using System.Data.SqlClient;
// Assuming 'ff' is an instance of your custom class handling database connections and commands
try
{
    SqlCommand cmd = ff.getCommand("selectResults");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@qid", qid);
    SqlParameter outputParam = new SqlParameter("@b", SqlDbType.Int);
    outputParam.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(outputParam);
    using (SqlDataReader dr = cmd.ExecuteReader())
    {
        while (dr.Read())
        {
            // Retrieve data from the reader
            string anstext = dr["anstext"].ToString();
            int anscount = int.Parse(dr["anscount"].ToString());

            // Process the data as needed
            // For example, you could populate a list or display the data
        }
        dr.Close();
    }

    // Retrieve the value of the output parameter after executing the procedure
    int total = (int)cmd.Parameters["@b"].Value;
}
catch (Exception ex)
{
    // Handle exceptions (e.g., logging, displaying error to the user, etc.)
}
finally
{
    // Close the database connection
    if (cmd != null && cmd.Connection != null)
    {
        cmd.Connection.Close();
    }
}

Download the source file to see the detailed description of this.

Future Scope

This has a 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.