Programmatically accessing data from DataSource controls

4.74 (19 votes)

For the main part, the DataSource controls are incredibly easy to work with. You just drag them onto your page in Design View, wire them up to a connection and provide some kind of command (ad hoc sql query or the name of a stored procedure - saved query in Access), then link them to a databound control on the page using its DataSource property. Then you run the page and all your data appears. But what if you want to get at one or more values from the returned data to do something else with it, like display it in a control that doesn't expose a DataSource property?

To programmatically access the contents of a Sql or Access DataSource control you need to explicitly call its Select() method. This method accepts a single input parameter of type DataSourceSelectArguments. This parameter can contain information regarding the filters to apply or the column to Order By. For example, when working with a sortable GridView, sorting a column calls the Select() method, and passes in a DataSourceSelectArguments instance with its SortExpression property set to the column name the user chose to sort by. If you don't want the DataSource to sort or filter, you pass in DataSourceSelectArguments.Empty.

Depending on the DataSourceMode of the DataSource control, one of two objects are returned when the Select() method is called. If the DataSourceMode is set to DataReader, a DataReader object is returned. The type of DataReader (SqlDataReader, OleDbDataReader, OdbcDataReader etc) that is returned depends entirely on the provider type used - in other words, whether you are using the OleDb provider, SqlClient provider etc. It has nothing to do with the type of DataSource control. The examples below both query versions of the NorthWind database. The AccessDataSource control connects to the mdb (Access) version, while the SqlDataSource shows SqlClient classes in operation - it connects to an mdf version of NorthWind.

If the DataSourceMode is set to Dataset, or not set at all (which means that the default setting of Dataset is used) the object that is returned is a DataView. A DataView is like a DataTable on steroids. It exposes methods that allow you to filter and sort data, for example, and bind it. A DataView contains a collection of DataRowView objects, which represent each row in the returned results.

So, with a DataReader, you would access the values during the DataReader.Read() operation, in very much the same way as if you are using plain ADO.NET code, whereas with the DataSet, you would need to create an object of the appropriate type - DataView, then iterate the DataRowView collection to access the values. In this, the code is remarkably similar to accessing values directly from a DataSet's table collection using plain ADO.NET.

The code below shows the contents of an aspx file, which contains four label controls, the two AccessDataSource controls and two SqlDataSource controls. Each type of DataSource control has its DataSource mode set to alternative values - DataReader and DataSet, and they all have an OnSelecting event defined in which the value of the EmployeeID parameter is assigned:


<asp:Label ID="Label1" runat="server" /> <asp:Label ID="Label2" runat="server" />
<br />
<asp:Label ID="Label3" runat="server" /> <asp:Label ID="Label4" runat="server" />


<asp:AccessDataSource 
ID="AccessDataSource1" 
runat="server" 
DataFile="~/App_Data/Northwind.mdb" 
SelectCommand="SELECT [LastName], [FirstName] FROM [Employees] WHERE ([EmployeeID] = ?)" 
DatasourceMode="DataSet"
OnSelecting="AccessDataSource1_Selecting">
    <SelectParameters>
        <asp:Parameter Name="EmployeeID" Type="Int32" />
    </SelectParameters>
</asp:AccessDataSource>

<asp:AccessDataSource 
ID="AccessDataSource2" 
runat="server" 
DataFile="~/App_Data/Northwind.mdb" 
SelectCommand="SELECT [LastName], [FirstName] FROM [Employees] WHERE ([EmployeeID] = ?)" 
DataSourceMode="DataReader"
OnSelecting="AccessDataSource2_Selecting">
    <SelectParameters>
        <asp:Parameter Name="EmployeeID" Type="Int32" />
    </SelectParameters>
</asp:AccessDataSource>

<asp:SqlDataSource 
ID="SqlDataSource1" 
runat="server" 
ConnectionString="<%$ ConnectionStrings:NorthWindConnectionString2 %>"
ProviderName="<%$ ConnectionStrings:NorthWindConnectionString2.ProviderName %>" 
DatasourceMode="DataSet"
SelectCommand="SELECT [LastName], [FirstName] FROM [Employees] WHERE ([EmployeeID] = @EmployeeID)" 
OnSelecting="SqlDataSource1_Selecting">
    <SelectParameters>
        <asp:Parameter Name="EmployeeID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

<asp:SqlDataSource
ID="SqlDataSource2" 
runat="server"
ConnectionString="<%$ ConnectionStrings:NorthWindConnectionString2 %>"
ProviderName="<%$ ConnectionStrings:NorthWindConnectionString2.ProviderName %>" 
DatasourceMode="DataReader"
SelectCommand="SELECT [LastName], [FirstName] FROM [Employees] WHERE ([EmployeeID] = @EmployeeID)" 
OnSelecting="SqlDataSource2_Selecting">
    <SelectParameters>
        <asp:Parameter Name="EmployeeID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

The following code snippet shows the aspx.cs file contents, where the parameter values are set in the Selecting event handler. In the Page_Load method, the data returned by each of the Sql DataSource controls is accessed and a value consigned to a label. The method of access depends on the DataSource mode, but is identical for both SqlDataSource and AccessDataSource:


protected void Page_Load(object sender, EventArgs e)
{
    //AccessDataSource DateSet Mode
    DataView dvAccess = (DataView)AccessDataSource1.Select(DataSourceSelectArguments.Empty);
    Label1.Text = dvAccess[0][1].ToString();

    //AccessDataSource DataReader Mode
    using (OleDbDataReader rdrAccess = (OleDbDataReader)AccessDataSource2.Select(DataSourceSelectArguments.Empty))
    {
        while (rdrAccess.Read())
            Label2.Text = rdrAccess["LastName"].ToString();
    }

    //SqlDataSource DateSet Mode
    DataView dvSql = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
    Label3.Text = dvSql[0][1].ToString();

    //SqlDataSource DataReader Mode
    using (SqlDataReader rdrSql = (SqlDataReader)SqlDataSource2.Select(DataSourceSelectArguments.Empty))
    {
        while (rdrSql.Read())
            Label4.Text = rdrSql["LastName"].ToString();
    }
}

protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["EmployeeID"].Value = 1;
}

protected void AccessDataSource2_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["EmployeeID"].Value = 1;
}

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["@EmployeeID"].Value = 2;
}

protected void SqlDataSource2_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["@EmployeeID"].Value = 2;
}

If you try to use these methods with your control in DataSet mode (the default), don't forget to reference System.Data in your page, as that is required to provide access to the DataView class.

Date Posted:
Last Updated:
Posted by:
Total Views to date: 116144

8 Comments

- Chris Desborough

Hi Mike,

Great example of setting label values - works well. Instead of:

Label1.Text = drvSql["FirstName"].ToString();

I'm trying this to programatically set the visible attribute of a placeholder:

DataView getDrType = (DataView)UpdateSurgAuditDrDetails.Select(DataSourceSelectArguments.Empty);

// There is only one record in the DataSource

foreach (DataRowView drvSql in getDrType)
{
string drType = drvSql["doctor_type"].ToString();
}


if (drType < 8 || drType > 7)
{
GPDoctorsReporting.Visible = true;
}
else
{
GPHDoctorsReporting.Visible = true;
}

In the 'if' statement is says that 'drType' does not exist in the current context. New to c#, and if you could help if would be greatly appreciated.

Cheers
Chris

- Laszlo

Hi!I just need this code, but it doesn't works for me, the error message is: "the type or namespace 'OleDbDataReader' could not be found.."
It's the same error with DataReader or DataView:(
Can you help me please?

- Mike

@Chris

Because you declared your drvSql within the foreach loop without an access modifier, it is private by default and therefore only available within the loop. That's scoping rules for you. What you need to do is to move the "if" test inside the loop.

foreach (DataRowView drvSql in getDrType)
{
string drType = drvSql["doctor_type"].ToString();

if (drType < 8 || drType > 7)
{
GPDoctorsReporting.Visible = true;
}
else
{
GPHDoctorsReporting.Visible = true;
}
}

- Mike

@Laslo

You need to add a reference to the namespace:

C#
using System.Data.OleDb;

VB
Imports System.Data.OleDb

- Daryle Macdonald

I get the following error, what am i doing wrong

BC30456: 'SqlDataSource1_Selecting' is not a member of 'ASP.test_aspx'.

- Mike

@Daryle

Perhaps you don't have a SqlDataSource control with an ID of 'SqlDataSource1'

- Fernando

What if I want to pass parameters natively using the DataSourceSelectArguments object, instead of using the Selecting event from the datasource?
How can this be done please?

- Mike

@Fernando,

I'm not clear what you mean, sorry.

Recent Comments

Thomas 05/03/2018 00:59
In response to I'm Not Writing A Book On Razor Pages
There's a typo on this page: = true)] should be [BindProperty(SupportsGet = true)]...

Rolf Herbert 04/03/2018 19:25
In response to I'm Not Writing A Book On Razor Pages
So is MS deprecating razor Web Pages..? Is it dead..? I wish they would stop killing things so its...

Borut 17/02/2018 12:59
In response to I'm Not Writing A Book On Razor Pages
Mike, is it possible that Web Pages and Razor Pages "live" together in one web application? I a I...

hrboyce 09/02/2018 04:44
In response to I'm Not Writing A Book On Razor Pages
Mike, First thanks for doing this but I have to ask, any chance you would consider converting one of...

aziz sallam 07/02/2018 10:18
In response to I'm Not Writing A Book On Razor Pages
u are a great man...

Satyabrata Mohapatra 31/01/2018 11:36
In response to I'm Not Writing A Book On Razor Pages
This is a great news!!!! Thanks...

tangdf 30/01/2018 07:25
In response to I'm Not Writing A Book On Razor Pages
=> { o.ConfigureFilter(new IgnoreAntiforgeryTokenAttribute()); }); The extension method does...

Obinna Okafor 30/01/2018 04:02
In response to I'm Not Writing A Book On Razor Pages
Thank you very much. I would like to see a project built from scratch using Razor Pages. And it show...

rachida Dukes 31/10/2017 13:52
In response to Customising Identity in Razor Pages
Thanks again for this wonderful tutorial. I followed all the steps in this section called: Adding...

Rachida 31/10/2017 12:06
In response to Customising Identity in Razor Pages
Thanks very much for this wonderful tutorial, it helped a lot....