Cascading DropdownList with MVC5, Ajax, C# and MSSQL Server

Question

I'm quite new to MVC coming from windows forms and 3 tier architecture.

I'm trying to figure out using cascading dropdownlists (DDL) populated from database. Im using MS SQL Server 2012, VS 2013

Currently I'm work on user questionnaire which users can select from multiple answers from DDL. Depend on some selections I need to change answers (again DDL) on next question.

Database:

Table DDLStacks:

StackId | StackName
   1    | Berry
   2    | BerryColor
   3    ....

Table DDLStackContents (SCId Stack content id, indexing purpose)

SCId | StackId | GroupId | Key | Value
--------------------------------------
1 | 1 | Null | 1 | Grape
2 | 1 | Null | 2 | Avocado
3 | 1 | Null | 3 | Banana
4 | 2 | Null | 1 | Yellow
5 | 2 | Null | 2 | Green
6 | 2 | 1 | 3 | Red
7 | 2 | 1 | 4 | Orange
8...

Procedure:

   CREATE PROCEDURE [dbo].[spDLLSelect]
        @p_StackName_in VARCHAR(20),
        @p_GroupId_in Int = null
    AS 
    BEGIN
        SELECT [Key],Value FROM DDLStackContents
        WHERE StackID IN (SELECT StackId FROM DDLStacks WHERE StackName = @p_StackName_in)
        AND (GroupId = @p_GroupId_in OR @p_GroupId_in IS null) 
        Order By [Key] 
    END

As you can see DDLStacks hold questions, DDLStackContents holds possible answers for that question.

If there is a group we can select just the answers from that group otherwise all the answers for particular stack.

Now then I have created an ADO.NET Entity Data model to access spDLLSelect.

Now my fruit model is this

public class FruitModel
{
    public IEnumerable<SelectListItem> BerryList { get; set; }
    public IEnumerable<SelectListItem> BerryColorList { get; set; }

    [Display(Name = "Berry")]
    public byte? Berry { get; set; }

    [Display(Name = "BerryColor")]
    public byte? BerryColor { get; set; }
}

My controller is this, I need to select color depend on type of berry. Say if Avacado select all, if Banana just group 1.

public class HomeController : Controller
{
    public ActionResult Index()
    {
        CherryEntities db = new CherryEntities();
        var model = new FruitModel();
        model.BerryList = new SelectList(db.spDLLSelect("Berry", null), "Key", "Value");
        //model.BerryColorList = new SelectList(db.spDLLSelect("BerryColor", null), "Key", "Value");
        //model.BerryColorList = new SelectList(db.spDLLSelect("BerryColor", 1), "Key", "Value");
        return View(model);
    }
}

Here's my view:

@using (Html.BeginForm("Register2", "Account", FormMethod.Post, new { @class = "form-horizontal", role = "form" }))
{

<div class="form-group">
    @Html.LabelFor(m => m.Berry, new { @class = "col-md-2 control-label" })
    <div class="col-md-10" >
        @Html.DropDownListFor(m => m.Berry, Model.BerryList, "Please Select")
    </div>
</div>

<div class="form-group">
    @Html.LabelFor(m => m.BerryColor, new { @class = "col-md-2 control-label" })
    <div class="col-md-10">
        @Html.DropDownListFor(m => m.BerryColor, Model.BerryColorList, "Please Select")
    </div>
</div>

}

This is my basic coding, I have tried various methods to make this working, I'd like to see proper way of doing this with ajax STRONGLY Typed code.

May be using partial views? any thoughts?

SQL is just the Structured Query Language - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what database system (and which version) you're using - whether that's Oracle, DB2, Informix, MySQL, PostgreSQL, SQL Server or whatever else you might be using (please update tags accordingly).... — marc_s
– marc_s, Commented Jul 29, 2014 at 6:26

SBirthare · Accepted Answer · 2014-07-29 06:44:07Z

2

You have already got a Model that your view is strongly typed with. All you need to do is, add a change event on dropdown list (for more information see referred link below). On change event, you can load the values as per selected value e.g. if Berry is selected you will need to fetch corresponding values for Berry i.e Grape, Avocado.

You can load values using JavaScript which is useful when you have large set of data. Or you can preload the view with all the data and in that case, you just need to filter the answers based on question selected on UI.

For help on how to achieve this in practice please refer Cascading DropDownList In MVC 4. You can find similar example based on your needs.

answered Jul 29, 2014 at 6:44

SBirthare

5,1475 gold badges36 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cascading DropdownList with MVC5, Ajax, C# and MSSQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related