CODEWITHSUNDEEP
asp.netsql-serverentity-frameworksearch
Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

ASP.net Searching from Database

I am developing an app in which i need to give searching option to user like if user enters 'scienc' the search functions should get the courses with name science or if in their description science like word occurs, i mean to say if user enters half word query should get full word by matching characters sequence but i don't know how to implement this and i am using Entity Framework in my application.

One thing more i need to search from multiple tables.

Thanks in advance.

Upvotes: 2

Views: 1185

Answers (1)

Ram Singh
Ram Singh

Reputation: 6918

i think you want to implement AUto complete if so then following code will solved out your problem:

Code file:

 [WebMethod(EnableSession = true)]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public string[] GetPatientLastName(string prefix)
{
    List<string> customers = new List<string>();
    using (SqlConnection conn = new SqlConnection())
    {
        string connectionstring = CCMMUtility.GetCacheForWholeApplication();
        conn.ConnectionString = connectionstring;
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "select distinct top(10) PatientLastname from tblMessage where  " +
            "PatientLastname  like '%'+ @SearchText + '%' order by PatientLastname";
            cmd.Parameters.AddWithValue("@SearchText", prefix);
            cmd.Connection = conn;
            conn.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    customers.Add(string.Format("{0}", sdr["PatientLastname"]));
                }
            }
            conn.Close();
        }
        return customers.ToArray();
    }
}

Jquery code:

 $(document).ready(function () {
        $('[ID$=txtPatientLastname]').live('keyup.autocomplete', function () {

            $(this).autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: '<%=ResolveUrl("~/Resources/WebService.asmx/GetPatientLastName") %>',
                        data: "{ 'prefix': '" + request.term + "'}",
                        dataType: "json",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item.split('-')[0],
                                    val: item.split('-')[1]
                                }
                            }))
                        },
                        error: function (response) {
                            alert(response.responseText);
                        },
                        failure: function (response) {
                            alert(response.responseText);
                        }
                    });
                },
                select: function (e, i) {
                },
                minLength: 1
            });
        });
});

Hope this will fulfill your requirement.

Upvotes: 1

Related Questions