Populate a WebGrid Based on Selected DropDownList Value in MVC using Razor C# and VB

asp.net

← PrevNext →

In this article, I am going to explain step-by-step on how to populate a WebGrid based on selected values from a DropDownList in an MVC application using Razor. I am assuming you know what a WebGrid is and how to use WebGrid in an MVC application. I have written the code in both C# and Vb.Net.

Populate a WebGrid Based on DropDownList Selections

I am using a DropDownList helper in my example, with an action method to populate the DropDownList with dynamic data, that is, data extracted from an SQL Server table. Upon selection, that is, when a user selects a value from DropDownList and submits the data (POST), it would populate a WebGrid with more data.

Create a Table in SQL Server

The table name is dbo.Birds. It holds a list of Bird names, of different types and their scientific names. Create the table.

CREATE TABLE [dbo].[Birds](
    [ID] [int] NOT NULL,
    [BirdName] [varchar](50) NULL,
    [TypeOfBird] [varchar](50) NULL,
    [ScientificName] [varchar](50) NULL,
    CONSTRAINT [PK_Birds] PRIMARY KEY CLUSTERED 
    ([ID] ASC)
) ON [PRIMARY]

Add few rows of data to the table. We need enough data to test the WebGrid with Paging option. This is crucial.

INSERT INTO Birds (ID, BirdName, TypeOfBird, ScientificName)
VALUES (
	1, 'Eurasian Collared-Dove', 'Dove', 'Streptopelia',
	2, 'Bald Eagle', 'Hawk', 'Haliaeetus Leucocephalus' 
)

Here’s the table with more data.

Now, create the MVC project. Select an Empty Project Template.

The Model modelBirds.cs (C# Code)
'

We’ll first create the Model and add few propertied to it.

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

namespace webgrid_using_dropdown_data.Models
{
    public class modelBirds
    {
        // PROPERTIES.
        public int ID { get; set; }
        public string BirdName { get; set; }
        public string TypeOfBird { get; set; }
        public string ScientificName { get; set; }
    }
}
The Model modelBirds.vb (Visual Basic)
Option Explicit On

Namespace webgrid_using_dropdown_data.Models
    Public Class modelBirds
        ' PROPERTIES.
        Public Property ID() As Integer
            Get
                Return m_ID
            End Get
            Set(value As Integer)
                m_ID = value
            End Set
        End Property
        Private m_ID As Integer

        Public Property BirdName() As String
            Get
                Return m_BirdName
            End Get
            Set(value As String)
                m_BirdName = value
            End Set
        End Property
        Private m_BirdName As String

        Public Property TypeOfBird() As String
            Get
                Return m_TypeOfBird
            End Get
            Set(value As String)
                m_TypeOfBird = value
            End Set
        End Property
        Private m_TypeOfBird As String

        Public Property ScientificName() As String
            Get
                Return m_ScientificName
            End Get
            Set(value As String)
                m_ScientificName = value
            End Set
        End Property
        Private m_ScientificName As String

    End Class
End Namespace
The Controller birdsController.cs (C#)

Create a Controller. See this page if you are new to MVC. Name the controller as birdsController.cs (birdsController.vb for Visual Basic). Choose Empty MVC controller Template.

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

using populate_webgrid_using_ajax.Models;
using System.Data.SqlClient;

namespace populate_webgrid_using_ajax.Controllers
{
    public class birdsController : Controller
    {
        const string sConnString = "Data Source=DNA;Persist Security Info=False;" +
            "Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;";

        List<modelBirds> birdDetails = new List<modelBirds>();

        public ActionResult viewBirds()
        {
            getBirdTypes();
            return View(birdDetails);
        }

        // GET UNIQUE BIRD TYPES FROM DATABASE.
        private void getBirdTypes()
        {
            List<SelectListItem> birds = new List<SelectListItem>();

            using (SqlConnection con = new SqlConnection(sConnString))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT TypeOfBird FROM dbo.Birds"))
                {
                    cmd.Connection = con;
                    con.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    // POPULATE THE LIST WITH DATA.
                    while (reader.Read())
                    {
                        birds.Add(new SelectListItem
                        {
                            Text = reader["TypeOfBird"].ToString(),
                            Selected = Request["vBirds"] == reader["TypeOfBird"].ToString() ? true : false
                        });
                    }

                    // ADD LIST TO A ViewBag. WILL USE THIS LIST TO POPULATE THE DROPDOWNLIST IN OUR VIEW.
                    ViewBag.vBirds = birds;

                    con.Close();
                }
            }
        }

        // GET EVERY DETAIL OF THE SELECTED BIRD.
        [HttpPost()]
        public ActionResult getBirdDetails()
        {
            using (SqlConnection con = new SqlConnection(sConnString))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Birds WHERE TypeOfBird = '" + Request["vBirds"] + "'"))
                {
                    cmd.Connection = con;
                    con.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    // POPULATE THE LIST WITH BIRD DETAILS.
                    while (reader.Read())
                    {
                        birdDetails.Add(new modelBirds
                        {
                            ID = Convert.ToInt32(reader["ID"]),
                            BirdName = reader["BirdName"].ToString(),
                            TypeOfBird = reader["TypeOfBird"].ToString(),
                            ScientificName = reader["ScientificName"].ToString()
                        });
                    }

                    con.Close();
                    getBirdTypes();
                }
            }

            // RETURN DETAILS TO THE PARENT VIEW.
            return View("viewBirds", birdDetails);
        }
    }
}

