Chapter 8. Overview of Data Access

Any web application worth writing involves data access. In this chapter, you learn how to take advantage of the rich set of controls included in ASP.NET 4 Framework for working with data.

You learn how to take advantage of the DataBound controls to display data in your ASP.NET pages. You also learn how to take advantage of the DataSource controls to represent different sources of data such as databases, XML files, and business objects.

Next, you are provided with an overview of Microsoft SQL Server 2008 Express, which is the royalty-free database included with Visual Web Developer. You learn how to connect to this database and use it for all your data access needs.

Finally, at the end of this chapter, we build a database-driven application, which illustrates how you can use many of the Data controls discussed in this chapter. We build an Employee Directory application.

Using DataBound Controls

You use DataBound controls to generate your application’s user interface for working with data. The DataBound controls display and edit database data, XML data, or just about any other type of data you can imagine.

There are three main types of DataBound controls: list controls, tabular DataBound controls, and hierarchical DataBound.

Working with List Controls

List controls display simple option lists. The ASP.NET 4 Framework includes the following five list controls:

• BulletedList—Displays a bulleted list of items. Each item can be displayed as text, a link button, or a hyperlink.

• CheckBoxList—Displays a list of check boxes. Multiple check boxes in the list can be selected.

• DropDownList—Displays a drop-down list. Only one item in the drop-down list can be selected.

• ListBox—Displays a list box. You can configure this control so that only one item in the list can be selected or multiple items can be selected.

• RadioButtonList—Displays a list of radio buttons. Only one radio button can be selected.

All five controls inherit from the same base ListControl class. This means that all these controls share a core set of properties and methods. In Chapter 10, “Using List Controls,” you can find detailed instructions on how to use each of the list controls.

The examples in this chapter rely on local databases. You can simply copy the database files (the .mdf files) from code examples found on the book’s website. We’ll discuss the SQL Server 2008 Express database engine that allows you to utilize these files later in the chapter.

The page in Listing 8.1 illustrates how to use all five list controls to display the same set of database records (see Figure 8.1).

Figure 8.1. Using list controls.

Listing 8.1. ShowListControls.aspx

In Listing 8.1, each list control is bound to a SqlDataSource control which represents the contents of the Movies database table. For example, the BulletedList control is bound to the DataSource control like this:

The BulletedList control includes a DataSourceID attribute, which points to the ID of the SqlDataSource control. The DataSourceID attribute associates a DataBound control with a DataSource control.

Working with Tabular DataBound Controls

The tabular DataBound controls are the main set of controls that you use when working with database data. These controls enable you to display and, in some cases, modify data retrieved from a database or other type of data source.

Six tabular DataBound controls can be divided into two types: those that display multiple data items at a time and those that display a single data item at a time.

First, you can use any of the following controls to display a set of data items:

• GridView—Displays a set of data items in an HTML table. For example, you can use the GridView control to display all the records contained in the Movies database table. This control enables you to display, sort, page, select, and edit data.

• DataList—Displays a set of data items in an HTML table. Unlike the GridView control, more than one data item can display in a single row.

• Repeater—Displays a set of data items using a template. Unlike the GridView and DataList controls, a Repeater control does not automatically render an HTML table.

• ListView—Displays a set of data items using a template. Unlike the Repeater control, the ListView control supports sorting, paging, and editing database data.

You can use either of the following two controls to display a single data item at a time:

• DetailsView—Displays a single data item in an HTML table. For example, you can use the DetailsView control to display a single record from the Movies database table. This control enables you to display, page, edit, and add data.

• FormView—Uses a template to display a single data item. Unlike the DetailsView, a FormView enables you to use to layout a form by using templates.

The page in Listing 8.2 illustrates how you can use each of the tabular DataBound controls (see Figure 8.2).

Figure 8.2. Using tabular DataBound controls.

Listing 8.2. ShowTabularDataBound.aspx

