Part 2 : Add Quiz and Quiz Options using ASP.NET MVC

We are using two tables:
1) Quiz
2) QuizOption




Step 1 : Create Function to split by passing split by like single comma or double comma etc.
  
CREATE FUNCTION [dbo].[func_Split]
    (
    @DelimitedString    varchar(8000),
    @Delimiter              varchar(100)
    )
RETURNS @tblArray TABLE
    (
    ElementID   int IDENTITY(1,1),  -- Array index
    Element     varchar(1000)               -- Array element contents
    )
AS
BEGIN

    -- Local Variable Declarations
    -- ---------------------------
    DECLARE @Index      smallint,
                    @Start      smallint,
                    @DelSize    smallint

    SET @DelSize = LEN(@Delimiter)

    -- Loop through source string and add elements to destination table array
    -- ----------------------------------------------------------------------
    WHILE LEN(@DelimitedString) > 0
    BEGIN

        SET @Index = CHARINDEX(@Delimiter, @DelimitedString)

        IF @Index = 0
            BEGIN

                INSERT INTO
                    @tblArray
                    (Element)
                VALUES
                    (LTRIM(RTRIM(@DelimitedString)))

                BREAK
            END
        ELSE
            BEGIN

                INSERT INTO
                    @tblArray
                    (Element)
                VALUES
                    (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))

                SET @Start = @Index + @DelSize
                SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)

            END
    END

    RETURN
END

Step 2 : Creating Procedure to save question in Quiz table and list of answer options in QuizOption table.

CREATE PROC usp_InsertQuizData
(
@Question VARCHAR(100),
@QuizOption VARCHAR(MAX),
@AnswerType VARCHAR(10)
)
AS
BEGIN
 DECLARE @QId INT
 DECLARE @Option VARCHAR(MAX)
 INSERT INTO Quiz (Question) VALUES(@Question)
 SET @QId= @@IDENTITY
 -- As  we are getting list of option as double comma separated, we are using function func_split to split it from double
 -- comma and storing it in temp table. Also we are using cursor to fetch and save all list of option to table.
 -- Example list of options : Option1-&ture,,Option2-&false,,Option2-&false,,
 SELECT * INTO #TempQuizOption FROM dbo.func_split(@QuizOption, ',,')
 DECLARE TempQuizOption_Cursor Cursor FOR SELECT Element FROM #TempQuizOption
 OPEN TempQuizOption_Cursor
 FETCH NEXT FROM TempQuizOption_Cursor INTO @Option
 WHILE @@FETCH_STATUS = 0
 BEGIN
  INSERT INTO QuizOption (Questionid,[Option],IsCorrect,AnsType)
   VALUES(@QId,LEFT(@Option, charindex('-&', @Option) - 1),
  CAST(SUBSTRING(@Option, charindex('-&', @Option)+2, len(@Option) - CHARINDEX('-&', LEFT(@Option, charindex('-&', @Option) - 1))) AS BIT),@AnswerType)
  FETCH NEXT FROM TempQuizOption_Cursor INTO @Option
 END
 CLOSE TempQuizOption_Cursor;
 DEALLOCATE TempQuizOption_Cursor;
 DROP TABLE #TempQuizOption
END

Step 3 : Add Class Quiz in Models and Function AddQuiz in Class QuizDA. Note this class is inheriting by SQLHelper Class (Refer in Part 1 for SQLHelper.cs).

Adding Class Models :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SampleMvc.Models
{
    public class Quiz
    {
        public string Question { get; set; }
        public int QuestionId { get; set; }
        public string QuizOptions { get; set; }
        public string Answertype { get; set; }
    }

}

Adding Function in Data Access

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SampleMvc.Models;
using System.Data.SqlClient;
using System.Data;

namespace SampleMvc.DA
{
    public class QuizDA : SQLHelper
    {
        public void AddQuiz(Quiz quiz)
        {
            try
            {
                int i = ExecNonQueryProc("usp_InsertQuizData",
                new SqlParameter("@Question", quiz.Question),
                new SqlParameter("@QuizOption", quiz.QuizOptions),
                new SqlParameter("@Answertype", quiz.Answertype));
            }
            catch (Exception ex)
            {
                ErrorMessage = "Error : " + ex.Message;
                OperationStatus = false;
            }
            finally
            {
                CloseConnection();
            }
        }
    }
}

