Want to Implement autocomplete function in my ASP.NET MVC project

Question

I have class namedclsUtilities and stored procedure named PhoneBook_SearchCustomers. I want to take FName and its respective Id. My DataTable has both Id and FName. Now want to convert DataTable that into list. List<object> ids = (from row in ss.AsEnumerable() select row["FName"]).ToList();. I do not have an idea how to getId into the same line so that i can pass this into my JavaScript.

My clsUtilities class

 public DataSet CreateCommandwithParams(string queryString, SqlParameter[] prms)
        {
            string mConnString = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(mConnString))
            {
                SqlCommand cmd = new SqlCommand(queryString, conn);
                cmd.CommandType = CommandType.Text;
                if (prms != null)
                {
                    foreach (SqlParameter p in prms)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                conn.Open();
                da.Fill(ds);
                return ds;
            }

        }

My Home Controller

[HttpPost]
        public JsonResult AutoComplete(string prefix)
        {
            clsUtilities cUtils = new clsUtilities();
            DataSet ds;
            SqlParameter[] prms = new SqlParameter[1];
            string sSQLName;
            sSQLName = " PhoneBook_SearchCustomers @FName";
            prms[0] = new SqlParameter("@FName", SqlDbType.VarChar);
            prms[0].Value = prefix;
            ds = cUtils.CreateCommandwithParams(sSQLName, prms);
           DataTable ss = ds.Tables[0];
           List<object> ids = (from row in ss.AsEnumerable() select row["FName"]).ToList();
           return Json(ids);

      }

My JavaScript

 $(function () {
        $("#txtCustomer").autocomplete({
            source: function (request, response) {
               // debugger;
                    $.ajax({
                    url: '/Home/AutoComplete/',
                    data: "{ 'prefix': '" + request.term + "'}",
                    dataType: "json",
                    type: "POST",
                    data: JSON.stringify({ prefix: request.term, count: 20 }),
                    contentType: "application/json; charset=utf-8",
                    success: function (data) {
                        response($.map(data, function (item) {
                           debugger;
                           return item;

                        }))
                    },
                    error: function (response) {
                        alert(response.responseText);
                    },
                    failure: function (response) {
                        alert(response.responseText);
                    }
                });
            },

            select: function (e, i) {
                debugger;
                $("#hfCustomer").val(i.item.val);
            },
            minLength: 1
        });
});

Please guide me how to get val value from controller. Now val is showing undefined. Thank you in advance.

Answer 1

Don't stringify JSON data before sending because you already precise dataType :

$("#name").autocomplete({
      source :function( request, response ) {
          $.ajax({
                  url: "/Home/AutoComplete/",
                  dataType: "json",
                  data: {
                      prefix: request.term
               },
               success: function(data) {
                   response($.map(data, function(item) {
                       return {
                           label : item.Value,
                           key: item.Key
                   };
               }));
             }
         });
     },
     select: function (event, ui) {
         $("#hfCustomer").val(ui.item.label);
         $("#hfCustomer").attr("id", ui.item.key);
         return false;
     }
});

And then on server side you can send datas (id and value) like :

[HttpPost]
public JsonResult AutoComplete(string prefix)
{
    clsUtilities cUtils = new clsUtilities();
    DataSet ds;
    SqlParameter[] prms = new SqlParameter[1];
    string sSQLName;
    sSQLName = " PhoneBook_SearchCustomers @FName";
    prms[0] = new SqlParameter("@FName", SqlDbType.VarChar);
    prms[0].Value = prefix;
    ds = cUtils.CreateCommandwithParams(sSQLName, prms);
    DataTable ss = ds.Tables[0];
    List<KeyValuePair<string, string> results = ss.Select(p=>new KeyValuePair<int, string>(int.Parse(p["FId"]), p["FName"]).ToList();
    return Json(results);
}

Answer 2

I changed JsonResult Autocomplete function like this and it worked for me now.

[HttpPost]
        public JsonResult AutoComplete(string prefix)
        {
            clsUtilities cUtils = new clsUtilities();
            DataSet ds;
            SqlParameter[] prms = new SqlParameter[1];
            string sSQLName;
            sSQLName = " PhoneBook_SearchCustomers @FName";
            prms[0] = new SqlParameter("@FName", SqlDbType.VarChar);
            prms[0].Value = prefix;
            ds = cUtils.CreateCommandwithParams(sSQLName, prms);
            DataTable ss = ds.Tables[0];
            List<Test> phoneBookList = new List<Test>();
            phoneBookList = (from DataRow dr in ss.Rows
                           select new Test()
                           {
                               FName = dr["FName"].ToString(),
                               Id = Convert.ToInt32(dr["Id"])

                           }).ToList();
            return Json(phoneBookList);
        }

And in JavaScript, few changes into Success and Select:

 $(function () {
        $("#txtCustomer").autocomplete({
            source: function (request, response) {
                    $.ajax({
                        url: '/Home/AutoComplete/',
                        data: "{ 'prefix': '" + request.term + "'}",
                        dataType: "json",
                        type: "POST",
                    contentType: "application/json; charset=utf-8",
                    success: function (data) {
                        response($.map(data, function (item) {
                           //debugger;
                            return {
                               value: item.FName,
                               id: item.Id
                            };
                   }))
                    },
                    error: function (response) {
                        alert(response.responseText);
                    },
                    failure: function (response) {
                        alert(response.responseText);
                    }
                });
            },
            select: function (event, ui) {
                //debugger;
                $("#hfCustomer").val(ui.item.value);
                $("#hfCustomer").val(ui.item.id);
            },
            minLength: 1
        });
});