Beginning ASP.NET 2

7

Reading Data

The hallmark of dynamic web application pages is the capability to use information from a database. No other capability so widely expands the horizons of functionality for the visitor. The majority of information in an enterprise is held in databases, so it is imperative that a web application representing a business interact with that data. A tremendous effort by the ASP.NET 2.0 development team has reduced the knowledge and lines of code needed to work with data. This chapter and Chapter 8 explain how to implement the data-based features.

This chapter covers several major ideas:

The theory of using data in ASP.NET 2.0, including a brief overview of the theory and terminology of databases, an introduction to ASP.NET 2.0 data source controls and databound controls, and the role of VWD in creating data-based pages

Data source controls

Data-bound selection lists

Various data-bound controls, including GridView, DataList, Repeater, DetailsView and FormView

Data source controls with parameters

Implementing multiple data controls that work together

Working with XML data

By the end of the chapter you will have the tools and experience to use ASP.NET 2.0 the server side controls for data to present on your pages information that comes from a database.

Chapter 7

Introducing Databases

Before starting on the ASP.NET 2.0 data controls, let’s take a moment to consider sources of data. Data can be broadly divided into one of three categories. Relational data is organized into sets of tables according to rules of normalization. This category includes the data held in Microsoft Access, Microsoft SQL Server, Oracle, SAP, DB2, and MySQL. A second category of data resides in tree structures, such as XML files, the Windows registry, and the Windows file system. Last, data can be held in a wide range of miscellaneous types, such as Excel files, text files, or proprietary formats. This book (as per the vast majority of web site data interaction) discusses relational data and XML files.

Relational databases divide information into tables, and the tables contain records (also called rows). A record represents one instance of the topic of the table. A table contains multiple fields, or columns, that organize values by their type. For instance, a table of employees could contain a record for each employee. The table’s columns may be NameFirst, NameLast, DateOfHire, and so forth. For each column there would be a value for each record. A group of tables makes a database in most management systems. In Microsoft SQL Server, which is used in this book, one or more databases together make an instance of the server. Typically, tables contain only the data. The description of how the data is organized, the names of fields, and restrictions all reside in a separate structure of the database called metadata.

XML files are different from relational databases. First, instead of tables, the data is organized into a tree with branches of the tree holding finer and finer points of data. Each set of data and each individual datum is contained in a node. For example, an Employees XML file would have an Employees node that would represent the main trunk. Then there would be a limb for each employee. From that limb would be branches for FirstName, LastName, and so on. Second, an XML file is self-describing in that the metadata is included with the data. Each piece of information has an HTML tag that acts like a container that states the description of that data. For example, a datum like “John” would actually be stored as <NameFirst>John</NameFirst>. Although the self-descriptors can make an XML file huge, they make it easy to understand the data without having the metadata information.

Almost all sources of data will have a system that controls who is allowed to use the data. The first step in security is authentication, wherein the system determines who is asking to use it. The topic of

authentication is covered in detail in Chapter 4, so we won’t spend much time on it here. Basically, there are two types of authentication: Windows Authentication (also known as Trusted Security) and SQL Authentication. The decision of which authentication to use is made when the database is installed. With SQL Server Express, you have the option of Windows Authentication or Mixed, which means you can use either Windows Authentication or SQL Authentication. SQL Server Express installs, by default, with Mixed Authentication. This book accepts the default of Windows Authentication.

This book mainly uses Microsoft’s SQL Server. The product is sold with different sets of features, but for our use the simplest version (SQL Server Express) is adequate. Fortunately, Microsoft provides SQL Server Express free of charge, and automatically installs with the instructions given in this book for the setup. The beauty of SQL Server Express is that when you go to deploy your site to the public, all of your code will fit without modification into the full-featured SQL Server.

Once you have been authenticated (you prove that you actually are who you say you are), there will probably be a set of rights and limitations for your use of the data. First are restrictions in how you can look at data. Database administrators (DBAs) generally restrict direct access to tables. Instead, data may only be available to you through a view or query that contains limited fields or records. Second, you

210

Reading Data

may have limits on how (or if) you can change data. Last, even if you have the right to change data there may be restrictions (called constraints) on how data can be changed. To use Wrox United as an example, you generally cannot delete a team that is listed in the schedule (thus leaving the schedule with the logical fault of a game without an existing team).

Using ASP.NET 2.0’s Data Controls

