Simple Online Quiz with timer using ASP.Net and SQL Server

Hello and welcome everyone, here I am going to share you very simple but advanced Online Quiz using ASP.Net.

.aspx page

<%@ Page Title="Product quiz Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" EnableEventValidation="false" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content ID="headerContent" ContentPlaceHolderID="HeadContent" runat="server">

</asp:Content>

<asp:Content runat="server" ID="FeaturedContent" ContentPlaceHolderID="FeaturedContent">

</asp:Content>

<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="MainContent">

<asp:Label ID="lblmessage" runat="server" ForeColor="#ff0000" Visible="false" /><br />

<asp:HiddenField ID="quizfield" runat="server" />

<asp:Label ID="lblquizname" runat="server" CssClass="quizname" />

<asp:Label ID="lbldescription" runat="server" CssClass="quizdesc" />

</div>

<asp:ValidationSummary ID="quizvalidationsummary" runat="server" ShowMessageBox="false" DisplayMode="BulletList" ShowSummary="true" HeaderText="<br/> You have not filled details or not attempted questions." ForeColor="Red" ValidationGroup="quizvalidation" BorderColor="Red" BorderStyle="Solid" BorderWidth="0px" Width="380px" />

</div>

<div>

<asp:Literal ID="ltName" runat="server"></asp:Literal>

<asp:Literal ID="ltSuccessFail" runat="server"></asp:Literal>

</div>

<legend>Please fill your details</legend>

<div>

<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtname" Display="Dynamic" CssClass="field-validation-error" ErrorMessage="name required" ValidationGroup="quizvalidation" Text="*" SetFocusOnError="true" />

</div>

<div><asp:Label ID="lblemail" runat="server" AssociatedControlID="txtemail">Email</asp:Label></div><div><asp:TextBox runat="server" ID="txtemail" />

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtemail" Display="Dynamic" CssClass="field-validation-error" ErrorMessage="email required" ValidationGroup="quizvalidation" Text="*" SetFocusOnError="true" /></div>

</div>

</fieldset>

<asp:Timer ID="timer" runat="server" interval="1000" ontick="Timer_Tick" Enabled="false"></asp:Timer>

<asp:Button ID="btnStart" runat="server" OnClick="startTimer" Text="Start Quiz"></asp:Button>

<asp:UpdatePanel runat="server" id="TimedPanel" updatemode="Conditional">

<asp:AsyncPostBackTrigger controlid="timer" eventname="Tick" />

</Triggers>

<asp:Label ID="lblTimer" Font-Bold="true" ForeColor="Green" runat="server"></asp:Label>

</ContentTemplate>

</asp:UpdatePanel>

</div>

<asp:Label ID="lblalert" runat="server" ForeColor="Red" Font-Size="15px" Visible="false" /><br />

<asp:Repeater ID="questionsrpt" runat="server" OnItemDataBound="questionsrpt_ItemDataBound">

<asp:HiddenField ID="hfID" runat="server" Value='<%# DataBinder.Eval(Container.DataItem, "id")%>' Visible="false" />

<asp:Label ID="lblquestion" CssClass="questionText" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "title")%>' /><br />

<asp:RadioButtonList ID="rbloptions" runat="server" CssClass="centreRadio" ValidationGroup="quizvalidation" />

<asp:RequiredFieldValidator ID="rfvquiz" runat="server" Display="Dynamic" ControlToValidate="rbloptions" ValidationGroup="quizvalidation" ForeColor="Red" Text="*" SetFocusOnError="true"/>

</ItemTemplate>

</asp:Repeater>

<asp:LinkButton ID="lbPrevious" runat="server" CssClass="linkButton" Enabled="false" OnClick="lbPrevious_Click1">Prev</asp:LinkButton>

<asp:LinkButton ID="lbNext" runat="server" CssClass="linkButton" OnClick="lbNext_Click1">Next</asp:LinkButton>

<asp:Button ID="btnsubmit" runat="server" OnClick="btnsubmit_Click" Text="Submit" ValidationGroup="quizvalidation" Visible="false" />

<asp:Repeater ID="rptPaging" runat="server" onitemcommand="rptPaging_ItemCommand">

