Working with Data in ASP.NET 2.0 :: Querying Data
with the SqlDataSource Control

Introduction

All of the tutorials we’ve examined so far have used a tiered architecture consisting of presentation, Business
Logic, and Data Access layers. The Data Access Layer (DAL) was crafted in the first tutorial (Creating a DataAccess Layer) and the Business Logic Layer in the second (Creating a Business Logic Layer). Starting with theDisplaying Data With the ObjectDataSource tutorial, we saw how to use ASP.NET 2.0’s new ObjectDataSourcecontrol to declaratively interface with the architecture from the presentation layer.

While all of the tutorials so far have used the architecture to work with data, it is also possible to access, insert,update, and delete database data directly from an ASP.NET page, bypassing the architecture. Doing so places thespecific database queries and business logic directly in the web page. For sufficiently large or complex applications, designing, implementing, and using a tiered architecture is vitally important for the success,updatability, and maintainability of the application. Developing a robust architecture, however, can be overkill when creating exceedingly simple, oneoff applications.

ASP.NET 2.0 provides five builtin data source controls – SqlDataSource, AccessDataSource, ObjectDataSource,XmlDataSource, and SiteMapDataSource. The SqlDataSource can be used to access and modify data directly froma relational database, including Microsoft SQL Server, Microsoft Access, Oracle, MySQL, and others. In this tutorial and the next three, we’ll examine how to work with the SqlDataSource control, exploring how to query and filter database data, as well as how to use the SqlDataSource to insert, update, and delete data.

Figure 1: ASP.NET 2.0 Includes Five BuiltIn Data Source Controls

Comparing the ObjectDataSource and SqlDataSource

Conceptually, both the ObjectDataSource and SqlDataSource controls are simply proxies to data. As discussed in the Displaying Data With the ObjectDataSource tutorial, the ObjectDataSource has properties that indicate the object type that provides the data and the methods to invoke to select, insert, update, and delete data from the underlying object type. Once the ObjectDataSource’s properties have been configured, a data Web control – such as a GridView, DetailsView, or DataList – can be bound to the control, using the ObjectDataSource’s Select(),Insert(), Delete(), and Update() methods to interact with the underlying architecture.

The SqlDataSource provides the same functionality, but operates against a relational database rather than an object library. With the SqlDataSource, we must specify the database connection string and the adhoc
SQL queries or stored procedures to execute to insert, update, delete, and retrieve data. The SqlDataSource’s Select(), Insert(),Update(), and Delete() methods, when invoked, connect to the specified database and issue the appropriate SQL query. As the following diagram illustrates, the these methods do the grunt work of connecting to a database,issuing a query, and returning the results.

                                Figure 2: The SqlDataSource Serves as a Proxy to the Database

Note: In this tutorial we’ll focus on retrieving data from the database. In the Inserting, Updating, and Deleteing Data with the SqlDataSource Control tutorial, we’ll see how to configure the SqlDataSource to support inserting,updating, and deleting.

The SqlDataSource and AccessDataSource Controls

In addition to the SqlDataSource control, ASP.NET 2.0 also includes an AccessDataSource control. These two different controls lead many developers new to ASP.NET 2.0 to suspect that the AccessDataSource control is designed to work exclusively with Microsoft Access with the SqlDataSource control designed to work exclusively with Microsoft SQL Server. While the AccessDataSource is designed to work specifically with Microsoft Access,the SqlDataSource control works with any relational database that can be accessed through .NET. This includes any OleDbor ODBCcompliant data stores, such as Microsoft SQL Server, Microsoft Access, Oracle, Informix,MySQL, and PostgreSQL, among many others.

The sole difference between the AccessDataSource and SqlDataSource controls is how the database connection information is specified. The AccessDataSource control needs just the file path to the Access database file. The SqlDataSource, on the other hand, requires a complete connection string.

Step 1: Creating the SqlDataSource Web Pages

Before we start exploring how to work directly with database data using the SqlDataSource control, let’s first take a moment to create the ASP.NET pages in our website project that we’ll need for this tutorial and the next three.Start by adding a new folder named SqlDataSource. Next, add the following ASP.NET pages to that folder,making sure to associate each page with the Site.master master page:

l Default.aspx
l Querying.aspx
l ParameterizedQueries.aspx
l InsertUpdateDelete.aspx
l OptimisticConcurrency.aspx

 

Figure 3: Add the ASP.NET Pages for the SqlDataSourceRelated Tutorials

Like in the other folders, Default.aspx in the SqlDataSource folder will list the tutorials in its section. Recall

that the SectionLevelTutorialListing.ascx User Control provides this functionality. Therefore, add this User
Control to Default.aspx by dragging it from the Solution Explorer onto the page’s Design view.

                    Figure 4: Add the SectionLevelTutorialListing.ascx User Control to Default.aspx

