Over the past several years, sites have evolved into complex web applications. As sites evolved, the consumers of those sites evolved as well. We expect more from our sites, and a large part of meeting that expectation is providing access to dynamic data.
Microsoft first introduced data access to mainstream web developers in FrontPage 97 with the inclusion of IDC/HTX. Although IDC/HTX allowed web developers to create data-driven pages, it was a far cry from the ease of use necessary to really push data-driven sites into the mainstream. However, when Active Server Pages (ASP) technology was released very soon thereafter, developers finally had a way to build dynamic content quickly.
Many years later, Microsoft unveiled the .NET Framework, which included a new server-side technology called ASP.NET. ASP.NET once again revolutionized site development because it finally gave web designers the toolset they needed to develop web applications that felt like Windows applications. In fact, many of today’s ASP.NET developers were developers of Windows-based applications just a few years ago.
For more information on the .NET Framework and ASP.NET, see Chapter 25, “Using Standard ASP.NET Controls.”
Because Expression Web 3 uses ASP.NET for data access, many powerful data access tools such as the DataView, the GridView, and other tools are available to you. Using these tools, you can connect to data in a database, in an Extensible Markup Language (XML) file, or even in a sitemap file.
ASP.NET used to be referred to by Microsoft as ASP+. In fact, the file extension for ASP.NET web forms is .aspx—the x is simply a + turned on its side.
For more information on sitemap files in ASP.NET, see Chapter 26, “Using ASP.NET Navigation Controls.”
A sitemap file is a special XML file used by ASP.NET to map out the navigation structure of a site.
I realize that many web designers are not programmers. In fact, Expression Web 3 is aimed squarely at the designer market and not the programmers among us. However, don’t let that dissuade you from digging into the data access features in Expression Web 3. You certainly don’t need to be a coder to appreciate and use the features that ASP.NET provides for Expression Web 3.
If you find that you enjoy building ASP.NET pages with the features available in Expression Web 3, you can easily move to the next level by downloading Visual Web Developer 2008 Express Edition free from Microsoft.
Additionally, some ASP.NET developers are more experienced in developing with ASP.NET, and yet they believe it is proprietary and that they are able to connect only to Microsoft database technologies. In fact, although you can certainly use the features in ASP.NET to connect to SQL Server and Microsoft Access, you can also use the same tools to connect to Oracle databases, IBM DB2 databases, MySQL databases, and so on. You can connect to all these data sources using what Microsoft calls the provider model.
You don’t need to understand providers to use them. In fact, we’ll use some powerful data access features in the next chapter without going into providers at all.
An in-depth discussion of providers is outside the scope of this book, but if you’d like to dig into the topic and work with a sample provider, Microsoft offers one for download at http://msdn2.microsoft.com/en-us/library/26xsd945.aspx.
ASP.NET comes with several providers that allow you to connect to the databases mentioned previously. However, anyone can write a provider and plug it into ASP.NET to enable extended functionality. For example, if you wanted to write a provider specific to MySQL, one that added some functionality geared toward MySQL databases, you certainly could. You could then use your provider along with all the existing data access functionality in ASP.NET.
The one drawback to using ASP.NET for data access is that not all hosting companies offer hosting with ASP.NET support. In fact, many of the less expensive hosting companies host sites on Linux or UNIX, and those servers won’t work with ASP.NET.
Many of today’s web designers are drawn to technologies such as PHP because they think PHP is easier to use than ASP.NET. Still others believe that you have to buy the .NET Framework to use ASP.NET. It is my assertion that both of these positions are flat-out wrong. It is every bit as easy to use ASP.NET as it is to use PHP. In fact, it is actually easier to create robust web applications with ASP.NET in Expression Web 3 because of the limited PHP support.
There is an open source movement to create a foundation on which ASP.NET can run on Linux and UNIX web servers. As of this writing, you can (with some amount of work) get ASP.NET working on a non-Windows web server, but ASP.NET features that are provided in Expression Web 3 are supported only on Windows 2000 and later.
As for those who believe that being an ASP.NET developer is an expensive endeavor, let me say that the .NET Framework is a free download (as is the .NET Framework software development kit [SDK]), and Microsoft provides a feature-rich, professional development environment for creating ASP.NET applications called Visual Web Developer 2008 Express Edition, which costs nothing. If you haven’t yet downloaded a copy of Visual Web Developer 2008 Express Edition, you should stop reading right now and do it. You’ll be absolutely amazed that Microsoft is giving away this tool! You can download a www.microsoft.com/express/vwd/Default.aspx copy at.
I usually make it a point to not talk about myself much in my books, but I have to let you know that I do approach the topic of ASP.NET with a bias. (After all, I’m a senior escalation engineer on the ASP.NET team at Microsoft.) Because of my occupation, I believe my bias is a legitimate one. I got to see first-hand the passion and hard work that went into the development of ASP.NET, and I get to see (on a daily basis) the dedication to always improving it and making it the best it can be.
Let’s explore how you can take advantage of the data access and ASP.NET features in Expression Web 3 to display and edit database data.
The previous chapters on ASP.NET have shown how you can design some pretty impressive ASP.NET pages without writing any code. It might surprise you to know that you also can create some powerful data access pages in ASP.NET without writing any code. The functionality for doing so is encapsulated within the ASP.NET data source controls.
ASP.NET data source controls are located in the Data section of the ASP.NET Controls Toolbox.
ASP.NET data source controls provide robust connectivity to many types of data sources simply by setting properties on a control using the Tag Properties panel or by specifying properties for a control in Code View declaratively.
For more information on setting ASP.NET control properties using the Tag Properties panel, see Chapter 25, “Using Standard ASP.NET Controls.”
Data source controls do not display data on a page. As you’ll see later in this chapter, they are used in conjunction with other controls to display data.
Four data source controls are included in the Expression Web 3 Toolbox. Each data source control is similar in functionality, but they are specialized for specific types of data.
Let’s go over the details of each data source control, and then we’ll create some pages that use the AccessDataSource
and SqlDataSource
controls to connect to a database.
Throughout this chapter, we will be using the Nwind.mdb
database to build examples. If you don’t already have this database on your system, you can download it from www.microsoft.com/downloads/details.aspx?familyid=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=en.
AccessDataSource
ControlThe AccessDataSource
control provides data connectivity to a Microsoft Access database file. It includes some advanced features such as support for paging, sorting, filtering, and more.
To insert an AccessDataSource
control onto a page, drag it from the Toolbox and drop it on the page. By default, Expression Web 3 is configured to not show nonvisual ASP.NET pages, so when you first insert any data source control, Expression Web 3 will ask whether you want to turn on the Visual Aid for ASP.NET controls. It’s recommended that you answer Yes to this prompt so you can work more easily with the data source control.
For more information on Visual Aids, see Chapter 4, “Using Page Views.”
The AccessDataSource
control cannot connect to a password-protected Access database. To connect to a password-protected database, use the SqlDataSource
control.
After you’ve inserted the AccessDataSource
control, the easiest way to configure it is to click the Configure Data Source link in the AccessDataSource Tasks pop-up, as shown in Figure 34.1.
We’ll go over the details of configuring the AccessDataSource
control and other data source controls later in this chapter. For now, let’s review some of the common properties used to configure the AccessDataSource
control:
• DataFile
—Specifies the location of the Microsoft Access database file used by the AccessDataSource
control.
• DataSourceMode
—This property can be set to either DataSet
(the default) or DataReader
. In most cases, you’ll leave this property set to DataSet
, but if you only need to display data in the database without sorting, filtering, and so on, you can set it to DataReader
for better performance.
• SelectQuery
—Specifies the SQL query used to select data from the database. When the SelectCommandType
is set to StoredProcedure
, the SelectQuery
property can be used to specify a stored query.
A discussion of creating SQL queries is outside the scope of this book. If you’d like more information on how to use SQL queries to work with databases, read Sams Teach Yourself SQL in 24 Hours, 3rd Edition from Sams Publishing, available at www.quepublishing.com/bookstore/product.asp?isbn=0672324423&rl=1.
• UpdateQuery
—Specifies the SQL query to use when updating the database.
• DeleteQuery
—Specifies the SQL query to use when deleting records from the database.
• FilterExpression
—Filters data displayed by the SelectQuery
property.
• FilterParameters
—Specifies the parameters used with FilterExpression
to filter the database results.
Unless you’re working with an enterprise-level application, you might not notice the optimizations in the SqlDataSource
control.
SqlDataSource
ControlThe SqlDataSource
control can be used to connect to many types of databases, but it contains performance optimizations that specifically target Microsoft SQL Server.
The SqlDataSource control shares many of the same properties with the AccessDataSource
control. All the properties discussed previously for the AccessDataSource
control also apply to the SqlDataSource
control.
To insert a SqlDataSource
control on a page, drag it from the Toolbox onto the page. After inserting the SqlDataSource
control, you can configure it using the Configure Data Source link on the SqlDataSource Tasks pop-up. We’ll cover the details of the SqlDataSource
control later in this chapter.
SiteMapDataSource
ControlThe SiteMapDataSource
control is a hierarchical data source control designed to work with the ASP.NET navigation controls. ASP.NET navigation controls are covered in detail in Chapter 26, “Using ASP.NET Navigation Controls,” so we won’t go into detail on this control here.
A discussion of using XML transforms and XPath is outside the scope of this book. For details on using these XML features, read Sams Teach Yourself XML in 10 Minutes from Sams Publishing, available from www.quepublishing.com/bookstore/product.asp?isbn=0672324717&rl=1.
XmlDataSource
ControlThe XmlDataSource
control provides data connectivity to XML files. To insert an XmlDataSource
control, drag it from the Toolbox onto the page. You can then configure it using the Configure Data Source link on the XmlDataSource Tasks pop-up.
When configuring the XmlDataSource
control, specify the XML data path, an optional XML transform file, and an optional XPath
statement for filtering data, as shown in Figure 34.2.
There are several approaches to displaying data with ASP.NET. Which one you choose depends largely on the type of data you need to display and your purpose for displaying it. For example, to display a list of products and prices, a tabular display might be the best choice. On the other hand, to display information such as the name and address of one particular user, a data-bound form would be a better choice.
ASP.NET makes it extremely easy to display data using both methods, and you can do it without writing any code because the work is all done by the data source control.
A few ASP.NET controls specialize in displaying data in tabular form. The two most common controls are the DataList
control and the GridView
control. Of the two, the GridView
is the easiest to use and offers the largest feature set.
Let’s create a new ASP.NET page to display data from the Northwind Traders Access database. You can download the sample Nwind.mdb
file using the link provided earlier in this chapter if you don’t already have the Northwind Traders database.
GridView
ControlWe’ll need to create a new ASP.NET page so we can add and configure a GridView
control to display our data. Here’s how:
1. Open an existing site or create a new one.
For more information on creating a site, see Chapter 2, “Creating, Opening, and Importing Sites.”
For more information on the Microsoft Expression Development Server, see Chapter 33, “Using the Microsoft Expression Development Server.”
The site can be either server-based or disk-based. If you use a disk-based site, the Microsoft Expression Development Server can be used to test the site.
2. Create a new ASP.NET page and save it as gridview.aspx
.
For more information on creating ASP.NET pages, see Chapter 3, “Creating Pages and Content.”
3. Drag a GridView
control from the ASP.NET section of the Toolbox and drop it on the page.
4. Import the Nwind.mdb
file into the root of the site.
For more information on importing files, see Chapter 3, “Creating Pages and Content.”
5. If the GridView Tasks pop-up is not visible, click the arrow button to display it, as shown in Figure 34.3.
6. Select <New Data Source...>
from the Choose Data Source drop-down.
7. Select Access Database in the Data Source Configuration Wizard, as shown in Figure 34.4, and click OK.
8. Type Nwind.mdb
in the Configure Data Source dialog, as shown in Figure 34.5; then click Next.
9. Be sure Specify Columns from a Table or View is selected.
10. From the Name drop-down, select Employees.
11. Check the EmployeeID, LastName, FirstName, Title, and HireDate check boxes, as shown in Figure 34.6; then click Next.
12. lick the Test Query button to ensure that you are successfully connecting to the database; then click Finish.
After you click Finish, you will see that the GridView
control now displays the columns you selected. Several more options are available in the GridView Tasks pop-up, as shown in Figure 34.7.
Save the page and test it in your browser. Even though you haven’t written any code, you will see that the GridView
control is pulling the employee data from the database and displaying it on the page, as shown in Figure 34.8.
GridView
Right now the employees are in employee number order. Let’s reconfigure the display so they are listed in order of last name and then first name:
1. Select the GridView
and click the arrow button to display the GridView Tasks pop-up.
2. Click the Configure Data Source link.
3. In the Configure Data Source dialog, click Next.
4. Click the Order By button shown previously in Figure 34.6.
5. In the Sort By drop-down, select LastName.
6. In the Then By drop-down, select FirstName, as shown in Figure 34.9.
8. Click Next and then click Finish in the Configure Data Source dialog.
Save and browse the page. Notice that the grid is now sorted by last name. If any two employees with the same last name are added to the database, the grid will display them sorted by first name.
In a real-world application, it’s usually best to give the user a choice as to how to display the data. Let’s reconfigure the GridView
control so the user can decide how to sort it:
1. Select the GridView
control.
2. Click the arrow button to display the GridView Tasks pop-up.
3. Check the Enable Sorting check box, as shown in Figure 34.10.
Save and view the page in your browser. Notice that the header on each column is now a hyperlink. Clicking a link will sort by that column in ascending order. Clicking the same link again will sort by that column in descending order.
In addition to binding data in a tabular fashion, you can bind data in forms and other controls in a page. We’ll explore data binding in the Lagniappe section of this chapter.
Let’s create a page that will allow us to edit employee information and save updated information back to the database. The GridView
control makes this convenient by enabling you to switch a row into edit mode. When you switch a row into edit mode, the information in the row is presented in text boxes so you can make changes.
Open the gridview.aspx
page and save it as edit.aspx
.
Now you need to reconfigure the data source to allow for updates and add a column to the GridView
so users can switch a row into edit mode.
1. Select the GridView
control and click the arrow button to display the GridView Tasks pop-up.
2. Click the Configure Data Source link to display the Configure Data Source dialog.
3. Click Next.
4. Click the Advanced button shown previously in Figure 34.6.
5. Check the Generate INSERT, UPDATE, and DELETE Statements check box (see Figure 34.11). This will cause Expression Web 3 to generate the necessary SQL statements to enable us to edit records from the database.
6. Click OK, and then click Next and Finish to close the Configure Data Source dialog.
GridView
Now that the data source is capable of updating information in the database, the final step is to configure the GridView
and add a new column so a specific row can be switched into edit mode.
I’m going to walk you through a long way of doing this because it will give you some experience in using some of the features of the GridView
that you wouldn’t see otherwise. After you’ve configured the GridView
, I’ll tell you an easier way to accomplish the same task. Here’s how:
1. Select the GridView
and click the arrow button to display the GridView Tasks pop-up.
2. Click the Edit Columns link to display the Fields dialog.
3. Scroll down in the Available Fields list, and click the plus sign next to CommandField.
4. Select the Edit, Update, Cancel field (see Figure 34.12). Then click Add to add it to the GridView
.
5. Select the Edit, Update, Cancel field in the Selected Fields list, and click the up arrow button repeatedly to move it to the top of the list, as shown in Figure 34.13.
6. Click OK in the Fields dialog.
Your page should now look like Figure 34.14.
When I started this section, I said I would describe the easy way to configure a GridView
for editing the database. After you’ve configured the data source so it will generate the code necessary to update the database, new check boxes will be added to the GridView Tasks pop-up, as shown in Figure 34.15. The easy way to configure the GridView
so records can be edited is to simply check the Enable Editing check box. When you do that, Expression Web 3 will automatically add the Edit, Update, Cancel column to the GridView
.
View the edit.aspx
page in your browser. Click the Edit link next to the employee of your choice. When you do, the Edit link will change to an Update and Cancel link and all the data will be displayed in text boxes, as shown in Figure 34.16.
To update data in the database, enter the new information and click Update. Alternatively, click Cancel and revert to the original data from the database.
Using the methods you’ve learned here, you can easily create a robust web application that allows for not only the display of, but also the easy editing of, data from a database. The example used in this chapter used an Access database, but the same methods apply to other databases.
There’s much more you can do with data access and ASP.NET. The easiest way to learn how to take advantage of this powerful feature set is to explore it and experiment with creating data-enabled pages.
In many cases, it’s preferable to display a small subset of data and allow users to drill down into more information. This kind of approach is typically called a master/detail view, and creating such an interface is simple using the ASP.NET controls in Expression Web 3.
The master view will contain a subset of the Products table in the Northwind Traders database. We’ll use a GridView
to display this data.
1. Create a new ASP.NET page and save it as masterdetail.aspx
.
2. Add a GridView
control to the page and select the option to add a new data source from the Choose Data Source drop-down in the GridView Tasks pop-up.
3. In the Data Source Configuration wizard, select Access Database and change the ID for the data source to MasterDataSource (see Figure 34.17).
4. Select the Nwind.mdb
database and click Next.
5. Select the Products database from the Name drop-down.
6. In the Columns list, select the ProductID, ProductName, and UnitPrice check boxes, as shown in Figure 34.18.
7. Click Next, and then click Finish.
You’ll need to use the ProductID
field to determine which record to display in the Detail View, but you don’t want the user to see the ProductID
field. Therefore, you need to make the ProductID
field invisible to the reader. Here’s how:
1. Select the GridView
and click the arrow button to display the GridView Tasks pop-up.
2. Select the Edit Columns link and select the ProductID field in the Selected Fields list.
3. Change the Visible property to False (see Figure 34.19).
4. Click OK to dismiss the Fields dialog.
5. Check the Enable Paging and Enable Selection check boxes in the GridView Tasks pop-up.
The next step is to create the detail view using a DetailsView
control.
Unlike the GridView
you added previously, the DetailsView
control will display all the fields for the selected record. Therefore, you’ll need to insert a new data source control for the DetailsView
control, and you’ll need to configure the new data source to retrieve only the record you select in the GridView
. Do the following:
1. Add a new AccessDataSource
control to the page.
2. Click the Configure Data Source link in the AccessDataSource Tasks pop-up.
3. Click Browse and select the Nwind.mdb
database in the first step of the Configure Data Source Wizard. Click Next.
4. Select the Products table from the Name drop-down.
5. Place a check in the * check box, so that all fields are retrieved.
6. Click the WHERE button.
7. Select ProductID from the Column drop-down in the Add WHERE Clause dialog.
8. Select = in the Operator drop-down.
9. Select Control in the Source drop-down.
10. Select GridView1 in the Control ID drop-down. The Add WHERE Clause dialog should now look like the one shown in Figure 34.20.
11. Click the Add button to add the new WHERE
clause and then click OK.
12. Click Next and then Finish to complete the Configure Data Source Wizard.
13. Select the new AccessDataSource
control, if it’s not already selected; then change the ID property in the Tag Properties panel to DetailsDataSource.
The changes you made in the Add WHERE Clause dialog cause the ProductID
for the record you select in the GridView
control to be passed to the query that retrieves the selected record from the database.
To complete the page, add a new DetailsView
control under the existing GridView
control. In the Choose Data Source drop-down on the DetailsView Tasks drop-down, select DetailsDataSource. If you want to improve the appearance of your page, drag the right side of the DetailsView
control so that it’s about 300 pixels wide; then save the page and preview it in your browser. When you click the Select link for one of the records in the GridView
control, the details for that record are displayed in the DetailsView
control as shown in Figure 34.21.
You can add more features to this page by using server-side code to control when the DetailsView
control is displayed, but doing so is outside the scope of this book. However, hopefully you’ve learned enough about using the ASP.NET data controls available to you in Expression Web 3 so you can implement powerful database functionality in your own sites.