For the moment, don’t worry too much about formatting the controls. Each of the tabular DataBound controls supports an abundance of properties that modify the control’s behavior and appearance. The GridView control gets a detailed examination in Chapter 11, “Using the GridView Control.” The DetailsView and FormView controls are covered in Chapter 12, “Using the DetailsView and FormView Controls.” The focus of Chapter 13, “Using the Repeater and DataList Controls,” is the Repeater and DataList controls. Finally, the new ListView control is discussed in Chapter 14, “Using the ListView and DataPager Controls.”

Working with Hierarchical DataBound Controls

A hierarchical DataBound control can display nested data items. For example, you can use hierarchical DataBound controls to display the folder and page structure of your website, the contents of an XML file, or a set of master/detail database records.

The ASP.NET 4 Framework includes two hierarchical DataBound controls:

• Menu—Displays data items in a static or dynamic menu.

• TreeView—Displays data items in a tree.

The page in Listing 8.3 illustrates how you can use both the Menu and TreeView controls. Both controls are bound to an XmlDataSource control, which represents the XML file in Listing 8.4 (see Figure 8.3).

Figure 8.3. Using hierarchical DataBound controls.

Listing 8.3. ShowHierarchicalDataBound.aspx

Listing 8.4. Movies.xml

Again, don’t worry about the appearance of the Menu and TreeView controls in the page rendered by Listing 8.3. Both controls support a rich set of options for modifying the control’s appearance. We examine the properties of both of these hierarchical controls in detail in Chapter 22, “Using the Navigation Controls.”

Working with Other Controls

You can bind any control in ASP.NET Framework to the data items represented by a data source. Imagine, for example, that you want to display a photo gallery. In that case, you might want to bind a set of Image controls to a data source.

You can bind any ASP.NET control to a data item by adding the control to a template. For example, the page in Listing 8.5 automatically displays all the pictures in a folder named Photos (see Figure 8.4).

Figure 8.4. Binding images to a data source.

Listing 8.5. ShowPhotos.aspx

The Repeater control contains an ItemTemplate, and the ItemTemplate contains an ASP.NET Image control. The Image control displays each of the photographs from the Photos folder.

Using DataSource Controls

You bind a DataBound control to a DataSource control. A DataSource control represents a particular type of data.

The ASP.NET 4 Framework includes the following six DataSource controls:

• SqlDataSource—Represents data retrieved from a SQL relational database, including Microsoft SQL Server, Oracle, or DB2.

• LinqDataSource—Represents a LINQ to SQL query.

• AccessDataSource—Represents data retrieved from a Microsoft Access database.

• ObjectDataSource—Represents data retrieved from a business object.

• XmlDataSource—Represents data retrieved from an XML document.

• SiteMapDataSource—Represents data retrieved from a Site Map Provider. A Site Map Provider represents the page and folder structure of a website.

The ASP.NET Framework contains two basic types of DataSource controls. The SqlDataSource, AccessDataSource, LinqDataSource, and ObjectDataSource controls all derive from the base DataSourceControl class. These controls can be used to represent tabular data. The XmlDataSource and SiteMapDataSource controls, on the other hand, derive from the base HierarchicalDataSourceControl control. These two controls can be used to represent both tabular and hierarchical data.

A DataBound control is associated with a particular data source control through its DataSourceID property. For example, the page in Listing 8.6 contains a GridView control bound to a SqlDataSource control (see Figure 8.5).

Figure 8.5. Using the SqlDataSource control.

Listing 8.6. BoundGridView.aspx

Using ASP.NET Parameters with DataSource Controls

Many of the DataSource controls support ASP.NET parameters. You use ASP.NET parameters to modify the commands that a DataSource control executes.

Different types of DataSource controls use ASP.NET parameters to represent different types of things. When you use ASP.NET parameters with a SqlDataSource control, the ASP.NET parameters represent ADO.NET parameters. In other words, they represent parameters used with SQL statements.

When you use parameters with the ObjectDataSource control, the ASP.NET parameters represent method parameters. They represent parameters passed to a particular method of a business object.

The SqlDataSource, AccessDataSource, LinqDataSource, and ObjectDataSource controls all support the following types of Parameter objects:

• Parameter—Represents an arbitrary static value.

• ControlParameter—Represents the value of a control or page property.

