Populate dropdownlist asynchronously...

Question

I have a page that will populate two dropdownlist by loading items from SQL Server during Page_Load event, so, when opening the page it may take some time to load. Currently, the dropdownlist will load asynchronously but it still will slow down the page loading. How can i load those dropdownlist after the page is completely loaded?

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
   If Not IsPostBack Then
      LoadDDL()
   End If
End Sub

Public Sub LoadDDL()
    Try
        Dim cnnStr As String = DBMgr1.asyncADOCnnStr("SQLDS")


        '===== Load Scheme DDL =====
        Dim cnnScheme As SqlConnection = New SqlConnection(cnnStr)
        cnnScheme.Open()

        Dim SchemeStr As String = "SELECT " & _
                                    "CASE CODE " & _
                                        "WHEN 'P-PFC' THEN 'PFC' " & _
                                        "ELSE CODE " & _
                                    "END AS [SchemeCode], " & _
                                    "CASE DESCR " & _
                                        "WHEN 'P-PFC' THEN 'P-PFC/C-PFC' " & _
                                        "ELSE CODE " & _
                                    "END AS [SchemeName] " & _
                                "FROM i_Library WHERE MODULE = 'SYS' AND TYPE = 'Schemes' AND Stat1 = 'ACT' " & _
                                "AND Code <> 'C-PFC' " & _
                                "ORDER BY DESCR DESC"

        Dim cmdScheme As New SqlCommand(SchemeStr, cnnScheme)
        cmdScheme.CommandType = CommandType.Text
        Dim arScheme As IAsyncResult = cmdScheme.BeginExecuteReader()
        '===== Load Scheme DDL =====


        '===== Load Branch DDL =====
        Dim cnnBranch As SqlConnection = New SqlConnection(cnnStr)
        cnnBranch.Open()

        Dim BranchStr As String = "" & _
                                    "SELECT Distinct TeamCode " & _
                                    "FROM v_staff " & _
                                    "ORDER BY TeamCode " & _
                                    " "
        Dim cmdBranch As New SqlCommand(BranchStr, cnnBranch)
        cmdBranch.CommandType = CommandType.Text
        Dim arBranch As IAsyncResult = cmdBranch.BeginExecuteReader()
        '===== Load Branch DDL =====


        '===== Load Region Code DDL =====
        Dim cnnRegion As SqlConnection = New SqlConnection(cnnStr)
        cnnRegion.Open()

        Dim RegionStr As String = "" & _
                                    "SELECT Distinct RegionCode " & _
                                    "FROM v_staff " & _
                                    "WHERE RegionCode IS NOT NULL " & _
                                    "ORDER BY RegionCode " & _
                                    " "
        Dim cmdRegion As New SqlCommand(RegionStr, cnnRegion)
        cmdRegion.CommandType = CommandType.Text
        Dim arRegion As IAsyncResult = cmdRegion.BeginExecuteReader()
        '===== Load Region Code DDL =====


        'wait for the Scheme Query to return result
        arScheme.AsyncWaitHandle.WaitOne()
        Dim drScheme As SqlDataReader = cmdScheme.EndExecuteReader(arScheme)
        While drScheme.Read
            ddlScheme.DataSource = drScheme
            ddlScheme.DataTextField = "SchemeName"
            ddlScheme.DataValueField = "SchemeCode"
            ddlScheme.DataBind()

            ddlScheme.Items.Insert(0, "Select Scheme")
            ddlScheme.Items(0).Value = CMM.sExcVal1
        End While

        'wait for the Branch Query to return result
        arBranch.AsyncWaitHandle.WaitOne()
        Dim drBranch As SqlDataReader = cmdBranch.EndExecuteReader(arBranch)
        While drBranch.Read
            ddlBranch.DataSource = drBranch
            ddlBranch.DataTextField = "TeamCode"
            ddlBranch.DataValueField = "TeamCode"
            ddlBranch.DataBind()

            ddlBranch.Items.Insert(0, "Select Branch")
            ddlBranch.Items(0).Value = CMM.sExcVal1
        End While

        'wait for the Region Query to return result
        arRegion.AsyncWaitHandle.WaitOne()
        Dim drRegion As SqlDataReader = cmdRegion.EndExecuteReader(arRegion)
        While drRegion.Read
            ddlRegionCode.DataSource = drRegion
            ddlRegionCode.DataTextField = "RegionCode"
            ddlRegionCode.DataValueField = "RegionCode"
            ddlRegionCode.DataBind()

            ddlRegionCode.Items.Insert(0, "Select Region")
            ddlRegionCode.Items(0).Value = CMM.sExcVal1
        End While

        drScheme.Close()
        drBranch.Close()
        drRegion.Close()
        cnnScheme.Close()
        cnnBranch.Close()
        cnnRegion.Close()

    Catch ex As Exception
        MSGMgr.errHandlerSys(ex.Message, lblMsg)
    End Try
