how to load million data from sql into html table by moving scrolling show data to be too fastRSS

• • Reply
  zhyanadil.it...

  Member

  65 Points

  407 Posts

  how to load million data from sql into html table by moving scrolling show data to be too fast

  Apr 05, 2020 08:28 AM|zhyanadil.it@gmail.com|LINK

  i want to return million record data from sql into html table by moving scroll down show data to be too fast here is my code

  i want use this tutorial on my code

  https://www.youtube.com/watch?v=t1zup6_KlKc

  controller

  
   SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
          List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
  
   public String ConvertDataTabletoString(String Company)
          {
              getColumns(Company);
              JavaScriptSerializer serializer = new JavaScriptSerializer();
              serializer.MaxJsonLength = Int32.MaxValue;
  
              return serializer.Serialize(rows);
          }
  
          public JsonResult getColumns(String Company)
          {
              List<dummy> listreg = new List<dummy>();
              SqlCommand cmd = new SqlCommand("[dbo].[REPORT__Chart_Of_Accounts]", con);
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.AddWithValue("@company", Company);
              SqlDataAdapter adapter = new SqlDataAdapter(cmd);
              DataTable dt = new DataTable();
              adapter.Fill(dt);
              foreach (DataColumn dr in dt.Columns)
              {
                  listreg.Add(new dummy
                  {
                      variable = dr.ColumnName.ToString(),
                  });
              }
              Dictionary<string, object> row;
              foreach (DataRow dr in dt.Rows)
              {
                  row = new Dictionary<string, object>();
                  foreach (DataColumn col in dt.Columns)
                  {
                      row.Add(col.ColumnName, dr[col].ToString());
                  }
                  rows.Add(row);
              }
              return Json(listreg, JsonRequestBehavior.AllowGet);
          }
  
          class dummy
          {
              public string variable { get; set; }
          }
  

  view

   function ToDatatable() {
          var Arr = [];
          var searches = "";
          $.ajax({
              type: "GET",
              url: "/Chart_of_Account/getColumns",
              data: { Company: $("#company2").val() },
              success: function (response) {
                  searches += "<tr>";
                  data = $.map(response, function (item, a) {
                      Arr.push({ data: item.variable, title: item.variable });
                      searches += "<td><input type='text' id=" + item.variable + " class='form-control' placeholder='Search " + item.variable + "'/></td>";
                  });
                  searches += "</tr>";
              },
              dataType: "json"
          }).done(function () {
              sample = $('#tbl').DataTable({
                  "language": {
                      "zeroRecords": "Nothing found - sorry",
                      "emptyTable": "Loading... please wait",
                      "loadingRecords": "Loading...",
                      "processing": "Processing...",
                      "lengthMenu": "Display _MENU_ records per page",
                      "info": "",
                      "sInfoFiltered": ""
                  },
                  "ajax": {
                      "processing": true,
                      "serverSide": true,
                      "url": "/Chart_of_Account/ConvertDataTabletoString",
                      "data": { "Company": $("#company2").val() },
                      "dataSrc": "",
                      "deferRender": true,
                  },
                  "lengthChange": false,
                  "paging": false,
                  "columns": Arr,
                  "order": [[2, "asc"]],
                  "sortable": false,
                  "ordering": false,
                  "autoWidth": true,
                  "searching": false,
  
                  "lengthMenu": [[-1], ["All"]],
                  "initComplete": function (setting, json) {
                      for (var i = 0; i <= $("#tbl tbody tr").length; i++) {
                          if ($("#tbl tr:eq('" + i + "') td:eq(1)").text() != "" || $("#tbl tr:eq('" + i + "') td:eq(3)").text() != "") {
                              Acc_Series = $("#tbl tr:eq('" + i + "') td:eq(0)").text();
                              Is_Group = $("#tbl tr:eq('" + i + "') td:eq(2)").text();
                              Acc = $("#tbl tr:eq('" + i + "') td:eq(1)").text();
                              Acc_pa = $("#tbl tr:eq('" + i + "') td:eq(3)").text();
                              Ordering(i, Is_Group, Acc_Series, Acc, Acc_pa);
                          }
                      }
                  },
                  "columnDefs": [
                      {
                          "orderable": false, targets: "_all"
                      },
                      {
                          "targets": [0, , 2, 3],
                          "className": "hide_column"
                      }
                  ]
              });
              //sample.row($("#tbl tr:eq(1)")).remove().draw();
              $("#tbl thead").addClass("hide_column");
  
          });
      }
  
  
   if ($.fn.dataTable.isDataTable('#tbl')) {
                      $('#tbl').DataTable().destroy();
                      $('#tbl').empty();
                      ToDatatable();
                  }
                  else {
                      ToDatatable();
                  }

• • Reply
  DA924

  Contributor

  4433 Points

  3754 Posts

  Re: how to load million data from sql into html table by moving scrolling show data to be too fa...

  Apr 05, 2020 03:08 PM|DA924|LINK

  i want to return million record data from sql into html table by moving scroll down show data to be too fast here is my code

  This is not an optimal approach. All you're going to do is bring the solution to its keens and piss the users off to the point that they won't even use it.

  You need to only return subsets of records from the 1 million records by implementing a search feature and using paging. 

  The second problem is that you're using a datatable, which is the slowest means of accessing the data. You should be using SQL Command objects, a data reader that is the fastest means of reading the data from a database table in a forward motion and eliminate the datatable that is using boxing and unboxing.

  https://dzone.com/articles/reasons-move-datatables

  https://www.codingblocks.net/programming/boxing-and-unboxing-7-deadly-sins/

  If you find the post has answered your issue, then please mark post as 'answered'.