Chapter 35. Accessing Data with ASP.NET 2.0

<feature><title>In this chapter</title>
</feature>

ASP.NET 2.0 Data Source Controls

The previous chapters on ASP.NET have shown how it’s possible to design some pretty impressive ASP.NET Web pages without writing any code at all. It may surprise you to know that you can also create some powerful data access pages in ASP.NET 2.0 without writing any code. The functionality for doing so is encapsulated within the ASP.NET data source controls.

Note

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 different types of data sources simply by setting properties on a control using the Tag Properties task pane or by specifying properties for a control in Code View declaratively.

→

For more information on setting ASP.NET control properties using the Tag Properties task pane, see “Using Standard ASP.NET Controls,” p. 495 (Chapter 28).

Note

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 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 Web pages that use the AccessDataSource and SqlDataSource controls to connect to a database.

Note

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 Control

The 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.

Note

The AccessDataSource control cannot connect to a password-protected Access database. To connect to a password-protected database, use the SqlDataSource control.

To insert an AccessDataSource control onto a page, drag it from the Toolbox and drop it on the page. By default, Expression Web is configured to not show non-visual ASP.NET pages, so when you first insert any data source control, Expression Web will ask if 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 “Using Web Page Views,” p. 127 (Chapter 8).

After you’ve inserted the AccessDataSource control, the easiest way to configure it is to click the Configure Data Source link in the Common AccessDataSource Tasks popup, as shown in Figure 35.1.

Configuring a data source is easily done using the Configure Data Source link in the Common AccessDataSource Tasks popup.

Figure 35.1. Configuring a data source is easily done using the Configure Data Source link in the Common AccessDataSource Tasks popup.

Note

Configuring a data source is easily done using the Configure Data Source link in the Common AccessDataSource Tasks popup.

If you are unable to see a data source control that you have placed on a page, see “Unable to See Data Source Control” in the “Troubleshooting” section of this chapter.

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.

    Note

    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.

SqlDataSource Control

The SqlDataSource control can be used to connect to many different types of databases, but it contains performance optimizations that specifically target Microsoft SQL Server.

Note

Unless you’re working with an enterprise-level application, you may not notice the optimizations in the SqlDataSource control.

The SqlDataSource control shares many of the same properties with the AccessDataSource control. All the properties we 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 Common SqlDataSource Tasks popup. We’ll cover the details of the SqlDataSource control later in this chapter.

SiteMapDataSource Control

The SiteMapDataSource control is a hierarchical data source control that is designed to work with the ASP.NET navigation controls. ASP.NET navigation controls are covered in detail in Chapter 29, “Using ASP.NET Navigation Controls,” so we won’t go into detail on this control here.

XmlDataSource Control

The 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 Common XmlDataSource Tasks popup.

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 35.2.

For greater flexibility, an XML transform and XPath expression can be configured for an XmlDataSource control.

Figure 35.2. For greater flexibility, an XML transform and XPath expression can be configured for an XmlDataSource control.

Note

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.

Displaying Data with ASP.NET 2.0

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 described above, and you can do it without writing any code because the work is all done by the data source control.

Displaying Data in Tabular Form

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.

Note

The completed examples from this chapter can be found in the ExamplesCh35FilesWebsite folder on the CD that accompanies this book.

Adding and Configuring a GridView Control

We’ll need to create a new ASP.NET page so we can add and configure a GridView control to display our data.

  1. Open an existing Web site or create a new Web site.

    Note

    The Web site can be either server-based or disk-based. If you use a disk-based Web site, the ASP.NET Development Server can be used for testing the site.

    →

    For more information on creating a Web site, see “Creating a Web Site,” p. 25 (Chapter 2).

    →

    For more information on the ASP.NET Development Server, see “Using the ASP.NET Development Server,” p. 485 (Chapter 27).

  2. Create a new ASP.NET page and save it as gridview.aspx.

    →

    For more information on creating ASP.NET pages, see “Creating Pages and Content,” p. 103 (Chapter 7).

  3. Drag a GridView control from the ASP.NET section of the Toolbox and drop it on the page.

    Note

    Note

    If the Toolbox is not visible, see “Toolbox Not Visible” in the “Troubleshooting” section of this chapter.

  4. Import the Nwind.mdb file into the root of the Web site.

    →

    For more information on importing files, see “Creating Pages and Content,” p. 103 (Chapter 7).

  5. If the Common GridView Tasks popup is not visible, click the arrow button to display it, as shown in Figure 35.3.

    To configure the GridView control, use the Common GridView Tasks popup.

    Figure 35.3. To configure the GridView control, use the Common GridView Tasks popup.

  6. Select <New Data Source...> from the Choose Data Source dropdown.

  7. Select Access Database in the Data Source Configuration wizard, as shown in Figure 35.4, and click OK.

    Instead of adding a data source control explicitly, you can configure a new one from the Common GridView Tasks popup.

    Figure 35.4. Instead of adding a data source control explicitly, you can configure a new one from the Common GridView Tasks popup.

  8. Enter “Nwind.mdb” in the Configure Data Source dialog, as shown in Figure 35.5, and click Next.

    When displaying data from an Access database, you will need to specify the location and filename of the database.

    Figure 35.5. When displaying data from an Access database, you will need to specify the location and filename of the database.

  9. Make sure that Specify Columns from a Table or View is selected.

  10. From the Name dropdown, select Employees.

  11. Check the EmployeeID, LastName, FirstName, Title, and HireDate checkboxes, as shown in Figure 35.6, and click Next.

    The Configure Data Source dialog makes it simple to specify the data you want to display.

    Figure 35.6. The Configure Data Source dialog makes it simple to specify the data you want to display.

  12. Click the Test Query button to make sure you are successfully connecting to the database and then click Finish.

