Accessing and Updating Data in ASP.NET: Programmatically Accessing Data using the Data Source Controls
By Scott Mitchell
Introduction
Working with data in ASP.NET version 1.x required writing data access code. This involved establishing a connection to the database, specifying the SQL command, and then executing the command. ASP.NET 2.0's data source controls greatly simplifies this process by encapsulating the data access pattern within a Web control construct. As such, data can be retrieved or modified and bound to a Web control without writing a single line of code. We explored the essentials of declarative data binding earlier in this article series in the Data Source Control Basics and Accessing Database Data articles.
In addition to being used declaratively, the data source Web controls can also be accessed programmatically. It is possible
to add a SqlDataSource or AccessDataSource control to a web page, specify SELECT
,
INSERT
, UPDATE
, or DELETE
queries, and then to programmatically execute one of
the control's SQL commands. Such an approach is useful when you need to work with data programmatically, but want to avoid
having to write the boilerplate data access code. In short, using the data source controls in this manner allows for data
to be accessed programmatically with a single line of code.
In this article we will examine how to use the SqlDataSource control to programmatically retrieve, insert, and delete data. Read on to learn more!
Programmatically Issuing SQL Commands with the SqlDataSource and AccessDataSource Controls
The SqlDataSource and AccessDataSource controls can be used to insert, update, delete, and retrieve data. The SQL commands used to perform the insert, update, delete, and data retrieval work are specified via the
InsertCommand
,
UpdateCommand
, DeleteCommand
, and SelectCommand
properties, respectively, and these
command are executed automatically when the data source control is declaratively bound to a data Web control (such as a GridView,
DetailsView, DropDownList, and so forth).
The SQL command specified in these properties may contain parameters. For example, the SelectCommand
might
contain a SELECT
query that returns all products from a particular category by using a parameter named @CategoryID
like so:
SELECT ProductID, ProductName, UnitPrice
|
The parameter's value can be specified via a Parameter control in the data source control's SelectParameters
collection. We talked about using parameters in detail in the Filtering
Database Data with Parameters portion of this article series. The data source control can use parameterized queries
for all of its SQL commands (InsertCommand
, UpdateCommand
, DeleteCommand
, and
SelectCommand
), each of which is specified by corresponding parameter collection properties (InsertParameters
,
UpdateParameters
, DeleteParameters
, and SelectParameters
, respectively).
The SqlDataSource and AccessDataSource controls provide four methods that enable a particular SQL command to be executed. These four methods are:
Select(DataSourceSelectArguments)
- executes theSelectCommand
and returns the results either as a DataView object or as a DataReader, depending on the value of the data source control'sDataSourceMode
property. TheDataSourceSelectArguments
object expected by this method is used to provide additional information on how the data should be modified before it is returned. For example, when sorting or paging through data in the GridView, this input parameter is used to indicate that the results should be ordered by a particular column or that only a particular subset of the records should be returned. For the demos in this article we will always pass in a value ofDataSourceSelectArguments.Empty
, indicating that we do not want the data to be modified.Insert()
- executes theInsertCommand
.Update()
- executes theUpdateCommand
.Delete()
- executes theDeleteCommand
.
INSERT
, UPDATE
, or DELETE
statement executed
with just a single line of code. The boilerplate data access code - creating a connection object, specifying the connection
string, opening the connection, creating a command object, specifying the command text, and so on - is all handled by the
data source control when its, Select()
, Insert()
, Update()
, or Delete()
methods
are called.
The remainder of this article examines two demos that illustrate working with data programmatically using a SqlDataSource control to retrieve and insert data from the Northwind database. The complete source code for these demos - as well as a demo that looks at deleting data in much the same manner - is available as a download at the end of this article.
Programmatically Retrieving Results Using the SqlDataSource Control
In order to programmatically retrieve the results from a data source control we first need to add the data source control to the web page and specify its properties. To illustrate working with data programmatically, let's create a page that allows the user to choose a product category from the
Categories
table. Upon selecting a category, imagine
that we want to display the average price for the products in that category as well as the three most expensive products (their
name and price). While we could accomplish both of these tasks declaratively using data Web controls like the Repeater or
FormView controls, let's instead use programmatic techniques to retrieve this information and display it in a Label Web control.
The following demo includes a DropDownList named Categories
that is declaratively bound to a
SqlDataSource that returns all of the categories. The page also contains two other SqlDataSource controls that are not
bound to any controls. The first one, AvgPriceDataSource
, retrieves the average UnitPrice
for the
specified category. Its SelectCommand
has been assigned to the following query:
SELECT AVG(UnitPrice) as AvgPrice
|
The second data source control, MostExpensiveProductsDataSource
, returns the ProductName
and
UnitPrice
values for the three most expensive products
for the specified category using the following SelectCommand
:
SELECT TOP 3 ProductName, UnitPrice
|
Both controls have a single ControlParameter
in their SelectParameters
collection that populates
the @CategoryID
parameter's value with the CategoryID
value selected from the Categories
DropDownList control.
When programmatically working with data returned from a SqlDataSource control is it important to take not of its
DataSourceMode
property. The DataSourceMode
property indicates the type of data
object returned by the SqlDataSource and it can be set to DataReader
or DataSet
(the default).
If the DataSourceMode
property is set to DataReader
then the Select
method returns
an instance of an IDataReader
object; if its set to DataSet
then a DataView
object
is returned instead.
The difference is important because the code you use to programmatically work with the data differs
for DataViews and Data Readers. Both objects can work with scalar data or sets of data. Data Readers are more efficient
when retrieving data that you simply need to display; DataViews provide more features - the ability to sort and filter the
retrieved data, to randomly access the records, and so forth. The AvgPriceDataSource
SqlDataSource illustrates
working with Data Readers, while MostExpensiveProductsDataSource
looks at using DataViews.
The following method is called whenever the DropDownList's selected index changes and when the data is first bound to
the DropDownList. As the code shows, it programmatically calls the Select()
method of the AvgPriceDataSource
and MostExpensiveProductsDataSource
data source controls and assigns the resulting data to the Text
properties of two different Label controls. Note how the syntax for accessing data differs depending on whether the
SqlDataSource is returning a Data Reader or DataView.
Private Sub ProgrammaticallyBindToDataSourceControls()
|
The code to retrive the data from the AvgPriceDataSource
data source uses an IDataReader
object. It then reads in the data using the Read
method. Since UnitPrice
can contain database
NULL
values, it is possible that all products in the selected category have a NULL
UnitPrice
value, in which case the average will be NULL
as well. If that is the case, we display "Unknown", otherwise
we display the result formatted as a currency.
To retrieve the three most expensive products in the selected category we use a DataView object and iterate through the resulting
records using a For Each
statement. For each record, we display the product's name and price.

