Code optimization avoid Database hitting.

Today, we will learn,how to avoid db hitting every time.If there are large data in a table,so we can store huge data in dataset/datatable and store dataset/datatable in ViewState in page-load.

we can understand it by an example:-

I am taking here datatable and 5 rows,but you can write your query and store data in viewstate as shown:-

protected void Page_Load(object sender, EventArgs e)
{
        if (!IsPostBack)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Id");
            dt.Columns.Add("Name");

            dt.Rows.Add("1", "Vishal");
            dt.Rows.Add("2", "Rajesh");
            dt.Rows.Add("3", "Prashant");
            dt.Rows.Add("4", "Dharmesh");
            dt.Rows.Add("5", "Nitin");

            DropDownList1.DataSource = dt;
            DropDownList1.DataTextField = "Name";
            DropDownList1.DataValueField = "Id";
            DropDownList1.DataBind();

            ViewState["DataTable"] = dt; 
        }
}

As we have seen that we have stored datatable in viewstate.

Now on DropDownList1-SelectedIndexChanged event,we can fill DropDownList2 based on DropDownList1 selection as shown:-

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
        try
        {
            DropDownList2.Items.Clear();

            if (ViewState["DataTable"]  != null)
            {
                //type cast viewstate with datatable
                DataTable dt = (DataTable)ViewState["DataTable"]; 

                if (dt != null)
                {
                    if (dt.Rows.Count > 0)
                    {
                        DataRow[] dr = dt.Select("Id='" + DropDownList1.SelectedValue + "'");
                        if (dr != null)
                        {
                            if (dr.Length > 0)
                            {
                                DataTable dt_new = dr.CopyToDataTable();
                                DropDownList2.DataSource = dt_new;
                                DropDownList2.DataTextField = "Name";
                                DropDownList2.DataValueField = "Id";
                                DropDownList2.DataBind();
                            }
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
}

Here,i have taken dropdownlist,but we can take gridview or any other controls.

Our aspx page will look like this:-

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
    </asp:DropDownList>
    
    <asp:DropDownList ID="DropDownList2" runat="server">
    </asp:DropDownList>