Chapter 3 presented the idea that ASP.NET offers server-side controls. These controls contain code written by Microsoft that offers various behaviors, such as a drop-down list or a button. ASP.NET 2.0 has two sets of controls that are specific to working with data. The first set is data source controls that allow a page to connect to a source of data and to read from and write to that source. However, a data source control has no means to display data on an ASP.NET 2.0 page, which is where data-bound controls come into play. Data-bound controls display data to the user by rendering data onto a page. This chapter discusses data source controls and the reading behaviors of data-bound controls.

Almost all data source controls can work with almost all data-bound controls. This gives designers a mix-and-match solution. Pick the data source control that is optimized for the data and then pick a databound control that displays the information as desired.

Introducing Data Source Controls

ASP.NET 2.0 ships with several types of data source controls that are tailored to work with different types of data sources. These controls are as follows:

The SqlDataSource control allows connections to most relational databases. The Sql in its name refers to databases that understand the SQL language. That includes almost every kind of database that holds its data in a relational format. Note that the Sql does not refer only to the Microsoft SQL Server database management system. SqlDataSource controls utilize one of several providers that are specific to different kinds of databases. The default provider is for Microsoft SQL Server. Another provider is for Oracle. Both of these are written in managed

code, the most robust option in the .NET Framework. ASP.NET 2.0 contains an additional provider that can communicate with any database that is OLEDB-enabled (OLEDB is an acronym for Object Linking and Embedding for Databases). Because OLEDB is an old standard, that includes almost every other database management system including IBM DB2, MySQL, and SAP. However, the provider for OLEDB connections is not written in managed code. That means it does not meet all the requirements of being .NET technology, but it still can work with

.NET. We can expect third parties to publish more data source controls and providers and can hope that they are in proper managed code.

If you begin writing more advanced scenarios you will discover that OLEDB data source controls are not part of the System.Data hierarchy. These controls actually reside in the System.Web.UI.Controls namespace. But this point does not arise for most scenarios where you can just drag data controls from the toolbar.

211

Chapter 7

The AccessDataSource control is a special case of the SqlDataSource control that contains a provider optimized for Microsoft Access.

The XMLDataSource control allows connection to XML sources.

The SiteMapDataSource control, a specialized form of the XMLDataSource control, is optimized for the specific architecture of the ASP.NET 2.0 web application site map (as you built in Chapter 2).

The ObjectDataSource control connects to business objects you build yourself (discussed in Chapter 10).

Regardless of which data source control (and in the case of the SqlDataSource, which provider), the data source control will enable a set of behaviors for your ASP.NET 2.0 page. These include a connection to the database and enablement of behaviors such as reading and writing data. These behaviors will be available to data-bound controls that display data and receive input from the user.

If you are familiar with older version of ASP, the ASP.NET 2.0 data source controls instantiate ADO.NET objects. Therefore, ADO.NET provides the underlying technology for data access. The creation and manipulation of ADO.NET objects for most scenarios is now handled automatically (and correctly and efficiently) by the higherlevel data source control objects.

In summary, the data source controls create the background infrastructure needed to use data. However, they do not create any rendering on the web page (see the next section for those capabilities). Rather, they make the data behaviors like reading and writing to data stores available to data-bound controls.

Introducing Data-Bound Controls

Data-bound controls provide the link between the data source controls and the user. They take the data and behaviors of the data source control and render it to the visitor. This division of labor works very well. You can select any data source control and link that to any of the data-bound controls. With just a few exceptions, it is a mix-and-match scenario.

Data-bound controls encapsulate remarkable amounts of behavior. For example, the GridView control can not only display data in a table, but it offers sorting, selecting, paging through sub-sets, and oneclick transition to data editing. If your needs extend beyond these capabilities, you can write custom code hooked into events exposed by the GridView control.

There is one constraint on the compatibility of data source controls and data-bound controls. Each control is optimized for tabular data, tree data, or custom class data. For example, XML data is organized as a tree and thus best accessed with the XMLDataSource control and displayed in Menu or TreeView data-bound controls. SQL Server data is organized into tables and thus accessed with the SqlDataSource control and displayed in GridView (tables) or DetailsView. List type data-bound controls can display either source of data. You can twist the controls to cross-utilize types of data, but in general it is best to stick to their intended purpose.

212

Reading Data

