How should I be using GridViews?

Question

Recent problems I've had are making me question my whole philosophy and assumptions regarding GridViews.

Currently, I have used something along the lines of the following model.

In Markup:

<asp:UpdatePanel ID="pnlSearch" UpdateMode="Conditional" runat="server">
    <ContentTemplate>
            <asp:TextBox ID="txtSearch" runat="server" 
                ontextchanged="txtSearch_TextChanged"></asp:TextBox>
    </ContentTemplate>
</asp:UpdatePanel>

<asp:UpdatePanel ID="pnlGridView" UpdateMode="Conditional" runat="server">
    <ContentTemplate>
        <asp:GridView ID="GridView1" EnableViewState="false" AllowPaging="true" PageSize="20" DataSourceID="MyDataSource" runat="server" AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField DataField="COL_1" HeaderText="Happy Data" SortExpression="COL_1" />
                <asp:BoundField DataField="COL_2" HeaderText="Happy Related Data" SortExpression="COL_2" DataFormatString="{0:M/dd/yyyy}" />
            </Columns>
        </asp:GridView>
    </ContentTemplate>
    <Triggers>
        <asp:AsyncPostBackTrigger ControlID="txtSearch" EventName="TextChanged" />
    </Triggers>
</asp:UpdatePanel>
<asp:SqlDataSource ID="MyDataSource" runat="server"></asp:SqlDataSource>

Pretty basic stuff. A GridView. A data source. A text box for searching results. I include the UpdatePanels only because I'm somewhat convinced they could be part of my problems.

Over in my code behind, I usually would do something like this:

protected void Page_Load(object sender, EventArgs e)
{

    if (!IsPostBack)
    {
        MyDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;
        MyDataSource.ProviderName = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ProviderName;
        GridView1.EmptyDataText = "No comments found.";
        PopulateGridView();
    }
}

protected void PopulateGridView()
{
    string strQuery = @"SELECT  COL_1,
                          COL_2
                    FROM  some_table
                   WHERE  COL_3 = :important_filter";

    MyDataSource.SelectCommand = strQuery;
    MyDataSource.SelectParameters.Clear();
    MyDataSource.SelectParameters.Add(":important_filter", Request.QueryString["filter"]);
    GridView1.DataBind();
    GridView1.PageIndex = 0;
}

protected void txtSearch_TextChanged(object sender, EventArgs e)
{
    string strQuery = @"SELECT  COL_1,
                          COL_2
                    FROM  some_table
                   WHERE  COL_3 = :important_filter AND lower(COL_2) LIKE :search";
    MyDataSource.SelectCommand = strQuery;
    MyDataSource.SelectParameters.Clear();
    MyDataSource.SelectParameters.Add(":important_filter", Request.QueryString["filter"]);
    MyDataSource.SelectParameters.Add(":search", "%" + txtSearch.Text.Trim().ToLower() + "%");
    GridView1.DataBind();
    GridView1.PageIndex = 0;
}

Nothing too fancy. I initially connect up the data source to a connection string from the config file (as is necessary in a multi instance environment), wire up the query and databind. On searches, I change the query and rebind.

The only problem?

The above doesn't work. Why? The data source loses it's query, connection string, provider name, etc on post back. So the gridview commits suicide. The same thing happens when I try to change pages...either on initial load or with a search populated.

I "solved" this problem last week by manually adding the datasource to control state and repopulating the values from control state, but that seems hackish.

What am I not understanding?

Answer 1

You have the update mode set to conditional so you need to call update on 'pnlGridView'

GridView1.DataBind();
GridView1.PageIndex = 0;
pnlGridView.Update();

I would also revist how you are using your DataSource

 <asp:SqlDataSource ID="ProductsDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:OracleConnectionString %>"
    SelectCommand="SELECT  COL_1, COL_2 FROM  some_table WHERE  COL_3 = @important_filter">

  <SelectParameters>
     <asp:ControlParameter ControlID="textBox?not sure where this is coming from" PropertyName="SelectedValue" Name="important_filter" Type="string" DefaultValue="" />
   </SelectParameters>
 </asp:SqlDataSource>

You could also use a server side event handler and set the important filter manually.

protected void SQLDataSource_Selecting(object sender, SQLDataSourceSelectingEventArgs e) {
    e.InputParameters["important_filter"] = "whatever";
 }

And add the event to your markup for your SQLDataSource.

<asp:SqlDataSource ID="MyDataSource" runat="server"     OnSelecting="SQLDataSource_Selecting" />