Accessing and Updating Data in ASP.NET: Accessing Database Data
By Scott Mitchell
A Multipart Series on ASP.NET's Data Source Controls |
---|
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data.
These controls allow page developers to declaratively access and modify data without writing any
code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.
Parameter controls for use in the data source controls' parameters collections.IDENTITY column value for the just-inserted record. |
Introduction
One of the coolest new features of ASP.NET 2.0 is its new Data Source controls. The Data Source controls are a collection of Web controls designed to provide a declarative approach to accessing and modifying data. In short, with the Data Source controls you can work with data without having to write a lick of data access code. Compare this to ASP.NET 1.x, which required oft-repeated code to access data. For example, to display the contents of a database table in a web page with ASP.NET 1.x, you'd need to write code to:
- Connect to the database,
- Issue the command,
- Retrieve the results, and
- Work with the results / bind them to a data Web control (such as a DataGrid)
With ASP.NET 2.0, data can be accessed entirely from declarative markup. In this article we'll be examining how to access data
from databases with the SqlDataSource and AccessDataSource controls. With both of these controls you can specify
the SELECT
query to use and any parameters in the WHERE
clause as properties of the Data Source
controls. The code to connect to the database, issue the command, and retrieve the results is handled internally by the
Data Source control. With the declarative, "code free" Data Source controls you can create a page that displays database
data in under 15 seconds and without having to write a line of code. Read on to learn more!
Accessing Database Data
ASP.NET 2.0 provides two Data Source controls designed specifically to access data from a database:
- SqlDataSource - useful for accessing data from any database that resides in a relational database. The "Sql" in the control name does not refer to Microsoft SQL Server, but rather the SQL syntax for querying relational databases, for the SqlDataSource control can be used to access not only Microsoft SQL Server databases, but Microsoft Access databases, Oracle databases... basically any OLE-DB or ODBC-compliant data store.
- AccessDataSource -
the AccessDataSource is very similar to the SqlDataSource. The key difference is that instead of requiring a connection
string to the database, the AccessDataSource control allows you to simply specify the file path to the Access
.MDB
file through itsDataFile
property.
App_Data
folder.)
In this article we'll examine the SqlDataSource in detail, just touching upon the one difference in the AccessDataSource. Furthermore, the SqlDataSource and AccessDataSource controls can be used to both access and modify data; however, this article only examines accessing data. We'll see how to use these Data Source controls to modify database data in a future installment. Also, we'll focus on working with these Data Source controls in Visual Studio 2005 through the Design view. While you can set all of these properties by hand in the Source view, the Design view offers a time-saving wizard for quickly specifying which database to connect to and what query to issue.
To get started, fire up Visual Studio 2005 and create a new website or, alternatively, download the code example at the end of this article. For this article I'm going to demonstrate connecting to, retrieving, and displaying data from the Microsoft Access Northwind database, which is included in the download. (Note that I'm using a slightly modified, slimmed down version of Northwind, one whose Forms, Modules, Reports, and Macros have all been deleted to reduce the file size.)
ASP.NET 2.0 introduces the App_Data
folder, a folder that you can add to your website specifically for storing
data files, such as XML files, Access database files (.mdb
), and SQL Server 2000 and SQL Server 2005 Express
edition database files (.mdf
). In the code download you'll find the Northwind.mdb
file in the
App_Data
folder. Of course, ASP.NET can work with databases that are not in the App_Data
folder.
When using professional grade database systems, like Microsoft SQL Server, typically the database is registered on a separate
database server.
Regardless of where your database is located, once you have a database to work with in order to access its contents you'll want to add a SqlDataSource (or AccessDataSource) control to the page. Start by creating a new ASP.NET page and go to the Design view. Next, drag a SqlDataSource control from the Toolbox onto the Designer. The Data Source controls display in the Designer as a little gray box; they do not emit any HTML markup when a browser visits the page. In order to correctly access data, we must set a variety of SqlDataSource properties that indicate the database to use and the query to execute. While these can be set through the Properties pane, it's usually quicker to use the wizard, which can be reached by clicking on the "Configure Data Source" link in the SqlDataSource control's smart tag.
Configuring the SqlDataSource Control Using the Wizard
When you click the "Configure Data Source" link a wizard appears, prompting you to select the data to retrieve. The first screen, shown below, prompts you to select the database from which the data will be queried. The drop-down list lists existing connection strings defined in the
Web.config
's <connectionStrings>
section and
those in the App_Data
folder. To connect to a database registered somewhere else, click the New Connection button
and specify the database server and authentication information. (For more information on working with the <connectionStrings>
section
refer to an earlier article of mine, Working with Databases
in ASP.NET 2.0 and Visual Studio 2005.)

