MVC Autocomplete Textbox Using JQuery

• Download source (RAR) - 5.3 MB
• Download source (ZIP) - 5.9 MB

Introduction

In this article, I am going to explain how to populate Autocomplete TextBox from database in MVC with JQuery.

It's useful when there are many rows, you can type part of the word in the text box, then it can offer all of the

words which are similar to it.

Background

AutoComplete widget provides suggestions while you type into TextBox.

We need a textbox in which when a user types the name of a country, it automatically shows suggestions as soon

as the user starts typing a word from database.

Database

Step 1 : Create a table in a database that stores the  country names. Use the following SQL Script to achieve this.

CREATE DATABASE MyDB;
CREATE TABLE [dbo].[tblCountry](
	    [CountryName] [nvarchar](200) NULL,
	    [ID] [int] IDENTITY(1,1) NOT NULL
        ) ON [PRIMARY];
        
insert into dbo.tblCountry(CountryName) values('Canada')
insert into dbo.tblCountry(CountryName) values('United States')
insert into dbo.tblCountry(CountryName) values('China')
insert into dbo.tblCountry(CountryName) values('Iran')
insert into dbo.tblCountry(CountryName) values('Brazil')

Using the Code

Step 2 : create a New Project ASP.NET MVC 4 Web Application

1. "Start", then "All Programs" and select "Microsoft Visual Studio 2013".
2. "File", then "New" and click "Project", then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click on OK.
3. Choose MVC empty application option and click on OK

Step 3 : Add Home controller

     1-Right click on controller folder

     2-Add controller called HomeController

     3-submit Add

Now open the HomeController.cs file and write the following code into the Home controller class to fetch and return Json type from controller to view  as in the following,

public ActionResult Index()
    {
        return View();
    }
    public JsonResult GetCountry(string countryName)
    {
        var cn = new SqlConnection();
        var ds = new DataSet();
        string strCn = ConfigurationManager.ConnectionStrings["ConnString"].ToString();
        cn.ConnectionString = strCn;
        var cmd = new SqlCommand
        {
            Connection = cn,
            CommandType = CommandType.Text,
            CommandText = "select CountryName from tblCountry
            Where CountryName like @myParameter and CountryName!=@myParameter2"
        };

        cmd.Parameters.AddWithValue("@myParameter", "%" + countryName + "%");
        cmd.Parameters.AddWithValue("@myParameter2",  countryName );

        try
        {
            cn.Open();
            cmd.ExecuteNonQuery();
            var da = new SqlDataAdapter(cmd);
            da.Fill(ds);
        }
        catch (Exception)
        {
        }
        finally
        {
            cn.Close();
        }
        DataTable dt = ds.Tables[0];


        var txtItems = (from DataRow row in dt.Rows
                        select row["CountryName"].ToString()
                            into dbValues
                            select dbValues.ToLower()).ToList();
        return Json(txtItems, JsonRequestBehavior.AllowGet);
    }

In the above code, I use Ado.net for fetch data from database but you can use other such as linq or hibernate   instead of ADO.NET , aslo my return type is Json.

Step 4 : Add Jquery reference There are many ways to add the reference of jQuery library into the our project. The following are some methods:

• Using NuGet package manager , you can install library and reference into the project
• Use CDN library provided by Microsoft, jQuery, Google or any other which requires active internet connection.
• Download jQuery files from jQuery official website and reference into the project.

In this example I will use jQuery CDN library.

Step 5: Create jQuery Ajax function to call controller JSON action method and invoke autocomplete function,

<script type="text/javascript">
    var textbox;
    var selectValue;
    $(function () {
        textbox = $("#txtCountrty");
        selectValue = $('ul#selectedValue');

        textbox.on("input", function () {
            getAutoComplete(textbox.val());
        });
    });
    function getAutoComplete(countryName) {
        var uri = "Home/GetCountry";
        $.getJSON(uri, { countryName: countryName })
       .done(function (data) {
           selectValue.html("");
           var count = 0;
           $.each(data, function (key, item) {
               //$('<option>').text(item).appendTo('#selectedValue');
               var li = $('<li/>').addClass('ui-menu-item').attr('role', 'menuitem')
                   .html("<a href='#' onclick=\"setText('" + item + "')\">" + item + "</a>")
                   .appendTo(selectValue);

               count++;
           });
       });
    }
    function setText(text) {
        textbox.val(text);
        getAutoComplete(text);
    }

</script>

To work above function don't forget to add the reference of the following jQuery CDN library as,

<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-2.0.3.min.js"></script>

Step 6 : right click on View() and Add view named index and put below code  into the view. After adding code necessary files and logic the Index.cshtml will look like the following,

@{
    ViewBag.Title = "Index";
}

<style>
    input[type="text"], select {
        width: 250px;
    }
    a:link, a:active, a:visited, a:hover {
        text-decoration: none;
        color: black;
    }
    /*--- DROPDOWN ---*/
    #nav ul {
        list-style: none;
        position: absolute;
        padding: 0;
        margin: 0;
        list-style-type: none;
    }
</style>
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-2.0.3.min.js"></script>
<script type="text/javascript">
    var textbox;
    var selectValue;

    $(function () {
        textbox = $("#txtCountrty");
        selectValue = $('ul#selectedValue');

        textbox.on("input", function () {
            getAutoComplete(textbox.val());
        });
    });
    function getAutoComplete(countryName) {
        var uri = "Home/GetCountry";
        $.getJSON(uri, { countryName: countryName })
       .done(function (data) {
           selectValue.html("");
           var count = 0;
           $.each(data, function (key, item) {
               //$('<option>').text(item).appendTo('#selectedValue');
               var li = $('<li/>').addClass('ui-menu-item').attr('role', 'menuitem')
                   .html("<a href='#' onclick=\"setText
                   ('" + item + "')\">" + item + "</a>")
                   .appendTo(selectValue);

               count++;
           });
       });
    }
    function setText(text) {
        textbox.val(text);
        getAutoComplete(text);
    }

</script>

<h2>MVC AutoComplete Sample</h2>

<div id="nav">
    <input type="text" id="txtCountrty"  />
    <ul id="selectedValue"></ul>
</div>

Step 7 : Finally, in webconfig in configuration tag, put your connection string:

<connectionStrings>
    <add name="ConnString" connectionString="Data Source=(local);
  Initial Catalog=MyDB; UID=sa; pwd=123; Integrated Security=True;"
  providerName="System.Data.SqlClient" />
</connectionStrings>

Now run the application and type any word then it will auto populate the records which exactly start with first word

Summary

I hope this article is useful for all readers. If you have a suggestion then please contact me.