Making jqGrid work with a .NET Web Service pt. 2

This is a follow up to my post: Making jqGrid work with a .NET Web Service.   I was asked to attach a full working example of the code I wrote in that post.

The attached Visual Studio project demonstrates how to use the GetJqgridJson method with a simple jqGrid and webservice.  (The GetJqgridJson method converts a DataTable to a JSON string suitable for jqGrid consumption.)

Download the Code
Note: To get the example to run, you will need to change the url parameter in the AJAX call on the default.aspx page.  Make sure the url parameter points to the correct location and port on your machine.

Making jqGrid work with a .NET Web Service

(Also see Part 2 of this blog entry for a full working example of the code.)

I tore my hair out trying to get jqGrid to display results from a .NET web service. I ended up creating a method (in c#) to convert a datatable to a JSON string appropriate for consumption by jqGrid. Here is what I did...

First, I wrote the following method to convert a datatable to a jqGrid-appropriate JSON string:

public static string GetJqgridJson(DataTable dt) {
            ///<summary>
            ///     This method converts a DataTable to a JSON string
suitable for jqGrid consumption.
            /// </summary>
            ///<param name="dt">
            ///     The DataTable to be processed.
            /// </param>
            ///<returns>
            ///     jqGrid-JSON string if dt parameter has rows, null if dt 
parameter has rows
            /// </returns>

            StringBuilder jsonString = new StringBuilder();
            if(dt != null && dt.Rows.Count > 0) {
                jsonString.Append("{");
                jsonString.Append("\"total\": \"1\",");  //set as 1 for example
                jsonString.Append("\"page\": \"1\",");   //set as 1 for example
                jsonString.Append("\"records\": \"" + dt.Rows.Count.ToString() + "\",");
                jsonString.Append("\"rows\":[");

                for(int i = 0; i < dt.Rows.Count; i++) {
                    jsonString.Append("{\"cell\" :[");
                    for(int j = 0; j < dt.Columns.Count; j++) {
                        if(j < dt.Columns.Count - 1) {
                            jsonString.Append("\"" + dt.Rows[i][j].ToString()
.Replace("\"", "\\\"") + "\",");
                        } else if(j == dt.Columns.Count - 1) {
                            jsonString.Append("\"" + dt.Rows[i][j].ToString()
.Replace("\"", "\\\"") + "\"");
                        }
                    }

                    if(i == dt.Rows.Count - 1) {
                        jsonString.Append("]} ");
                    } else {
                        jsonString.Append("]}, ");
                    }
                }

                jsonString.Append("]}");
                return jsonString.ToString();
            } else {
                //Return null if datatable has no rows.
                return null;
            }
        }

Next, I created a .Net WebMethod that uses the GetJqgridJson method. My WebMethod fetches data as a DataTable, uses the GetJqgridJson method to convert the DataTable to a JSON string, then returns the JSON string to the browser. (I wont include my WebMethod code here for sake of brevity.)

When you're creating your WebMethod, just be sure to include the following directive above your method to ensure you are sending a JSON and not an XML string back to the browser:

[ScriptMethod(ResponseFormat = ResponseFormat.Json)]

Finally, I wrote my JavaScript code. Here is some sample code that can be used to set up a jqGrid and fetch the JSON string via AJAX. (Note: The following JavaScript code will not work out of the box. You'll need to update the portions surrounded with "**" to make it work within your project.)

$("**YOUR JQUERY SELECTOR HERE**").jqGrid({
                            datatype: 'jsonstring',
                            jsonReader: { root: "rows",
                                page: "page",
                                total: "total",
                                records: "records",
                                repeatitems: true,
                                cell: "cell"
                            },
                            gridview:true,
                            colNames: [**YOUR COLUMN NAMES HERE**],
                            colModel: [**YOUR COLMODEL HERE**],
                            viewrecords: true,
                            caption: '',
                            loadonce: false,
                            loadui: "enabled",
                            beforeSelectRow: function(rowid, e) {return false;},
                            hoverrows:false,
                            height:200,
                            scroll:1
                });
                $("**YOUR JQUERY SELECTOR HERE**").clearGridData();
                $.ajax({
                    type: "POST",
                    url: "**YOUR WEBMETHOD URL HERE**",
                    data: JSON.stringify(webservice_object),
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (msg) {
                        var data = msg.d;
                        $("**YOUR JQUERY SELECTOR HERE**").jqGrid('setGridParam', 
                                        {datatype: 'jsonstring', datastr: data});
                        $("**YOUR JQUERY SELECTOR HERE**").trigger('reloadGrid');
                    }
            });