Four general groups of data-bound controls ship with ASP.NET 2.0. Because there is overlap in their functionality, this chapter spends some time differentiating them. First you will look at their renderings, then a comparison chart, and finally a guide to selecting the correct data-bound control for your purposes.

Note that other controls, such as text boxes, can be data-bound. However, these independent controls are best connected to data in context of a template within one of the controls just mentioned. This topic is discussed in detail in Beginning ASP.NET 2.0 Databases from Wrox, ISBN 0-7645-7081-1 or 0-4717-8134-7.

The trick with data-bound controls arises in the selection. It can be confusing in the first attempts to get the correct control for your purpose. To help, the following sections organize the data-bound controls into four groups. Later in the chapter you will practice using each of them in a series of exercises.

Tabular Controls

Tabular controls produce the classic HTML table listing the data of records, albeit with opportunity for significant enhancements. These controls show multiple records in rows and one or more fields from each record as columns. The GridView control shows one value (datum) in each cell in a table layout. The DataList and Repeater controls behave in the same way, and render each cell with all of the fields for one record. Figure 7-1 shows how these controls will look in a browser.

Figure 7-1

The GridView control offers the most behaviors, as it can read, edit, and select records. The DataList control allows reading and editing, whereas the Repeater is a read-only control. The name of DataList is a bit confusing because you have a separate set of List controls that are optimized for selecting a record. The DataList is a display control in a tabular format.

Single Record Display Controls

Single record controls (DetailsView and FormView) display one record at a time. You can think of them as a deck of playing cards in a pile face up. At any moment all of the cards are there, but you can only see the top one. You have to navigate down through the deck to see other cards (see Figure 7-2 for an example of the DetailsView control). Single record controls have navigation features to permit the visitor to go to the next record, jump to a specific record, or fly to the first or last record. The DetailsView

213

Chapter 7

control provides some default layout when you create the control, whereas the FormView control creates a blank slate upon which you create the entire layout. Both of these data-bound controls support reading, editing, and the creation of new records.

Figure 7-2

Selection List Controls

Selection list controls are optimized to accept a user selection. These two controls display just one field from each record and stand by for a mouse-click. As shown in Figure 7-3, ListBox controls (right side of figure) are by default expanded, whereas DropDownList controls (left side of figure) display a single row until the user expands. As you would expect, these controls are display-only with no capability to change data.

Figure 7-3

Tree Controls

Tree controls have been optimized to handle data that are stored in nodes rather than tables. The Menu control provides a slide-out dynamic so that when you pass your mouse over a menu choice the submenu slides out. The TreeView control gives the user the option to expand or collapse nodes (see Figure 7-4). The Menu control is on the left of Figure 7-4, and the TreeView control is on the right. The SiteMapPath control offers a navigation trail that automatically updates based on the current page.

The differences among the data-bound controls discussed are summarized in the following table.

214

Reading Data

Figure 7-4
Control	Primary Data	Capabilities	Description and Primary Uses
	Structure
GridView	Table	Read and	Separate column for each field
		edit	Each field value in its own cell
			Display multiple records in a grid
			Edit existing records
DataList	Table or Tree	Read and	All fields in one cell
		edit	One cell equals one record
			Display multiple records in a grid
			Create new record for GridView
Repeater	Table or Tree	Read only	All fields in one cell
			One cell equals one record
			Display multiple records in a grid
			Create new record for GridView
			Table continued on following page

215

Chapter 7

Control	Primary Data	Capabilities	Description and Primary Uses
	Structure
DetailsView	Table or Tree	Read, edit,	Display single records
		create	Default structure provided
			Edit existing records
			Create new records
FormView	Table or Tree	Read, edit,	Display single records
		create	No default structure
			Edit existing records
			Create new records
DropDownList	Table or Tree	Read only	List of a few fields
and ListBox			Invites a user selection
			Display data for user selection
SiteMapPath	Tree	Read only	List page names between home and
			current page
			Used to identify current position in site
Menu	Tree	Read only	Displays top-level nodes with option to
			expand one sub-node at a time
			Used to display a menu where there
			will be one selection
TreeView	Tree	Read only	Displays top-level nodes with option to
			expand one or many sub-nodes
			Used to display multiple sub-nodes at
			once

Data Source Controls and Data-Bound

Controls Work Together

