Creating a SelectCommand in code behind which works identically to creating one in the SQLDataSource

Question

I'm using asp.nets webforms and gridview to create large data tables on my website. I also have a very simple method in code behind which allows the entire gridview to download to an excel file. This all works perfectly when I create the selectCommand in the sqlDataSource. My problem is I want to create a SelectCommand in code behind so I can add a lot of parameters and make it much more dynamic. I know you can add parameters as well in the sqlDataSource SelectCommand but this a lot simpler to do for what I want in code behind.

The selectCommand created in code behind works perfectly and displays the gridview. The problem is when I try to download to excel, the excel file is empty. In other words the data from the gridview is not being carried over. I think it must have something to do with the way I'm creating the select command... This is how I do it.

The Aspx file:

<asp:SqlDataSource ID="RegionCompliance" Runat="server"
    ConnectionString="<%$ ConnectionStrings:ApplicationServices %>">        
</asp:SqlDataSource>   

<%  
    SetSelectCommand(); // this is where the select command is created
%>

<h2>
    Download To:    
    <asp:LinkButton  ID="Button1" runat="server" OnClick="DownloadToExcel" Text="Excel" />
</h2>


    <asp:GridView 

        ID="GridView1" 
        DataSourceID="RegionCompliance" 
        DataKeyNames="Region">

        <Columns>
            <asp:BoundField ReadOnly="true" HeaderText="Region" DataField="Region"></asp:BoundField>
        </Columns>
   </asp:GridView>

Code-behind where the select command is built

protected void SetSelectCommand()
{
    sqlCommand = SELECT region FROM tablename
    RegionCompliance.SelectCommand = sqlCommand;
}

If I built the select command in the sqlDataSource it self it will work perfectly... Can anyone see why this is not allowing the excel file to use the selectCommand and the gridview data

Answer 1

The code block is being created during render, which is too late for the excel (you can check with the debugger if it being called or not). You can call this method explicitly when you create the excel file and also call DataBind().

Answer 2

You need to use page methods...

protected void Page_Load(object sender, EventArgs e)
{
    RegionCompliance.SelectCommand = "SELECT region FROM tablename"
}

You shouldn't be trying to call methods in-line like that - that's classic ASP stuff.