SqlDataSource_OnSelected: Get data (DataTable) without re-executing the query

Question

I have a SqlDataSource bound to a GridView with an SQL that takes about 10 seconds to retreive the data.

Also there is a UserControl called "PageSizeControl" which hooks the selected-event of the GridView's SqlDataSource. In this event, I need the DataTable to prepare some settings of the PageSizeControl.

Currently, I'm doing this with following code:

protected void ds_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
    SqlDataSourceView dsv = (sender as SqlDataSourceView);
    dsv.Selected -= ds_Selected;
    DataTable dt = (dsv.Select(DataSourceSelectArguments.Empty) as DataView).Table;
    int iRowCount = dt.Rows.Count;

    // some gui-adaption like visibility, text, ...
 }

In older versions we used e.AffectedRows. But the value stored in e.AffectedRows is not correct when a Filter is applied to the DataSource. And we have use-cases where we don't only need the row count but the whole DataTable.

The problem is, that the .Select() re-executes the Db-Query and this takes another 10s to finish.

I also tried to turn caching on the SqlDataSource on:

EnableCaching="true" CacheDuration="Infinite"

But this wasn't helpful in two reasons: 1. The OnSelected event is not fired when cached data get accessed 2. If the OnSelected event get's fired (because data wasn't yet cached), the .Select() is still executing uncached and takes 10s.

Does anybody have clues how I can get the data without a time-consumpting re-execution of the query? Best would be in the OnSelected, but I'm open for another suggestions.

Vortex852456 · Accepted Answer · 2017-01-10T14:53:13.680

I got a workaround running that fits my requirements. I use the event GridView.OnRowDataBound and get the DataItem of the first GridRow, which contains the DataTable.

private DataTable oData = null;
protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (oData == null && e.Row.DataItem != null)
    {
        oData = (e.Row.DataItem as DataRowView).Row.Table;
    }
}

This solution works, but it looks quite dirty and it requires a GridView (which in my case is no problem). I would be grateful for a more clean solution.

UPDATE After a longer research with IlSpy I got to the conclusion that it is not possible to get the data in the OnSelected event. Even not if caching is enabled, since cache is written after OnSelected.

So the easiest way is to turn cache on and call the SqlDataSource.Select(...) function where you need the data.

Another way is to get the data by yourself with SqlDataSource.Select(...) and then bind the table it to the controls. But this has some disadvantages. For example: sorting and paging on GridView doesn't work out of the box when bound to a dataset/datatable.

And yet another way is to extract the data from the control which selected it. See above for an example for GridView.

SqlDataSource_OnSelected: Get data (DataTable) without re-executing the query

1 Answers1