Step 4 : Adding EmployeeTest Controller 

using SampleMvc.DA;
using SampleMvc.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace SampleMvc.Controllers
{
    public class EmployeeTestController : Controller
    {    
        public ActionResult AddQuiz()
        {
            return View();
        }
        [HttpPost]
        public ActionResult AddQuiz(Quiz quiz)
        {
            QuizDA quizDA = new QuizDA();
            quizDA.AddQuiz(quiz);
            return View();
        }
    }
}

Step 5 : Adding Empty View

@{
    Layout = null;
}

<script src="~/Scripts/jquery-3.2.1.js"></script>
<script>
    $(document).ready(function () {
        // We are creating list of radio button, checkbox or single textbox on selection of anstype from dropdown.
        // As many options we want we can add on option upon selecting radio button or checkbox.
        // Options are added to div havind id lstOptions.
        $('#btnAddQuiz').click(function () {
            if ($('#drpAnsType :selected').text() != "Select") {
                if ($('#drpAnsType').val() == "R") {
                    var radioBtn = $('<input type="radio" name="rbtnCount" value = "' + $("#txtAns").val() + '"/>' + $("#txtAns").val() + '<br/>');
                    $("#lstOptions").append(radioBtn);
                }
                else if ($('#drpAnsType').val() == "C") {
                    var radioBtn = $('<input type="checkbox" name="chkCount" value = "' + $("#txtAns").val() + '"/>' + $("#txtAns").val() + '<br/>');
                    $("#lstOptions").append(radioBtn);
                }              
            }
        });

        $('#btnsave').click(function () {
            var Option = "";
            if ($('#drpAnsType').val() == "R") {
                // Now we have to pass the list of options for particular question.
                // We passing list of option as double comma separated. We are separating it using double comma (,,) as there could be single comma in answers as well.
                // So we may to not get expected result while saving answers options in database.
                // Also we are appending status of option whether this option is correct or not separated by -& as there could also be single - and & in answer.
                // You may use what ever suits you.
                // Ex : Option1-&ture,,Option2-&false,,Option2-&false,,
                $('#lstOptions input:radio').each(function (index) {
                    Option = Option + $(this).attr('value') + "-&" + $(this).is(':checked') + ",,";
                });
            }
            else if ($('#drpAnsType').val() == "C") {              
                // Ex : Option1-&ture,,Option2-&true,,Option2-&false,,
                $('#lstOptions input:checkbox').each(function (index) {
                    Option = Option + $(this).attr('value') + "-&" + $(this).is(':checked') + ",,";
                });
            }
            else {
                Option = $("#txtAns").val() + "-&" + "true" + ",,";
            }
            // Passing values to controller using ajax.
            var quiz = {};
            quiz.Question = $('#txtQues').val();
            quiz.QuizOptions = Option.substring(0, Option.length - 2);
            quiz.Answertype = $('#drpAnsType option:selected').val();

            $.ajax({
                url: 'EmployeeTest/AddQuiz',
                method: 'post',
                data: '{quiz: ' + JSON.stringify(quiz) + '}',
                contentType: "application/json; charset=utf-8",
                success: function () {

                },
                error: function (err) {
                    alert(err);
                }
            });
        });


        $('#drpAnsType').change(function () {
            if ($('#drpAnsType').val() == "T") {
                $('#btnAddQuiz').hide();
            }
            else {
                $('#btnAddQuiz').show();
            }
        });
    });
</script>

<div style="padding: 30px;">
    Question<br />
    <input type="text" id="txtQues" /><br />
    Answer Type
    <br />
    <select id="drpAnsType">
        <option value="S">Select</option>
        <option value="R">Radio</option>
        <option value="C">Checkbox</option>
        <option value="T">Text</option>
    </select><br />
    Answer (Option) <br />
    <input type="text" id="txtAns" /><br />
    <br />
    <input type="button" value="Add Option" id="btnAddQuiz" />
    <div id="lstOptions" style="padding-top: 30px;">
    </div>
    <input type="button" value="Save" id="btnsave" />
</div>

Output : When selecting radio button type as options for answers

Output : When selecting check box type as options for answers