As discussed in the previous two sections, ASP.NET 2.0 offers two families of controls for working with data: data source controls and data-bound controls. This section takes a moment to look at how they work together. The data source control handles the behind-the-scenes connections to the data as well as a set of behaviors such as editing, sorting, and paging. The data-bound control presents the data and behavior to the user in actual renderings to the page.

The two controls you use on your page should match in type. Tabular-type data-bound controls such as GridView and FormView should be sourced by table-type data source controls such as the

SqlDataSource. Tree-type data-bound controls should be sourced by an XML data source. Lists can be sourced by either type. Having said that, there are some exceptions. Some tree sources can be used with tabular data-bound controls. This combination generally requires an InfoPath statement that narrows the scope of information that is harvested from the tree source of data. Examples of InfoPath statements are presented later in this chapter.

216

Reading Data

A data-bound control must have a property that sets its source to the data source control. In addition, for many behaviors there must be some coordination of properties between the pair of controls. For example, if editing is to be enabled, the data source control must have that option turned on and the databound control must also have its editing features enabled. As the controls are demonstrated later in the chapter you will see more details of how data source and data-bound controls work together.

Configuring Data Controls with VWD

When you place a data source control on your page (in Design View) VWD will lead you through a wizard to configure the various properties of the control. This procedure is remarkably cognizant of all the details to work with data on a page.

You can add data interactions using VWD at several levels. If you like to create controls one at a time, you can drag a data source control and walk through its wizard, then drag a data-bound control and connect it to the data source control. Alternatively, you can start with a data-bound control, and its wizard will step you through creating a data source control. You can even work at a higher level by opening the database explorer, selecting several fields, and dragging them to the page. VWD will then create both the data source controls and data-bound controls for you.

VWD also offers tools for editing data controls that already exist on the page. The properties window offers a very graphical user interface for changing settings. If you are working with code in Source View you can move the insertion bar within a data control tag and press the space bar to get a list of the appropriate attributes in IntelliSense.

Last, VWD offers a very useful tool within wizards for building the SQL statements needed to display or modify data. Instead of you typing (and commonly mistyping) the names of fields and the SQL keywords, the wizard offers a drag-and-drop interface with checkbox options. When you click Finish, VWD builds the SQL statement for you.

The tools of VWD greatly speed the development of data pages. However, you may find that some of the default settings and tags created by VWD are not to your liking. Particularly for beginners, the number of tags created by VWD can be overwhelming (like long lists of parameters). Advanced designers will want to substitute validation controls for the basic text boxes created by VWD. So although VWD will do a large amount of the page construction for you, most designers follow on with tweaks or partial rewrites. You will see an example in a Try It Out later in this chapter.

Data Source Controls

This section moves from the theory to the practice by adding data source controls to a page. As mentioned in the previous section on VWD and data controls, a data source control is most easily created by adding a data-bound control to a page and letting the VWD wizard set up the data source control for you. However, at times you will have to create the data source control yourself, so you will walk through that process here. The first step, of course, is to decide which data source control to use based on your type of data. For a first discussion you can look at a SQL Server Express data source (as used in this book).

217

Chapter 7

The Basic Properties of Data Source Controls

Data source controls require several attributes. The obvious are an ID and the runat=”server”. You must also specify which database on which server to use and your log on name and password for authentication. These data are held in a connection string. Next you must specify which records and fields to read from the data source. The data source control also requires a provider that describes how to interact with the database (the default provider for the SqlDataSource control is the provider for Microsoft SQL Server). In this case VWD walks you through these specifications in a wizard.

In this chapter you mostly work on a Fixtures.aspx page that will list Wrox United’s schedule. In this Try It Out you add the data source control using the VWD wizard and then examine the source code that the IDE created for you.

Try It Out

Data Source Control

1.Open the web site for the chapter (C:\BegASPNET2\Chapters\Begin\Chapter07) and create a Web Form called Fixtures.aspx in the site’s root using the web form template with the site master page and with code in a separate page. Switch to Design View and type Fixtures in the content panel.

2.From the Data section of the toolbar (see Figure 7-5), drag a SqlDataSource control to the middle of the content pane. You may have to scroll down to see the new control. Open the smart task panel and click Configure Data Source.

Figure 7-5

3.Click the small arrow at the top right of the control to open the smart task panel (as shown in Figure 7-6) and click Configure Data Source. A wizard will start and in the first dialog click New Connection.

218