Populate Nested jqGrid in ASP.Net Application

Here in this article i will explain how to populate the nested grid using jqGrid.

First of all we need to add the required jQuery and jqGrid js files to our project as listed in the image below. Also we need to add the style and images required for jqGrid plugin. 

then we need to reference them into our page like as below :-

Now we need to add the table into the page in which we need to populate the data :-

Now let us assume we have Persons list and their respective address details. Each person might have more than one address like Home address and Office address etc. Now we want to show the address associated with a person in the expanded grid or inner grid of the master one.

Here i have taken hard coded list of persons and addresses and they are associated with the 'PersonID' attribute.

//All person listvar objPersons = [            { PersonID: 1, Forename: 'Adam', Surname: 'Herman', DOB: '08/04/2014' },            { PersonID: 2, Forename: 'Adam', Surname: 'Berman', DOB: '08/04/2014' }        ];

//All addresses related to the above persons        var objAddress = [            { PersonID: 1, AddressID: 1, AddressType: 'Home', AddressLine1: 'Role', AddressLine2: '#32-36, EPIP Area', City: 'Rourkela, Odisha', Postcode: '769016' },            { PersonID: 1, AddressID: 2, AddressType: 'Office', AddressLine1: 'Role', AddressLine2: '#32-36, EPIP Area', City: 'Bangalore, Karnataka', Postcode: '560093' },            { PersonID: 2, AddressID: 3, AddressType: 'Optional', AddressLine1: 'Role', AddressLine2: 'Electronic City', City: 'Bangalore, Karnataka', Postcode: '560093' }        ];

Now we have to prepare the main method to bind all those result sets which will take the following parameters like :-
containerID - Container table id into which we will populate the data.
dataList - data to display(Here Persons List as JSON object array.)
pageSize - size of the grid per page if paging enabled.
columnName - column names as json formatted array.
columnModel - JSON formatted array list.
pagerId - If paging enabled the pager div element id.
gridHeight - Height of the grid in integer.
isPagerVisible - to view or hide pager.
pagerSize - pager size.
gridWidth - Width of the grid as integer.

        function BindResultData(containerId, dataList, pageSize, columnName, columnModel, pagerId, isScroller, gridHeight, isPagerVisible, pagerSize, gridWidth) {            if (isScroller == true && dataList.length > 5) {                pageSize = dataList.length;                gridHeight = 150;            }            jQuery('#' + containerId).GridUnload();            jQuery('#' + containerId).jqGrid({                data: dataList,                datatype: "local",                rowNum: pageSize,                colNames: columnName,                colModel: columnModel,                pager: '',                toppager: false,                autowidth: true,                gridview: true,                scrollerbar: isScroller,                height: gridHeight,                width: gridWidth,                cache: false,                loadonce: false,                rowList: [],                pgbuttons: false,                pgtext: null,                viewrecords: false,                recordtext: '',                subGrid: true,                subGridRowExpanded: function (subgridId, rowid) {                    var currentRow = $('#tblData').jqGrid('getRowData', rowid);                    var filteredAddresses = [];                    $.each(objAddress, function (index, item) {                        if (item.PersonID == currentRow.PersonID) {                            filteredAddresses.push(item);                        }                    });                    var addressColumnNames = ['PersonID', 'AddressID', 'AddressType', 'AddressLine1', 'AddressLine2', 'City', 'Postcode'];                    var addressColumnModel = [                                        { name: 'PersonID', index: 'PersonID', sortable: false, align: "center" },                                        { name: 'AddressID', index: 'AddressID', sortable: false, align: "center" },                                        { name: 'AddressType', index: 'AddressType', sortable: false, align: "center" },                                        { name: 'AddressLine1', index: 'AddressLine1', sortable: false, align: "center" },                                        { name: 'AddressLine2', index: 'AddressLine2', sortable: false, align: "center" },                                        { name: 'City', index: 'City', sortable: false, align: "center" },                                        { name: 'Postcode', index: 'Postcode', sortable: false, align: "center" },                    ];                    var subgridTableId = subgridId + "_t";                    $("#" + subgridId).html("<table id='" + subgridTableId + "'></table>");                    $("#" + subgridTableId).jqGrid({                        datatype: "local",                        data: filteredAddresses,                        colNames: addressColumnNames,                        colModel: addressColumnModel,                        height: "100%",                        rowNum: 10,                        sortname: "Product",                        idPrefix: "s_" + rowid + "_"                    });                },            });        }

