Binding Dropdown List from Database Using jQuery in ASP.NET Core

In this part, we are going to learn how to bind dropdown list in ASP.NET Core from Database using Jquery in a step by step way.

Creating ASP.NET Core Application
Setting up Database and Table
Adding Entity Framework Core package from NuGet
Configuring Connection string in appsettings.json
Adding Product Model
Adding DatabaseContext Class and inheriting with DbContext class
Adding Controller

We are going to create ASP.NET Core Web Application for that we are going to choose ASP.NET Core Web Application template.

Next, we are going to set Project Name WebApplication11 and location and in last part, we are going to choose .Net Core framework and ASP.NET Core Version 3.0 as the framework for application and few advance settings for such as configuring https and enabling docker we are not going to enable docker settings for this project.

Now finally click on create button to create a project.
Project structure
The project structure generated according to the configuration.

After creating a project, we are going to run this project.
Setting up Database and Table
We have created a database with Name Sample2, and then we have added product table in it.
Below is Table structure along with a script to create a table.

Script to Create Product Table

CREATE TABLE [dbo].[Product](
	[ProductId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Quantity] [int] NULL,
	[Color] [varchar](50) NULL,
	[Price] [decimal](18, 2) NULL,
	[ProductCode] [varchar](50) NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

After creating a database and table next, we are going to Entity framework reference to project from the NuGet package.

Adding Entity Framework Core package from NuGet

In this part, we are going to learn how to add Entity Framework Core package to project from NuGet package manager.
For adding a package from NuGet right-click on the Main project WebApplication11 and from the menu select Manage NuGet Packages. As you choose it a New dialogue of NuGet Package Manager with search box will pop up.
In choose browse tab and search Microsoft.EntityFrameworkCore.SqlServer and choose version 2.2.6. The latest version of Microsoft.EntityFrameworkCore.SqlServer is in the preview that why we have chosen them, finally click on the install button for adding the package.

After adding Microsoft.EntityFrameworkCore.SqlServer to project next, we are going to Configuring Connection string in appsettings.json.

Configuring Connection string in appsettings.json

Here in ASP.NET core, we don’t have web.config file which was there in the older version of ASP.NET and ASP.NET MVC here we have appsettings.json file where we are going to keep application-level settings, and this file is Json based.

appsettings.json file

In this file, we have added ConnectionStrings section inside that we have added DatabaseConnection key and other is value.

Notice: – here I am using SQL based authentication for database connection that why I have added User Id and password of SQL server.

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DatabaseConnection": "Data Source=SAI-PC\\SQLEXPRESS; initial catalog=Sample2; user id=sa; password=Pass$123"
  }
}

Next, after adding the connection string, we are going to add Model Product in Models folder.

Adding Product Model

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication11.Models
{
    [Table("Product")]
    public class Product
    {
        [Key]
        public int ProductId { get; set; }
        public int Name { get; set; }
        public int Quantity { get; set; }
        public int Color { get; set; }
        public int Price { get; set; }
        public int ProductCode { get; set; }
    }
}

After adding Product Model next, we are going to add the ProductView Model which we are going to Use on View for binding data to control.

Adding ProductView Model

using Microsoft.AspNetCore.Mvc.Rendering;
using System.Collections.Generic;

namespace WebApplication11.Models
{
    public class ProductViewModel
    {
        [DisplayName("Product")]
        public string ProductId { get; set; }
    }
}

Next, after adding the Product and ProductView Model, we are going to add DatabaseContext Class and inheriting with DbContext class.

Adding DatabaseContext Class and inheriting with DbContext class

We are going to add DatabaseContext class which will inherit DbContext class, and DatabaseContext constructor accepts the DbContextOptions as an argument. The DbContextOptions carries the configuration information needed to configure the DbContext.
After adding DatabaseContext class, we have defined DbSet entity for product Model.
DbContext in Entity Framework Core – TekTutorialsHub. https://www.tektutorialshub.com/entity-framework-core/ef-core-dbcontext/

Code Snippet of DatabaseContext Class and inheriting with DbContext class

using Microsoft.EntityFrameworkCore;
namespace WebApplication11.Models
{
    public class DatabaseContext : DbContext
    {
        public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
        {
        }
        public DbSet<Product> Product { get; set; }
    }
}

After Configuring DatabaseContext class next, we are going Register DatabaseContext as a service.

Code Snippet of ConfigureServices in Startup Class

public void ConfigureServices(IServiceCollection services)
{
    var connection = Configuration.GetConnectionString("DatabaseConnection");
    services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(connection));
    services.AddControllersWithViews();
}

Now we can use DatabaseContext class as service for dependency injection in the entire application.

Adding Controller

In this part, we are going to add Controller with name ShowProductController Controller after adding controller we have default Index action method in it.

using Microsoft.AspNetCore.Mvc;

namespace WebApplication10.Controllers
{
    public class ShowProductController : Controller
    {
        public IActionResult Index()
        {
            return View();
        }
    }
}

