Undercover1989 Undercover1989 - 1 year ago 79
ASP.NET (C#) Question

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

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 free for another suggestions.

Answer Source

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 (Data == null && e.Row.DataItem != null)
        oData = (e.Row.DataItem as DataRowView).Row.Table;
        // some gui-adaption like visibility, text, ...

This solution works, but doesn't make a good impression on me. It seems quite dirty and it requires a GridView (which in my case is no problem). I would be grateful for a more clean solution.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download