Dynamic DropDownList Binding in ASP.NET MVC With Database

• Download team_statistics.rar - 9.2 MB
• Download team_statistics.zip - 10.1 MB

Introduction

This is my second Tip/trick and this Tip/trick is all about how we can create a dynamic dropdown list in ASP .NET MVC. In MVC mostly user find difficulties to bind the the data to user the controles. so here i am going to show a simple application demo in which we will bind the data to dropdown list from the databse at the runtime.

Background

This demo application contains the javascript and simple logic of ADO.NET. little concept of ASP.NET MVC and javascript is enough to understand this demo.

Design

Motive - Now my goal is to create a application team_statistics which will contain the 3 dropdownlist.

first dropdownlist - It will contain the type of tournaments and these data will come from the database.

second dropdownlist - On selection of the tournament type from the first dropdownlist second dropdownlist automatically should generate the teams from the database.

third dropdownlist - On selection of the type of team from the second dropdown list the third dropdownlist should generate the player name.

So to create this application we need three table.

Following are the steps to create the application:

1. Open Visual Studio and select the new Project.
2. Within the Web template, select ASP.NET MVC4 WEB Application with empty template.
3. Now add a Home Controller and a view Page for Index Action Method.
4. Now add a database file .mdf file in your project. This can be added by right clicking on App_data folder and add a database file.
5. In the database file, add 3 tables by the name of Player, Team, TounamentType.
6. below diagram give the complete idea about the database.

.

Using the code

Here is the complete code for fetching the data from the database and passing the data to the UI.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Data.Sql;


namespace team_statistics.Controllers
{

   public class HomeController : Controller    
    {
       string connection = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; 
       List<selectlistitem> team = new List<selectlistitem>();             
       List<selectlistitem> player = new List<selectlistitem>();      //declare the 3 list object
       List<selectlistitem> tournament = new List<selectlistitem>();
       public ActionResult Index()
       {
          ViewBag.var1 = GetOptions();   //first request come and move to this method
          ViewBag.var2 = team;  
           ViewBag.var3 = player;
           return View();
       }     
                      //var1,var2,var3 are the objects to pass the data from controller to index
          
       
 private SelectList GetOptions()           //fetch the tournament type details from the table
  {
    using (SqlConnection conn = new SqlConnection(connection))
      {
         conn.Open();
          SqlDataReader myReader = null;
          SqlCommand myCommand = new SqlCommand("SELECT Id, Tournament FROM TounamentType", conn);
           myReader = myCommand.ExecuteReader();
            while (myReader.Read())
             {

            tournament.Add(new SelectListItem { Text = myReader["Tournament"].ToString(), Value =             myReader["Id"].ToString() });
             }
      
              conn.Close();
              return new SelectList(tournament, "Value", "Text", "id");  //return the list objects
               
            }
        }

   
     
        
 public JsonResult Team(string name)  //fetch the team details from database
     {
       using (SqlConnection conn = new SqlConnection(connection))
         {
            conn.Open();
            SqlDataReader myReader = null;
            SqlCommand myCommand = new SqlCommand("select Team,Id from Team where Tournament =                 '" + name + "' ", conn);
            myReader = myCommand.ExecuteReader();
            while (myReader.Read())
             {
               team.Add(new SelectListItem { Text = myReader["Team"].ToString(), Value =                         myReader["Id"].ToString() });
             }
            }
           return Json(team, JsonRequestBehavior.AllowGet); //return the list objects in json form
        }


   public JsonResult Player(string name) // fetch the details of player from database
        {
           using (SqlConnection conn = new SqlConnection(connection))
            {   
                conn.Open();
                SqlDataReader myReader = null;
                SqlCommand myCommand = new SqlCommand("select Player,Id from Player where Team =                  '" + name + "' ", conn);
                myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                 player.Add(new SelectListItem { Text = myReader["Player"].ToString(), Value =                     myReader["Id"].ToString() });
                }
            }
            
         return Json(player, JsonRequestBehavior.AllowGet); //return the list objects in json form

        }
        }

       
    }

Now I am going to show the UI for this application.

 <h2>Index</h2>
<meta http-equiv="X-UA-Compatible" content="IE=9" />
<script src="../../Scripts/modernizr-2.5.3.js" type="text/javascript"></script>
<script src="../../Scripts/jquery-2.1.1.min.js" type="text/javascript"></script>

Tournament    @Html.DropDownList("var1","Choose tournament")
Team          @Html.DropDownList("var2","Choose team")  
Player        @Html.DropDownList("var3", "Choose player")
                                   //dropdown with name var1, var2,var3 and with viewbag object
<script type="text/javascript">    //initial var2, var3 are empty 
                                  //dropdownlist name and viewbag object name must be same

$(function () {     
      $("#var1").change(function () {             
           var name = $("#var1 :selected").text();  //if user select the tournament 
           var url = 'Home/Team'; 
            var data1 = { "name": name };
       $.post(url, data1, function (data) {    //ajax call
           var items = [];
           items.push("<option value=" + 0 + ">" + "Choose team" + "</option>"); //first item
           for (var i = 0; i < data.length; i++) {
           items.push("<option value=" + data[i].Value + ">" + data[i].Text + "</option>"); 
          }                                         //all data from the team table push into array
            $("#var2").html(items.join(' '));  
      })                                            //array object bind to dropdown list
   }); 

    $("#var2").change(function () {                  //same logic for 3rd dropdown list
          var name = $("#var2 :selected").text();

          var url = 'Home/Player';  
           var data1 = { "name": name };
      $.post(url, data1, function (data) { 
             var items = [];
             items.push("<option value=" + 0 + ">" + "Choose Player" + "</option>"); 
             for (var i = 0; i < data.length; i++) {
        items.push("<option value=" + data[i].Value + ">" + data[i].Text + "</option>");
            }
       $("#var3").html(items.join(' '));  
       }) 
   }); 

});

</script>

<input type="submit" value="submit" />

Result

Here is our final UI page which will show the result.

In our final page we can see that there are three dropdownlist. so if user select tournament from the dropdownlist then Team will be appear according to the Tournament and if we select the team from the dropdownlist then player will be appear according to team.

For complete idea you can download and debug the application.

Points that are Important to Run this Application

In Web.Config file change the connection string.

 <connectionStrings>
 <add name="DBCS" connectionString="Data Source=.\SQLEXPRESS; 
 AttachDbFilename=E:\team_statistics\team_statistics\App_Data\Team.mdf;
 Integrated Security=True; User Instance=True"/>
   </connectionStrings>  // change the connection string

Step 1: To change the connection string, right click on mdf file -> properties->copy the address and replace the attachDbfilename.

Now in this application, you can modify and you can add some extra features like for saving the user data, adding the new data in dropdownlist etc.

I hope you have enjoyed it and got some idea about dynamic dropdownlist binding using JavaScript from this post. Next time i will post some new tip/trick until that enjoy...:)

If some error or wrong concept is there, please comment below as it will be helpful for me.