Home | About | Contact | Article | Cheat Sheet | FAQ | General | Snippet

Blogs I Like

Programmatically accessing data from DataSource controls

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 an Access database, but one uses the AccessDataSource control, and the other uses the SqlDataSource control. Both return OleDbDataReaders, because it is the OleDbProvider library that is used for the connection.

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, and four Datasource controls - two AccessDataSources and two SqlDataSources. Each pair of DataSource controls has its DataSource mode set to alternative values - DataReader and DataSet. and all of them have an OnSelecting event defined:

<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:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
DatasourceMode="DataSet"
SelectCommand="SELECT [LastName], [FirstName] FROM [Employees] WHERE ([EmployeeID] = ?)" 
OnSelecting="SqlDataSource1_Selecting">
	<SelectParameters>
		<asp:Parameter Name="EmployeeID" Type="Int32" />
	</SelectParameters>
</asp:SqlDataSource>

<asp:SqlDataSource
ID="SqlDataSource2" 
runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
DatasourceMode="DataReader"
SelectCommand="SELECT [LastName], [FirstName] FROM [Employees] WHERE ([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 four DataSource controsl 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)
{

	DataView dvAccess = (DataView)AccessDataSource1.Select(DataSourceSelectArguments.Empty);

	foreach (DataRowView drvAccess in dvAccess)
	{
		Label1.Text = drvAccess["FirstName"].ToString();
	}


	OleDbDataReader rdrAccess = 
                   (OleDbDataReader)AccessDataSource2.Select(DataSourceSelectArguments.Empty);
	while (rdrAccess.Read())
	{
		Label2.Text = rdrAccess["LastName"].ToString();
		
	}
	rdrAccess.Close();


	DataView dvSql = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
	foreach (DataRowView drvSql in dvSql)
	{
		Label3.Text = drvSql["FirstName"].ToString();
	}

	OleDbDataReader rdrSql = 
                   (OleDbDataReader)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
	while (rdrSql.Read())
	{
		Label4.Text = rdrSql["LastName"].ToString();

	}
	rdrSql.Close();
}

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;
}

Date Posted: 16 May 2007 10:06
Last Updated: 16 May 2007 14:44
Posted by: Mikesdotnetting
Total Views to date: 20550
  Printer Friendly Version

Comments