After you click Finish, you will see that the GridView control now displays the columns you selected. There are several more options available to you in the Common GridView Tasks popup, as shown in Figure 35.7.

After you’ve configured a data source, you will have other options available for the GridView. In this case, we can now enable paging, sorting, and selection.

Figure 35.7. After you’ve configured a data source, you will have other options available for the GridView. In this case, we can now enable paging, sorting, and selection.

Save the page and test it in your browser. Even though you haven’t written any code at all, 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 35.8.

We have successfully pulled data from the Access database, and we did it without writing a single line of code.

Figure 35.8. We have successfully pulled data from the Access database, and we did it without writing a single line of code.

Sorting the 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 Common GridView Tasks popup.

  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 35.6.

  5. In the Sort By dropdown, choose LastName.

  6. In the Then By dropdown, choose FirstName, as shown in Figure 35.9.

    Configuring a data source so that results are sorted is easy using the Add ORDER BY Clause dialog.

    Figure 35.9. Configuring a data source so that results are sorted is easy using the Add ORDER BY Clause dialog.

  7. Click OK.

  8. Click Next and then 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 ever 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 Common GridView Tasks popup.

  3. Check the Enable Sorting checkbox, as shown in Figure 35.10.

    You can give the user a choice for how to sort a GridView by checking the Enable Sorting checkbox.

    Figure 35.10. You can give the user a choice for how to sort a GridView by checking the Enable Sorting checkbox.

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 also bind data in forms and other controls in a Web page. We’ll explore data binding in the Lagniappe section of this chapter.

Editing Data with ASP.NET 2.0

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 providing the ability to switch a row into edit mode. When you switch a row into edit mode, the information in the row is presented in textboxes so you can make changes.

Open the gridview.aspx page and save it as edit.aspx. After you do that, you’ll 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.

Configuring the Data Source

  1. Select the GridView control and click the arrow button to display the Common GridView Tasks popup.

  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 35.6.

  5. Check the Generate INSERT, UPDATE, and DELETE Statements checkbox, as shown in Figure 35.11. This will cause Expression Web to generate the necessary SQL statements to allow us to edit records from the database.

    The Advanced SQL Generation Options dialog makes it a one-step process to add the code necessary to edit the database.

    Figure 35.11. The Advanced SQL Generation Options dialog makes it a one-step process to add the code necessary to edit the database.

  6. Click OK and then click Next and Finish to close the Configure Data Source dialog.

Configuring the 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.

  1. Select the GridView and click the arrow button to display the Common GridView Tasks popup.

  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, as shown in Figure 35.12, and click Add to add it to the GridView.

    The Fields dialog makes it easy to change the columns displayed in a GridView. In this case, we’re adding an Edit, Update, Cancel link in a new column.

    Figure 35.12. The Fields dialog makes it easy to change the columns displayed in a GridView. In this case, we’re adding an Edit, Update, Cancel link in a new column.

  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 35.13.

    Columns can be rearranged by using the arrow buttons in the Fields dialog.

    Figure 35.13. Columns can be rearranged by using the arrow buttons in the Fields dialog.

  6. Click OK in the Fields dialog.

Your page should now look like Figure 35.14.

The GridView now has a new column so that employees can be edited.

Figure 35.14. The GridView now has a new column so that employees can be edited.

When I started this section, I said I would describe the easy way of configuring 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 checkboxes will be added to the Common GridView Tasks popup, as shown in Figure 35.15. The easy way of configuring the GridView so records can be edited is to simply check the Enable Editing checkbox. When you do that, Expression Web will automatically add the Edit, Update, Cancel column to the GridView.

The Common GridView Tasks popup offers different options based on how the data source is configured. We now have the option to enable editing by simply checking a box.

