Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

jQuery sortable from database

Suggested Videos
Part 97 - jQuery selectable widget
Part 98 - jQuery selectable filter
Part 99 - jQuery sortable widget



In this video we will discuss a real world example of where we would use jQuery sortable widget.



jQuery sortable from database

1. The Question text, question options and answer options should be from the database
2. The user should be able to reorder the capital cities to match with the respective country
3. After the reordering is done, click "Check Answer" button. If the capital cities are ordered correctly the background color should change to green else red.

Step 1 : Create the required SQL Server tables and stored procedures
Create table tblQuestions
(
    Id int primary key,
    QuestionText nvarchar(255)
)
Go

Insert into tblQuestions values
(1, 'Reorder to match the capital city with their respective country')
Go

Create table tblQuestionOptions
(
    Id int primary key,
    OptionText nvarchar(100),
    QuestionId int foreign key references tblQuestions(Id)
)
Go

Insert into tblQuestionOptions values (1, 'USA', 1)
Insert into tblQuestionOptions values (2, 'India', 1)
Insert into tblQuestionOptions values (3, 'UK', 1)
Insert into tblQuestionOptions values (4, 'Australia', 1)
Go

Create table tblAnswerOptions
(
    Id int primary key,
    OptionText nvarchar(100),
    QuestionId int foreign key references tblQuestions(Id)
)
Go

Insert into tblAnswerOptions values (1, 'London', 1)
Insert into tblAnswerOptions values (2, 'Canberra', 1)
Insert into tblAnswerOptions values (3, 'Washington D.C.', 1)
Insert into tblAnswerOptions values (4, 'New Delhi', 1)
Go

Create table tblAnswers
(
    Id int primary key,
    QuestionId int foreign key references tblQuestions(Id),
    Answer nvarchar(100)
)
Go

Insert into tblAnswers values (1, 1, '3,4,1,2')
Go

Create proc GetQuestionDataById
@QuestionId int
as
Begin
    Select * from tblQuestions where Id = @QuestionId
    Select * from tblQuestionOptions where QuestionId = @QuestionId
    Select * from tblAnswerOptions where QuestionId = @QuestionId
End
Go

Create proc GetAnswerByQuestionId
@QuestionId int
as
Begin
    Select * from tblAnswers where QuestionId = @QuestionId
End
GO

 Step 2 : Create new asp.net web application project. Name it Demo. 

Step 3 : Include a connection string in the web.config file to your database.
<add name="DBCS"
      connectionString="server=.;database=SampleDB;integrated security=SSPI"/>

 Step 4 : Add a class file to the project. Name it Answer.cs. Copy and paste the following code.
namespace Demo
{
    public class Answer
    {
        public int Id { get; set; }
        public int QuestionId { get; set; }
        public string AnswerText { get; set; }
    }
}

 Step 5 : Add a class file to the project. Name it Option.cs. Copy and paste the following code.
namespace Demo
{
    public class Option
    {
        public int Id { get; set; }
        public string OptionText { get; set; }
        public int QuestionId { get; set; }
    }
}

 Step 6 : Add a class file to the project. Name it QuestionPageData.cs. Copy and paste the following code.
using System.Collections.Generic;

namespace Demo
{
    public class QuestionPageData
    {
        public int QuestionId { get; set; }
        public string QuestionText { get; set; }
        public List<Option> QuestionOptions { get; set; }
        public List<Option> AnswerOptions { get; set; }
    }
}

 Step 7 : Add a new WebService (ASMX). Name it QuestionService.asmx. Copy and paste the following code.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;

namespace Demo
{
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    [System.Web.Script.Services.ScriptService]
    public class QuestionService : System.Web.Services.WebService
    {
        [WebMethod]
        public void GetQuestionData()
        {
            string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            QuestionPageData questionPageData = new QuestionPageData
            {
                QuestionOptions = new List<Option>(),
                AnswerOptions = new List<Option>()
            };
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("GetQuestionDataById", con);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;

                SqlParameter paramQuestionId = new SqlParameter()
                {
                    ParameterName = "@QuestionId",
                    Value = 1
                };
                da.SelectCommand.Parameters.Add(paramQuestionId);

                DataSet ds = new DataSet();
                da.Fill(ds);

                questionPageData.QuestionId = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                questionPageData.QuestionText = ds.Tables[0].Rows[0]["QuestionText"].ToString();

                foreach (DataRow dr in ds.Tables[1].Rows)
                {
                    Option questionOption = new Option();
                    questionOption.Id = Convert.ToInt32(dr["Id"]);
                    questionOption.OptionText = dr["OptionText"].ToString();
                    questionOption.QuestionId = Convert.ToInt32(dr["QuestionId"]);
                    questionPageData.QuestionOptions.Add(questionOption);
                }

                foreach (DataRow dr in ds.Tables[2].Rows)
                {
                    Option answerOption = new Option();
                    answerOption.Id = Convert.ToInt32(dr["Id"]);
                    answerOption.OptionText = dr["OptionText"].ToString();
                    answerOption.QuestionId = Convert.ToInt32(dr["QuestionId"]);
                    questionPageData.AnswerOptions.Add(answerOption);
                }
            }