Here in the above method the main section which is used for the nested grid creation is 'subGridRowExpanded' this callback function will be called on expanding the master grid row and it will again create and bind the inner grid. Here we need to manually create the inner grid ID and populate it as described in the above code example.

Finally we need to call this 'BindResultData' method with proper parameter values which will be taken care again by the below method 'PopulateData'. It will create the Column names array and column model array and pass it to the method 'BindResultData' for displaying in the grid with many other parameters which are already mentioned above.

        function PopulateData() {            var personColumnNames = ['PersonID', 'Forename', 'Surname', 'DOB'];
            var personColumnModel = [                                { name: 'PersonID', index: 'PersonID', sortable: false, align: "center" },                                { name: 'Forename', index: 'Forename', sortable: false, align: "center" },                                { name: 'Surname', index: 'Surname', sortable: false, align: "center" },                                { name: 'DOB', index: 'DOB', sortable: false, align: "center" },            ];
            BindResultData('tblData', objPersons, 10, personColumnNames, personColumnModel, 'tblDataPager', 500, 'auto', true, 10, 'auto');        }

Hope this makes sense to have a good start for the nested grid implementation using jqGrid. Also the working copy of this project with VS 2012 solution is being attached with code section of this article. Please download and go through its implementation.

CodeProject

Using stored procedures in Entity Framework Model First Approach (Part II - Updating an existing Stored procedure)

I have already explained how to add a new stored procedure to Entity Framework, now in this article I will explain if any changes made to a stored procedure in database then how can we go for updating it in Entity Framework so that the changes will reflect in the result. (changes include adding or removing the columns in stored procedure mainly)

Lets take the same stored procedure which we already added in the first part of this article named as 'GetAllPersons' which was having only 4 columns previously like ID, Name, Age, DOB and now i want to add a new column named as 'Department' which should also reflect in the result set.

Here are the below steps we may follow to get this work as expected. 

STEP I(Refresh the Model to get the new column added in the Person table in database):

STEP II(Refresh the model to get the new column returned by the stored procedure):

STEP III(Go for the EDIT mode of the Function import already in use):

STEP IV(Update the Function Import to get the new column):
           i) Click on the button 'Get Column Information' to get the information for the new               column added in stored procedure. 
           ii) Click on 'Update' button to update the Complex Type to add the new attribute.
           iii) Click on 'OK' button to apply and save the changes.

STEP V(Run the custom tool for reflect the changes in model classes):

Hope this will be helpful for them who are already started with using Entity Framework and just looking for a guide in using stored procedure in Entity Framework. CodeProject

Using stored procedures in Entity Framework Model First Approach (Part I - Adding New Stored procedure)

Before starting with this article I will expect that you have a basic idea on the Entity Framework - Model First approach and its use.

Here I am explaining the step by step approach how to use a newly created stored procedure in our C# code through the EntiFramework. Let say we have created a new procedure named as 'GetAllPersons', now we can follow the below steps :-

STEP I (Open EDMX file right click and choose the option 'Update Model from Database...') :

STEP II (Now in this below screen choose save entity connection string in Web.Config as the name given at the time of creation. Then click on NEXT.) : 

STEP III (Select 'Add' tab and there select the particular stored procedure you want to add to entity model) :

Unselect the option 'Pluralize or singularize object names' and click on FINISH button.

STEP IV(Rename the Entity added automatically.) :

Open the Model Browser now and we can see the 3 things added automatically in entity model like :-

StoredProcedure - The procedure we added recently.

Function Import - Function which actually will be called in code by the object of the entity context.

ComplexType - The entity returned by the SP or the Function generated.

These Function and ComplexType can be renamed as we want them.

Rename the ComplexType name as our feasibility :-

STEP V(Edit the Function Import as per requirement):

Open the function import in EDIT mode.

Rename the function and select appropriate Stored Procedure and ComplexType.

 After editing done :-

STEP VI(Finish & Run Custom Tool):

Now after all the changes are being done to make the latest change available in code we have to run the custom tool as mentioned in below image. This step is to update the classes from the model to avail latest changes made to it in code behind.

For more information on this custom tool and tt files please refer to the description here.

STEP VII(Get result in code file) :

To get the list of all the Persons as returned by the function imported :-

Create the object of the Entity context class,
Test_SPEntities objTestSPEntity = new Test_SPEntities();

Call the function and get the result as list of the particular Complex Type,
List<GetAllPersonsEntity> listAllPersons = objTestSPEntity.GetAllPersons().ToList();


Hope this will be definitely of help for the guys who just started with basics of the Entity Framework. CodeProject