Figure 35.15. The Common GridView Tasks popup offers different options based on how the data source is configured. We now have the option to enable editing by simply checking a box.

Testing the Page

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 textboxes, as shown in Figure 35.16.

The GridView is now a full-featured data editor.

Figure 35.16. The GridView is now a full-featured data editor.

To update data in the database, enter the new information and click Update. Alternatively, you can choose to 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 as well.

There’s much more that you can do with data access and ASP.NET 2.0. 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.

Troubleshooting

Unable to See Data Source Control

I’ve added a data source control to my page and I’m trying to configure it. I know it’s there, but I can’t see it in Design View.

You haven’t enabled the ASP.NET Non-visual Controls Visual Aid. By default, non-visual controls are not visible. To make them visible, select View, Visual Aids, ASP.NET Non-visual Controls.

Toolbox Not Visible

I’d like to insert an ASP.NET control, but the Toolbox is not visible.

Select Task Panes, Toolbox to toggle on the display of the Toolbox.

Lagniappe (lan yap’) n., a gift or bonus: Creating a Master/Detail View

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 quite simple using the ASP.NET 2.0 controls in Expression Web.

Creating the Master View

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 dropdown in the Common GridView Tasks popup.

  3. In the Data Source Configuration wizard, choose Access Database and change the ID for the data source to “MasterDataSource,” as shown in Figure 35.17.

    Specifying a descriptive name for your data sources will help when you’re configuring them later.

    Figure 35.17. Specifying a descriptive name for your data sources will help when you’re configuring them later.

  4. Choose the Nwind.mdb database and click Next.

  5. Select the Products database from the Name dropdown.

  6. In the Columns list, select the ProductID, ProductName, and UnitPrice checkboxes as shown in Figure 35.18.

    The Fields dialog is used to remove the majority of the columns from the GridView.

    Figure 35.18. The Fields dialog is used to remove the majority of the columns from the GridView.

  7. Click Next and then click Finish.

We’ll need to use the ProductID field in order to determine which record to display in the detail view, but we don’t want the user to see the ProductID field. Therefore, we’ll need to make the ProductID field invisible to the reader.

  1. Select the GridView and click the arrow button to display the Common GridView Tasks popup.

  2. Select the Edit Columns link and select the ProductID field in the Selected Fields list.

  3. Change the Visible property to False as shown in Figure 35.19.

    Change the Visible property for the ProductID field to False, so that the user doesn’t see the ProductID column.

    Figure 35.19. Change the Visible property for the ProductID field to False, so that the user doesn’t see the ProductID column.

  4. Click OK to dismiss the Fields dialog.

  5. Check the Enable Paging and Enable Selection and textboxes in the Common GridView Tasks popup.

The next step is to create the detail view using a DetailsView control.

Creating the Detail View

Unlike the GridView you added previously, the DetailsView control will display all of the fields for the selected record. Therefore, we’ll need to insert a new data source control for the DetailsView control, and we’ll need to configure the new data source to retrieve only the record that we select in the GridView.

  1. Add a new AccessDataSource control to the page.

  2. Click the Configure Data Source link in the Common AccessDataSource Tasks popup.

  3. Click Browse and select the Nwind.mdb database in the first step of the Configure Data Source wizard. Click Next.

  4. Choose the Products table from the Name dropdown.

  5. Place a check in the * checkbox, so that all fields are retrieved.

  6. Click the WHERE button.

  7. Select ProductID from the Column dropdown in the Add WHERE Clause dialog.

  8. Select = in the Operator dropdown.

  9. Select Control in the Source dropdown.

  10. Select GridView1 in the Control ID dropdown. The Add WHERE Clause dialog should now look like the one shown in Figure 35.20.

    Adding the WHERE clause for the data source control so that it will retrieve only the record that is selected in the GridView.

    Figure 35.20. Adding the WHERE clause for the data source control so that it will retrieve only the record that is selected in the GridView.

  11. Click the Add button to add the new WHERE clause.

  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, and change the ID property in the Tag Properties task pane 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 dropdown on the Common DetailsView Tasks dropdown, select DetailsDataSource. If you want to improve the appearance of your page, you can drag the right side of the DetailsView control so that it’s about 300 pixels in width. After you’ve done that, 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 35.21.

The master/detail page is now fullyfunctional, and we did it without writing any code.

Figure 35.21. The master/detail page is now fullyfunctional, and we did it without writing any code.

You can add additional features to this page by using server-side code to control when the DetailsView control is displayed, but doing so is outside of the scope of this book. However, hopefully you’ve learned enough about using the ASP.NET data controls available to you in Expression Web so that you can implement powerful database functionality in your own websites.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.141.202.54