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.

Data Source Control Basics - explores the concepts and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.

Accessing Database Data - shows how to use the SqlDataSource and AccessDataSource controls to query data from a relational database.

Filtering Database Data with Parameters - learn how to retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page, session variables, and so on.

Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.

Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.

Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.

Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.

Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.

Inserting Data - learn how to insert data using a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.

Deleting Data - see how to delete data using a SqlDataSource control. Also looks at how to programmatically cancel a delete.

Updating Basics - learn the basics of updating database data using a SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.

Customizing the Editing Interface - see how to customize the GridView's columns to provide a customized editing interface that includes input validation and alternative user interface elements.

Handling Database NULL Valuese - explore how to extend the GridView's customized editing interface to handle database NULL values.

Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.

Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.

(Subscribe to this Article Series! )

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. Read on to learn more!

- continued -

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.)

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 JOINs. 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 the Web.config's <connectionStrings> section. If it's a reference to a Web.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
If you define your connection string in Web.config you can use the following syntax to set this property to the value defined there: ProviderName="<%$ ConnectionStrings:ConnectionStringName.ProviderName %>"
SelectCommand - the SELECT query to issue to the database, such as SELECT [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.

See Contrasting the ADO.NET DataReader and DataSet for more information on the differences and similarities.

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.

HTML Portion <asp:Label ID="RandomProductNames" runat="server"></asp:Label> <asp:SqlDataSource ID="rndProductsDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" ProviderName="<%$ ConnectionStrings:NorthwindConnectionString.ProviderName %>" SelectCommand="SELECT [ProductID], [ProductName] FROM [Products]"> </asp:SqlDataSource> Page_Load Event Handler 'Programmatically access the SqlDataSource - get back a DataView Dim dv As DataView = _ CType(rndProductsDataSource.Select(DataSourceSelectArguments.Empty), DataView) Dim rndCoinFlip As New Random RandomProductNames.Text = String.Empty For Each dr As DataRow In dv.Table.Rows 'See if we want to show this If rndCoinFlip.Next(2) = 0 Then 'Show RandomProductNames.Text &= _ dr("ProductName").ToString() & "<br />" Else 'Don't show End If Next

HTML Portion The average price of all products in the Products database table is: <asp:Label ID="AvgPrice" runat="server" ForeColor="Red"></asp:Label> <asp:SqlDataSource ID="avgPriceDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" ProviderName="<%$ ConnectionStrings:NorthwindConnectionString.ProviderName %>" SelectCommand="SELECT AVG(UnitPrice) FROM Products"> </asp:SqlDataSource> Page_Load Event Handler // Retreive the results from the SqlDataSource as a DataReader OleDbDataReader reader = (OleDbDataReader) avgPriceDataSource.Select(DataSourceSelectArguments.Empty); // Read in the value if (reader.Read()) // Format the price as a currency AvgPrice.Text = string.Format("{0:c}", reader[0]); else // Whoops, there were no results! AvgPrice.Text = "I don't know!"; // Close the reader reader.Close();

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!

By Scott Mitchell

Attachments

Download the code used in this article