jQWidgets Forums

This topic contains 3 replies, has 3 voices, and was last updated by Minko 12 years, 10 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)

Author

Virtual Scrolling with SQL and JSON Posts

May 25, 2012 at 5:49 pm Virtual Scrolling with SQL and JSON #4382

Kloc

Member

I am trying to display a 24,000 row SQL table with virtual scrolling using jqxgrid in MVC 3. I am having problems getting any of my data to display. Here is my code.

Index


    $(document).ready(function () {

        // prepare the data

        var source = {

            datatype: "json",

            datafields: [{ name: 'CPT1' }, { name: 'MOD' },

            { name: 'SDESC' }, { name: 'FAGE' }, { name: 'TAGE' }, ],

            url: 'dbCPT/GetCustomers'

        };

        var dataAdapter = new $.jqx.dataAdapter(source, { async: false, autoBind: true });

        var records = dataAdapter.records;
        $("#jqxgrid").jqxGrid({

            source: dataAdapter,

            width: 900,

            theme: 'classic',

            columnsresize: true,

            virtualmode: true,

            rendergridrows: function (params) {

                    var start = params.startindex;

                    var end = params.endindex;

                    var array = new Array();

                    for (i = start; i < end; i++) {

                        array[i] = records[i];

                    }

                    return array;},
            columns: [

                { text: 'CPT Code', datafield: 'CPT1', width: 250 },

                { text: 'Modifiers', datafield: 'MOD', width: 150 },

                { text: 'Short Description', datafield: 'SDESC', width: 180 },

                { text: 'From Age', datafield: 'FAGE', width: 200 },

                { text: 'To Age', datafield: 'TAGE', width: 120 }

                ]

        });

    });
Index

Controller
public LargeJsonResult GetCustomers() { var dbResult = db.CPTs.ToList(); var customers = from customer in dbResult

select new { customer.CPT1, customer.MOD, customer.SDESC, customer.FAGE, customer.TAGE }; return new LargeJsonResult { Data = customers, JsonRequestBehavior = System.Web.Mvc.JsonRequestBehavior.AllowGet }; }

Right now all I get is an empty grid, the JSON request is working because I tried the url and had no errors. Is this the way I should be handling this large of data? The file is 2.4 MB. If you have an suggestions that would be great. I will also need to be able to enter a code and be able to jump directly to that code in the list so if you have an idea of how I can set that up it would be greatly appreciated.

May 25, 2012 at 7:14 pm Virtual Scrolling with SQL and JSON #4383

Peter Stoev

Keymaster

Hi Kloc,

I created a sample application with ASP .NET MVC3 and jqxGrid in virtual mode.

1. The content of the _Layout.cshtml. The different in the binding is that in your code you were mixing data bound with virtual modes. In the code below, the data adapter is separated from the Grid. The Grid knows only about the DataFields and the number of records.

<!DOCTYPE html>
<html>
<head>
    <title>@ViewBag.Title</title>
    <link href="@Url.Content("~/Content/Site.css")" 
rel="stylesheet" type="text/css" />
    <link href="@Url.Content("~/Content/styles/jqx.base.css")" 
rel="stylesheet" type="text/css" />
    <link href="@Url.Content("~/Content/styles/jqx.classic.css")" 
rel="stylesheet" type="text/css" />
    <script src="@Url.Content("~/Scripts/jquery-1.7.2.min.js")" 
type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/jqxcore.js")" 
type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/jqxbuttons.js")" 
type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/jqxdata.js")" 
type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/jqxgrid.js")" 
type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/jqxgrid.selection.js")" 
type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/jqxmenu.js")" 
type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/jqxscrollbar.js")" 
type="text/javascript"></script>
<script type="text/javascript">
    $(document).ready(function () {
        // prepare the data
        var datafields = [
            { name: 'CompanyName' },
            { name: 'ContactName' },
            { name: 'ContactTitle' },
            { name: 'Address' },
            { name: 'City'}];
        // get data records.
        var source = {
            datatype: "json",
            datafields: datafields,
            url: 'Home/GetCustomers'
        };
        var dataAdapter = new $.jqx.dataAdapter(source, { async: false, autoBind: true });
        var records = dataAdapter.records;
      
        $("#jqxgrid").jqxGrid({
            source: { datafields: datafields, totalrecords: records.length },
            theme: 'classic',
            virtualmode: true,
            rendergridrows: function (params) {
                var start = params.startindex;
                var end = params.endindex;
                var array = new Array();
                for (i = start; i < end; i++) {
                    array[i] = records[i];
                }
                return array;
            }, columns: [{ text: 'Company Name', datafield: 'CompanyName', width: 250 },
                { text: 'Contact Name', datafield: 'ContactName', width: 150 },
                { text: 'Contact Title', datafield: 'ContactTitle', width: 180 },
                { text: 'Address', datafield: 'Address', width: 200 },
                { text: 'City', datafield: 'City', width: 120}]
        });
    });
</script>
</head>
<body>
    <div class="page">
        <div id="header">
            <div id="title">
                <h1>
                    My MVC Application</h1>
            </div>
            <div id="logindisplay">
                @Html.Partial("_LogOnPartial")
            </div>
            <div id="menucontainer">
                <ul id="menu">
                    <li>@Html.ActionLink("Home", "Index", "Home")</li>
                    <li>@Html.ActionLink("About", "About", "Home")</li>
                </ul>
            </div>
        </div>
        <div id="main">
            @RenderBody()
        </div>
        <div id="footer">
        </div>
    </div>
</body>
</html>

2. In the HomeController, I added the LargeJSONResult implementation. It is important to set the MaxJsonLength to Int32.MaxValue. Otherwise, there would be not enough space for the JSON serialization.

public class LargeJsonResult : JsonResult
    {
        const string JsonRequest_GetNotAllowed = "This request has been blocked because sensitive information could be disclosed to third party web sites when this is used in a GET request. To allow GET requests, set JsonRequestBehavior to AllowGet.";
        public LargeJsonResult()
        {
            MaxJsonLength = Int32.MaxValue;
            RecursionLimit = 100;
        }
        public int MaxJsonLength { get; set; }
        public int RecursionLimit { get; set; }
        public override void ExecuteResult(ControllerContext context)
        {
            if (context == null)
            {
                throw new ArgumentNullException("context");
            }
            if (JsonRequestBehavior == JsonRequestBehavior.DenyGet &&
                String.Equals(context.HttpContext.Request.HttpMethod, "GET", StringComparison.OrdinalIgnoreCase))
            {
                throw new InvalidOperationException(JsonRequest_GetNotAllowed);
            }
            HttpResponseBase response = context.HttpContext.Response;
            if (!String.IsNullOrEmpty(ContentType))
            {
                response.ContentType = ContentType;
            }
            else
            {
                response.ContentType = "application/json";
            }
            if (ContentEncoding != null)
            {
                response.ContentEncoding = ContentEncoding;
            }
            if (Data != null)
            {
                JavaScriptSerializer serializer = new JavaScriptSerializer() { MaxJsonLength = MaxJsonLength, RecursionLimit = RecursionLimit };
                response.Write(serializer.Serialize(Data));
            }
        }
    }

3. I am using a custom class called Customer.

    public class Customer
    {
        public Customer()
        {
        }
        public string CompanyName { get; set; }
        public string ContactName { get; set; }
        public string ContactTitle { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
    }

4. In the HomeController class, the GetCustomers method creates a List of 25000 records which will be displayed in the Grid.

    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Message = "Welcome to ASP.NET MVC!";
            return View();
        }
        public ActionResult About()
        {
            return View();
        }
        public LargeJsonResult GetCustomers()
        {
            List<Customer> customers = new List<Customer>();
            for (int i = 0; i < 25000; i++ )
            {
                Customer customer = new Customer(){CompanyName = "CompanyName" + i,
                    ContactName = "ContactName" + i, ContactTitle = "ContactTitle" + i,
                    Address = "Address" + i, City = "City" + i};
                customers.Add(customer);
            }
            return new LargeJsonResult { Data = customers, JsonRequestBehavior = System.Web.Mvc.JsonRequestBehavior.AllowGet };
        }
    }

Hope this helps.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

May 30, 2012 at 4:46 pm Virtual Scrolling with SQL and JSON #4489

Kloc

Member

Thanks for the reply but I am still having issues. I tried your code in a new project and everything worked fine so I’m not sure why it’s not working when I try to implement it within my code. Here is part of my LargeJsonResult when I download it.