The ActionResult method viewBirds will call a private procedure getBirdTypes(), which populates a <SelectListItem> with Distinct or unique list of types of bird, extracted from the table.

I am adding the <SelectListItem> to the ViewBag. I’ll use the ViewBag (with the list) to populate the DropDownList.

ViewBag.vBirds = birds;

The controller also has a [HttpPost] method named getBirdDetails(). Since, it’s a POST method, this is called upon form submit event. See the View section below. This method returns complete list of birds for the selected DropDownList value, that is, a type of bird.

The Controller birdsController.vb (Vb)
Option Explicit On

Imports webgrid_using_dropdown_data.webgrid_using_dropdown_data.Models
Imports System.Data.SqlClient

Namespace webgrid_using_dropdown_data
    Public Class birdsController
        Inherits System.Web.Mvc.Controller

        ' DEFINE PARAMETERS FOR CONNECTION STRING.
        Const sConnString As String = "Data Source=DNA;Persist Security Info=False;" & _
            "Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;"

        Dim birdDetails As New List(Of modelBirds)()

        Function viewBirds() As ActionResult
            getBirdTypes()
            Return View(birdDetails)
        End Function

        ' GET UNIQUE BIRD TYPES FROM DATABASE.
        Private Sub getBirdTypes()
            Dim birds As New List(Of SelectListItem)()

            Using con As SqlConnection = New SqlConnection(sConnString)
                Using cmd As SqlCommand = New SqlCommand("SELECT DISTINCT TypeOfBird FROM dbo.Birds")
                    With cmd
                        .Connection = con
                        con.Open()

                        Dim reader As SqlDataReader = cmd.ExecuteReader()

                        ' POPULATE THE LIST WITH DATA.
                        While reader.Read()
                            birds.Add(New SelectListItem() With { _
                                .Text = reader("TypeOfBird").ToString(), _
                                .Selected = If(Request("vBirds") = reader("TypeOfBird").ToString(), True, False)
                             })
                        End While

                        ' ADD LIST TO A ViewBag. WILL USE THIS LIST FOR THE DROPDOWNLIST IN OUR VIEW.
                        ViewBag.vBirds = birds

                        con.Close()
                    End With
                End Using
            End Using
        End Sub

        '  GET EVERY DETAIL OF THE SELECTED BIRD.
        <HttpGet()>
        Public Function getProductDetails() As ActionResult
            Using con As SqlConnection = New SqlConnection(sConnString)
                Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.Birds WHERE TypeOfBird = '" & Request("vBirds") & "'")
                    cmd.Connection = con
                    con.Open()

                    Dim reader As SqlDataReader = cmd.ExecuteReader()

                    ' POPULATE THE LIST WITH PRODUCT DETAILS.
                    While reader.Read()
                        birdDetails.Add(New modelBirds With { _
                            .ID = Convert.ToInt32(reader("ID")),
                            .BirdName = reader("BirdName").ToString(),
                            .TypeOfBird = reader("TypeOfBird").ToString(),
                            .ScientificName = reader("ScientificName")
                        })
                    End While

                    con.Close()
                    getBirdTypes()  ' UPDATE VIEWBAG vBirds.
                End Using
            End Using

            ' RETURN DETAILS TO THE PARENT VIEW.
            Return View("viewBirds", birdDetails)
        End Function
    End Class
End Namespace

Now, lets’ create the View.

From the Solution Explorer window, find the Views folder. Create a New Folder under Views, and name it Birds. Right click the Birds folder, add a View and name it viewBirds.cs (or viewBirds.vb for Visual Basic).

Update RouteConfig file

You have to update the RouteConfig.cs or Route.Config.vb file and change the controller and action values. Why? Read this post here.

Change this ...

defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }

to this ...

defaults: new { controller = "birds", action = "viewBirds", id = UrlParameter.Optional }

Run the application to check if everything is ok.

In our view, we’ll first add a DropDownList helper.

viewBirds.cshtml (C#)
@{
    ViewBag.Title = "Birds around the World";
}

@*ADD A DropDownList TO POPULATE UNIQUE BIRD TYPES TO THE LIST.*@

@using (Html.BeginForm())
{
    <div>
        Types of Birds: @Html.DropDownList("vBirds")
    </div>
}
viewBirds.vbhtml (Visual Basic)

Html helpers work slightly different in VB. You will have to add a @ before the markup.

@Code
    ViewData("Title") = "Birds around the World"
End Code

@*ADD A DropDownList TO POPULATE UNIQUE BIRD TYPES TO THE LIST.*@

@Using (Html.BeginForm()
    @<div>
        Types of Birds: @Html.DropDownList("vBirds")
    </div>    
End Using

Run the application (or refresh the browser page).

I am using Html.BeginForm() Helper method, where I have added the DropDownList helper. The HtmlBeginForm helper is used to create the opening and closing HTML <form> tags.

If you an output like the below image, you have successfully bound the helper (DDL) with dynamic data.

DropDownList Helper Example in MVC

The DropDownList helper has a string argument (or parameter) named vBirds. It’s the ViewBag that I have declared in my controller (see the controller code above) and has a list of Bird Types.

The Html.BeginForm() method allows us to specify the action method, the controller and the form method (GET or POST), so we can submit the data (the Dropdown selected value).

Now add a submit button just below the DropDownList like this,

<div>
    Types of Birds: @Html.DropDownList("vBirds")
    <p>
        <input type="submit" value="SHOW DETAILS" />
    </p>
</div>

Update Html.BeginForm() by specifying the action method, the controller and the form method. Your view would look like this.

viewBirds.cshtml
@{
    ViewBag.Title = "Birds around the World";
}

@*ADD A DropDownList AND POPULATE UNIQUE BIRD TYPES TO THE LIST.*@

@using (Html.BeginForm("getBirdDetails", "birds", FormMethod.Post))
{
    <div>
        Types of Birds: @Html.DropDownList("vBirds")
        <p>
            <input type="submit" value="SHOW DETAILS" />
        </p>
    </div>
}

Run the application and click the submit button. It will call the [HttpPost] method getBirdDetails in the controller and update the birdDetails list with values.

viewBirds.vbhtml
@Code
    ViewData("Title") = "Birds around the World"
End Code

@*ADD A DropDownList TO POPULATE UNIQUE BIRD TYPES TO THE LIST.*@

@Using (Html.BeginForm("getBirdDetails", "birds", FormMethod.Post))
    @<div>
        Types of Birds: @Html.DropDownList("vBirds")
        <p>
            <input type="submit" value="SHOW DETAILS" />
        </p>
    </div>    
End Using

Add style to the Grid and the DropDownList.

<style>
    select, p, div, input
    {
        font: 17px Calibri;
    }
    div 
    {
        border: dotted 1px #555;
        width: 300px;
        padding: 10px;
    }
    
    .grid {
        font: 17px Calibri;
        width: 500px; 
        background-color: #FFF; 
        border: solid 1px #CD6736;
    }
    .grid td, th {
        padding: 2px; 
        border: solid 1px #CD6736; 
        text-align: center; 
        text-transform: capitalize;
    }
    .grid-header 
    {
        background-color: #CD6736;
        color: #FFF;
        text-align: left;
    }
    .grid-footer 
    {
        color: #000;
        font: 15px Calibri;
        text-align: left;
    }
</style>

We’ll now add the WebGrid in the view and bind it with a View model.

viewBirds.cshmtl (adding WebGrid)
@*ADD THE WebGrid.*@

@if (Model.Count > 0)
{
    WebGrid objGrid = new WebGrid(source: Model, rowsPerPage: 5, canSort: false);
    @objGrid.GetHtml(
        caption: "Birds",
        tableStyle: "grid",
        headerStyle: "grid-header",
        footerStyle: "grid-footer",
        columns: objGrid.Columns (
            objGrid.Column("ID"),
            objGrid.Column("BirdName", "Bird Name"),
            objGrid.Column("ScientificName", "Scientific Name"),
            objGrid.Column("TypeOfBird", "Type of Bird")
        )
    )
}
viewBirds.vbhmtl (adding WebGrid)
@If Model.Count > 0 Then
    Dim objGrid As New WebGrid(Model, rowsPerPage:=5, canSort:=False)
    @objGrid.GetHtml(
        caption:="Birds",
        tableStyle:="grid",
        headerStyle:="grid-header",
        footerStyle:="grid-footer",
        columns:=objGrid.Columns(
            objGrid.Column("ID"),
            objGrid.Column("BirdName", "Bird Name"),
            objGrid.Column("ScientificName", "Scientific Name"),
            objGrid.Column("TypeOfBird", "Type of Bird")
        )
    )
End If

Run the application. Choose a value from the Dropdown list and click the Submit button. You will see the grid with the values.

Add jQuery Script for WebGrid’s Paging Links

Finally, we’ll add a jQuery script for the WebGrid’s paging links to work efficient (without errors).

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script>
    $(document).ready(function () {
        // SUMBIT EVENT FOR WEBGRID PAGING.
        $('.grid-footer a').click(function () {
            $('form').attr('action', $(this).attr('href')).submit();
            return false;
        });
    });
</script>

WebGrid with Paging using Form Method POST

I am submitting the data using https POST method, which I have defined within the Html.BeginForm() method. Therefore, every-time you click something, it will post or send data to the controller’s [HttpPost] method getBirdDetails().

The WebGrid’s paging links will show when you have more than five rows for a selected type. Clicking the next or previous links will throw an error saying, The resource cannot be found. Therefore, we need to bind the links to the forms submit event.

I have defined a CSS class named grid-footer to the WebGrid’s footerStyle option. I’ll use this CSS class (grid-footer) in the script to trigger the form’s submit event.

Here’s our complete View (In case you do not want to go through the step-by-step procedure).

@{
    ViewBag.Title = "Birds around the World";
}

<style>
    select, p, div, input
    {
        font: 17px Calibri;
    }
    div 
    {
        border: dotted 1px #555;
        width: 300px;
        padding: 10px;
    }
    
    .grid {
        font: 17px Calibri;
        width: 500px; 
        background-color: #FFF; 
        border: solid 1px #CD6736;
    }
    .grid td, th {
        padding: 2px; 
        border: solid 1px #CD6736; 
        text-align: center; 
        text-transform: capitalize;
    }
    .grid-header 
    {
        background-color: #CD6736;
        color: #FFF;
        text-align: left;
    }
    .grid-footer 
    {
        color: #000;
        font: 15px Calibri;
        text-align: left;
    }
</style>

@*ADD A DropDownList AND POPULATE UNIQUE BIRD TYPES TO THE LIST.*@

@using (Html.BeginForm("getBirdDetails", "birds", FormMethod.Post))
{
    <div>
        Types of Birds: @Html.DropDownList("vBirds")
        <p>
            <input type="submit" value="SHOW DETAILS" />
        </p>
    </div>
}

@*ADD THE WebGrid.*@

@if (Model.Count > 0)
{
    WebGrid objGrid = new WebGrid(source: Model, rowsPerPage: 5, canSort: false);
    @objGrid.GetHtml(
        caption: "Birds",
        tableStyle: "grid",
        headerStyle: "grid-header",
        footerStyle: "grid-footer",
        columns: objGrid.Columns (
            objGrid.Column("ID"),
            objGrid.Column("BirdName", "Bird Name"),
            objGrid.Column("ScientificName", "Scientific Name"),
            objGrid.Column("TypeOfBird", "Type of Bird")
        )
    )
}
 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script>
    $(document).ready(function () {
        // SUMBIT EVENT FOR WEBGRID PAGING.
        $('.grid-footer a').click(function () {
            $('form').attr('action', $(this).attr('href')).submit();
            return false;
        });
    });
</script>

← PreviousNext →