Sunday, 10 December 2017

Grid.MVC with MultiSelect DropDownList with CheckBox auto selected from database in MVC

In this tutorial, I am going to explain you bind how to bind multiple selection (MultiSelect) DropDownList with CheckBox in Grid.MVC in ASP.Net MVC from database using jQuery, Bootstrap and MultiSelect JS. I will be using Visual Studio 2013 and ADO.Net code to get data from SQL-Server Database.

As you can see in below screen-shot, multi-select dropdownlist is auto-selected. Here in Subject column , subject value is concatenated with comma from database, while binding to MVCGrid subject is auto selected in last column as shown below.

Grid.MVC with MultiSelect DropDownList with CheckBox auto selected from database in MVC

Creating Table and Procedure

Use below script to create a Student Table and procedure to fetch data from database.

CREATE TABLE tbl_Students
(
ID INT PRIMARY KEY IDENTITY,
Name VARCHAR(50),
Subjects VARCHAR(100)
)
GO
INSERT INTO tbl_Students VALUES('Rahul','C#,ASP.Net')
INSERT INTO tbl_Students VALUES('Sumit','SQL,ASP.Net')
INSERT INTO tbl_Students VALUES('Deepak','MVC,ASP.Net,SQL')
GO

CREATE PROCEDURE usp_Students
AS
BEGIN
 SELECT ID,Name, Subjects AS 'FavSubjects' FROM tbl_Students
END

Creating ASP.NET MVC Empty Application

Now the next step is to create ASP.NET MVC empty application as shown below.
Go to FileNewProject. A new window will be open as shown below.
Now go to Web and select .NET Framework 4.5 and give project name and click on OK .

Creating asp.net mvc 5 application

Now new window will open as shown below.
Now Select Empty Template, check on MVC checkbox and click on OK.

Selecting asp.net mvc 5 empty template

Now, a new project will be created as shown below.

Asp.net mvc 5 empty folder structure

Adding Models

Go to Models folder and add new class file Student.cs. Now replace all code with below code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace WebApplication1.Models
{
public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string FavSubjects { get; set; }

    // collection to store all subjects
    public List<SelectListItem> Subjects { get; set; }

    // collection to store selected subjects
    public List<SelectListItem> AutoSelSubjs { get; set; }
}

public class StudentModel
{
    //list for all students
    public IEnumerable<Student> StudentList { get; set; }
}
}

Now add a new folder named DALayer to the root directory of the application and add a new class file StudentDAL.cs into DALayer folder and replace all code with below code.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using WebApplication1.Models;

namespace WebApplication1.DALayer
{
public class StudentDAL
{
    public IEnumerable<Student> GetAllStudent()
    {
        // fetching connection string from web.config file
        // you can also use Entity Framework or Dapper ORM to fetch data from database
        // it should return IEnumerable of class
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ToString()))
        {
            List<Student> studentList = new List<Student>();
            SqlCommand cmd = new SqlCommand("usp_Students", con);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.Read())
            {
                Student st = new Student();
                st.Id = Convert.ToInt32(sdr["ID"].ToString());
                st.Name = sdr["Name"].ToString();
                st.FavSubjects = sdr["FavSubjects"].ToString();

                studentList.Add(st);
            }
            return studentList;
        }
    }
}
}

Adding Controller

Next step is to add controller to application. Go to controller folder and add new empty controller named as Home controller. Replace all code with below code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication1.DALayer;
using WebApplication1.Models;

namespace WebApplication1.Controllers
{
public class HomeController : Controller
{
    //
    StudentDAL dal = new StudentDAL();

    public ActionResult Index()
    {
        StudentModel sm = new StudentModel();
           
        // fetching student details and storing into studentlist collection
        sm.StudentList = dal.GetAllStudent();

        // iterating over each student and storing All the subjects from C# code
        foreach (var item in sm.StudentList)
        {
            item.Subjects = GetSubjectList();
        }

        foreach (var obj in sm.StudentList)
        {
            // spliting subjects for each student using comma which is coming from database
            var Statuslist = obj.FavSubjects.Split(',');

            // storing favourite subject as SelectListItem into AutoSelSubjs
            obj.AutoSelSubjs = Statuslist.Select(i => new SelectListItem() { Text = i.ToString(), Value = i }).ToList();
        }
        return View(sm);
    }