<asp:LinkButton ID="btnPage"

style="padding:8px; margin:2px; background:#ffa100; border:solid 1px #666; font:8pt tahoma;"

CommandName="Page" CommandArgument="<%# Container.DataItem %>"

runat="server" ForeColor="White" Font-Bold="True"><%# Container.DataItem %>

</asp:LinkButton>

</ItemTemplate>

</asp:Repeater>

</div>

</asp:Content>

.aspx.cs code

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.Collections;

public partial class _Default : Page

{

string quizname = "";

string description = "";

DateTime updatedate = new DateTime();

DateTime start = new DateTime();

DateTime end = new DateTime();

string terms = "";

string lbr = "<br /><br />";

int quizId = 0;

int tempval = 0;

int SelectedPage = 0;

SqlDataReader dReader;

string email = "";

string name = "";

string selectedanswer = "";

string correctanswer = "";

int questionId = 0;

int questionscount = 0;

int correctcount = 0;

int wrongcount = 0;

int success = 0;

//ArrayList answersList = new ArrayList();

DataTable dtAnswer = new DataTable("dtAnswer");

DataColumn dtColumn;

DataRow myDataRow;

int qid = 0;

int timerCntr = 0;

protected void Page_Load(object sender, EventArgs e)

{

updatedate = DateTime.Now;

if (!Page.IsPostBack)

{

//show quiz details

Bindquizes();

//show questions

BindQuestions(0);

divCertificate.Visible = false;

}

else

{

if (int.TryParse(quizfield.Value, out tempval) == true)

{

quizId = tempval;

}

//get the recent quizes

protected void Bindquizes()

{

SqlDataReader dReader;

SqlCommand cmd = new SqlCommand("usp_GetQuizDetails");

db dr = new db();

dReader = dr.DataReader(cmd, null);

if (!dReader.HasRows)

{

lblmessage.Visible = true;

lblmessage.Text = "There is no quiz available at this moment";

}

else

{

while (dReader.Read())

{

quizfield.Value = dReader["id"].ToString();

quizId = Convert.ToInt32(quizfield.Value);

quizname = dReader["name"].ToString();

description = dReader["description"].ToString();

start = Convert.ToDateTime(dReader["startdate"].ToString());

end = Convert.ToDateTime(dReader["enddate"].ToString());

terms = dReader["termsandconditions"].ToString();

}

//get questions

protected void BindQuestions(int SelectedPage)

{

DataTable dTable = new DataTable();

SqlCommand cmd = new SqlCommand("usp_GetQuizQuestions");

SqlParameter[] parrAy = new SqlParameter[1];

parrAy[0] = new SqlParameter("@quizid", quizId);

db getquestionslist = new db();

dTable = getquestionslist.DataTable(cmd, parrAy);

if (dTable.Rows.Count > 0)

{

PagedDataSource pgitems = new PagedDataSource();

DataView dv = new DataView(dTable);

pgitems.DataSource = dv;

pgitems.AllowPaging = true;

pgitems.PageSize = 1;

pgitems.CurrentPageIndex = PageNumber;

if (SelectedPage > (pgitems.PageCount - 1))

SelectedPage = pgitems.PageCount - 1;

if (SelectedPage < 0)

SelectedPage = 0;

pgitems.CurrentPageIndex = SelectedPage;

questionsrpt.DataSource = pgitems;

questionsrpt.DataBind();

if (pgitems.IsLastPage)

lbNext.Enabled = false;

else

lbNext.Enabled = true;

if (pgitems.IsFirstPage)

lbPrevious.Enabled = false;

else

lbPrevious.Enabled = true;

if ((dTable.Rows.Count- 1) == SelectedPage)

{

btnsubmit.Visible = true;

}

else

{

questionsdiv.InnerHtml = "There is no quiz available at this moment.";

}

public int PageNumber

{

get

{

if (ViewState["PageNumber"] != null)

return Convert.ToInt32(ViewState["PageNumber"]);

else

return 0;

}

set

{

ViewState["PageNumber"] = value;

}

protected void rptPaging_ItemCommand(object source, RepeaterCommandEventArgs e)

{

PageNumber = Convert.ToInt32(e.CommandArgument) - 1;

BindQuestions(0);

}

//quiz answers submitted

protected void btnsubmit_Click(object sender, EventArgs e)

{

SubmitQuiz();

}

protected void SubmitQuiz()

{

GetRightAnswer();

email = txtemail.Text.Trim();

name = txtname.Text.Trim();

DataTable accounts = new DataTable();

SqlCommand checkentrycmd = new SqlCommand("usp_ValidateQuiz");

SqlParameter[] parrAyv = new SqlParameter[3];

parrAyv[0] = new SqlParameter("@quizid", quizId);

parrAyv[1] = new SqlParameter("@email", email);

db checkentry = new db();

accounts = checkentry.DataTable(checkentrycmd, parrAyv);

if (accounts.Rows.Count > 0)

{

quizdetails.Visible = false;

detailsdiv.Visible = false;

questionsdiv.Visible = false;

lblmessage.Visible = true;

lblmessage.Text = "You have already taken this quiz!";

}

else

{

db insertentry = new db();

SqlCommand insertentrycmd = new SqlCommand("usp_AddQuizResponses");

SqlParameter[] parrAyc = new SqlParameter[6];

parrAyc[0] = new SqlParameter("@quizid", quizId);

parrAyc[1] = new SqlParameter("@email", email);

parrAyc[2] = new SqlParameter("@name", name);

parrAyc[3] = new SqlParameter("@correctanswers", Convert.ToInt32(ViewState["correctcount"]));

parrAyc[4] = new SqlParameter("@wronganswers", Convert.ToInt32(ViewState["wrongcount"]));

parrAyc[5] = new SqlParameter("@lastupdated", updatedate);

success = insertentry.ExecuteScalar(insertentrycmd, parrAyc);

if (success > 0)

{

dtAnswer = (DataTable)ViewState["dtAnswer"];

foreach (DataRow dr in dtAnswer.Rows)

{

SqlCommand insertresponsecmd = new SqlCommand("usp_AddQuestionResponses");

SqlParameter[] parrAy = new SqlParameter[4];

parrAy[0] = new SqlParameter("@responseid", success);

parrAy[1] = new SqlParameter("@questionid", dr[0].ToString());

parrAy[2] = new SqlParameter("@optionid", dr[1].ToString());

parrAy[3] = new SqlParameter("@lastupdated", updatedate);

db insertresponses = new db();

insertresponses.ExecuteQuery(insertresponsecmd, parrAy);

}

detailsdiv.Visible = false;

questionscount = Convert.ToInt32(ViewState["correctcount"]) + Convert.ToInt32(ViewState["wrongcount"]);

lblalert.Visible = true;

double percent = (Convert.ToDouble(Convert.ToInt32(ViewState["correctcount"])) / Convert.ToDouble(questionscount)) * 100;

detailsdiv.Visible = false; //Hide Questions Div

divCertificate.Visible = true; //Show certificate div

ltName.Text = txtname.Text;

ltSuccessFail.Text = percent > 60 ? "Pass" : "Fail";

}

else

{

lblalert.Visible = true;

lblalert.Text = "Sorry! we could not process your request. Please try again.";

}

protected void questionsrpt_ItemDataBound(object sender, RepeaterItemEventArgs e)

{

if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)

{

HiddenField hfl = (HiddenField)e.Item.FindControl("hfID");

qid = Convert.ToInt32(hfl.Value);

RadioButtonList rbl = (RadioButtonList)e.Item.FindControl("rbloptions");

DataTable qTable = new DataTable();

SqlCommand getoptions = new SqlCommand("usp_GetQuizOptions");

SqlParameter[] parrAy = new SqlParameter[1];

parrAy[0] = new SqlParameter("@questionid", qid);

db getoptionslist = new db();

qTable = getoptionslist.DataTable(getoptions, parrAy);

if (qTable.Rows.Count > 0)

{

rbl.DataSource = qTable;

rbl.DataTextField = "questionoption";

rbl.DataValueField = "id";

rbl.DataBind();

}

protected void lbPrevious_Click1(object sender, EventArgs e)

{

if (ViewState["SelectedPage"] != null)

{

ViewState["SelectedPage"] = Convert.ToInt32(ViewState["SelectedPage"]) - 1;

SelectedPage = Convert.ToInt32(ViewState["SelectedPage"]);

}

else

{

ViewState["SelectedPage"] = 1;

SelectedPage = Convert.ToInt32(ViewState["SelectedPage"]);

}

GetRightAnswer();

BindQuestions(SelectedPage);

}

protected void lbNext_Click1(object sender, EventArgs e)

{

if (ViewState["SelectedPage"] != null)

{

ViewState["SelectedPage"] = Convert.ToInt32(ViewState["SelectedPage"]) + 1;

SelectedPage = Convert.ToInt32(ViewState["SelectedPage"]);

}

else

{

ViewState["SelectedPage"] = 1;

SelectedPage = Convert.ToInt32(ViewState["SelectedPage"]);

}

GetRightAnswer();

BindQuestions(SelectedPage);

}

private void GetRightAnswer()

{

if (ViewState["dtAnswer"] != null)

{

dtAnswer = (DataTable)ViewState["dtAnswer"];

}

if (dtAnswer.Columns.Count == 0)

{

dtAnswer.Columns.Add("QuestionId", typeof(Int32));

dtAnswer.Columns.Add("Selectedanswer", typeof(Int32));

}

foreach (RepeaterItem item in questionsrpt.Items)

{

if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem)

{

var hfId = item.FindControl("hfID") as HiddenField;

questionId = Convert.ToInt32(hfId.Value);

//get the submitted answer

var rdbList = item.FindControl("rbloptions") as RadioButtonList;

selectedanswer = rdbList.SelectedValue;

rdbList.Enabled = true;

SqlCommand getanswercmd = new SqlCommand("usp_GetRightAnswer");

SqlParameter[] parrAy = new SqlParameter[1];

parrAy[0] = new SqlParameter("@questionid", questionId);

db getanswer = new db();

dReader = getanswer.DataReader(getanswercmd, parrAy);

if (!dReader.HasRows)

{

}

else

{

while (dReader.Read())

{

correctanswer = dReader["optionid"].ToString();

}

if (selectedanswer == correctanswer)

{

if (ViewState["correctcount"] != null)

{

ViewState["correctcount"] = Convert.ToInt32(ViewState["correctcount"]) + 1;

correctcount = Convert.ToInt32(ViewState["correctcount"]);

}

else

{

ViewState["correctcount"] = 1;

correctcount = Convert.ToInt32(ViewState["correctcount"]);

}

rdbList.SelectedItem.Attributes.Add("style", "color: #006400");

}

else

{

if (ViewState["wrongcount"] != null)

{

ViewState["wrongcount"] = Convert.ToInt32(ViewState["wrongcount"]) + 1;

wrongcount = Convert.ToInt32(ViewState["wrongcount"]);

}

else

{

ViewState["wrongcount"] = 1;

wrongcount = Convert.ToInt32(ViewState["wrongcount"]);

}

myDataRow = dtAnswer.NewRow();

myDataRow["QuestionId"] = questionId;

selectedanswer = selectedanswer == "" ? "0" : selectedanswer;

myDataRow["Selectedanswer"] = Convert.ToInt32(selectedanswer);

dtAnswer.Rows.Add(myDataRow);

ViewState["dtAnswer"] = dtAnswer;

}

protected void Timer_Tick(object sender, EventArgs e)

{

if (ViewState["timerCntr"] != null)

{

ViewState["timerCntr"] = Convert.ToInt32(ViewState["timerCntr"]) + 1;

timerCntr = Convert.ToInt32(ViewState["timerCntr"]);

}

else

{

ViewState["timerCntr"] = 1;

timerCntr = Convert.ToInt32(ViewState["timerCntr"]);

}

if (timerCntr == 50) // change the time as per your wish with calculation of thousand mili seconds

{

lblTimer.Text = "Your time is up.";

timer.Enabled = false;

SubmitQuiz();

btnStart.Enabled = false;

}

lblTimer.Text = Convert.ToString(timerCntr);

}

protected void startTimer(object sender, EventArgs e)

{

timer.Enabled = true;

questionsdiv.Visible = true;

}

SQL Helper (db.cs) file

using System;

using System.Collections.Generic;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

public class db

{

private SqlConnection conn;

private SqlDataReader dr;

private DataSet ds = new DataSet();

private DataTable dt = new DataTable();

string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

public db()

{

conn = new SqlConnection(connString);

}

public SqlConnection Openconn()

{

if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)

{

conn.Open();

}

return conn;

}

//close the db connection

public SqlConnection Closeconn()

{

if (conn.State == ConnectionState.Open)

{

conn.Close();

}

return conn;

}

public void ExecuteQuery(SqlCommand cmd, SqlParameter[] pArray)

{

try

{

cmd.Connection = Openconn();

cmd.CommandType = CommandType.StoredProcedure;

if (pArray != null)

{

cmd.Parameters.AddRange(pArray);

}

cmd.ExecuteNonQuery();

}

catch (Exception Ex)

{

Ex.Message.ToString();

}

finally

{

cmd.Connection = Closeconn();

}

public int ExecuteScalar(SqlCommand cmd, SqlParameter[] pArray)

{

int insertid = 0;

try

{

cmd.Connection = Openconn();

cmd.CommandType = CommandType.StoredProcedure;

if (pArray != null)

{

cmd.Parameters.AddRange(pArray);

}

insertid = Convert.ToInt32(cmd.ExecuteScalar());

}

catch (Exception Ex)

{

Ex.Message.ToString();

}

finally

{

cmd.Connection = Closeconn();

}

return insertid;

}

public DataSet DataSet(SqlCommand DScmd)

{

SqlDataAdapter DSadapater;

try

{

ds.Clear();

DScmd.Connection = Openconn();

DSadapater = new SqlDataAdapter(DScmd);

DSadapater.Fill(ds);

}

catch (Exception Ex)

{

Ex.Message.ToString();

}

finally

{

DScmd.Connection = Closeconn();

}

return ds;

}

public DataTable DataTable(SqlCommand cmd, SqlParameter [] pArray)

{

SqlDataAdapter DTadapter;

try

{

dt.Clear();

cmd.Connection = Openconn();

cmd.CommandType = CommandType.StoredProcedure;

if (pArray != null)

{

cmd.Parameters.AddRange(pArray);

}

DTadapter = new SqlDataAdapter(cmd);

DTadapter.Fill(dt);

}

catch (Exception Ex)

{

Ex.Message.ToString();

}

finally

{

cmd.Connection = Closeconn();

}

return dt;

}

public SqlDataReader DataReader(SqlCommand cmd, SqlParameter[] pArray)

{

try

{

cmd.Connection = Openconn();

cmd.CommandType = CommandType.StoredProcedure;

if (pArray != null)

{

cmd.Parameters.AddRange(pArray);

}

dr = cmd.ExecuteReader();

return dr;

}

catch (Exception Ex)

{

throw Ex;

}

finally

{

cmd.Connection = null;

}

.SQL Scripts

USE [Onlinequiz]

/****** Object: Table [dbo].[question_answer] Script Date: 29-Jul-20 10:07:29 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[question_answer](

[id] [int] IDENTITY(1,1) NOT NULL,

[questionid] [int] NOT NULL,

[optionid] [int] NOT NULL,

[lastupdated] [datetime] NULL,

CONSTRAINT [PK_question_answer] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

/****** Object: Table [dbo].[question_options] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[question_options](

[id] [int] IDENTITY(1,1) NOT NULL,

[questionid] [int] NOT NULL,

[questionoption] [nvarchar](500) NULL,

[lastupdated] [datetime] NULL,

CONSTRAINT [PK_question_options] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

/****** Object: Table [dbo].[question_responses] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[question_responses](

[id] [int] IDENTITY(1,1) NOT NULL,

[responseid] [int] NULL,

[questionid] [int] NULL,

[optionid] [int] NULL,

[lastupdated] [datetime] NULL,

CONSTRAINT [PK_question_responses] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

/****** Object: Table [dbo].[quiz_questions] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[quiz_questions](

[id] [int] IDENTITY(1,1) NOT NULL,

[quizid] [int] NOT NULL,

[questionorder] [int] NULL,

[type] [nvarchar](50) NULL,

[title] [nvarchar](2000) NULL,

[lastupdated] [datetime] NOT NULL,

CONSTRAINT [PK_quizquestions] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

/****** Object: Table [dbo].[quiz_responses] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[quiz_responses](

[id] [int] IDENTITY(1,1) NOT NULL,

[quizid] [int] NULL,

[email] [nvarchar](250) NULL,

[name] [nvarchar](500) NULL,

[correctanswers] [int] NULL,

[wronganswers] [int] NULL,

[lastupdated] [datetime] NULL,

CONSTRAINT [PK_quizresponses] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

/****** Object: Table [dbo].[quizdetails] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[quizdetails](

[id] [int] IDENTITY(1,1) NOT NULL,

[name] [nvarchar](1000) NULL,

[description] [nvarchar](4000) NULL,

[completiondescription] [nvarchar](4000) NULL,

[startdate] [datetime] NULL,

[enddate] [datetime] NULL,

[termsandconditions] [nvarchar](max) NULL,

[lastupdated] [datetime] NOT NULL,

[Active] [bit] NULL,

CONSTRAINT [PK_quizdetails] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[question_answer] ON

INSERT [dbo].[question_answer] ([id], [questionid], [optionid], [lastupdated]) VALUES (11, 26, 64, CAST(N'2013-08-20T11:28:50.610' AS DateTime))

INSERT [dbo].[question_answer] ([id], [questionid], [optionid], [lastupdated]) VALUES (12, 27, 70, CAST(N'2013-08-20T10:40:32.303' AS DateTime))

INSERT [dbo].[question_answer] ([id], [questionid], [optionid], [lastupdated]) VALUES (13, 28, 72, CAST(N'2013-08-20T10:37:34.267' AS DateTime))

INSERT [dbo].[question_answer] ([id], [questionid], [optionid], [lastupdated]) VALUES (14, 29, 77, CAST(N'2013-08-20T10:40:05.917' AS DateTime))

INSERT [dbo].[question_answer] ([id], [questionid], [optionid], [lastupdated]) VALUES (15, 30, 83, CAST(N'2013-08-20T10:41:34.470' AS DateTime))

SET IDENTITY_INSERT [dbo].[question_answer] OFF

SET IDENTITY_INSERT [dbo].[question_options] ON

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (64, 26, N'Sri Ratan Tata', CAST(N'2013-08-20T10:26:12.823' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (65, 26, N'Sri Dhirubhbai Ambani', CAST(N'2013-08-20T10:26:12.823' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (66, 26, N'Sri Ramdev Baba', CAST(N'2013-08-20T10:31:33.033' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (67, 26, N'Sri Sunder Pichai', CAST(N'2013-08-20T10:26:12.823' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (68, 27, N'Sri L.K. Advani', CAST(N'2013-08-20T10:36:08.763' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (69, 27, N'Sri Abdul Kalam', CAST(N'2013-08-20T10:36:08.763' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (70, 27, N'Sri Ramnath Kovid', CAST(N'2013-08-20T10:36:08.763' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (71, 27, N'Sri. M.M. Joshi', CAST(N'2013-08-20T10:36:08.763' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (72, 28, N'Uttar Pradesh', CAST(N'2013-08-20T10:37:20.257' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (73, 28, N'Bihar', CAST(N'2013-08-20T10:37:20.257' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (74, 28, N'Maharashtra', CAST(N'2013-08-20T10:37:20.257' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (75, 28, N'Goa', CAST(N'2013-08-20T10:37:20.257' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (76, 29, N'Actor', CAST(N'2013-08-20T10:39:53.047' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (77, 29, N'Cricketer', CAST(N'2013-08-20T10:39:53.047' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (78, 29, N'Businessman', CAST(N'2013-08-20T10:39:53.047' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (79, 29, N'Doctor', CAST(N'2013-08-20T10:39:53.047' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (80, 30, N'Gelly Ford', CAST(N'2013-08-20T10:41:31.990' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (81, 30, N'Ford Anger', CAST(N'2013-08-20T10:41:31.990' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (82, 30, N'Leryn Ford', CAST(N'2013-08-20T10:41:31.990' AS DateTime))

INSERT [dbo].[question_options] ([id], [questionid], [questionoption], [lastupdated]) VALUES (83, 30, N'Henery Ford', CAST(N'2013-08-20T10:41:31.990' AS DateTime))

SET IDENTITY_INSERT [dbo].[question_options] OFF

SET IDENTITY_INSERT [dbo].[question_responses] ON

INSERT [dbo].[question_responses] ([id], [responseid], [questionid], [optionid], [lastupdated]) VALUES (1, 25, 26, 64, CAST(N'2020-07-29T21:42:18.593' AS DateTime))

INSERT [dbo].[question_responses] ([id], [responseid], [questionid], [optionid], [lastupdated]) VALUES (2, 25, 27, 70, CAST(N'2020-07-29T21:42:18.593' AS DateTime))

INSERT [dbo].[question_responses] ([id], [responseid], [questionid], [optionid], [lastupdated]) VALUES (3, 25, 28, 74, CAST(N'2020-07-29T21:42:18.593' AS DateTime))

INSERT [dbo].[question_responses] ([id], [responseid], [questionid], [optionid], [lastupdated]) VALUES (4, 25, 29, 77, CAST(N'2020-07-29T21:42:18.593' AS DateTime))

INSERT [dbo].[question_responses] ([id], [responseid], [questionid], [optionid], [lastupdated]) VALUES (5, 25, 30, 83, CAST(N'2020-07-29T21:42:18.593' AS DateTime))

INSERT [dbo].[question_responses] ([id], [responseid], [questionid], [optionid], [lastupdated]) VALUES (6, 26, 26, 0, CAST(N'2020-07-29T21:50:11.317' AS DateTime))

INSERT [dbo].[question_responses] ([id], [responseid], [questionid], [optionid], [lastupdated]) VALUES (7, 27, 26, 0, CAST(N'2020-07-29T21:50:36.367' AS DateTime))

SET IDENTITY_INSERT [dbo].[question_responses] OFF

SET IDENTITY_INSERT [dbo].[quiz_questions] ON

INSERT [dbo].[quiz_questions] ([id], [quizid], [questionorder], [type], [title], [lastupdated]) VALUES (26, 1, 10, N'single', N'Who is the owner of Tata Group of Companies?', CAST(N'2013-08-20T11:28:50.610' AS DateTime))

INSERT [dbo].[quiz_questions] ([id], [quizid], [questionorder], [type], [title], [lastupdated]) VALUES (27, 1, 11, N'single', N'Who is the current president of India?', CAST(N'2013-08-20T10:40:32.303' AS DateTime))

INSERT [dbo].[quiz_questions] ([id], [quizid], [questionorder], [type], [title], [lastupdated]) VALUES (28, 1, 12, N'single', N'Where is Kanpur located?', CAST(N'2013-08-20T10:37:34.267' AS DateTime))

INSERT [dbo].[quiz_questions] ([id], [quizid], [questionorder], [type], [title], [lastupdated]) VALUES (29, 1, 13, N'single', N'Who is Sachin Tendulkar?', CAST(N'2013-08-20T10:40:05.917' AS DateTime))

INSERT [dbo].[quiz_questions] ([id], [quizid], [questionorder], [type], [title], [lastupdated]) VALUES (30, 1, 14, N'single', N'Who is the owner of Ford motor?', CAST(N'2013-08-20T10:41:31.990' AS DateTime))

SET IDENTITY_INSERT [dbo].[quiz_questions] OFF

SET IDENTITY_INSERT [dbo].[quiz_responses] ON

INSERT [dbo].[quiz_responses] ([id], [quizid], [email], [name], [correctanswers], [wronganswers], [lastupdated]) VALUES (27, 1, N'test@yahoo.com', N'hare', 0, 1, CAST(N'2020-07-22T21:50:36.367' AS DateTime))

SET IDENTITY_INSERT [dbo].[quiz_responses] OFF

SET IDENTITY_INSERT [dbo].[quizdetails] ON

INSERT [dbo].[quizdetails] ([id], [name], [description], [completiondescription], [startdate], [enddate], [termsandconditions], [lastupdated], [Active]) VALUES (1, N'Online Quiz', N'As a part of the this event we are offering an online quiz. To participate, answer all the questions. You will receive certificate based on your score.', N'Thanks for taking the quiz!!', CAST(N'2020-07-26T14:50:02.233' AS DateTime), CAST(N'2020-07-26T14:50:02.233' AS DateTime), N'Copyright allright reserverd, VedPathak.blogspot.com', CAST(N'2013-08-20T11:29:07.007' AS DateTime), 1)

SET IDENTITY_INSERT [dbo].[quizdetails] OFF

ALTER TABLE [dbo].[question_answer] WITH CHECK ADD CONSTRAINT [FK_question_answer_quizquestions] FOREIGN KEY([questionid])

REFERENCES [dbo].[quiz_questions] ([id])

ON UPDATE CASCADE

ON DELETE CASCADE

ALTER TABLE [dbo].[question_answer] CHECK CONSTRAINT [FK_question_answer_quizquestions]

ALTER TABLE [dbo].[question_options] WITH CHECK ADD CONSTRAINT [FK_question_options_quizquestions] FOREIGN KEY([questionid])

REFERENCES [dbo].[quiz_questions] ([id])

ON UPDATE CASCADE

ON DELETE CASCADE

ALTER TABLE [dbo].[question_options] CHECK CONSTRAINT [FK_question_options_quizquestions]

ALTER TABLE [dbo].[quiz_questions] WITH CHECK ADD CONSTRAINT [FK_quizquestions_quizdetails] FOREIGN KEY([quizid])

REFERENCES [dbo].[quizdetails] ([id])

ON UPDATE CASCADE

ON DELETE CASCADE

ALTER TABLE [dbo].[quiz_questions] CHECK CONSTRAINT [FK_quizquestions_quizdetails]

/****** Object: StoredProcedure [dbo].[usp_AddQuestionResponses] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[usp_AddQuestionResponses]

@responseid int,

@questionid int,

@optionid int,

@lastupdated datetime

BEGIN

INSERT INTO question_responses (responseid, questionid, optionid, lastupdated) values (@responseid, @questionid, @optionid, @lastupdated)

END

/****** Object: StoredProcedure [dbo].[usp_AddQuizResponses] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[usp_AddQuizResponses]

@quizid int,

@email nvarchar(70),

@name nvarchar(90),

@correctanswers int,

@wronganswers int,

@lastupdated datetime

BEGIN

INSERT INTO quiz_responses (quizid, email, name, correctanswers, wronganswers, lastupdated) values (@quizid, @email, @name, @correctanswers, @wronganswers, @lastupdated);SELECT CAST(scope_identity() AS int)

END

/****** Object: StoredProcedure [dbo].[usp_GetQuizDetails] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

--Quiz Details

CREATE PROCEDURE [dbo].[usp_GetQuizDetails]

BEGIN

select id, name, description, startdate, enddate, termsandconditions, active from quizdetails WHERE Active=1

END

/****** Object: StoredProcedure [dbo].[usp_GetQuizOptions] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[usp_GetQuizOptions]

@questionid as int

BEGIN

select id, questionoption from question_options where questionid=@questionid

END

/****** Object: StoredProcedure [dbo].[usp_GetQuizQuestions] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[usp_GetQuizQuestions]

@quizid as int

BEGIN

select id, title from quiz_questions where quizid=@quizid order by questionorder ASC

END

/****** Object: StoredProcedure [dbo].[usp_GetRightAnswer] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[usp_GetRightAnswer]

@questionid int

BEGIN

select optionid from question_answer where questionid=@questionid

END

/****** Object: StoredProcedure [dbo].[usp_ValidateQuiz] Script Date: 29-Jul-20 10:07:30 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[usp_ValidateQuiz]

@quizid int,

@email nvarchar(70)

BEGIN

select * from quiz_responses where quizid=@quizid and email=@email

END

Hope you enjoy this code. Feel free to share your feedback/comments.

The Ved Blog

Search This Blog

Simple Online Quiz with timer using ASP.Net and SQL Server

Comments