TimK TimK - 3 months ago 19
ASP.NET (C#) Question

jquery UI autocomplete list from SQL Server query in cshtml

I am following this jquery ui autocomplete template (https://jqueryui.com/autocomplete/#default) but trying to pull my data for the autocomplete from a SQL Server db query.

It looks like when I get to my javascript I need my availableTags javascript array to have the form ["abc", "def", "ghi",..."] based on what is in the jquery ui demo source code.

var availableTags = [
"ActionScript",
"AppleScript",
"Asp",
"BASIC",
"..."];


My code currently gets me a list like this: abc, def, ghi,... . Actually I'm not even sure if it shows up as {abc, def, ghi,...} or [abc, def, ghi...] when I pass it to the javascript variable.

Here is my code to get the list from my db

@{
List<string> availableTags = new List<string>();
foreach (var item in db.Query("SELECT Tag FROM my_tags_table"))
{
var Tag = item.Tag ;
availableTags.Add(Tag);
string tagString = (string.Join(", ", availableTags.Select(x => x.ToString()).ToArray()));
}
}


debugging shows that tagString is created as a list abc, def, ghi, ... but as I mentioned I need to add double quotes in there. Here is how I pass the tagString to a javascript array/variable.

<script>
$(function () {
var availableTags = '<%=tagString%>';
$("#tags").autocomplete({
source: availableTags
});
});
</script>


Then here's my input box.

<div class="ui-widget">
<input id="tags">
</div>


Can someone help me 1) add the double quotes in and 2) do anything else that is required to availableTags to show up as a proper javascript array?

Answer

jQuery ui autocomplete needs the source property value as an array. string.Join method is going to return a string value separated by commas like Item1, Item2, Item3.

What you need is an array.

@{
  List<string> availableTags = new List<string>();
  foreach (var item in db.Query("SELECT Tag FROM my_tags_table"))
  {
    var Tag = item.Tag ;
    availableTags.Add(Tag);       
  }
 }

and in your script section in the same view, you can convert this c# varibale (availableTags list) to a javascript array and use that.

$(function () {

    var availableTags = @Html.Raw(Newtonsoft.Json.JsonConvert
                                                .SerializeObject(availableTags.ToArray()));
    $("#tags").autocomplete({ source: availableTags });
});
Comments