After selecting your database, click the Next button. If you don't have a <connectionStrings>
defined for
this database in Web.config
, the wizard will kindly do so for you, if you like. There are a plethora of advantages
to defining your connection strings in Web.config
, the key one being that it makes your application more maintainable.
Imagine that, down the road, you move database servers and need to update the connection string. If you have it defined in
Web.config
, you have one place to update. If not, you have to find each and every page that references the connection
string and update it accordingly. Furthermore, by placing your connection string information in Web.config
you
can encrypt your connection strings to further
protect your site. (However, the wizard won't automatically write to the <connectionStrings>
section if
you have it encrypted.) The screenshot below shows this second step; as you can see, I've opted to save the connection string to
Web.config
using the name NorthwindConnectionString
.

Contrasting the AccessDataSource... |
---|
The AccessDataSource differs from the SqlDataSource in one minor aspect - rather than specifying a connection string as with the SqlDataSource, the AccessDataSource prompts for the path to the database file. Therefore, the screen shots above are different for the AccessDataSource. Specifically, the first screen shot examined prompts for a file location; since there is no connection string, the second screen shot does not appear in the AccessDataSource. However, the remaining screen shots are identical for both the AccessDataSource and SqlDataSource. |
On the next screen you are asked to choose your SELECT
statement. As you can see by the two radio buttons at the
top of the screen you can either pick a table or view and select the columns to return (as shown in the screen shot below),
or write a custom SQL statement or pick a stored procedure. If you opt to specify columns from a table or view, simply pick
the table or view from the drop-down list and then check those columns you want to return. Here you can see that I'm returning
the ProductID
, ProductName
, UnitPrice
, and UnitsInStock
columns from the
Products
table. This query will return all records from the Products
table; we'll see how to
filter the returned records in a future article.

By default the data is returned in the order the database stores the records. If there's a clustered index (such as a
primary key constraint) on the table, the data will be ordered by the column(s) that make up that index; otherwise, the
data is ordered by order it was entered into the database table. To specify a different ordering, click the ORDER BY button.
Here you can choose what columns the data should be sorted by, and if it should be sorted in ascending or descending order.
The selections here tack on an ORDER BY
clause to the SELECT
statement.

If you opt to select the columns to return from a table or view you can only configure simple queries that involve a single
table or view. With relational databases, often there's data in related tables that we need to grab, requiring JOIN
s.
Or the data to retrieve might be accessible from a stored procedure. In these cases we must select the "Specify a custom SQL
statement of stored procedure" radio button and click Next. Doing so will take us to the screen shot shown below. Here you can type in a SQL query by hand into the SELECT
tab, or pick a stored procedure from those listed in the drop-down list.

If you're creating a particularly nasty SQL statement or are a bit rusty on the SQL syntax, you may want to click the Query
Builder button. This will launch a graphical tool that lets you select what tables to query and automatically adds the
JOIN
syntax. The screen shot below shows the Query Builder in action. As you can see, I've added two tables
to the query window - Categories
and Products
- and checked those columns from the tables I want
to return. I also specified that the results should be ordered first by the CategoryName
column, and then by
ProductName
. With just a few mouse clicks the Query Builder has kindly created the SELECT
query.

After configuring your SELECT
statement - either by selecting a table or view or by picking a stored procedure
or writing a custom SQL statement, click the Next button to reach the final screen. Here you can run the query you've configured
against the database and observe the results. To complete the wizard, click the Finish button.

Configuring the SqlDataSource Through the Properties Pane
Prior to ASP.NET 2.0 I was firmly in the camp "doing things by hand." I didn't like wizards, I felt that they abstracted away too much and were too often used as a crutch and enabled developers to be able to achieve some goal without really learning what it was they just did. This fact often jumps up and bites the head of the wizard-using developer when something goes wrong. Since they've relied on the wizard, they don't know how to fix whatever bug or problem has surfaced.
I still have that overall feeling about wizards, in general, but have really been turned on to the wizards in Visual Studio 2005. Partly because they save so much time, but primarily because all they're doing is setting properties. They're not writing oodles of code on your behalf, which is when the wizards become dangerous. However, if you're still a fan of not using wizards, that's quite all right, as configuring the SqlDataSource through its properties is also a walk in the park. Simply set the following properties, either through the Properties pane or as declarative markup:
ConnectionString
- Set this to your database's connection string. This can either be a fully-qualified connection string or a reference to a connection string name in theWeb.config
's<connectionStrings>
section. If it's a reference to aWeb.config
connection string use the syntax:ConnectionString="<%$ ConnectionStrings:ConnectionStringName %>"
.For the AccessDataSource control set the
DataFile
property to the path to the Access database file, like~/App_Data/Northwind.mdb
.ProviderName
- the name of the database provider to use. By default, the SqlDataSource uses the SqlClient provider, which is a provider designed specially for Microsoft SQL Server. If you are using a different database, set this property accordingly. The .NET Framework ships with four database providers:- System.Data.SqlClient - used for fast access to Microsoft SQL Server (the default)
- System.Data.OleDb - used for accessing OLE-DB-compliant data stores, such as Access
- System.Data.Odbc - used for accessing older, ODBC-compliant data stores
- System.Data.OracleClient - used for fast access to Oracle databases
Web.config
you can use the following syntax to set this property to the value defined there:ProviderName="<%$ ConnectionStrings:ConnectionStringName.ProviderName %>"
SelectCommand
- theSELECT
query to issue to the database, such asSELECT [ProductID], [ProductName] FROM [Products] ORDER BY [ProductName]
.
Retrieving the Data from a SqlDataSource Control
Typically, when accessing data from a database we want to display or process it in some manner. To simply display the data, use any data Web control (such as the GridView) and set the data Web control's
DataSourceID
property to the ID
of the SqlDataSource.
(This can most easily be done through the data Web control's smart tag in the Design view. There you'll find a drop-down list
from which you can select the SqlDataSource control to bind to the data Web control.)
When the page is visited, the data Web control will automatically grab the data from the associated SqlDataSource control and
display it. There's a demo of doing just this in this article's download. The screen shot below shows the GridView when displaying
the ProductID
, ProductName
, UnitPrice
, and UnitsInStock
columns from the
Products
table ordered by UnitPrice
ascending with ties broken by the alphabetic sorting of ProductName
.

