JSON SQL executing twice on grid load

[sjkcwatsonParticipant]

I have implemented the grid controls in several places on my application and they are great. However, I noticed some performance issue and upon further looking using the query analyzer on my sql server, the sql for the main grid is executing twice for every load. I’m hoping that you can tell me if I have something set wrong or in the wrong place that is causing it to load twice.

url is set to the webservice

                    Response.Write("var url = 'WebServices/OriasWcfService.svc/GetSysAdministratorsList?EmpID=" & cs_EmpID & "&ShowRole=" & vShowRole & "&demSiteID=" & cs_workingSiteID & "&ShowModule=" & vShowModule & "';")

In the js, I set a gridsource to use the URL and then a dataadaptor to the gridsource and then the grid control “jpxgridSysAdministrators” to the dataAdaptor.

var removeSysAdministratorsUrl, 
    gridData, gridDataAdapter, gridSource, id, result, rowindex, key, rowId, themeSource, editrow,
    gridRowCount, index, item, label, value, mainGridDetail, container, rowSysValue;

var rowModule, rowSiteID, rowEmpID, rowRole, rowEmpName, rowID;

var selectedAddEmpID;

$(document).ready(function () {
    // array which keeps the indexes of the edited rows.            
    var editedRows = new Array();

    // prepare the data               
    var searchAdminsource = {
        datatype: "json",
        datafields: [
            { name: 'EmployeeID', type: 'string' },
            { name: 'LastName', type: 'string' },
            { name: 'FirstName', type: 'string' },
            { name: 'EmpSearchStr', type: 'string' }
        ],
        url: searchAdminsurl,
        id: 'EmployeeID',
        async: false
    };

    var searchAdmindataAdapter = new $.jqx.dataAdapter(searchAdminsource);

    // Create a jqxInput       
    $("#rFindEmployeeInput").jqxInput({
        source: searchAdmindataAdapter,
        placeHolder: "Enter name or employeeID",
        displayMember: "EmpSearchStr",
        valueMember: "EmployeeID",
        width: 200,
        height: 25
    });

    $("#rFindEmployeeInput").on('select', function (event) {
        if (event.args) {
            var item = event.args.item;
            if (item) {
                selectedAddEmpID = item.value;
            }
        }
    });
    // define source for main grid
    gridSource = {
        datatype: "json",
        datafields: [
            { name: 'AdministrationID', type: 'integer' },
            { name: 'EmployeeID', type: 'string' },
            { name: 'ShowModule', type: 'string' },
            { name: 'ShowRole', type: 'string' },
            { name: 'demSiteID', type: 'integer' },
            { name: 'LastName', type: 'string' },
            { name: 'FirstName', type: 'string' }
        ],
        cache: false,
        id: 'AdministrationID',
        url: url,
        async: false,
        pager: function (pagenum, pagesize, oldpagenum) {
            // callback called when a page or page size is changed.
        },
        deleterow: function (rowid, commit) {
            $.ajax({
                type: "POST",
                data: JSON.stringify({ AdministrationID: rowid }),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                url: "WebServices/OriasWcfService.svc/RemoveSysAdministrators",
                success: function (result) {
                    // update command is executed.
                    if (result.d == "success") { commit(true); }
                    else { commit(false);  }
                },
                error: function () {
                    // cancel changes.
                    commit(false);
                }
            });
        }
    };

    //Preparing the employee data for use by grid
    gridDataAdapter = new $.jqx.dataAdapter(gridSource, {
        contentType: 'application/json; charset=utf-8',
        autoBind: true,
        downloadComplete: function (data) {
            return data.d;
        },
        loadError: function (status, error) {
            alert("An error occurred with the jqxDataAdapter: " + String(error) + ".\nThe status is currently: " + String(status));
        }
    });

    // Determine if the user has permission to remove administrator, if so show icon, if not show blank space
    var RemoveRolecellrenderer = function (row, column, value, defaultHtml) {
        if (vIsAdmin == 'Y') {
            return '<div style="width: 100%"><img src="images/newicons/delete.png" title="' + RemoveTitle + '" style="margin-left: 25%;margin-top: 10%;" /></div>';
        }
        else {
            return '<div style="width: 100%">&nbsp;</div>';
        };
    };
 
    $("#rCancel").jqxButton({ theme: 'energyblue' });

    $("#rOK").jqxButton({ theme: 'energyblue' });
    $('#rOK').click(function () {
        // confirmed remove, so remove employee
        var rowID = $('#jpxgridSysAdministrators').jqxGrid('getrowid', editrow); //AdministrationID of current record
        var commit = $("#jpxgridSysAdministrators").jqxGrid('deleterow', rowID);
    });

    $("#aCancel").jqxButton({ theme: 'energyblue' });

    $("#aOK").jqxButton({ theme: 'energyblue' });
    $('#aOK').click(function () {
        // add user .. 
        $.ajax({
            type: "POST",
            data: JSON.stringify({ EmployeeID: selectedAddEmpID, mModule: vShowModule, Role: vShowRole, demSiteID: vdemSiteID }),
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            url: "WebServices/OriasWcfService.svc/AddSysAdministrators",
            success: function (result) {
                // update command is executed.
                if (result.d != "success") {alert('Error adding employee'); }
            },
            error: function () {
                // cancel changes.
                alert('Error adding employee');
            }
        });

        $("#popupAddUserWindow").jqxWindow('hide'); //hide the pop up window
        $('#jpxgridSysAdministrators').jqxGrid('updatebounddata'); //this causes the data to refresh so that the newly added record shows up and can be edited
    });

    // initialize the popup window and buttons.           
    $("#popupRemoveUserWindow").jqxWindow({
        theme: 'oriasmenu',
        width: 630,
        height: 150,
        resizable: false,
        isModal: true,
        autoOpen: false,
        cancelButton: $("#rCancel"),
        okButton: $("#rOK"),
        modalOpacity: 0.2
    });

    // initialize the popup window and buttons.           
    $("#popupAddUserWindow").jqxWindow({
        theme: 'oriasmenu',
        width: 630,
        height: 150,
        resizable: false,
        isModal: true,
        autoOpen: false,
        cancelButton: $("#aCancel"),
        okButton: $("#aOK"),
        modalOpacity: 0.2
    });

    // Initialize the grid
    $("#jpxgridSysAdministrators").jqxGrid({
        theme: 'OriasBlue',
        width: '95%',
        source: gridDataAdapter,
        selectionmode: 'singlerow',
        sortable: true,
        pageable: true,
        autoheight: true,
        columnsresize: true,
        pagesize: 20,
        pagermode: 'default',
        showfilterrow: true,
        filterable: true,
        altrows: true,
        enabletooltips: false,
        editable:false,
        columns: [
            { text: "Module", hidden: 'true', width: '0', dataField: 'ShowModule', width: '10%', cellsalign: 'left', align: 'left' },
            { text: 'ID', hidden: 'true', dataField: 'AdministrationID', width: '0', cellsalign: 'left', align: 'left' },
            { text: 'ShowRole', hidden: 'true', width: '0', dataField: 'ShowRole', cellsalign: 'left', align: 'left' },
            { text: 'demSiteID', hidden: 'true', width: '0', dataField: 'demSiteID', cellsalign: 'left', align: 'left' },            
            { text: vEmployeeIDTitle, dataField: 'EmployeeID', cellsalign: 'left', align: 'left' },
            { text: vLastNameTitle, dataField: 'LastName', cellsalign: 'left', align: 'left' },
            { text: vFirstNameTitle, dataField: 'FirstName', cellsalign: 'left', align: 'left' },
            { text: '', width: '50', datafield: 'Remove', cellsrenderer:RemoveRolecellrenderer }
        ]
    });

    

    $("#jpxgridSysAdministrators").on("cellclick", function (event) {
        var column = event.args.column;
        var rowindex = event.args.rowindex;
        var columnindex = event.args.columnindex;
        if (columnindex == 7) { //Remove button column
            // open the popup window when the user clicks a button.       
            editrow = rowindex;
            // get the clicked row's data and initialize the input fields.           
            var dataRecord = $("#jpxgridSysAdministrators").jqxGrid('getrowdata', editrow);
            rowEmpID = dataRecord.EmployeeID;
            rowEmpName = dataRecord.LastName + ', ' + dataRecord.FirstName;

            if (vIsAdmin == 'Y') {
                //Allow Remove

                //To keep the popup window for updating status centered on the visible sreen and not just in relationship to the div it is in
                //GET WINDOW HEIGHT AND WIDTH
                var winHeight = $(window).height();
                var winWidth = $(window).width();

                //KEEP CENTERED
                var posX = (winWidth / 2) - ($('#popupRemoveUserWindow').width() / 2) + $(window).scrollLeft();
                var posY = (winHeight / 2) - ($('#popupRemoveUserWindow').height() / 2) + $(window).scrollTop();
                $('#popupRemoveUserWindow').jqxWindow({ position: { x: posX, y: posY } });
          
                document.getElementById('rFullName').innerHTML = rowEmpName;
                document.getElementById('rEmpID').innerHTML = rowEmpID;

                // show the popup window.          
                $("#popupRemoveUserWindow").jqxWindow('show');

            };
        }
    });

    $('#popupAddUserWindow').on('open', function (event) {
        // Have to put the focus and selectAll inside a timeout event per tech support
        setTimeout(function () {
            $("#rFindEmployeeInput").jqxInput('focus');
            $('#rFindEmployeeInput').val('');
            $("#rFindEmployeeInput").jqxInput('selectAll');
        }, 150);
    });

    $('#addadminbutton').jqxButton({ theme: 'energyblue' });
    $('#addadminbutton').click(function () {
        searchAdmindataAdapter.dataBind();  //forces refresh of the dataadaptor so that it shows the msot current list (ie if a user has been deleted then they should show as option to add

        //To keep the popup window for updating status centered on the visible sreen and not just in relationship to the div it is in
        //GET WINDOW HEIGHT AND WIDTH
        var winHeight = $(window).height();
        var winWidth = $(window).width();

        //KEEP CENTERED
        var posX = (winWidth / 2) - ($('#popupAddUserWindow').width() / 2) + $(window).scrollLeft();
        var posY = (winHeight / 2) - ($('#popupAddUserWindow').height() / 2) + $(window).scrollTop();
        $('#popupAddUserWindow').jqxWindow({ position: { x: posX, y: posY } });

        // show the popup window.          
        $("#popupAddUserWindow").jqxWindow('show');
    });
  
    $('#clearfilteringbutton').jqxButton({ theme: 'energyblue' });
    $('#clearfilteringbutton').click(function () {
        $("#jpxgridSysAdministrators").jqxGrid('clearfilters');
    });

    $("#excelExport").jqxButton({ theme: 'energyblue' });
    $("#excelExport").click(function () {
        $("#jpxgridSysAdministrators").jqxGrid('exportdata', 'xls', 'jqxgrid', true, null, true, 'JQWidgetsExport.ashx');
    });

});

In the query analyzer each time this page is loaded I see the following – it is executing the Stored procedure from the URL twice:
RPC Completed: exec sp_reset_connection
RPC Completed: exec x_ADM_Administrators_bySiteIDModuleRoleEmpID @EmpID=’977543′,@ShowRole=’Admin’,@demSiteID=’1′,@ShowModule=’Inspection’
RPC Completed: exec sp_reset_connection
RPC Completed: exec x_ADM_Administrators_bySiteIDModuleRoleEmpID @EmpID=’977543′,@ShowRole=’Admin’,@demSiteID=’1′,@ShowModule=’Inspection’

Any idea why?

Thanks!

[Peter StoevKeymaster]

Hi sjkcwatson,

autoBind: true is set in your code. This means that the adapter will automatically data bind. So, when you set the Grid’s source to point to the adapter, it will dataBind again.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[Author]

Posts