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');
                    }
            });