Online Examination in ASP.NET

Posted on 2012-09-02
Last Modified: 2012-09-07
Hello Experts,
I am going to develop an online examination system. But I am confuse, what approach should I follow. Because the server load will be too much heavy. May be around 10,000-20,000 users. So how can I handle these huge amount of connections to the Database.
My requirement is Questions should be random, so if I am giving one panel for switching question, how can I track the previous questions and answers to store in DB and after finished exam display result immediately..
Question by:Manoj Patil
    LVL 14

    Expert Comment

    Not sure how many questions each exam will have. You could always run more than one database server. If you're sure the person will take the examination in one session, you could get all of the questions at once and use session variables.

    For example, have a Question class that has the following properties:

    Create an array or linked list of your questions and store that array or linked list in a session variable. You can then move between questions and there is only one hit to the database to get the question and one hit when you save their results to the database.
    LVL 26

    Expert Comment

    by:Alan Warren
    Hi techChallenger1,

    You should probably have look at how other reputable certification authorities administer online testing.

    Brain Bench Visual Basic 2008 - Free

    PRACTICE EXAMS - Microsoft

    You would need to consider policies, for example:
    In the event of lost connection during the testing process, does the user re-take the test, or will your system enable to user to pick up where they left off, in the event of lost connection. If they are to re-take the test, do you have a different set of questions?

    Possibly need a dedicated server to handle the expected demand on resources.

    Success with your project, it sounds both interesting and challenging!

    LVL 16

    Expert Comment

    by:Kalpesh Chhatrala
    LVL 19

    Author Comment

    by:Manoj Patil
    Hello Experts,
    I am getting more confused with your answers.
    My requirement is -
    Display Random questions, (Lets say 40 Questions) for every user.
    Display Remaining Time to user.
    After Timeout forcefully end the Exam.
    Give some panel to show question numbers from 1 - 40.  So that user can directly jump to questions.
    Allow user to resubmit the answer for previous question.

    Just give me the ans for my following questions.
    1. How Can I keep track of random questions generated by system, Because we are giving panel of questions numbers to go back for change of answer.

    2. What if system generate same question 2 times in same exam ??

    3. How to manage correct answers with answers given by user, Because we need to display result after finish the exam. ??
    LVL 26

    Accepted Solution

    Hi techChallenger1,

    I have no idea regarding your db structure(schema), but the following logic flow may be of some use.
    Create a table for questions:
    CREATE TABLE [dbo].[tblQuestions](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Question] [varchar](50) NULL,
    	[Answer] [varchar](50) NULL,
    	[ID] ASC
    ) ON [PRIMARY]

    Open in new window

    Create a table for tracking:

    CREATE TABLE [dbo].[tblQuestion_Tracking](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[QuestionID] [int] NULL,
    	[Correct_Answer] [varchar](50) NULL,
    	[Users_Answer] [varchar](50) NULL,
    	[ID] ASC
    ) ON [PRIMARY]

    Open in new window

    Populate tblQuestions with some data:
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(1, 'Q 1Text goes here', 'b')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(2, 'Q 2Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(3, 'Q 3Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(4, 'Q 4Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(5, 'Q 5Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(6, 'Q 6Text goes here', 'd')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(7, 'Q 7Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(8, 'Q 8Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(9, 'Q 9Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(10, 'Q 10Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(11, 'Q 11Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(12, 'Q 12Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(13, 'Q 13Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(14, 'Q 14Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(15, 'Q 15Text goes here', 'd')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(16, 'Q 16Text goes here', 'd')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(17, 'Q 17Text goes here', 'd')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(18, 'Q 18Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(19, 'Q 19Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(20, 'Q 20Text goes here', 'd')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(21, 'Q 21Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(22, 'Q 22Text goes here', 'b')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(23, 'Q 23Text goes here', 'b')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(24, 'Q 24Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(25, 'Q 25Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(26, 'Q 26Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(27, 'Q 27Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(28, 'Q 28Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(29, 'Q 29Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(30, 'Q 30Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(31, 'Q 31Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(32, 'Q 32Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(33, 'Q 33Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(34, 'Q 34Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(35, 'Q 35Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(36, 'Q 36Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(37, 'Q 37Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(38, 'Q 38Text goes here', 'c')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(39, 'Q 39Text goes here', 'a')
    INSERT INTO [dbo].[tblQuestions] ([ID],[Question],[Answer]) VALUES(40, 'Q 40Text goes here', 'a')

    Open in new window

    Select random questions from tblQuestions and insert them into tblQuestions_Tracking:

    declare @QuestionID int = null
    declare @Correct_Answer varchar(50) = null
    declare @NewID uniqueidentifier = null
    -- get random questions one at a time from a table of questions
    -- and append the questionID and expected answer to the tracking table
    -- might want to include a userID in this transaction, 
    -- so you can filter out questions that have been previously offered up to that user.
     @QuestionID = t.[ID]
    ,@Correct_Answer = t.[Answer]
    from	(
    			SELECT top (1) 
    				 NewID() AS MagicNumber
    			FROM [sessiontest].[dbo].[tblQuestions] 
    			ORDER BY MagicNumber DESC
    		) t
    -- select @QuestionID, @Correct_Answer
    INSERT INTO [dbo].[tblQuestion_Tracking]

    Open in new window

    Use your tracking table to deliver questions to the user and record the users answer.

    You may want to include a few other fields like SessionID and UserID in your tracking.

    LVL 19

    Author Comment

    by:Manoj Patil
    Hey alanwarren,
    Really Thanks for your efforts.
    But if user click on previous button, then how can I retrieve the same question he answered.
    Since, we are fetching the random questions....?
    LVL 26

    Expert Comment

    by:Alan Warren
    Hi techChallenger1,

    We are not fetching random questions for the user, we are populating the tracking table with random questions, which we are delivering up to the user sequentially. If the user clicks "Back", we deliver the previous question offered to the same user from the tracking table.

    select top 1 * from [dbo].[tblQuestion_Tracking] where userid=@UserId AND SessionID=@SessionID and ID < @ID
    order by ID desc

    -- @ID is the ID of the current question from the tracking table.

    LVL 19

    Author Closing Comment

    by:Manoj Patil
    Hey thanks for clarification...

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now