AccessDataSource control in ASP.NET2.0

Author：Eve Cole Update Time：2009-06-30 16:37:44

ASP.NET 2.0 includes the AccessDataSource control, which is used to extract data from the Access database to ASP.NET 2.0 (.aspx) pages. The properties this control has are very simple. The most important attribute of AccessDataSource is the DataFile attribute, which is used to point to the path of the MDB file on the hard disk. Other properties owned by AccessDataSource include SelectCommand, which is used to set a statement that displays the result set (tables and columns) that needs to be returned. SelectCommand must be defined using SQL syntax.

In VWD, there are two ways to add the AccessDataSource control to the page. If the MDB file is added to the Database Explorer as in the previous example, column names can be dragged and dropped onto the page, and VWD will automatically create AccessDataSource controls and GridViews to display the data. If you don't want to use GridView or want to customize the control, you can just add the AccessDataSource control from the tool box and install it through the configuration wizard. The wizard will prompt you to browse for the data file and allow you to specify a SelectCommand by selecting columns in the database table. If you enter the page outside VWD, you can enter it as follows:

<asp:AccessDataSource>
ID="MySourcName"
Runat="server"
DataFile="MyMDBName.mdb"
SelectCommand="SELECT MyField1, MyField2 FROM MyTable">
＜/asp:AccessDataSource＞

The feature that needs to be mentioned again is simple. All you need to provide is an ID, MDB file name, and a SelectCommand.

This article is about data source controls, but recall that data source controls don't render anything visible on the page. So to give you a visual way to test the data source control, the next "Try It Away" will cover the basic steps of using the GridView control (evolved from the ASP.NET version 1 DataGrid control) to display data in a table. Please note that the focus of this chapter is not on the configuration and customization of the GridView; in this case, the focus is on the data source control. Later in Chapter 5, the GridView control will be studied in more detail.

Try #2 - Connect to MDB and display data with GridView

In the example of this article, the information of the products that have been sold will be displayed through Northwind on the page.

(1) Start the page editor and browse to C:WebsitesBegAspNet2Dbch02.

(2) Add a page named TIO-ch02-2-DisplayAccessData from the Web Form template. Click the label below to switch to Design view.

(3) At the top of the page, enter a title such as "Demo of connecting to MDB data source" and put the same text in the title of the page. Although this step is not discussed in every exercise, we recommend including a title and HTML text on each page so that the page you are viewing is not lost.

<html>
＜head＞
＜title＞TIO ch02-1 Display MDB Data＜/title＞
＜head＞
<body>
＜h3＞ch02 TIO 2 Demonstration of connection to an MDB source ＜/h3＞
<body>
＜/html＞

(4) Display the tool box (Menu: View|toolbox or type Ctrl+Alt+X) and expand the Data panel of the tool box.

(5) Drag an AccessDataSource control to the page.

(6) The convenient task panel of the control should appear automatically. If the panel does not appear automatically, you can select the new data source and click the small arrow on the upper right to open the convenient task panel. Click Configure Data Source.

(7) In the Choose a Database step, click the Browse button and select C:WebsitesBegAspNet 2DbApp_DataNorthwind.mdb, and note that the designer converts the file description into a relative reference, that is, using the symbol (~) to represent the site the root directory.

(8) In the Configure Select Statement dialog box, select the Specify Columns option and select the Products table. Click the asterisk to select all columns. Click Next, Test Query, and Finish.

(9) Select the Data control and display the Properties window by pressing F4. Change its ID to NorthwindProducts.

(10) Return to the Data panel of the toolbox, double-click on the GridView to add the control to the page and enter the Common Tasks Menu, and then click Choose Data Source. Select NorthwindProducts and end the wizard.

(11) Select the GridView and change its ID to NorthwindProducts. The page should look like this:

＜%@ page language="VB" %＞
<html>
＜head＞＜title＞ch02 TIO 2 Display MDB Data＜/title＞＜/head＞
<body>
＜h3＞ch02 TIO 2 Demonstration of Connection to an MDB Source ＜/h3＞
＜form runat="server"＞
<asp:accessdatasource id="NorthwindProductsAccDataSource" runat="server"
selectcommand="Select * From Products"
datafile="~/App_Data/Northwind.mdb">
＜/asp:accessdatasource＞
<asp:gridview id="NorthwindProductsGridView" runat="server"
datasourceid="NorthwindProductsAccDataSource">
＜/asp:gridview＞
＜/form＞＜/body＞＜/html＞

(12) Open the browser and view C:WebsitesBegAspNet2Dbch02ch02_TIO_2_DisplayAccessData.aspx (see Figure 2-2).

Figure 2-2

(13) The final operation is to display data from MDB Query instead of the table. Close the browser and return to VWD. Save the file with a new name ch02_TIO_2_DisplayAccessData-Query.aspx. Select the GridView and open its Common Task Menu by clicking the small arrow in the upper right corner of the control. Click Configure Data Source and Next to skip the selection of the MDB file. On the Configure Select Statement interface, change the name to "Sales Classification" and click the asterisk to get all fields. This query joins four tables together using JOIN, limits the number of records used in a table and goes through some grouping and aggregation operations on the data to get the total. Click Next, Test the Query, and Finish. The data source control should look like this:

<asp:accessdatasource id="NorthwindProductsAccDataSource" runat="server"
selectcommand="Selcet * From [Sales by Category] "
Datafile="~/App_Data/Northwind.mdb">
＜/asp:accessdatasource＞

(14) Open the browser and view the page.

Example Note #2 - Connect to MDB and display data with GridView

You have two controls on the page. The first, AccessDataSource, performs all the work of connecting to the ADO.NET objects that connect to the JET engine that interacts with the MDB file. The second is GridView, which is used to obtain data and convert its format into HTML format that can be displayed on the page. Please note that it is very important to give each control a meaningful name (ID). You must then make sure that the data-bound control (GridView) uses its DataSourceID property to reference the AccessDataSource's ID in order to use the AccessDataSource as its data source.

Displaying data from a query is not very difficult; in Access you can use query names instead of table names. There are two instructions here. First, if there are spaces in the name of a table or query, the entire name must be enclosed in square brackets. Second, queries that require user input (for example, "sales in a given year" require knowing which year) involve techniques that are beyond the scope of this book.

After completing the previous steps, you can see the data obtained from the AccessDataSource control displayed on the page. Later chapters will cover GridView in detail, but this chapter will continue to focus on data source controls.

Select variables in statements

When creating a data source in VWD, the wizard will ask you to specify the columns that need to be displayed or to create a custom SQL statement. In the previous "Try It Away", you simply selected a few columns. More complex SQL statements can be specified in several different ways:

● Use interactive dialog boxes

● Enter customized SQL statements in the AccessDataSource wizard (called by the Configure Data Source convenience task in the control)

● Enter statements in the property grid

● Enter statements directly in the markup of the Source view

Selecting columns in the Access DataSource wizard is a preferred technique for simple queries that return one or more columns from a separate table because it reduces typography and syntax errors (see Figure 2-3 ). In this wizard, you can select the name of the table or query from the Name drop-down list. You can then select the required columns in the table or query by selecting all columns (*) or any group of columns. If you click the Order By button, you can sort by any column in the data source. If there is a constraint on the first column, the field selected in Then By will be used. As you select options from the wizard, note that the actual SQL syntax for the SelectCommand is also displayed in a read-only text box.

Figure 2-3

You can use the WHERE button in the wizard to create a SQL statement with parameters. This part will be introduced in Chapter 9. We will skip this selection for now, but it is important that before deployment you use these parameters which will be discussed later in this book. In a deployed site, avoid directly connecting user input to SQL statements. Ignoring parameter issues will expose your site to SQL injection attacks. This attack technique uses false characters from user input to invalidate the original SQL statement and then replaces it with a destructive statement. You can use parameter collections to send user input to ADO.NET parameter collections, which can use the functionality of collections to reduce SQL injection problems.

Although the Configure Data Source wizard has many options to help quickly create SQL statements, sometimes you still need to directly enter (or modify) the SQL statement in the marked SelectCommand. The AccessDataSource wizard allows these operations. In the wizard page for selecting tables, queries, and columns, you can select the radio button for "Specify a custom SQL statement or stored procedure." After selecting this option in the wizard page, click the Next button to enter another page where you can directly enter custom statements in the text area. You can also use Visual Studio QueryBuilder to visually create custom statements, using tools that are very similar to Access QueryBuilder.

If you do not want to use the wizard, you can choose to enter a custom SQL statement in the AccessDataSource control's property grid, or you can switch to Source view and enter the statement in the SelectCommand property of the AccessDataSource control tag.

There are many books about SQL (the same type as this one is Beginning SQL Programming , ISBN 1-861001-80-0), and this book provides a brief introduction in the appendix. If you plan to learn SQL syntax, you can start by learning the commands for returning part of a record (TOP and DISTINCT), the syntax for renaming a field (AS), and the technique for returning fields from two related tables (JOIN). The following exercises will explore some variables in SQL statements.

Try #3 - Changing the Select Statement in AccessDataSource In this exercise, you will display specific columns and specific records from Northwind's Products table. A page will also be created to display the data obtained from the query. Please note that in this exercise, the selection rules are already in the source code. Currently, there is no user input as an option.

(1) Create a file named ch02_TIO_3_AlternateSelect-Commands.aspx in C:WebsitesBegAspNet2Dbch02.

(2) Add an AccessDataSource control with Northwind as the ID and set the MDB to App_DataNorthwind.mdb. In a series of dialog boxes, set up the Select command to retrieve all fields from the Products table ("name"=Products). Take a look at the Source view and notice the statement you created, as shown below:

SELECT * FROM Products

(3) Add GridView, set the ID to Northwind, and set the data source to Northwind. Here's what the entire page looks like so far:

＜%@ page language="VB" %＞
<html>
＜head runat="server"＞
＜title＞ch02 TIO 3 Alternate Select Conimands＜/title＞
＜/head＞
<body>
＜h3＞ch02 TIO 3 Alternate Select Coinmands＜/h3＞
＜form runat="server"＞
<asp:accessdatasource id="NorthwindAccDataSource" runat="server"
selectcommand="SELECT * FROM Products"
datafile=" ~/App_Data/Northwind.mdb" >
＜/asp:accessdatasource＞
<asp:gridview id="GridViewl" runat="server"
datasourceid="NorthwindAccDataSource">
＜/asp:gridview＞
＜/form＞
＜/body＞
＜/html＞

(4) Now open the page in the browser and pay attention to the interface shown in Figure 2-4 (not all columns are shown in the screenshot).

Figure 2-4

(5) Return to the Design view and select AccessDataSource (not GridView). Open its handy task panel and click Configure Data Source. In the Configure Select Statement window, select "Specify a custom SQL statement" and click Next. Modify the SQL statement in the editor as follows:

SELECT * FROM [Products] WHERE (CategroyID=3)

(6) Click Next and test the query. Click OK to close the dialog box. Take a look at the page in the Source view and note the following code:

<asp:accessdatasource id="NorthwindAccDataSource" runat="server"
selectcommand="SELECT * FROM [Products] WHERE (CategoryID = 3) "
Datafile="~App_Data/Northwind.mdb">
＜/asp:accessdatasource＞

(7) Try to use the dialog box to create some of the following Select commands. You can also enter them in the Source view, or use a combination of the two.

SELECT * FROM Products WHERE ProductID = 12
SELECT * FROM Products WHERE ProductName = 'Northwoods Cranberry Sauce'
SELECT * FROM Products WHERE ProductID <11
SELECT * FROM Products WHERE ProductID <11 ORDER BY ProductName Ascending
SELECT * FROM Products WHERE SupplierID = 6 OR SupplierID = 8
SELECT * FROM Products WHERE SupplierID = 24 AND UnitPrice ＞ 10

Example Note #3 - Changing the Select Statement in AccessDataSource

In the first few steps, create an AccessDataSource control and GridView as before. But then I started experimenting with Select statements. Adding and removing columns via checkboxes is most intuitive. You can also directly enter (or edit) SQL statements in the editor window.

A WHERE clause has been added to limit the records returned from the MDB file. If the column name does not contain spaces, the square brackets can be omitted. These square brackets must be used if the column name contains spaces.

Variables in MDB file location

MDB files are stored in various physical locations on the hard disk: the same folder as the Web page, a subfolder under the Web page, or other folders on the machine, etc. In Visual Web Developer, these files can be browsed frequently in the designer, and the correct path to the MDB file has been entered. However, if you plan to enter your own code, you will need to follow the syntax described in this section.

The DataFile property of the AccessDataSource control contains a page path that can be fully qualified (for example, starting with a drive letter) or specified relative to the location of the page that contains the AccessDataSource. Paths can also be relative to the application, i.e. using URL syntax to reference the path. This syntax replaces the symbol (~) with the application root directory, for example: ~App_Dataproduces.mdb. Using application-relative paths makes it easier to move pages from one location to another without breaking references to the database, so it is recommended to use application-relative paths whenever possible.

First, let's look at the syntax of a fully qualified path, which contains the entire path to an MDB file, starting with the root of the computer drive where the file is located:

<asp:accessdatasource . . .

datafile= "C:WebSitesWebApplicationApp_DataMyMdb.mdb">

Although this syntax can be used, its disadvantage is that it reduces the portability of the application. If you move the application to another machine, the fully qualified path to the drive or file will be different, and you will need to modify the DataFile property each time you move the page.

An improvement over fully qualified paths is to use relative path syntax, which only requires specifying the portion of the fully qualified path that is different from the page that contains the AccessDataSource. The following is the path syntax for a database file in the same folder as the page, specified as a relative path:

<asp:accessdatasource . . . datafile="MyMdb.mdb">

Because the page and MDB files are in the same directory, there is no difference in the paths of these files; therefore, you only need to specify their respective file names. If the MDB is in one or more directories below the folder, the following syntax is very similar to the previous one. In this case, just specify the subfolder name, followed by a slash and the MDF file name:

<asp:accessdatasource . . . datafile="MyDaughterFolder/MyMdb.mdb">

If the MDB file is in the upper-level directory of the page directory, you can also use double period syntax to specify the upper-level directory of the current location:

<asp:accessdatasource . . . datafile="../MyMdb.mdb">

By using relative path syntax, you can easily move applications from one place to another without changing the path of the MDB file. Because the page and the MDB file are always inseparable (if the MDB file is part of the application), the relative positions of these files are always kept consistent. But what happens when you want to move a path within the application, such as moving a page to a subdirectory? In this case, the relative position between the page and the MDB file has changed, and the relative path syntax is incorrect.

Application relative syntax solves this problem. In this case, the path is always specified relative to the application root directory, not relative to the page itself. The root directory is represented by the symbol (~):

<asp:accessdatasource . . . datafile="~App_Data/MyMdb.mdb>

Now you can easily move your application, or move pages within your application, without changing references to the database. Therefore, application relative paths should be used whenever possible.