• CookieParameter—Represents the value of a browser cookie.

• FormParameter—Represents the value of an HTML form field.

• ProfileParameter—Represents the value of a Profile property.

• QueryStringParameter—Represents the value of a query string field.

• SessionParameter—Represents the value of an item stored in Session state.

For example, the page in Listing 8.7 contains a DropDownList, GridView, and SqlDataSource control. The DropDownList displays a list of movie categories. When you select a new category, the GridView displays matching movies (see Figure 8.6).

Figure 8.6. Using the ControlParameter object.

Listing 8.7. ShowControlParameter.aspx

The SqlDataSource control includes a ControlParameter object. The ControlParameter represents the selected item in the DropDownList control. The value of the ControlParameter is used in the SqlDataSource control’s SelectCommand to select movies that match the category selected in the DropDownList control.

Using Programmatic DataBinding

When you bind a DataBound control to a DataSource control, you can take advantage of declarative databinding. When you use declarative databinding, ASP.NET Framework handles all the messy details of deciding when to retrieve the data items represented by a DataSource control.

In certain situations, you want to handle these messy details yourself. For example, you might want to force a GridView control to refresh the data it displays after you add a new record to a database table. Or you might want to bind a DataBound control to a data source that can’t be easily represented by one of the existing DataSource controls. In these situations, you want to use programmatic databinding.

Every DataBound control has a DataSource property and a DataBind() method. By using this property and method, you can programmatically associate a DataBound control with a data source.

For example, the page in Listing 8.8 displays a list of all the fonts installed on your computer (see Figure 8.7).

Figure 8.7. Programmatic databinding.

Listing 8.8. ShowFonts.aspx

The list of fonts is displayed by a GridView control. The actual list of fonts is retrieved from the InstalledFontCollection class (which inhabits the System.Drawing.Text namespace). The list of fonts is assigned to the GridView control’s DataSource property, and the DataBind() method is called.

In Listing 8.8, a collection of fonts has been assigned to the DataSource property. In general, you can assign any object that implements the IEnumerable interface to the DataSource property. For example, you can assign collections, arrays, DataSets, DataReaders, DataViews, and enumerations to the DataSource property.

When you call the DataBind() method, the GridView control actually retrieves its data from the data source. The control iterates through all the items represented by the data source and displays each item. If you neglect to call the DataBind() method, the control never displays anything.

The GridView is bound to its data source only when the page is requested for the first time. The Page.IsPostBack property determines whether the page has been posted back to the server. You don’t need to rebind the GridView to its data source every time the page is requested because the GridView uses View State to remember the data items that it displays.

You can’t mix declarative and programmatic databinding. If you attempt to use both the DataSource and DataSourceID properties, you get an exception.

On the other hand, you can call the DataBind() method even when you have declaratively bound a control to a DataSource control. When you explicitly call DataBind(), the DataBound control grabs the data items from its DataSource control again. Explicitly calling DataBind() is useful when you want to refresh the data displayed by a DataBound control.

Understanding Templates and DataBinding Expressions

Almost all the DataBound controls support templates. You can use a template to format the layout and appearance of each of the data items that a DataBound control displays. Within a template, you can use a DataBinding expression to display the value of a data item.

In this section, you learn about the different kinds of templates and DataBinding expressions that you can use with the DataBound controls.

Using Templates

Every DataBound control included in ASP.NET 4 Framework supports templates with the sole exception of the TreeView control. The Repeater, DataList, ListView, and FormView controls all require you to use templates. If you don’t supply a template, these controls display nothing. The GridView, DetailsView, and Menu controls also support templates, but they do not require a template.

For example, when you use the Repeater control, you must supply an ItemTemplate. The Repeater control uses the ItemTemplate to format each of the records that it displays. Listing 8.9 contains a Repeater control that formats each of the records from the Movies database table (see Figure 8.8).

Figure 8.8. Using an ItemTemplate.

Listing 8.9. ShowItemTemplate.aspx

A template can contain HTML, DataBinding expressions, and other controls. In Listing 8.9, the template includes the following two DataBinding expressions:

<%# Eval("Title") %>
<%# Eval("Director") %>

The first DataBinding expression displays the value of the Title column and the second DataBinding expression displays the value of the Director column.

A template can contain other controls—even other DataBound controls. For example, the page in Listing 8.10 displays a list of hyperlinks (see Figure 8.9).

Figure 8.9. Displaying a list of hyperlinks.

Listing 8.10. ShowLinks.aspx

In Listing 8.10, a HyperLink control displays for each item from the data source. The HyperLink control displays the movie title and links to a details page for the movie.

Using DataBinding Expressions

A DataBinding expression is a special type of expression not evaluated until runtime. You mark a databinding expression in a page by wrapping the expression in opening <%# and closing %> brackets.

A DataBinding expression isn’t evaluated until a control’s DataBinding event is raised. When you bind a DataBound control to a DataSource control declaratively, this event is raised automatically. When you bind a DataSource control to a data source programmatically, the DataBinding event is raised when you call the DataBind() method.

For example, the page in Listing 8.11 contains a DataList control that contains a template that includes two DataBinding expressions.

Listing 8.11. ShowDataList.aspx

The first DataBinding expression displays the title of the movie and the second DataBinding expression displays the date the movie was released (see Figure 8.10).

Figure 8.10. Using databinding expressions.

Both DataBinding expressions call the Eval() method. The Eval() method is a protected method of the Page class. Behind the scenes, the Page.Eval() method calls the static (shared) DataBinder.Eval() method. If you want to be verbose, instead of using the Eval() method, you could use the following two expressions:

In ASP.NET version 1.x, you had to use DataBinder.Eval() when displaying data items in a template. However, Microsoft took pity on programmers after ASP.NET 2.0 and provided us with the shorter syntax.

The second DataBinding expression in Listing 8.11 includes a second parameter. The Eval() method, optionally, accepts a format string. You can use the format string to format values such as dates and currency amounts. In Listing 8.11, the format string formats the DateReleased column as a long date.

You can call other methods than the Eval() method in a DataBinding expression. For example, the DataBinding expression in Listing 8.12 calls a method named FormatTitle() to format the movie titles.

Listing 8.12. FormatMovieTitles.aspx

The FormatTitle() method is defined in the page in Listing 8.12. This method formats each of the titles displayed by the Repeater control by making each title bold and uppercase (see Figure 8.11).

Figure 8.11. Formatting movie titles.

Using Two-Way DataBinding Expressions

The ASP.NET Framework actually supports two types of templates and two types of DataBinding expressions. The ASP.NET Framework supports both one-way DataBinding expressions and two-way DataBinding expressions.

Up to this point, we have used one-way DataBinding expressions exclusively. In a one-way DataBinding expression, you use the DataBinding expression to display the value of a data item. You use the Eval() method to display the value of a one-way DataBinding expression.

In a two-way DataBinding expression, you not only can display the value of a data item, you also can modify the value of a data item. You use the Bind() method when working with a two-way DataBinding expression.

For example, the page in Listing 8.13 contains a FormView control that includes a template for editing a movie record in the Movies database table (see Figure 8.12).

Figure 8.12. Editing a movie.

Listing 8.13. ShowFormView.aspx

The FormView contains an EditItemTemplate. The EditItemTemplate contains three TextBox controls. Each TextBox control has a two-way DataBinding expression assigned to its Text property.

The DataBinding expressions associate the TextBox control properties with the properties of the data item being edited. When you click the Update button, any changes you make to the Text properties are updated in the Movies database table.

Overview of SQL Server 2008 Express

Microsoft SQL Server 2008 Express is the version of SQL Server bundled with Visual Web Developer. You can also download this database engine from the Microsoft website (http://www.microsoft.com/express/Database/). SQL Server Express is used for almost all the database examples in this book.

In this section, you are provided with a brief overview of the features of this database. You also learn how to connect to SQL Server Express.

Features of SQL Server Express

One of the most important features of SQL Server 2008 Express is that it is a royalty-free database engine. You can download it and use it for free in your applications. You also can distribute the database in commercial applications that you produce for others without paying royalties to Microsoft. (Registration at the Microsoft site is required to do this.)

Microsoft SQL Server 2008 Express uses the same database engine as the full retail version of SQL Server 2008. However, because it is a free product, Microsoft has limited some of its features to encourage you to upgrade to the full version of SQL Server 2008.

First, unlike the full version of SQL Server 2008, a SQL Server Express database can be no larger than 4 gigabytes. Furthermore, SQL Server Express is limited to using 1 gigabyte of RAM. Also, SQL Server Express uses only a single processor even when used on a multiprocessor server.

SQL Server Express also does not support several of the advanced features of the full version of SQL Server 2008. For example, it doesn’t support Analysis Services, Notification Services, English Query, Data Transformation Services, or OLAP.

However, SQL Server Express does not have a Workload Governor. The performance of a SQL Server Express database is never throttled. This means that you can use SQL Server Express for small websites without worrying about performance limitations.

Finally, like the full version of SQL Server 2008, SQL Server Express supports the Common Language Runtime. In other words, you can use C# or Visual Basic .NET to create stored procedures, triggers, user-defined functions, and user-defined types.

SQL Server 2008 Express Management Tools

You can use three tools to create new database objects when using SQL Server 2008 Express. You can use Database Explorer in Visual Web Developer, Microsoft SQL Server Management Studio Express, and SQLCMD utility.

The Database Explorer included in Visual Web Developer provides you with a user-friendly interface for working with database objects (see Figure 8.13). I assume that you use the Database Explorer for the database samples in this book.

Figure 8.13. The Database Explorer window in Visual Web Developer.

Alternatively, you can use Microsoft SQL Server Management Studio Express. You can download Management Studio from the Microsoft site at http://www.microsoft.com/sqlserver/2008/en/us/express.aspx. This tool enables you to browse database objects and execute SQL queries (see Figure 8.14).

Figure 8.14. Using the Microsoft SQL Server Management Studio Express.

Finally, SQL Server 2008 Express includes a command-line tool named SQLCMD. You can use the SQLCMD tool to fire off SQL queries from the Command Prompt (see Figure 8.15). This alternative is the most painful, but it works.

Figure 8.15. Executing a SQL query with SQLCMD.

You use SQLCMD by opening a command prompt and connecting to your database with the following command:

SQLCMD -S .SQLExpress

Next, you can enter SQL statements at the command prompt. The statements are not executed until you type GO. You can get help using SQLCMD by typing :HELP after starting the tool. When you finish using the tool, type EXIT to quit.

Server Databases Versus Local Databases

You can create two different types of databases with SQL Server Express: Server and Local.

By default, when you install SQL Server 2008 Express, a named instance of the server is created with the name SQLExpress. You can create a new Server database by connecting to the named instance and adding a new database.

If you own Visual Studio 2010, you can create a new Server database directly from the Server Explorer window. Simply right-click the Data Connections node in the Server Explorer window, and select Create New SQL Server Database.

Unfortunately, you can’t use Visual Web Developer to create a new Server database. This option is grayed out. If you need to create a new Server database, and you don’t have the full version of Visual Studio, you need to use Microsoft SQL Server Management Studio Express as discussed in the previous section (see Figure 8.16).

Figure 8.16. Creating a new Server database.

When you create a Server database, the database is attached and available to any application running on the server. You can connect to the database easily from any ASP.NET application.

For example, the following connection string enables you to connect to a Server database named MyData:

Data Source=.SQLExpress;Initial Catalog=MyData;Integrated Security=True

The other option is to create a Local database instead of a Server database. When you create a Local database, you create the database in your project. The database file is added to the App_Data folder in your website.

Here are the steps for creating a Local database in Visual Web Developer:

1. Open the Add New Item dialog box by selecting the website, Add New Item (see Figure 8.17).

   Figure 8.17. Creating a new Local database.

   

2. Select Sql Database and provide the database with a name (for example, MyLocalData.mdf).
3. Click Add.

When you click Add, Visual Web Developer warns you that it needs to create the App_Data folder (if the folder doesn’t already exist). The MyLocalData.mdf file will be added to this folder. Click OK to create the new folder.

You can connect to a Local database named MyLocalData.mdf by using the following connection string:

When you connect to the MyLocalData.mdf file, the database is attached automatically to Microsoft SQL Server Express.

The connection string includes an AttachDbFilename parameter. This parameter represents the physical path to a database file (.mdf file). The keyword |DataDirectory| is used in the path. The |DataDirectory| keyword represents a website’s App_Data folder.

Instead of using the |DataDirectory| keyword, you could supply the entire physical path to a database file. The advantage of using the |DataDirectory| keyword is that you can move your web application easily to a new location without needing to change the connection string.

The connection string also includes a User Instance parameter. Creating a User Instance connection enables you to connect to a Local database without using an Administrator account. Because the ASPNET account is not an Administrator account, you need to add this parameter to use Local databases from ASP.NET pages.

Including the User Instance parameter in a connection string causes a separate user instance of SQL Server to execute with the security context of the user. The first time a user creates a User Instance connection, copies of the system databases are copied to a user’s application data folder located at the following path:

A separate set of system databases is created for each user.

One of the primary advantages of using a Local database rather than a Server database is that a Local database can be moved easily to a new location. If you email a Local database file (the .mdf file stored in the App_Data folder) to a friend, your friend can start using the database immediately. The only requirement is that your friend has SQL Server Express installed on a computer.

Sample Database-Driven Web Application

The following chapters get into all the gritty details of the Data controls. Before you get lost in the details, however, I want to provide you with a sample of a data-driven web application. I want to provide you with a real-world application that illustrates what can be built with the Data controls.

In this section, a complete Employee Directory application is built, which supports displaying, adding, editing, and deleting employee information. The sample application includes all the necessary form field validation.

One of the amazing things about ASP.NET 4 Framework is how much the Framework simplifies data access. The sample application consists of a single page that contains little code. Writing the same application with ASP.NET 1.x Framework would require pages of code. (I won’t even mention how much code it would require to write the same application in ASP Classic.)

Because the Employee Directory application includes all the required validation code, the page is a little too long to include in the pages of this book. However, it is included on the book’s website. Open the page named EmployeeDirectory.aspx.

After you open the EmployeeDirectory.aspx page in your browser, you see a list of employees. This list is rendered by a GridView control (see Figure 8.18).

Figure 8.18. Displaying a list of employees with the GridView control.

Next to each employee, there is a Delete link and a Details link. If you click Delete, the selected employee is deleted from the database. A client-side confirmation dialog box appears when you click the Delete link (see Figure 8.19). This dialog box is added to each of the Delete links in the grdEmployees_RowCreated() method. This method is called automatically by the GridView control as the GridView creates each row.

Figure 8.19. Deleting employee information.

If you click the Details link, a window appears that displays detailed information for the Employee (see Figure 8.20). The detailed information is rendered by a FormView control. The window that appears is created with an absolutely positioned <div> tag.

Figure 8.20. Displaying employee details.

If you click Edit when viewing a employee’s details, you can edit the employee record. The edit form is contained in the FormView control’s EditItemTemplate. Each of the form fields is associated with a RequiredFieldValidator control.

Finally, you can add new employees to the directory by clicking the Add Employee button. The form that appears is also rendered by a FormView control (see Figure 8.21).

Figure 8.21. Adding a new employee.

Summary

In this chapter, you were provided with an overview of the Data controls included in the ASP.NET 4 Framework. You learned how to use the DataBound controls to render the user interface for working with data. You also were provided with an introduction to the DataSource controls, which can be used to represent different types of data such as database data and XML data.

You also learned about two important features of the DataBound controls. You learned how to use Templates and databinding expressions. You learned about the difference between one-way databinding and two-way databinding expressions.

Next, you were provided with an overview of SQL Server 2008 Express. You learned how to create a SQL Server Express database. You also learned how to create both Server and Local databases.

Finally, the Data controls were used to build a sample application: the Employee Directory application. You learned how to use the controls to build an application that enables you to list, edit, insert, and delete database records.