Next, we are going to inject DatabaseContext dependency via Constructor injection. The IOC container will resolve DatabaseContext dependency at runtime automatically.

using Microsoft.AspNetCore.Mvc;
using WebApplication11.Models;

namespace WebApplication11.Controllers
{
    public class ShowProductController : Controller
    {
        private readonly DatabaseContext _databaseContext;

        public ShowProductController(DatabaseContext databaseContext)
        {
            _databaseContext = databaseContext;
        }
        public IActionResult Index()
        {
            return View();
        }
    }
}

After Configuring Constructor injection next, we are going to add another action method with Name GetProducts which will return Json response.

using System.Linq;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using WebApplication11.Models;

namespace WebApplication11.Controllers
{
    public class ShowProductController : Controller
    {
        private readonly DatabaseContext _databaseContext;

        public ShowProductController(DatabaseContext databaseContext)
        {
            _databaseContext = databaseContext;
        }
        public IActionResult Index()
        {
            return View();
        }

        public IActionResult GetProducts()
        {
            var productsList = (from product in _databaseContext.Product
                select new SelectListItem()
                {
                    Text = product.Name,
                    Value = product.ProductId.ToString(),
                }).ToList();

            productsList.Insert(0, new SelectListItem()
            {
                Text = "----Select----",
                Value = string.Empty
            });

            return Json(productsList);
        }
    }
}

After Adding GetProducts Action Method Next, we are going to Add Index View.

Adding the GetProducts Action Method and Index View

For Adding View just by right-clicking inside Index Action Method and from the list choose Add View option a new dialogue will pop up which will have View Name same as Action Method Name Index next we are not going to select Template and Model class for this View click on Add button to create View.

After Creating Index View Next on Index View first, we are going specify ProductViewModel model object which has productid property.

On View, we are creating dropdown using new tag helper, which helps to apply CSS with intelligence which was not available in HTML helper method.

<select asp-for="ProductId"
        class="form-control"
        asp-items="@(new SelectList(Enumerable.Empty<SelectListItem>(),"Value", "Text"))">
</select>

Code Snippet of Index.cshtml

@model ProductViewModel
@{
    ViewData["Title"] = "Index";
    Layout = null;
}

<link href="~/lib/bootstrap/dist/css/bootstrap.css" rel="stylesheet" />
<script src="~/lib/jquery/dist/jquery.js"></script>

<div class="col-md-4">
    <form asp-controller="ShowProduct" asp-action="Index" method="post" role="form">
        <div class="form-group">
            <div class="alert-danger" asp-validation-summary="ModelOnly"></div>
            <label asp-for="ProductId"></label>
            <select asp-for="ProductId"
                    class="form-control"
                    asp-items="@(new SelectList(Enumerable.Empty<SelectListItem>(),"Value", "Text"))">
            </select>
        </div>
        <div class="form-group">
            <input id="Submit1" class="btn btn-success" type="submit" value="submit" />
        </div>
    </form>
</div>

<script type="text/javascript">
    $(document).ready(function () {
        $.ajax({
            type: "GET",
            url: "/ShowProduct/GetProducts",
            success: function (data)
            {
                $.each(data, function () {
                    $("#ProductId").append($("<option></option>").val(this['Value']).html(this['Text']));
                });
            }
        });
    });
</script>

We are binding dropdown list with jQuery ajax for getting data we are calling GetProducts Action method which returns Json object which we are binding to the dropdown list.

Next, we are going to set SerializerSettings in ConfigureServices method because by default it uses camel case SerializerSettings.

e.g.:-
Camel Case
{"id":1,"fullName":"Sai Bageri"}
Default
{"Id":1,"FullName":" Sai Bageri "}

Doing that we need to install Microsoft.AspNetCore.Mvc.NewtonsoftJson package from NuGet package.

After installing next we need to set Serializer Settings in ConfigureServices Method in Startup class.

Here are going to set SerializerSettings.ContractResolver to DefaultContractResolver.

services.AddControllersWithViews().AddNewtonsoftJson(
options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());

Code Snippet of ConfigureServices Method after adding Serializer Settings

  public void ConfigureServices(IServiceCollection services)
        {
            var connection = Configuration.GetConnectionString("DatabaseConnection");
            services.AddDbContext<DatabaseContext>(
options => options.UseSqlServer(connection), optionsLifetime: ServiceLifetime.Transient);

            services.AddControllersWithViews().AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver =new DefaultContractResolver());
        }

After Setting Serializer, Next, let’s read selected value after posting form for doing that we need to add another action with similar action method Name Index, but this will handle Http POST request.

 [HttpPost]
 public IActionResult Index(ProductViewModel productViewModel)
 {
     var selectedValue = productViewModel.ProductId;
     return View(productViewModel);
 }

Let’s run the application and select value from the dropdown list and submit the form to see we are able to read the value of the selected item of a dropdown list.