You can also programmatically access the contents of a SqlDataSource control and work with the data in code. Before we see how that is accomplished, let's first look at how the data is returned from the underlying database by the SqlDataSource control. If you're familiar with data access in ASP.NET version 1.x you know that there are two techniques/objects for working with data:
- DataReaders - a DataReader offers forward-only, read-only access to a database, and requires an active connection. More efficient than a DataSets/DataTables/DataViews.
- DataSet/DataTable/DataView - a DataTable offers a random access, editable representation of a query. A DataSet is a collection of DataTables. DataViews can be used to retrieve a filtered or sorted subset of records in a DataTable.
The SqlDataSource can return either a DataSet or DataReader. This option is configurable via the SqlDataSource's
DataSourceMode
property,
with the default being DataSet. In order to utilize the GridView's built-in sorting and paging capabilities, or the
SqlDataSource's caching capabilities (another topic for a future article!) you'll need to have the SqlDataSource return a
DataSet. (The AccessDataSource control also has the DataSourceMode
property.)
To programmatically access the contents of a SqlDataSource control call the
Select()
method. This method expects a single input parameter of type DataSourceSelectArguments
.
This parameter can be used to pass along requests for the SqlDataSource to message the data before returning it. For example,
when working with a sortable GridView, sorting a column calls the Select()
method passing in a
DataSourceSelectArguments
instance with its SortExpression
property set to the column name the
end user sorted the data by. If you don't want the SqlDataSource to sort, filter, or return only a "page" of data, simply pass
in DataSourceSelectArguments.Empty
, as shown in the code example below.
The Select()
method returns an object that implements IEnumerable
. Specifically, it returns a
DataView
if the DataSourceMode
property is set to DataSet, and a DataReader object if its set to
DataReader. The following two examples show how to programmatically access the data from a SqlDataSource. The first one, written
in VB, returns a DataSet and then is iterated through, displaying a random subset of results. The second example, written in C#, uses a
SqlDataSource that returns a scalar value (the average price of all products) and displays that in a Label Web control on the
page.
|
|
Note that in both examples I explicitly cast the return value from the Select()
method either into a DataView
or OleDbDataReader
object, depending on the SqlDataSource's DataSourceMode
property. (Since DataReaders
are provider-specific objects, if I was using the SqlClient provider I'd be using a SqlDataReader
instead; if
I was using the OracleProvider, I'd be using an OracleDataReader
, and so on.)
Conclusion
In this installment we saw the basics of accessing data from databases using the SqlDataSource and AccessDataSource controls. While we only looked at retrieving all records from a table, we'll turn our attention to more useful SqlDataSource tasks in future articles. When querying data with the SqlDataSource control you can indicate the database to connect to and the data to return via the "Configure Data Source" wizard, which is accessible through the SqlDataSource control's smart tag. Once the Data Source control has been configured, the data can be displayed by binding it to a data Web control or processed programmatically by calling the SqlDataSource control's
Select()
method. The precise code you use for programmatically
working with a SqlDataSource control's data depends on how the data is returned (which depends on the DataSourceMode
property's
value).
Happy Programming!
Attachments
Further Reading
A Multipart Series on ASP.NET's Data Source Controls |
---|
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data.
These controls allow page developers to declaratively access and modify data without writing any
code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.
Parameter controls for use in the data source controls' parameters collections.IDENTITY column value for the just-inserted record. |