Mvc4 AutoComplete TextBox Using JQuery And data from sql database

AutoComplete TextBox in Mvc4 Using Sql Databse and JQuery

Introduction : In this tutorial explain how to work autocomplete textbox or searchbox in mvc4 using asp.net C#. here data can be bind from the sql database and JQery Plugin can be need to add because autocomplete textbox can need to JQuery Plugin. In below Each Step are explain clearly to display searchbox.

Tools : Visual Studio 2012 and Sql server database

Download Complete code : Download

Step 1 : First you Need to Create Mvc4 WebApplication Project

Step 2 : Then after you can create database and table as following

Step 3 : Now you can add the Model class and give name DAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace MvcAutoComplete.Models
{
    public class DAL
    {
        public int id { get; set; }
        public string Name{ get; set; }
        public string City { get; set; }
        public string Education { get; set; }

        SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\Chetan_program\MvcAutoComplete\MvcAutoComplete\App_Data\Database1.mdf;Integrated Security=True");

        public List<DAL> Select_data()
        {
            List<DAL> D = new List<DAL>();
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from tbl_user", con);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DAL da = new DAL();
                da.id = Convert.ToInt32(dr["id"].ToString());
                da.Name = dr["Name"].ToString();
                da.City = dr["City"].ToString();
                da.Education = dr["Education"].ToString();
                D.Add(da);
            }
            con.Close();
            return D;
        }
        public List<string> SearchString(string name)
        {
              return this.Select_data().Where(p => p.Name.StartsWith(name, StringComparison.OrdinalIgnoreCase)).Select(p => p.Name).ToList();
        }

    }

}

Step 4 : Now add Controller and give name like ABC and here need to add Namespace as following

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcAutoComplete.Models;
namespace MvcAutoComplete.Controllers
{
    public class ABCController : Controller
    {
        //
        // GET: /ABC/

        public ActionResult Index()
        {
            return View();
        }
        public ActionResult Search(string term)
        {
            DAL da = new DAL();
            return Json(da.SearchString(term), JsonRequestBehavior.AllowGet);
        }

    }
}

Step 5 : Now You can Create view and add the following jquery plugin in the head tag. whithuot the Jquery
Plugin Your Autocomplete Textbox can not be display.

@model MvcAutoComplete.Models.DAL

@{
    Layout = null;
}

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>

    <link href="@Url.Content("~/Content/")themes/base/jquery.ui.all.css" rel="stylesheet" />
    <script src="@Url.Content("~/Content/")themes/base/jquery-1.8.2.js"></script>
     <script src="@Url.Content("~/Content/")themes/base/jquery-ui-1.8.24.js"></script>

    <script type="text/ecmascript">
          $(document).ready(function () {
              $('#txtname').autocomplete({
                  source: '@Url.Action("Search","ABC")'

              });

          });
    </script>
</head>
<body>
    Name : <input type="text" id="txtname" />
</body>
</html>

Now you can go to app_start Folder and add controller information in RouteConfig.cs as following.