In previous article, I have mentioned using jQuery AJAX in ASP.NET Core MVC but now in this article, I have mentioned step by step procedure to create autocomplete textbox using jquery in ASP.NET Core MVC with database values.
Step 1: Create a new ASP.NET Core MVC (Model View Controller) project in Visual Studio, I am using VS 2019 with .NET 5 Version, also we will be using AdventureWorks2012 database, which you can download and install locally.
Step 2: Install Nuget packages for 'Microsoft.AspNetCore.Mvc.NewtonsoftJson' to return JSON from controller and 'Microsoft.EntityFrameworkCore.SqlServer' to connect with SQL Server database using EF Core.
You can open Nuget Package manager by navigating to "Tools" -> "Nuget Package Manager" -> "Manage Nuget package for solution" -> Search for 'Microsoft.AspNetCore.Mvc.NewtonsoftJson' and Install 'Microsoft.AspNetCore.Mvc.NewtonsoftJson Version 5' if you are using .NET Core 5 (depening on your .NET core version, I am installing Version 5 since I am using .NET 5)
Similarly, you need to install 'Microsoft.EntityFrameworkCore.SqlServer' package.
Step 3: Once you have installed above NuGet Packages, you can add database connection string appsettings.json file.
"ConnectionStrings": {
"MyConnection": "Data Source=VKS-PC-NEW\\SQLEXPRESS;Initial Catalog=AdventureWorks2012;Integrated security=true"
}Now, navigate to Startup.cs -> ConfigureServices method to add below C# Code to use Database
string conStr = this.Configuration.GetConnectionString("MyConnection");
services.AddDbContext<DBcontext>(options => options.UseSqlServer(conStr));so it would look like
public void ConfigureServices(IServiceCollection services)
{
services.AddControllersWithViews();
string conStr = this.Configuration.GetConnectionString("MyConnection");
services.AddDbContext<DBcontext>(options => options.UseSqlServer(conStr));
}Step 4: Add DBcontext .cs file, in your project
using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations.Schema;
namespace jQueryDatatableASPNetCore
{
public class DBcontext : DbContext
{
public DBcontext(DbContextOptions<DBcontext> options) : base(options)
{
}
public DbSet<Person> Person { get; set; }
}
[Table("Person", Schema = "Person")] // to refer table Person.Person in AdventureWorks2012 DB
[Keyless] //as not loading PrimaryKey for this table
public class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
}
}In the above code, we are creating a Dbcontext class, which will help us connect to database.
We are also creating "Person" class (which in generally should be seperate file, but we are using it in same file for example purpose).
Since, we will be using AdventureWorks2012 database table Person.Person in this example, we have mentioned it using
[Table("Person", Schema = "Person")]code as DataAnnotations above Person Class.
Step 5: Now, we will need to create C# code in Controller to fetch data from the database using DbContext class and pass it as JSON to create jquery Autocomplete input.
using Microsoft.AspNetCore.Mvc;
using System;
using System.Linq;
namespace jQueryDatatableASPNetCore.Controllers
{
public class HomeController : Controller
{
private DBcontext Context { get; }
public HomeController(DBcontext _context)
{
this.Context = _context;
}
public IActionResult Index()
{
return View();
}
//called for autocomplete, using ajax
[HttpPost]
public JsonResult AutoComplete(string prefix)
{
var persons = (from person in this.Context.Person
where person.FirstName.StartsWith(prefix)
select new
{
label = person.FirstName,
val = person.FirstName
}).Take(5).ToList();
return Json(persons);
}
// post selected personName
[HttpPost]
public ActionResult Index(string personName)
{
ViewBag.Message = "Selected Person Name: " +personName;
return View();
}
}
}Step 6: Create Input textbox for autocomplete with jQuery UI reference and also jQuery autocomplete function call
So, for "Index.cshtml" inside "Home" folder view
@{
ViewData["Title"] = "Home Page";
}
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/start/jquery-ui.css">
@using (Html.BeginForm("Index", "Home", FormMethod.Post))
{
<input type="text" id="textPerson" name="personName" />
<br />
<input type="submit" id="btnSubmit" value="Submit" />
<br />
<br />
@ViewBag.Message
}
@section scripts{
<script src="https://code.jquery.com/ui/1.13.1/jquery-ui.min.js" integrity="sha256-eTyxS0rkjpLEo16uXTS0uVCS4815lc40K2iVpWDvdSY=" crossorigin="anonymous"></script>
<script type="text/javascript">
$(document).ready(function () {
$("#textPerson").autocomplete({
source: function (request, response) {
$.ajax({
url: '/Home/AutoComplete/',
data: { "prefix": request.term },
type: "POST",
success: function (data) {
response($.map(data, function (item) {
return item;
}))
},
error: function (response) {
alert(response.responseText);
},
failure: function (response) {
alert(response.responseText);
}
});
},
select: function (e, i) {
//get selected value
//$("#personNameValue").val(i.item.val);
},
minLength: 1
});
});
</script>
}in the above code, we are using "_Layout.cshtml" as Layout page which as jQuery reference, but we have added jQuery UI reference CSS and JS files in the above .cshtml page.
The jQuery AutoComplete plugin has been applied to the TextBox and the URL('/home/autocomplete') of the plugin is set to the AutoComplete Action method.
We have also enclosed input textbox in HTML.BeginForm, which we are using to send value to controller, to check which value was selected.
Once you will build and run project, you will see output
That's it, hope it helped.
You may also like to read:
Autocomplete using jQuery in ASP.NET MVC
Autocomplete Textbox using Javascript (Multiple Methods)
Buy us a coffee
Become a Patron