End Sub

Answer 1

perhaps use an ajax method?

Answer 2

I had to do this to get it to work.

First, in the .aspx file, I put a hidden input that had the correct id of the element I wanted to access from javascript:

<input type='hidden' id='dropdownelem1ID' value='<%= DropdownElem1.ClientID %>' />

Then, as MikeW pointed out, just make an ajax call, preferably using jQuery, and fill in the results.

In order to get the correct element I would use something like this:

$('#' + $('#dropdownelem1ID').val())

Answer 3

Are the contents of the drop-downs the same for all users, at least for a while? If so, you could cache the generated HTML in a user control.

Otherwise, in addition to Ajax / jQuery, you can also use Silverlight to issue a WCF call to retrieve the contents of the drop-downs and write them into the DOM.

Answer 4

in your page, write this js method, it takes a parent and a child dropdown and it will fill the child dropdown asynchronously based on the parent dropdown's selected value:

  function LoadDataHandle(cmbParent,cmbChild,Type) {
            var cmb = document.getElementById(cmbParent);
            var mydate = new Date()
            mydate.setDate(mydate.getDate())

            $.get("GetItems.aspx?ID=" + cmb.value + "&Type=" + Type + "&x=" + mydate.getMilliseconds(), function(data) {
            LoadData(data, cmbChild);
        });

    }

function LoadData(strData, cmbChild) {
    var cmb = document.getElementById(cmbChild);
    cmb.options.length = 0;
    var choose = document.createElement('option');
    choose.text = "--- اختر ---";
    choose.value = -1;
    cmb.add(choose);
    if (strData.length > 0) {
        var data = strData.split('$');
        var i = 0;
        for (i = 0; i < data.length - 1; i++) {
            var data1 = data[i].split('#');
            var item = document.createElement('option');
            item.value = data1[0];
            item.text = data1[1];
            cmb.add(item);
        }
    }
}

note that use of jqyery's $get method passing in the aspx page having the parentid in the query string.

now in your page_load event of the aspx page containing the dropdowns

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    cmdParent.Attributes.Add("onchange", "LoadDataHandle('" & cmdParent.ClientID & "','" & cmdChild.ClientID & "');")
End Sub

we are almost done, note that in the GetItems.aspx age you will need to write out the items that should be filled in the child dropdown items as a single long string where each item is separated by a '#'. here is a sample of doing this :

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim ID As Integer = Request.QueryString("ID")
Response.Write(GetDepartment(ID))
    End Sub

        Private Function GetDepartment(ByVal ID As String) As String
            Dim dt As DataTable = 'get from db
            If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
                Dim sb As New StringBuilder(dt.Rows.Count * 20)
                For Each row As DataRow In dt.Rows
                    sb.AppendFormat("{0}#{1}$", row(0), row(1))
                Next
                Return sb.ToString
            End If
            Return ""
        End Function