Inserting a New Record Programmatically with the SqlDataSource
The ASP.NET 2.0 data Web controls offer declarative inserting, updating, and deleting capabilites. Without writing a line of code it is possible to craft a GridView, DetailsView, or FormView that displays data as well as allows the visitor to edit, delete, and insert data. However, there may be times where this declarative, code-free approach is not what you want or need. Of course, it's always possible to write code to connect to database, craft the
INSERT
, UPDATE
,
or DELETE
statement, and execute the command, but the data source controls make it even easier than that.
Imagine that we wanted to create an interface that allowed visitors to add a new product to the Products
table. In particular, we wanted a highly customizable interface that, for now, displays textboxes for the product's name
and price, a DropDownList for the category, and a CheckBox for the product's discontinued state. This interface would also include
an "Add Product" button that, when clicked, would add a new record to the database using the values entered by the user.
To create such a page without using a FormView or one of the other ASP.NET 2.0 data Web controls you would start by
adding the TextBox, DropDownList, CheckBox, and Button Web controls. Next, add a SqlDataSource control that specifies
the following parameterized INSERT
statement via its InsertCommand
property:
INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued)
|
Map these parameters' values in the InsertParameters
collection using ControlParameters
that point
to the appropriate Web controls on that page. There are a variety of ways to accomplish this. From the Designer, click on
the SqlDataSource and go to the Properties window. There you will see an InsertQuery option that, if clicked, displays the
Command and Parameter Editor shown below. Here you can specify the InsertCommand
, the parameters, and their
source. Note that each of the four parameters uses a Control as its Parameter source, with the ControlID drop-down list
set to the appropriate Web control on the page.

Alternatively, these parameters can be specified via the SqlDataSource control's declarative syntax:
<asp:SqlDataSource ID="AddProductDataSource" runat="server" ConnectionString="..."
|
In any event, once the Web controls have been added to the page and the SqlDataSource's InsertCommand
and
InsertParameters
properties have been correctly configured, inserting a new record is as simple as calling
the data source control's Insert()
method. That is, the only code you need to write is the following line
of code (which would be placed in the "Add Product" Button's Click
event handler):
Protected Sub btnAddProduct_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddProduct.Click
|

Conclusion
ASP.NET 2.0's data source controls are typically used declaratively, providing a code-free way to work with data. In scenarios where the data needs to be retrieved, inserted, updated, or deleted programmatically, however, you can save a few lines of boilerplate data access code by using the data source controls as we saw in this tutorial. Simply add a SqlDataSource or AccessDataSource control to the page, configure its properties, and then execute the specified SQL command by invoking the control's
Select()
, Insert()
, Update()
, or Delete()
method. This tutorial
showcased two demos: one that retrieved data and one that inserted data. The download available at the end of this article
provides the complete source code to these two tutorials, plus an additional demo on using the SqlDataSource control to
programmatically delete database data.
Happy Programming!
Attachments: