Best way to load data from DB and show in GridView

Question

I am newbie to the ASP.Net world and have a confusion on how to approach the below scenario.

In my application I have to fetch the data from the database when page is loaded and show this in a GridView. The table currently has around 1000 records with about 7 columns. But the data will keep growing.

Here is the code of how I am binding the data to the grid.

 protected void Page_Load(object sender, EventArgs e)
 {
     var data = new AppsAuthData().GetAllUsers();
     gridUsersInfo.DataSource = data;
     gridUsersInfo.DataBind();
 }

I came to know that on every post back above code is getting executed (which obviously is not good). So I added the following to that start of the function

if (IsPostBack)
     return;
var data = new AppsAuthData().GetAllUsers();
gridUsersInfo.DataSource = data;
gridUsersInfo.DataBind();

Page Markup

<%@ Page Language="C#" AutoEventWireup="true" MasterPageFile="~/Site.Master" CodeBehind="RemoveUsers.aspx.cs" Inherits="AppsAuth.Authencations.RemoveUsers" %>

This again has an issue that after Postbacks, GridView has nothing to show. So next I saved my results to the ViewState and on each post back i was retrieving/updating the ViewState.

But since data can be huge in some scenarios, so what are best options available to deal with such issues?

GridView snippet

 <asp:GridView ID="gridUsersInfo" runat="server" Width="100%" ForeColor="#333333" AllowPaging="True"
        AllowSorting="True" AutoGenerateColumns="false" OnSorting="UserInfo_Sorting" OnRowEditing="gridUsersInfo_RowEditing"
        OnPageIndexChanging="gridUsersInfo_PageIndexChanging" AutoGenerateEditButton="True">
 >    <Columns>
            <asp:BoundField DataField="USER_ID" ReadOnly="True" HeaderText="ID" SortExpression="USER_ID" />
            <asp:BoundField DataField="USER_NAME" ReadOnly="False" HeaderText="User Name" SortExpression="USER_NAME" />
        </Columns>
    </asp:GridView>

protected void gridUsersInfo_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gridUsersInfo.PageIndex = e.NewPageIndex;
        gridUsersInfo.DataBind();
    }

Answer 1

In .aspx file

<form runat="server" onload="Page_Load">
  <asp:GridView runat="server" ID="gridEvent" AutoGenerateColumns="False" BackColor="White"  
        BorderStyle="None" BorderWidth="0px" class="table mb-0"  
        > 
        <RowStyle BackColor="White" /> 
        <Columns>
            <asp:BoundField DataField="EventId" HeaderText="#" />
            <asp:BoundField DataField="Title" HeaderText="Event Title" />
            <asp:BoundField DataField="EventDate" HeaderText="Event Date" />
            <asp:BoundField DataField="Location" HeaderText="Venue" />
            <asp:BoundField DataField="RegisteredUsers" HeaderText="Registred User(s)" />
            <asp:CommandField ShowEditButton="True" /> 
            <asp:CommandField ShowDeleteButton="True" />

        </Columns>
        <FooterStyle BackColor="#99CCCC" ForeColor="#003399" /> 
        <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" /> 
        <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" /> 
        <HeaderStyle BackColor="#FBFBFB" Font-Bold="True" ForeColor="#5A6169" /> 
  </asp:GridView>
 </form>

in the .aspx.designer.cs

public partial class Default
    {
        /// <summary>
        /// txtLocation control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.WebControls.GridView gridEvent;
    }

in the .aspx.cs file

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                // Enable the GridView paging option and  
                // specify the page size. 
                gridEvent.AllowPaging = true;
                gridEvent.PageSize = 15;

                // Initialize the sorting expression. 
                ViewState["SortExpression"] = "EventId ASC";
                // Enable the GridView sorting option. 
                gridEvent.AllowSorting = true;
                BindGrid();
            }
        }
        private void BindGrid()
        {
            // Get the connection string from Web.config.
    // When we use Using statement,  
    // we don't need to explicitly dispose the object in the code,  
    // the using statement takes care of it. 
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
            {
                // Create a DataSet object. 
                DataSet dsPerson = new DataSet();


                // Create a SELECT query. 
                string strSelectCmd = "SELECT * FROM EventsList";


                // Create a SqlDataAdapter object 
                // SqlDataAdapter represents a set of data commands and a  
                // database connection that are used to fill the DataSet and  
                // update a SQL Server database.  
                SqlDataAdapter da = new SqlDataAdapter(strSelectCmd, conn);


                // Open the connection 
                conn.Open();


                // Fill the DataTable named "Person" in DataSet with the rows 
                // returned by the query.new n 
                da.Fill(dsPerson, "EventsList");


                // Get the DataView from Person DataTable. 
                DataView dvPerson = dsPerson.Tables["EventsList"].DefaultView;


                // Set the sort column and sort order. 
                dvPerson.Sort = ViewState["SortExpression"].ToString();


                // Bind the GridView control. 
                gridEvent.DataSource = dvPerson;
                gridEvent.DataBind();
            }
        }
        //Implementing Pagination
        protected void OnPaging(object sender, GridViewPageEventArgs e)
        {
            gridEvent.PageIndex = e.NewPageIndex;
            gridEvent.DataBind();
        }

Answer 2

Instead of putting everything on PageLoad. You can put a button and write the code to populate GridView in the click event of that button.

Using the asp.net control Gridview with pagination enabled will do this for you.

Check the following example:

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
    OnPageIndexChanging="OnPageIndexChanging" PageSize="10">
    <Columns>
        <asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="Customer ID" />
        <asp:BoundField ItemStyle-Width="150px" DataField="ContactName" HeaderText="Contact Name" />
        <asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />
        <asp:BoundField ItemStyle-Width="150px" DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>

CodeBehind

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGrid();
    }
}

private void BindGrid()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, ContactName, City, Country FROM Customers"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
        }
    }
}

Implementing Pagination

The event handler is called when the page is changed inside the GridView. The value of the PageIndex of the Page which was clicked is present inside the NewPageIndex property of the GridViewPageEventArgs object and it is set to the PageIndex property of the GridView and the GridView is again populated by calling the BindGrid function.

protected void OnPaging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    GridView1.DataBind();
}

Source : http://www.aspsnippets.com/Articles/Paging-in-ASPNet-GridView-Example.aspx

Answer 3

What you have to do is just bind bind gridview when !IsPostback in page_load

 if(!IsPostBack)
{    var data = new AppsAuthData().GetAllUsers();
     ViewState["UserData"] = data;
     gridUsersInfo.DataSource = data;
     gridUsersInfo.DataBind();
}

Hear is an example : Asp.Net Bind Grid View