How to get DataTable out of SqlDataSource
In many scenarios developers would like to combine the great simplicity of Asp.Net 2.0 declarative databinding with the same level of control over data as they get with good old traditional databinding. Sometimes the developers need to manipulate on the data in the datasource and thus to access it.
If the data come from a SQL Server database, you will use the SqlDataSource control. Setting the DataSourceMode property to DataSet will result in creating a DataSet object in the server memory. The object will be loaded with the data after running the Select method. The Select method returns a DataView populated with the rows returned from the database. Having obtained the DataView, you can easily convert it to a DataTable with the DataView.ToTable method.
In the case of declarative databinding the Select method is called automatically by controls that are bound to the SqlDataSource and it is not obvious where is your chance to catch the datasource, convert it to a DataTable and do something on it.
If you need to access the dataset prior to databinding, you don’t have any choice but to call the Select manually, which contradicts the whole idea of declarative databinding. Too bad.
In some scenarios however you can first databind and then operate on the DataTable. One example is when you need to save the table for postbacks. In this case you can catch the table in the ItemDataBound or RowDataBound event of the control bound to the SqlDataSource. The event reaches the DataView via the DataItem property exposed by the data items:
bool firstTime = true;
System.Data.DataTable dt;
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
if (this.firstTime)
{
System.Data.DataView dv =
(e.Row.DataItem as System.Data.DataRowView).DataView;
this.dt = dv.ToTable();
this.Label1.Text = dt.Rows.Count.ToString();
this.firstTime = false;
}
}