    [NonAction]
    public List<SelectListItem> GetSubjectList()
    {
        List<SelectListItem> subjects = new List<SelectListItem>()
        {
        new SelectListItem (){ Text = "C#", Value = "C#" },
        new SelectListItem() { Text = "ASP.Net", Value = "ASP.Net" },
        new SelectListItem() { Text = "SQL", Value = "SQL" },
        new SelectListItem() { Text = "Java", Value = "Java" },
        new SelectListItem() { Text = "MVC", Value = "MVC" }
        };
        return subjects;
    }
}
}

Installing Grid.Mvc using Nuget

Next step is to install Grid.Mvc. Right click on References and go to Manage NuGet Packages as shown below.

Adding Grid.Mvc using NuGet Packages

After that a new window will open as shown below. Search for Grid.Mvc and install it.

Installing Grid.Mvc in ASP.Net Mvc

Adding View

Now, right click on home controller index action method, add new view and name Index. Select Empty template and uncheck Use layout page and click on Add as shown below.

Addling index view

Now go to Views / Home folder, open Index.cshtml file and replace all code with below code. Here in script JS files, First is jQuery file and Second, Third is bootstrap JS and CSS files and Fourth and Fifth file is MultiSelect JS and CSS Files. Make sure all files is loaded when application runs.
Here set @multiple = "multiple" for dropdownlist and set j('.multi').multiselect() in jquery code.
In DropDownList, first parameter is Id, second is actual drowdown items , third is items that need to be auto-selected and fourth is property.

Index.cshtml Code:
@model  WebApplication1.Models.StudentModel
@using GridMvc.Html
@{
    ViewBag.Title = "Grid.MVC with MultiSelect DropDownList with CheckBox auto selected from database in MVC";
    Layout = null;
}
<title>Grid.MVC with MultiSelect DropDownList with CheckBox auto selected from database in MVC</title>

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js" type="text/javascript"></script>
<link href="http://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.0.3/css/bootstrap.min.css" rel="stylesheet" type="text/css" />
<script src="http://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.0.3/js/bootstrap.min.js" type="text/javascript"></script>
<link href="http://cdn.rawgit.com/davidstutz/bootstrap-multiselect/master/dist/css/bootstrap-multiselect.css" rel="stylesheet" type="text/css" />
<script src="http://cdn.rawgit.com/davidstutz/bootstrap-multiselect/master/dist/js/bootstrap-multiselect.js" type="text/javascript"></script>

<script type="text/javascript">
    var j = jQuery.noConflict();
    j(function () {
        j('.multi').multiselect({ // you can use Id selector if dropdown is outside of grid (for single dropdownlist)
            //includeSelectAllOption: true
        });
    });
</script>

<div class="col-sm-6">
<div class="table-responsive">
@using (Html.BeginForm("Index", "Home"))
{
    @Html.Grid(Model.StudentList).Columns(columns =>
{
    columns.Add(m => m.Id).Titled("Id").Encoded(false)
    .Sanitized(false).SetWidth(12);

    columns.Add(m => m.Name).Titled("Name").Encoded(false)
    .Sanitized(false).SetWidth(12);

    columns.Add(m => m.FavSubjects).Titled("Subject Comma Appended").Encoded(false)
    .Sanitized(false).SetWidth(30);

    columns.Add().Titled("Subjects Auto Selected").Encoded(false)
    .Sanitized(false).SetWidth(20)
    .RenderValueAs(@<text> @Html.DropDownList("ddlSubject", new MultiSelectList(@item.Subjects, "Value", "Text", @item.AutoSelSubjs.Select(a => a.Text)),
                new
                {  @multiple = "multiple", @class = "multi"
                })</text>);
}).Sortable()
}
</div>
</div>
Complete folder structure is shown below
Final Folder Structure in ASP.NET MVC
Now run your application and output is shown in first screen-shot.
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

Ads By Chitika