Video Tutorial:
Download Full Source Code:
Download here
Introduction:
Here I will explain how to create autocomplete
textbox using Jquery from database in Asp.net MVC. In this example we connect
this textbox with database and show result with relevant result.
Description:
In previous articles I will explain How
to bind Country, State, City Dropdown list in mvc using ajax,
How
to show database values using Dataset in table with Edit and delete operation
using Asp.net MVC, How to bind dropdown in mvc using ajax, How to login with Facebook in
asp.net mvc, How
to upload multiple image or files in asp.net mvc with Source Code. ,Retrieve
data from database in asp.net web api,How
to login with gmail in Asp.net MVC , How to add controls dynamically in asp.net mvc using
javascript with
relevant example
So now we
start.
Step1. Firstly create Asp.net MVC empty
project and create fallowing table in database
CREATE TABLE
[dbo].[tbl_registration] (
[Sr_no]
INT IDENTITY (1,
1) NOT NULL,
[Email]
NVARCHAR (100) NULL,
[Password] NVARCHAR (MAX) NULL,
[Name]
VARCHAR (MAX) NULL,
[Address]
NVARCHAR (MAX) NULL,
[City]
NVARCHAR (MAX) NULL,
CONSTRAINT
[PK_tbl_registration] PRIMARY KEY CLUSTERED
([Sr_no] ASC)
);
Step2: Now we add
class file in database access layer
folder for communication with database as fallows
Step3: In this
step we add connection string under configuration section in web.config file
<connectionStrings>
<add name="con" connectionString="Data
Source=.;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Step4: Now
we write fallowing code in db.cs class under database_Access_layer folder
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Web;
using
System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
namespace
AutocompleteMVC.database_Access_Layer
{
public class db
{
SqlConnection
con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
public DataSet
GetName(string prefix)
{
SqlCommand
com = new SqlCommand("Select
* from tbl_registration where Name like '%'+@prefix+'%'",
con);
com.Parameters.AddWithValue("@prefix",
prefix);
DataSet
ds = new DataSet();
SqlDataAdapter
da = new SqlDataAdapter(com);
da.Fill(ds);
return
ds;
}
}
}
Step5: We create fallowing class in
model for assign data to properties
public class search
{
public string Sr_no { get;
set; }
public string
Name { get; set;
}
}
Step6: Now go to the HomeController.cs class
and write fallowing method
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Web;
using
System.Web.Mvc;
using
System.Data;
using
System.Data.SqlClient;
using
AutocompleteMVC.Models;
namespace
AutocompleteMVC.Controllers
{
public class HomeController
: Controller
{
//
// GET: /Home/
database_Access_Layer.db
dblayer = new database_Access_Layer.db();
public ActionResult
Index()
{
return
View();
}
public JsonResult
GetRecord(string prefix)
{
DataSet
ds = dblayer.GetName(prefix);
List<search>
searchlist = new List<search>();
foreach
(DataRow dr in
ds.Tables[0].Rows)
{
searchlist.Add(new search
{
Name = dr["Name"].ToString(),
Sr_no = dr["Sr_no"].ToString()
});
}
return
Json(searchlist, JsonRequestBehavior.AllowGet);
}
}
}
Step7: Now write fallowing html and javascript in
Index.cshtml view
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<script>
$(document).ready(function
() {
$("#CityName").autocomplete({
source: function
(request,response) {
$.ajax({
url: "/Home/GetRecord",
type: "POST",
dataType: "json",
data: { prefix:
request.term },
success: function
(data) {
response($.map(data, function
(item) {
return
{ label: item.Name, value: item.Name };
}))
}
})
},
});
});
</script>
</head>
<body>
<div>
<input type="text" id="CityName" style="width:200px;" />
</div>
</body>
</html>
Step8: Now run Application and get fallowing
output
Thanks….
