Hello,
Gridview loading data very slow but dont want to use Paging
I'm using gridview to show bills and there are around 5000 records in the database. I'm using jQuery's DataTable that renders on the client side to the gridview. Now the problem is that it takes around 80 seconds to load the whole gridview and If I use asp.net paging I can't use the datatable because of server side & client side paging, datatables won't render at all. What probably could be the solution for this should I use dataset to bind data as soon as I login and then bind that to the gridview? I'm not able to find a work around. Pease help!
below is my aspx markup:
<asp:GridView ID="Gridview1" runat="server" CssClass="table table-bordered table-responsive table-sm gridviewDataTable" AutoGenerateColumns="False" EmptyDataText="No bills available at athe moment!" ShowFooter="true" PageSize="10">
<Columns>
<asp:TemplateField HeaderText="Print">
<ItemTemplate>
<asp:LinkButton ID="lnkPrint" runat="server" Text="" CausesValidation="false" CommandArgument='<%# Eval("bno")%>' CssClass="btn btn-primary btn-outline-primary btn-sm fa fa-print" OnClientClick="return confirm('Do you want to print this bill?')" OnClick="lnkPrint_Click"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Details">
<ItemTemplate>
<asp:LinkButton ID="lnkView" runat="server" Text="" CausesValidation="false" CommandArgument='<%# Eval("bno")%>' OnClick="lnkView_Click" CssClass="btn btn-warning btn-outline-warning btn-sm fa fa-eye"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="id" Visible="false" />
<asp:BoundField DataField="bno" HeaderText="Bill No." HeaderStyle-Wrap="false" ControlStyle-Width="100%" ItemStyle-Wrap="false" ItemStyle-Font-Size="Small" ItemStyle-Font-Bold="true"></asp:BoundField>
<asp:BoundField DataField="cdate" HeaderText="Date" HeaderStyle-Wrap="false" ControlStyle-Width="100%" ItemStyle-Wrap="false" ItemStyle-Font-Size="Small" ItemStyle-Font-Bold="true"></asp:BoundField>
<asp:BoundField DataField="ctime" HeaderText="Time" HeaderStyle-Wrap="false" ControlStyle-Width="100%" ItemStyle-Wrap="false" ItemStyle-Font-Size="Small" ItemStyle-Font-Bold="true"></asp:BoundField>
<asp:BoundField DataField="name" HeaderText="Name" HeaderStyle-Wrap="false" ControlStyle-Width="100%" ItemStyle-Wrap="false" ItemStyle-Font-Size="Small" ItemStyle-Font-Bold="true"></asp:BoundField>
<asp:BoundField DataField="mobile" HeaderText="Mobile" HeaderStyle-Wrap="true" ControlStyle-Width="100%" ItemStyle-Wrap="false" ItemStyle-Font-Size="Small" ItemStyle-Font-Bold="true"></asp:BoundField>
<asp:BoundField DataField="vehicle" HeaderText="Vehicle" HeaderStyle-Wrap="false" ControlStyle-Width="100%" ItemStyle-Wrap="false" ItemStyle-Font-Size="Small" ItemStyle-Font-Bold="true"></asp:BoundField>
<asp:BoundField DataField="vehicleno" HeaderText="Vehicle No." HeaderStyle-Wrap="false" ControlStyle-Width="100%" ItemStyle-Wrap="false" ItemStyle-Font-Size="Small" ItemStyle-Font-Bold="true"></asp:BoundField>
</Columns>
</asp:GridView>
here is my c# code:
private void BindData()
{
string strQuery = "select distinct(bno), cdate, ctime, name, mobile, vehicle, vehicleno from transacts where bno!='0' and rem!='0'";
SqlCommand cmd = new SqlCommand(strQuery);
Gridview1.DataSource = GetData(cmd);
Gridview1.DataBind();
}
private DataTable GetData(SqlCommand cmd)
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(constring);
SqlDataAdapter sda = new SqlDataAdapter();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
and this is my masterpage's jquery for datatable:
<!-- DataTables -->
<script src="plugins/datatables/jquery.dataTables.js"></script>
<script src="plugins/datatables/dataTables.bootstrap4.js"></script>
<%--<script src="plugins/jQuery/jquery-2.2.3.min.js"></script>--%>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap4.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.1.1/js/dataTables.responsive.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.1.1/js/responsive.bootstrap4.min.js"></script>
<script type="text/javascript">
$(function () {
$('.gridviewDataTable').prepend($("<thead></thead>").append($('.gridviewDataTable').find("tr:first"))).DataTable({
"responsive": true,
"paging": false,
"scrollY": 400
});
});
</script>
