Accessing and Updating Data in ASP.NET: Programmatically Accessing Data using the Data Source ControlsBy Scott Mitchell
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
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
SelectCommandproperties, 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
SELECT query that returns all products from a particular category by using a parameter named
The parameter's value can be specified via a Parameter control in the data source control's
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 (
SelectCommand), each of which is specified by corresponding parameter collection properties (
The SqlDataSource and AccessDataSource controls provide four methods that enable a particular SQL command to be executed. These four methods are:
Select(DataSourceSelectArguments)- executes the
SelectCommandand returns the results either as a DataView object or as a DataReader, depending on the value of the data source control's
DataSourceSelectArgumentsobject 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 of
DataSourceSelectArguments.Empty, indicating that we do not want the data to be modified.
Insert()- executes the
Update()- executes the
Delete()- executes the
DELETEstatement 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,
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
Categoriestable. 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:
The second data source control,
MostExpensiveProductsDataSource, returns the
UnitPrice values for the three most expensive products
for the specified category using the following
Both controls have a single
ControlParameter in their
SelectParameters collection that populates
@CategoryID parameter's value with the
CategoryID value selected from the
When programmatically working with data returned from a SqlDataSource control is it important to take not of its
DataSourceMode property indicates the type of data
object returned by the SqlDataSource and it can be set to
DataSet (the default).
DataSourceMode property is set to
DataReader then the
Select method returns
an instance of an
IDataReader object; if its set to
DataSet then a
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
MostExpensiveProductsDataSource data source controls and assigns the resulting data to the
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.
The code to retrive the data from the
AvgPriceDataSource data source uses an
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
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
DELETEstatement, 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
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
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:
In any event, once the Web controls have been added to the page and the SqlDataSource's
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):
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
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.