title: Can't make Datatables Pagination with Stored Procedure in MVC5
body: <p>I've used DataTable to load data from SQL Server and display to Table.
  But it just loads data. 
  Reference: <a href="https://gyazo.com/a77f5bee9c8f4fda3be8f9d130499bbf" rel="nofollow">https://gyazo.com/a77f5bee9c8f4fda3be8f9d130499bbf</a></p>
  
  <p>Not only show this message: </p>
  
  <blockquote>
    <p>No records found to show</p>
  </blockquote>
  
  <p>but also, select page and page size selector don't display </p>
  
  <p>Normal: <a href="https://gyazo.com/3f6d5193f36b756f752bdd20523d64e0" rel="nofollow">https://gyazo.com/3f6d5193f36b756f752bdd20523d64e0</a></p>
  
  <p>I hope you guys can give me a kind helpings. Thanks so much</p>
  
  <p><strong>HTML:</strong></p>
  
  <pre><code>&lt;table class="table table-striped table-bordered table-hover table-checkable" id="datatable_products"&gt;
     &lt;thead&gt;
        &lt;tr role="row" class="heading"&gt;
           &lt;th width="1%"&gt;
              &lt;input type="checkbox" class="group-checkable"&gt;
           &lt;/th&gt;
           &lt;th width="10%"&gt; ID &lt;/th&gt;
           &lt;th width="15%"&gt; Product&amp;nbsp;Name &lt;/th&gt;
           &lt;th width="15%"&gt; Category &lt;/th&gt;
           &lt;th width="10%"&gt; Price &lt;/th&gt;
           &lt;th width="15%"&gt; Promotion&amp;nbsp;Price &lt;/th&gt;
           &lt;th width="10%"&gt; Quantity &lt;/th&gt;
           &lt;th width="15%"&gt; Date&amp;nbsp;Created &lt;/th&gt;
           &lt;th width="10%"&gt; Status &lt;/th&gt;
           &lt;th width="10%"&gt; Actions &lt;/th&gt;
        &lt;/tr&gt;
     &lt;/thead&gt;
     &lt;tbody&gt;
     &lt;/tbody&gt;
  &lt;/table&gt;
  </code></pre>
  
  <p><strong>SQL Stored Procedure:</strong></p>
  
  <pre><code>USE [OnlineShop]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[LoadProduct]
      @Start     INT=0, 
      @PageLimit INT=10
  AS
  BEGIN
      SET NOCOUNT ON;
      SELECT p.ID, p.Name, c.Name Category, p.Price, p.PromotionPrice, (SELECT SUM(Quantity) FROM ProductSizeColor WHERE ProductID = p.ID) as 'SumQuantity', p.CreatedDate, p.[Status]
      FROM Product p
      LEFT JOIN ProductCategory c
      ON p.CategoryID = c.ID
      ORDER  BY ID
      OFFSET @Start ROW
      FETCH NEXT @PageLimit ROWS ONLY
  END
  </code></pre>
  
  <p><strong>Javascript:</strong></p>
  
  <pre><code> var handleProducts = function() {
      var grid = new Datatable();
  
      grid.init({
          src: $("#datatable_products"),
          onSuccess: function(grid) {
              // execute some code after table records loaded
          },
          onError: function(grid) {
              // execute some code on network or other general error  
          },
          loadingMessage: 'Loading...',
          dataTable: {
              "lengthMenu": [
                  [10, 20, 50, 100, 150],
                  [10, 20, 50, 100, 150] // change per page values here 
              ],
              "pageLength": 10, // default record count per page
              "ajax": {
                  "url": "LoadProductTest", // ajax source
              },
              "processing": true,
              "serverSide": true,
              "columns": [
                            {"data": "ForCheckbox"}, 
                            {"data": "ID"}, 
                            {"data": "Name"}, 
                            {"data": "Category"}, 
                            {"data": "Price"}, 
                            {"data": "PromotionPrice"}, 
                            {"data": "SumQuantity"}, 
                            {"data": "CreatedDate"}, 
                            {"data": "DisplayStatus"}, 
                            {"data": "ForAction"},
              ],
              "order": [
                      [1, "asc"]
                  ] // set first column as a default sort by asc
          }
      });
  </code></pre>
  
  <p><strong>Controller:</strong></p>
  
  <pre><code>public ActionResult LoadProductTest()
      {
          //
          var draw = Request.Form.GetValues("draw").FirstOrDefault();
          var start = Request.Form.GetValues("start").FirstOrDefault();
          var length = Request.Form.GetValues("length").FirstOrDefault();
  
          int pageSize = length != null ? Convert.ToInt32(length) : 0;
          int skip = start != null ? Convert.ToInt32(start) : 0;
  
          var model = new ProductDao().LoadProduct(skip, pageSize);
  
          int totalRecords = 0;
          return Json(new { draw = draw, recordsFiltered = totalRecords, recordsTotal = totalRecords, data = model } , JsonRequestBehavior.AllowGet);
      }
  </code></pre>
  
  <p><strong>Method Load Product:</strong></p>
  
  <pre><code>public List&lt;ProductViewModel&gt; LoadProduct(int start, int pageLimit)
      {
          object[] sqlParams = {
              new SqlParameter("@Start", start),
              new SqlParameter("@PageLimit", pageLimit)
          };
          return _db.Database.SqlQuery&lt;ProductViewModel&gt;("LoadProduct @Start, @PageLimit", sqlParams).ToList();
      }
  </code></pre>