How can we integrate jQuery autocomplete using asp.net, webservice and sql database?

Question

I am trying to implement the code given for "jQuery Autocomplete and ASP.NET", but unable to integrate it because you are using subsonic to query database.

So can you tell me how to query sqldatabase and bind the query result to the plugin from webservice in asp.net using C#?

score 2 · Answer 1 · answered May 27 '09 at 18:54

This is a pretty easy task, the catch is that the jQuery autocomplete extender expects an array of values. Here is example of how I parse the standard XML results from a ASMX web serivce to use with the jQuery autocomplete extender.

Since ASP.NET likes to rewrite your ID's, you can pass in the ClientID to get the dynamic ID.

    $("#<%= TextBox1.ClientID %>").autocomplete("/Demo/WebSvc.asmx/SuggestCustomers", {
        parse: function(data) {
            var parsed = [];

            $(data).find("string").each(function() {
                parsed[parsed.length] = {
                    data: [$(this).text()],
                    value: $(this).text(),
                    result: [$(this).text()]
                };
            });
            return parsed;
        },
        dataType: "xml"
    });

Here is what the associated web service would look like, remember to uncomment the [ScriptService] attribute on the web service:

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
public class WebSvc: WebService
{
    [WebMethod]
    public string[] SuggestedCustomers(string q)
    {
        // Do Query

        // Add items into string array
        List<string> items = new List<string>();
        while (dr.Read())
        {
            items.Add(dr[0].ToString());
        }

        // Return array
        return items.ToArray();
    }

}

@Zachary: You forgot the formatItem: override to handle the new array you defined in the parse: override. @Everyone: Zachary does excellent by explaining how you can format the results into your own array object ("parsed[]" as mentioned above). But, now that you have an array object being passed arround, you need to handle the display of it. Override the formatItem: method as well. formatItem: function(row, i, n) { return row.result }. You may also want to override the root autocompleter.result() method for special handling of the "Selected" action item. — eduncan911, Jul 11 '09 at 15:54

score -1 · Answer 2 · answered Jan 19 '09 at 10:52

I am not fluent in asp.net but fundamentally like most web coding questions this involves breaking your problem into smaller ones.

From an architectural perspective your components might include the following...

a service layer that potentially uses your db etc to answer or produce a result for your query.
a web component or service entry point that uses the completed service mentioned above to return the data in a format the browesrr can easily understand - eg JSON.
some javascript using jquery which invokes the end point define in the immediate point above.
write unit tests for all the above components - don't forget to test failure cases because as we all know software sometimes fails ...

How can we integrate jQuery autocomplete using asp.net, webservice and sql database?

2 Answers2

Linked