Display a sql query output in jquery autocomplete

480 views Asked by At

This is my view where I have used jquery autocomplete control.

<script type="text/javascript">
  $(function() {
    var availableTags = [
      "ActionScript",
      "AppleScript",
      "Asp",
      "BASIC",
      "C",
      "C++",
      "Clojure",
      "COBOL",
      "ColdFusion",
      "Erlang",
      "Fortran",
      "Groovy",
      "Haskell",
      "Java",
      "JavaScript",
      "Lisp",
      "Perl",
      "PHP",
      "Python",
      "Ruby",
      "Scala",
      "Scheme"
    ];
    $( "#tags" ).autocomplete({
      source: availableTags
    });
  });
  </script>
<div>
  <input id="tags">
</div>

Here I have displayed the autocomplete options manually. But I have to display only BrandNames from the following sql query.

select BrandName from BrandMaster order by BrandName

I have to do this in MVC4. Please help. As I am a beginner please do not vote me down if I have mistaken anywhere asking the question.

This is my controller.

public class HomeController : Controller
    {
        string connString = "Data Source=DEVELOPER1;Initial Catalog=FR8DemoDB;Integrated Security=True";
        string commString = "";

        public ActionResult Index()
        {
            List<Products> listTemp = new List<Products>();

            List<string> list = new List<string>();
            commString = "select BrandName from BrandMaster order by BrandName";
            DataTable dataTable = GetDataTable(commString);

            foreach (DataRow item in dataTable.Rows)
            {
                listTemp.Add(new Products()
                {
                    BrandName = item["BrandName"].ToString()
                });
            }

            return View(listTemp);
        }

        public DataTable GetDataTable(string commString)
        {
            DataTable dataTable = new DataTable();

            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(commString, conn))
                {
                    SqlDataReader sqlDataReader = comm.ExecuteReader();
                    dataTable.Load(sqlDataReader);
                }
            }
            return dataTable;
        }
    }

    public class Products
    {
        public string BrandName { get; set; }
    }
}
1

There are 1 answers

5
Arijit Mukherjee On

View

@Html.TextBox("searchid", "", new {@placeholder = "Search Store..."})

Controller

    public JsonResult Search(string term)
    {
        var ent = new Entities();
        var Stores = ent.BrandMaster.Where(x=>x.BrandName.Contains(term)).OrderBy(x=>x.BrandName).ToList();
        return Json(Stores, JsonRequestBehavior.AllowGet);
    }

JQUERY

$(document).ready(function () {
        $("#searchid").autocomplete({
            source: function (request, response) {
                $.ajax({
                    url: "/Menu/Search",
                    type: "get",
                    dataType: "json",
                    data: { term: request.term },
                    success: function(data) {
                        response($.map(data, function(item) {
                            return { id: item.Id, value: item.BrandName };
                            })
                        );
                    }

                });
            },
            select: function (event, ui) {
                window.location = '/Home/ActionName/'+ ui.item.id ;
                }

        });
    })

DEMO