Chapter 12: Advanced Data Access

However, SqlDataReader isn’t the only means of getting to your data, and in many scenarios, it makes sense to use one of the two popular alternatives:

1.The first alternative involves using the new ADO.NET data source controls, which are tightly integrated with the GridView and DetailsView controls, and allow you to implement reading, updating, deleting, inserting, paging, and sorting features very easily—for the most part, you don’t even need to write any code!

2.The second alternative involves using the SqlDataAdapter class in conjunction with the DataTable, DataView, and DataSet classes, which are able to read data from the database and store it locally, allowing you to browse, filter, and sort data in your code without leaving a connection to the database open. This method occupies more memory on the server that runs your application, and means that fewer of Visual Web Developer’s automated features are available to you (you have to write more code!), but it does give you more flexibility in terms of what you can do with your data.

In this chapter, you’ll learn how to use both of these alternate data access methods.

Using Data Source Controls

The .NET framework offers five data source controls: SqlDataSource,

AccessDataSource, ObjectDataSource, XmlDataSource, and SiteMapDataSource. These objects enable automatic connection to various data sources, and provide easy ways to read or modify your database using data-bound controls.

SqlDataSource allows you to connect to any data source that has an ADO.NET data provider. The default providers that ship with .NET 2.0 are

SqlClient, OracleClient, OleDb, and Odbc. Even though the name of the class is SqlDataSource, the fact that its name begins with Sql doesn’t meant it works only with SQL Server—it’s really very flexible. This is the data source we’ll work with in this chapter.

AccessDataSource is the data source object we use to connect to Access databases.

ObjectDataSource allows us to connect to custom data access classes, and is appropriate when we’re creating complex application architectures.

XmlDataSource knows how to connect to XML files.

470

Using Data Source Controls

SiteMapDataSource knows how to connect to a sitemap data source, and can be used to generate sitemaps. We worked a little with this data source control in Chapter 4.

You can find these controls in the Data tab of Visual Web Developer’s Toolbox, as Figure 12.1 shows.

Figure 12.1. The Data Source Controls in the Toolbox

In Chapter 11, we implemented the functionality required to show employee details in the Dorknozzle address book page (AddressBook.aspx). We used the

SqlDataReader class for the task, which means we’ve achieved the best possible performance. However, we wrote quite a bit of code to implement the viewing and editing features for that page, and we’d need to do even more hand coding to implement paging, sorting, and inserting features.

This time, to make it easier on our fingers, we’ll use the SqlDataSource object instead. This object can automate many tasks for us, and while it may not always provide the best performance or the greatest flexibility, it’s important that we know how to use it, because it can come in very handy for quick programming tasks.

471

Chapter 12: Advanced Data Access

Binding the GridView to a SqlDataSource

We’ll start by binding the GridView control in AddressBook.aspx to a SqlDataSource; we’ll deal with the DetailsView control later. Since the data sources work with different SQL queries, we’ll need to create two data sources: one that reads all employees (to populate the GridView), and one that reads the details of one employee (to populate the DetailsView).

Let’s start by deleting all the code in the code-behind file (AddressBooks.aspx.vb or AddressBook.aspx.cs). Yes, you’ve read this correctly: we’re starting from scratch! As you’ll see, we can implement a large number of features without any code at all when using the SqlDataSource class. Leave your code-behind files like this:

Visual Basic

File: AddressBook.aspx.vb (excerpt)

Imports System.Data.SqlClient

Partial Class AddressBook

Inherits System.Web.UI.Page

End Class

C# File: AddressBook.aspx.cs (excerpt)

using System; using System.Data;

using System.Configuration; using System.Collections; using System.Web;

using System.Web.Security; using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

public partial class AddressBook : System.Web.UI.Page

{

}

If you’re using C#, you’ll also need to delete the event handler declarations from

AddressBook.aspx. Remove the OnSelectedIndexChanged property from the GridView control, and the OnModeChanging and OnItemUpdating properties from the DetailsView control.

Open AddressBook.aspx in Design View, and drag the SqlDataSource control from the Toolbox (it’s located under the Data tab) onto the form. You can place

472

Binding the GridView to a SqlDataSource

it anywhere you like—the location isn’t relevant because the control doesn’t display in the browser. Of course, it will appear in the Design View, as Figure 12.2 shows.

Figure 12.2. AddressBook.aspx with an SqlDataSource control

Rename the object employeesDataSource. In Source View, the code for the new control should look like this:

File: AddressBook.aspx (excerpt)

