Programmatically accessing data from DataSource controls

4.72 (18 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.

You might also like...

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

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

Pam 30/08/2017 11:30
In response to Sending Email in Razor Pages
Mike, RazorPages sound like a nice choice for somebody still working in ASP classic who wants to to...

Robby Robson 15/08/2017 00:43
In response to Routing in Razor Pages
Mike: great stuff. Now that .Core Standard 2.0 is formally out, how soon will you rewrite your book...

Satyabrata Mohapatra 28/07/2017 08:59
In response to Sending Email in Razor Pages
Bit off topic, but congratulation sir for your MVP award. You deserve it !!!...

Satyabrata Mohapatra 23/07/2017 16:43
In response to Razor Pages - The Elevator Pitch
@Dale Severin You can continue to build apps using asp.net web pages....

Satyabrata Mohapatra 23/07/2017 16:40
In response to Sending Email in Razor Pages
Thanks for sharing...learned a lot...

Gfw 22/07/2017 11:53
In response to Sending Email in Razor Pages
Question... Does System.Net.Mail support SSL?...

Dale Severin 20/07/2017 03:38
In response to Razor Pages - The Elevator Pitch
I work with razor web pages extensively. I appreciate the rapid development it permits me to I am as...

Obinna Okafor 14/07/2017 01:19
In response to Routing in Razor Pages
Thank you, Mike. Good post....

Satyabrata Mohapatra 11/07/2017 16:02
In response to Routing in Razor Pages
Very powerful routing system!!...

Cyrus 05/07/2017 03:41
In response to Razor Pages - Getting Started With The Preview
How can I trim packages and services as much as possible to use just razor pages? I don’t want to to...