I create a Sample to test jqgrid.
GetDataService.asmx:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Web.Script.Services;
using System.Web.Script.Serialization;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace ExampleJqGrid
{
/// <summary>
/// Summary description for GetDataService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
[ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class GetDataService : System.Web.Services.WebService
{
[WebMethod]
public string GetProduct()
{
SqlConnection con = new SqlConnection(@"Data Source=NGUYEN-LAPTOP\SQLEXPRESS;Initial Catalog=ProductDB;Integrated Security=True");
//SqlCommand cmd = new SqlCommand("SELECT * FROM Products", con);
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Products", con);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
GetJSONFromDataTable getJsonDataTable = new GetJSONFromDataTable();
string json = getJsonDataTable.GetJSONString(dt);
string jonsData = GetJSONFromDataTable.JsonForJqgrid(dt, 10, 10, 2);
return jonsData;
}
}
}
GetJSONFromDataTable.cs
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;
using System.Web.Script.Serialization;
namespace ExampleJqGrid
{
public class GetJSONFromDataTable
{public static string JsonForJqgrid(DataTable dt, int pageSize, int totalRecords,int page)
{
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
StringBuilder jsonBuilder = new StringBuilder();
jsonBuilder.Append("{");
jsonBuilder.Append("\"total\":" + totalPages + ",\"page\":" + page + ",\"records\":" + (totalRecords) + ",\"rows\"");
jsonBuilder.Append(":[");
for (int i = 0; i < dt.Rows.Count; i++)
{
jsonBuilder.Append("{\"i\":"+ (i) +",\"cell\":[");
for (int j = 0; j < dt.Columns.Count; j++)
{
jsonBuilder.Append("\"");
jsonBuilder.Append(dt.Rows[i][j].ToString());
jsonBuilder.Append("\",");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
jsonBuilder.Append("}");
return jsonBuilder.ToString();
}
}
}
GetJSONFromDataTable.cs
public static string JsonForJqgrid(DataTable dt, int pageSize, int totalRecords,int page)
{
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
StringBuilder jsonBuilder = new StringBuilder();
jsonBuilder.Append("{");
jsonBuilder.Append("\"total\":" + totalPages + ",\"page\":" + page + ",\"records\":" + (totalRecords) + ",\"rows\"");
jsonBuilder.Append(":[");
for (int i = 0; i < dt.Rows.Count; i++)
{
jsonBuilder.Append("{\"i\":"+ (i) +",\"cell\":[");
for (int j = 0; j < dt.Columns.Count; j++)
{
jsonBuilder.Append("\"");
jsonBuilder.Append(dt.Rows[i][j].ToString());
jsonBuilder.Append("\",");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
jsonBuilder.Append("}");
return jsonBuilder.ToString();
}
default.asp
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"
Inherits="ExampleJqGrid._Default" %>
<script type="text/javascript" src="Resources/js/i18n/grid.locale-en.js"></script>
<script type="text/javascript" src="Resources/js/jquery.jqGrid.js"></script>
<script type="text/javascript" src="Resources/js/ui.multiselect.js"></script>
<script type="text/javascript" src="Resources/js/jquery.layout.js"></script>
<script type="text/javascript" src="Resources/js/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="Resources/js/jquery-ui-1.8.2.custom.min.js"></script>
<script type="text/javascript" src="Resources/js/jqDnR.js"></script>
<script type="text/javascript" src="Resources/js/jqModal.js"></script>
<link href="Resources/themes/ui.jqgrid.css" />
<link href="Resources/themes/redmond/jquery-ui-1.8.2.custom.css" />
<script type="text/javascript">
function getProducts()
{
$.ajax({
url: "/GetDataService.asmx/GetProduct",
data:"{}", // For empty input data use "{}",
dataType: "json",
type: "'GET'",
contentType: "application/json; charset=utf-8",
//success: successFunction
/*complete*/success: function(jsondata)
{
alert(jsondata);
var thegrid = jQuery("#list")[0];
thegrid.addJSONData(JSON.parse(jsondata));
}
});
}
function dataBindToGrid()
{
jQuery("#list").jqGrid({
datatype: getProducts(),
colNames: ['ProductId', 'ProductName', 'Description', 'Price'],
colModel: [{ name: 'ProductId', index: 'ProductId', width: 200, align: 'left' },
{ name: 'ProductName', index: 'ProductName', width: 200, align: 'left' },
{ name: 'Description', index: 'Description', width: 200, align: 'left' },
{ name: 'Price', index: 'Price', width: 200, align: 'left' }
],
rowNum: 10,
rowList: [5, 10, 20, 50, 100],
pager: jQuery('#pager'),
//imgpath: '<%= ResolveClientUrl("~/Resources/themes/redmond/images") %>',
imgpath: '~/Resources/themes/redmond/images',
width: 300,
viewrecords: true
}).navGrid(pager, { edit: true, add: false, del: false, search: false });
}
jQuery(document).ready(function() {
$("#btnAdd").click(dataBindToGrid);
});
</script>
<table id="list" class="scroll">
</table>
<div id="pager" class="scroll" style="text-align:center;"></div>
<button id="btnAdd" >Load Data</button>
<asp:TextBox ID="hidenfield" runat = "server" style="display:none"></asp:TextBox>
</div>
</form>
When i alert jsondata string , it return null.
Please review and help me to load data to jqgrid
Best regards