<asp:SqlDataSource id="employeesDataSource" runat="server"> </asp:SqlDataSource>

Switch back to Design View, click the SqlDataSource control’s smart tag, and select Configure Data Source. A dialog will appear, giving us the opportunity to provide the details of the data source. In the first page of the dialog, we specify the data connection we want to use. If we hadn’t already added the Dorknozzle connection string to the Web.config file, we could have clicked the New Connection… button, and used the wizard to add a connection string to Web.config.

473

Chapter 12: Advanced Data Access

However, as we’ve already set up the connection string, we simply choose it from the drop-down list, as shown in Figure 12.3.

Figure 12.3. Specifying the connection string

After we’ve selected the Dorknozzle connection, we click Next. This is where the fun begins!

In the next screen, we can specify the database table and the columns that we want our data source object to handle. Select the Employees table, and check the following columns: EmployeeID, Name, City, and MobilePhone, as depicted in Figure 12.4.

Click the ORDER BY… button and select the Name column (or any other column by which you want to sort your employees), as illustrated in Figure 12.5.

474

Binding the GridView to a SqlDataSource

Figure 12.4. Choosing columns

Figure 12.5. Specifying an ORDER BY clause

475

Chapter 12: Advanced Data Access

Figure 12.6. Testing the data source

Click OK, then Next. In the dialog that appears, press the Test Query button to test that the query will work with this data source. If everything worked well, you should be shown a list of employees similar to the one depicted in Figure 12.6.

Finally, click Finish.

Before we move on, let’s take a look at the new code we’ve added to AddressBook.aspx. If you switch to Source View, you’ll see that quite a bit of code has been created for you. Let’s look at the SqlDataSource object first:

File: AddressBook.aspx (excerpt)

<asp:SqlDataSource id="employeesDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:Dorknozzle %>" SelectCommand="SELECT [EmployeeID], [Name], [City],

[MobilePhone] FROM [Employees] ORDER BY [Name]"> </asp:SqlDataSource>

This object is amazing in its simplicity, yet the GridView can connect to it and display the required data with very little additional effort. Let’s use this

SqlDataSource object to populate the GridView.

476

Binding the GridView to a SqlDataSource

In AddressBook.aspx, use either Source View or the Properties window in Design View to set the following properties of the GridView control:

Table 12.1. Properties to set for the GridView control

Property	Value
DataSourceID	employeesDataSource
DataKeyNames	EmployeeID
AllowPaging	True
AllowSorting	True
PageSize	3

Don’t Overwrite the Columns!

If you set the DataSourceID property in Design View, Visual Web Developer will ask if you’d like to clear the column data and replace it with that from the data source, as Figure 12.7 illustrates. Make sure you choose No, because we’re happy with the columns we decided to display when creating the grid in Chapter 11.

Figure 12.7. We’re not refreshing the GridView fields

PageSize specifies the number of records the GridView should display on every page of products. Normally, we’d want this number to be greater than three, but we’ve set it to a low number here so that we can test the paging functionality. AllowPaging enables GridView’s paging functionality, which will cause (working) paging links to be displayed. When we set AllowSorting to True, the column names become links that users can click to sort the data on the basis of that field.

Let’s also deal with style issues by adding the line below to the skin file, SkinFile.skin. The PagerStyle defines the style used by the cells that contain the paging buttons; we’ll see these buttons in a moment.

477

Chapter 12: Advanced Data Access

File: SkinFile.skin (excerpt)

<asp:GridView runat="server" CssClass="GridMain" CellPadding="4" GridLines="None">

<RowStyle CssClass="GridRow" /> <SelectedRowStyle CssClass="GridSelectedRow" /> <HeaderStyle CssClass="GridHeader" />

<PagerStyle CssClass="GridRow" />

</asp:GridView>

<asp:DetailsView runat="server" CssClass="GridMain" CellPadding="4" GridLines="None">

<RowStyle CssClass="GridRow" /> <HeaderStyle CssClass="GridHeader" />

<PagerStyle CssClass="GridRow" />

</asp:DetailsView>

Execute the project. If everything goes well, you should see a functional GridView, with working paging buttons, like the one in Figure 12.8.

Figure 12.8. Address Book paging in action

Yes, paging works, and you didn’t write a single line of C# or VB code to implement it! You can even select rows—although when you do, nothing happens, because we haven’t implemented any functionality for the DetailsView control as yet! (Remember that in the beginning of the chapter, we deleted all the code.)

478