Accessing and Updating Data in ASP.NET 2.0: Accessing Database Data
by Scott Mitchell

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)
While only five to ten lines of code is needed to perform these four steps, and libraries like the Data Access Application Block help reduce the volume of code that you need to write, the fact remains that in order to access or modify data in an ASP.NET 1.x application you must write code.
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.

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 its DataFile property.
Both controls have virtually the same featureset, the only difference being how you specify the connection information. In fact, the AccessDataSource control is really superfluous since Microsoft Access databases can be accessed through the SqlDataSource control just as easily. (True, you have to provide a connection string rather than simply the path to the file, but Visual Studio 2005 can automatically create appropriate connection strings for those databases residing in your 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.)