Lastly, add these four pages as entries to the Web.sitemap file. Specifically, add the following markup after the“Adding Custom Buttons to the DataList and Repeater” <siteMapNode>:

<siteMapNode url="~/SqlDataSource/Default.aspx" title="Using the SqlDataSource Control"
description="Work directly with database data using the SqlDataSource control.">
<siteMapNode url="~/SqlDataSource/Querying.aspx" title="Retrieving Database Data"
description="Examines how to query data from a database that can then be
displayed through a data Web control."/>
<siteMapNode url="~/SqlDataSource/ParameterizedQueries.aspx"
title="Parameterized Queries"
description="Learn how to specify parameterized WHERE clauses in the
SqlDataSource's SELECT statement." />
<siteMapNode url="~/SqlDataSource/InsertUpdateDelete.aspx"
title="Inserting, Updating, and Deleting Database Data"
description="See how to configure the SqlDataSource to include INSERT, UPDATE,
and DELETE statements." />
<siteMapNode url="~/SqlDataSource/OptimisticConcurrency.aspx"
title="Using Optimistic Concurrency"
description="Explore how to augment the SqlDataSource to include support for
optimistic concurrency." />
</siteMapNode>

After updating Web.sitemap, take a moment to view the tutorials website through a browser. The menu on the left now includes items for the editing, inserting, and deleting tutorials.

Step 2: Adding and Configuring the SqlDataSource Control

Start by opening the Querying.aspx page in the SqlDataSource folder and switch to Design view. Drag a
SqlDataSource control from the Toolbox onto the Designer and set its ID to ProductsDataSource. As with the ObjectDataSource, the SqlDataSource does not produce any rendered output and therefore appears as a gray box on the design surface. To configure the SqlDataSource, click on the Configure Data Source link from the SqlDataSource’s smart tag.

             Figure 6: Click on the Configure Data Source Link from the SqlDataSource’s Smart Tag

This brings up the SqlDataSource control’s Configure Data Source wizard. While the wizard’s steps differ from the ObjectDataSource control’s, the end goal is the same – to provide the details on how to retrieve, insert, update, and delete data through the data source. For the SqlDataSource this entails specifying the underlying database to use and providing the adhoc SQL statements or stored procedures.

Since we’ve already added a connection string for the NORTHWIND.MDF database in the App_Data folder to our project’s Web.config file, the dropdown list includes a reference to that connection string,
NORTHWINDConnectionString. Choose this item from the dropdown list and click Next.

                  Figure 7: Choose the NORTHWINDConnectionString from the DropDown List

After choosing the database, the wizard asks for the query to return data. We can either specify the columns of a table or view to return or can enter a custom SQL statement or specify a stored procedure. You can toggle between this choice through the “Specify a custom SQL statement or stored procedure” and “Specify columns from a table or view” radio buttons.

Figure 8 shows the “Configure the Select Statement” screen when the “Specify columns from a table or view”radio button is selected. The dropdown list contains the set of tables and views in the Northwind database, with the selected table or view’s columns displayed in the checkbox list below. For this example, let’s return the ProductID, ProductName, and UnitPrice columns from the Products table. As Figure 8 shows, after making these selections the wizard shows the resulting SQL statement – SELECT [ProductID], [ProductName],[UnitPrice] FROM [Products].

                                               Figure 8: Return Data from the Products Table

Then Next Once you have configured the wizard to return the ProductID, ProductName, and UnitPrice columns from the Products table, click the Next button. This final screen provides an opportunity to examine the results of the query configured from the previous step. Clicking the Test Query button executes the configured SELECT statement and displays the results in a grid.

                            Figure 9: Click the Test Query Button to Review Your SELECT Query

Then use This Command in code

<asp:SqlDataSource ID="ProductsDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products]">
</asp:SqlDataSource>

Step 3: Adding a Data Web Control and Binding it to the
SqlDataSource

                          Figure 10: Add a GridView and Bind it to the SqlDataSource Control

Once you’ve selected the SqlDataSource control from the dropdown
list in the GridView’s smart tag, Visual Studio will automatically add a BoundField or CheckBoxField to the GridView for each of the columns returned by the data source control. Since the SqlDataSource returns three database columns – ProductID, ProductName,and UnitPrice – there are three fields in the GridView.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="ProductID" DataSourceID="ProductsDataSource"
EnableViewState="False">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ProductID"
InsertVisible="False" ReadOnly="True" SortExpression="ProductID" />
<asp:BoundField DataField="ProductName" HeaderText="Product Name"
SortExpression="ProductName" />
<asp:BoundField DataField="UnitPrice" HeaderText="Price"
SortExpression="UnitPrice" DataFormatString="{0:c}"
HtmlEncode="False" />
</Columns>
</asp:GridView>