[{“CPT1″:”01844″,”MOD”:null,”SDESC”:”Anesth vascular shunt surg”,”FAGE”:0,”TAGE”:0},
{“CPT1″:”01844″,”MOD”:null,”SDESC”:”Anesth vascular shunt surg”,”FAGE”:0,”TAGE”:0},
{“CPT1″:”01844″,”MOD”:null,”SDESC”:”Anesth vascular shunt surg”,”FAGE”:0,”TAGE”:0},
{“CPT1″:”01844″,”MOD”:null,”SDESC”:”Anesth vascular shunt surg”,”FAGE”:0,”TAGE”:0},
{“CPT1″:”01900″,”MOD”:null,”SDESC”:”Anes- inj proc hysterosalpingography (Deleted 12/31/99)”,”FAGE”:0,”TAGE”:0},{“CPT1″:”01900″,”MOD”:null,”SDESC”:”Anes- inj proc hysterosalpingography (Deleted 12/31/99)”,”FAGE”:0,”TAGE”:0},

I have nullable and double fields, is this what might be causing the problem? Here is my new script.


    $(document).ready(function () {

        // prepare the data
        var datafields = [

            { name: 'CPT1'},

            { name: 'MOD'},

            { name: 'SDESC'},

            { name: 'FAGE'},

            { name: 'TAGE'}];
        // get data records.

        var source = {

            datatype: "json",

            datafields: datafields,

            url: 'dbCPT/GetCustomers'

        };

        var dataAdapter = new $.jqx.dataAdapter(source, { async: false, autoBind: true });

        var records = dataAdapter.records;
        $("#jqxgrid").jqxGrid({

            source: { datafields: datafields, totalrecords: records.length },

            theme: 'classic',

            virtualmode: true,

            rendergridrows: function (params) {

                var start = params.startindex;

                var end = params.endindex;

                var array = new Array();

                for (i = start; i < end; i++) {

                    array[i] = records[i];

                }

                return array;

            }, columns: [{ text: 'CPT Code', datafield: 'CPT1', width: 250 },

                { text: 'Modifiers', datafield: 'MOD', width: 150 },

                { text: 'Short Description', datafield: 'SDESC', width: 180 },

                { text: 'From Age', datafield: 'FAGE', width: 200 },

                { text: 'To Age', datafield: 'TAGE', width: 120}]

        });

    });

My controller

public class dbCPTController : Controller { private dbCPTEntities db = new dbCPTEntities();


        public ViewResult Index()

        {

            return View(db.CPTs.ToList());

        }
        public LargeJsonResult GetCustomers()

        {

            var dbResult = db.CPTs.ToList();

            var customers = from customer in dbResult

Any help would be appreciated!

May 31, 2012 at 9:02 am Virtual Scrolling with SQL and JSON #4491

Minko

Blocked

Hi Kloc,

Make sure that you’re using the last version of jQWidgets (2.2.0). Here is approach that works with the sample data that you’ve posted and jQWidgets 2.2.0:

@{
    ViewBag.Title = "Index";
}
<h2>Index</h2>
<div id="jqxgrid"></div>
@{
ViewBag.Title = "Index";
}
@Html.ActionLink("Create New", "Create")
<script type="text/javascript">
    var datafields = [{ name: 'CPT1' },
                      { name: 'MOD' },
                      { name: 'SDESC' },
                      { name: 'FAGE' },
                      { name: 'TAGE'}];
    var source = {
        datatype: "json",
        datafields: datafields,
        url: 'Customers/GetCustomers'
    };
    var dataAdapter = new $.jqx.dataAdapter(source, { async: false, autoBind: true });
    var records = dataAdapter.records;
    $("#jqxgrid").jqxGrid({
        source: { datafields: datafields, totalrecords: records.length },
        theme: 'classic',
        virtualmode: true,
        rendergridrows: function (params) {
            var start = params.startindex;
            var end = params.endindex;
            var array = new Array();
            for (var i = start; i < end; i++) {
                array[i] = records[i];
            }
            return array;
        }, columns: [{ text: 'CPT Code', datafield: 'CPT1', width: 250 },
            { text: 'Modifiers', datafield: 'MOD', width: 150 },
            { text: 'Short Description', datafield: 'SDESC', width: 180 },
            { text: 'From Age', datafield: 'FAGE', width: 200 },
            { text: 'To Age', datafield: 'TAGE', width: 120}]
        });
</script>

Here is the Controller:

public string GetCustomers()
{
    return "[{\"CPT1\":\"01844\",\"MOD\":null,\"SDESC\":\"Anesth vascular shunt surg\",\"FAGE\":0,\"TAGE\":0},{\"CPT1\":\"01844\",\"MOD\":null,\"SDESC\":\"Anesth vascular shunt surg\",\"FAGE\":0,\"TAGE\":0}, {\"CPT1\":\"01844\",\"MOD\":null,\"SDESC\":\"Anesth vascular shunt surg\",\"FAGE\":0,\"TAGE\":0},{\"CPT1\":\"01844\",\"MOD\":null,\"SDESC\":\"Anesth vascular shunt surg\",\"FAGE\":0,\"TAGE\":0},{\"CPT1\":\"01900\",\"MOD\":null,\"SDESC\":\"Anes- inj proc hysterosalpingography (Deleted 12/31/99)\",\"FAGE\":0,\"TAGE\":0},{\"CPT1\":\"01900\",\"MOD\":null,\"SDESC\":\"Anes- inj proc hysterosalpingography (Deleted 12/31/99)\",\"FAGE\":0,\"TAGE\":0}]";
}

Best regards,
Minko

jQWidgets Team
http://jqwidgets.com/

Author

Posts

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.