            JavaScriptSerializer js = new JavaScriptSerializer();
            Context.Response.Write(js.Serialize(questionPageData));
        }

        [WebMethod]
        public void GetAnswer()
        {
            string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            Answer answer = new Answer();
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlDataAdapter da = new SqlDataAdapter("GetAnswerByQuestionId", con);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;

                SqlParameter paramQuestionId = new SqlParameter()
                {
                    ParameterName = "@QuestionId",
                    Value = 1
                };
                da.SelectCommand.Parameters.Add(paramQuestionId);

                DataSet ds = new DataSet();
                da.Fill(ds);

                answer.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                answer.AnswerText = ds.Tables[0].Rows[0]["Answer"].ToString();
                answer.QuestionId = Convert.ToInt32(ds.Tables[0].Rows[0]["QuestionId"]);
            }

            JavaScriptSerializer js = new JavaScriptSerializer();
            Context.Response.Write(js.Serialize(answer));
        }
    }
}

 Step 8 : Add a new WebForm to the ASP.NET project. Copy and paste the following HTML and jQuery code
<%@ Page Language="C#" AutoEventWireup="true"
    CodeBehind="WebForm1.aspx.cs" Inherits="Demo.WebForm1" %>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="jquery-1.11.2.js"></script>
    <script src="jquery-ui.js"></script>
    <link href="jquery-ui.css" rel="stylesheet" />
    <script type="text/javascript">
        $(document).ready(function () {
            $.ajax({
                url: 'QuestionService.asmx/GetQuestionData',
                dataType: "json",
                method: 'post',
                success: function (data) {
                    $('#question').text(data.QuestionText)
                    $(data.QuestionOptions).each(function () {
                        $('#questionOptions')
                            .append('<li id=' + this.Id + '>' + this.OptionText + '</li>');
                    });

                    $(data.AnswerOptions).each(function () {
                        $('#answerOptions').append('<li id=' + this.Id + '>' + this.OptionText + '</li>');
                    });

                    $('#answerOptions').sortable({
                        placeholder: 'placeholder',
                        axis: 'y',
                        start: function () {
                            $('#answerOptions li').removeClass('wrongAnswer correctAnswer');
                        }
                    });
                },
                error: function (err) {
                    alert(err.statusText);
                }
            });

            $('#btnCheck').click(function () {

                $.ajax({
                    url: 'QuestionService.asmx/GetAnswer',
                    dataType: "json",
                    method: 'post',
                    success: function (data) {
                        var userAnswer = '';
                        $('#answerOptions li').each(function () {
                            userAnswer += $(this).attr('Id') + ',';
                        });

                        userAnswer = userAnswer.substr(0, userAnswer.lastIndexOf(','));

                        if (userAnswer == data.AnswerText) {
                            $('#answerOptions li')
                                .removeClass('wrongAnswer').addClass('correctAnswer');
                        }
                        else {
                            $('#answerOptions li')
                                .removeClass('correctAnswer').addClass('wrongAnswer');
                        }
                    },
                    error: function (err) {
                        alert(err.statusText);
                    }
                });

            });
        });
    </script>
    <style>
        .ui-sortable-handle {
            background-color: grey;
        }

        ul {
            float: left;
        }

        li {
            border: 1px solid black;
            padding: 10px;
            height: 20px;
            cursor: pointer;
            width: 150px;
            margin: 3px;
            color: black;
            list-style-type: none;
        }

        .placeholder {
            border: 1px solid black;
            padding: 10px;
            height: 20px;
            width: 150px;
            margin: 3px;
            color: black;
            background-color: silver;
        }

        .correctAnswer {
            background-color: green;
            color: white;
        }

        .wrongAnswer {
            background-color: red;
            color: white;
        }
    </style>
</head>
<body style="font-family: Arial">
    <form id="form1" runat="server">
        <div id="question"></div>

        <ul id="questionOptions">
        </ul>

        <ul id="answerOptions">
        </ul>
        <br />
        <input id="btnCheck" type="button" value="Check Answer"
               style="clear: both; float: left;" />
    </form>
</body>
</html>

jQuery tutorial for beginners

No comments:

Post a Comment

It would be great if you can help share these free resources

Subscribe to: Post Comments (Atom)