CODEWITHSUNDEEP
c#jqueryasp.netdrop-down-menu
user1046415
user1046415

Reputation: 789

asp.net dropdownlist caching for Huge data

I have to bind nearly 50k records to my asp.net dropdownlist and it has to be searchable. What is the best way to do implement it . Are there any caching techniques so that the list loads as we scroll?Appreciate the suggestions.

Please advise.

Upvotes: 4

Views: 5193

Answers (9)

PSK
PSK

Reputation: 17943

Definitely any Autocomplete implementation will work in your scenario.

Solution 1: Use Autocomplete select box

  • If you don’t want to waste bandwidth or want to support devices with low specification, you should go for AutoComplete with server side data fetching.
  • If you want high usability and don’t care about the bandwidth, you can use autocomplete with local data (Fetch 50k records one time and bind to the autocomplete). But to make sure that you don’t paint all these into DOM at a single time. You need to limit the records being shown at a particular time.

Solution 2 : Use select with virtualization

  • But if you want to give the best of the usability to your clients, you should go with a solution where your select box is virtualized and the data loaded into DOM on scrolling of the select box. By virtualization you are making sure that only those items are getting pushed to DOM which are being shown in the screen at that point of time.

    You can find a jQuery based virtual select here

    Similar implementation in React here

    enter image description here

Upvotes: 0

Krishan Dutt Sharma
Krishan Dutt Sharma

Reputation: 216

You can achieve this by use of web service.

first of all add following code in your aspx page.

<div>
    <input type="text" value="" id="tbCountries" />
</div>

Now, Create your Web service using following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;

[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.>WebService {
    [WebMethod]
    public List<string> ShowCountryList(string sLookUP)
    {
        List<string> lstCountries = new List<string>();

        string sConnString = "Data Source=DNA;Persist Security Info=False;" +
            "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=;Connect Timeout=30;";

        SqlConnection myConn = new SqlConnection(sConnString);
        SqlCommand objComm = new SqlCommand("SELECT CountryName FROM Country " + 
            "WHERE CountryName LIKE '%'+@LookUP+'%' ORDER BY CountryName", myConn);
        myConn.Open();

        objComm.Parameters.AddWithValue("@LookUP", sLookUP);
        SqlDataReader reader = objComm.ExecuteReader();

        while (reader.Read()) {
            lstCountries.Add(reader["CountryName"].ToString());
        }
        myConn.Close();  return lstCountries;
    }
}

Finally, Create jquery Method for bind Textbox with webservice,

<script>
    $(document).ready(function() {
        BindControls();
    });

    function BindControls() {
        $("#tbListOfCountries").autocomplete({
            source: function(request, response) {
                $.ajax({
                    url: "WebService.asmx/ShowCountryList",
                    data: "{ 'sLookUP': '" + request.term + "' }",
                    dataType: "json",
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    dataFilter: function(data) { return data; },
                    success: function(data) {
                        response($.map(data.d, function(item) {
                            return { value: item }
                        }))
                    },
                    error: function(XMLHttpRequest, textStatus, errorThrown) {
                        alert(textStatus);
                    }
                });
            },
            minLength: 1    // MINIMUM 1 CHARACTER TO START WITH.
        });
    }
</script>

Upvotes: 2

Bhuban Shrestha
Bhuban Shrestha

Reputation: 1324

I see two immediate solution for this.

  1. As other has suggested usign ajax to search related item as user types and show them.
  2. On page load get all your data and store them in javascript variable then you can perform search on that variable as user types and bind search result to dropdown list.

Similar to this one:

Upvotes: 0

Ali Soltani
Ali Soltani

Reputation: 9937

I had same problem like you and I used RadAutoCompleteBox. It has many client-side and server-side events that help you to handle various situations. It's very comfortable for ASP.NET projects.

Upvotes: 0

Kaptan
Kaptan

Reputation: 336

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
    <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>    

</head>
<body>
    <form id="form1" runat="server">
        <div class="ui-widget">
            <asp:TextBox ID="txtDepartment" runat="server"  ClientIDMode="Static" />            
        </div>
    </form>
    <script>       

        $(function () {

            $("[id$=txtDepartment]").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: "FetchDropdownList.aspx/GetDepartment",
                        data: "{'departmentName':'" + document.getElementById('txtDepartment').value + "'}",
                        dataType: "json",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        dataFilter: function (data) { return data; },
                        success: function (data) {
                            response($.map(data.d, function (item) {
                                return {
                                    value: item.Name
                                }
                            }))
                        },
                        error: function (XMLHttpRequest, textStatus, errorThrown) {
                            alert(textStatus);
                        }
                    });
                },
                minLength: 1
            });
        });        
    </script>
</body>
</html>

public class Department
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }

        private static List<Department> GetDepartment()
        {
            List<Department> departments = new List<Department>();
            for (int i = 0; i < 10000; i++)
            {
                Department department = new Department();
                department.Id = i;
                department.Name = "Department " + i.ToString();
                departments.Add(department);
            }
            return departments;
        }

        [WebMethod]
        public static List<Department> GetDepartment(string departmentName)
        {            
            int totalDepartments = GetDepartment().Count;
            List<Department> departments = GetDepartment().Where(d => d.Name.ToLower().StartsWith(departmentName.ToLower())).Take(20).ToList();

            return departments;
        }

Upvotes: 0

turdus-merula
turdus-merula

Reputation: 8854

In the backend, create a controller action (if you are using ASP.NET MVC) or a page method (if you're using ASP.NET Web Forms) that receives a searchTerm parameter and returns an array of the top (say, 100) results.

In the frontend, use a typeahead / autocomplete plugin such as this one. When the user sets the search term, perform an Ajax request against the backend and display the results. When performing the Ajax request, you can also enable and configure caching. No more optimization shall be needed.

Upvotes: 2

Jebin
Jebin

Reputation: 163

Use AutoComplete textbox and set datasource from remote API especially when working with huge dataset. This will avoid your application UI getting hanged on every character search.

Ref the Link: https://github.com/ghiden/angucomplete-alt

Upvotes: 1

user1753377
user1753377

Reputation:

depends on where the list items are coming from. if they are coming from a list or database just append them then use javascript to search the list.

Upvotes: 0

Adam
Adam

Reputation: 151

I'd recommend taking advantage of jQuery's autocomplete plugin:

https://jqueryui.com/autocomplete/

It's configurable and has autocomplete search out of the box. It can also consume your remote data source (though you might consider a paginated API response):

http://api.jqueryui.com/autocomplete/#option-source

Upvotes: 3

Related Questions