Declaring Data SourcesThe Control

Although much of the content of Web pages comes from fixed text and HTML and from output produced from user inputs, the majority of information comprising modern Web sites is extracted from external data sources residing on the server -- from databases, text files, XML files, and other such data stores. Web page controls and scripts retrieve this external information, process it, and display it through other server controls to create final Web page output. The page is created dynamically, when it is requested, by embedding external information in the output areas reserved for its display.

Databases are often the primary data stores for Web page information. Many of the topics in these tutorials deal with database access and in maintaining the currency of database information. Here, consideration is given to some basic techniques for withdrawing that information for display on a Web page.

Under ASP.NET 2.0 convenient and easy-to-use methods to access and display database information are introduced. These are in the form of new data source controls. Whereas previously database access required scripts to link to, open, extract, and iterate records in a database, all of this processing is now encapsulated in single controls coded declaratively on the page.

[@more@]

The Control

When using a database as the source of Web page content it is necessary to perform a series of steps to (1) identify the server path to the database, (2) specify the driver software to use when accessing the database, (3) open the database for access by scripts, (4) issue an appropriate SQL statement to retrieve fields of data from specified tables in the database, (5) iterate the returned set of records to display them on the page, and (6) close the database connection when done. Previous to ASP.NET 2.0 these steps required a script to explicitly perform these actions to retrieve information for page display. Now, most of these steps are encapsulated in and can be performed automatically through special data source controls.

Figure 3-8 gives the general format for using the <asp:AccessDataSource> control to link to and return a set of records from a Microsoft Access database. This control is one of several available to work with different database products. The present format shows control properties needed to extract database information for page display. Later formats are introduced for performing database maintenance activities.

id="string" Runat="Server" DataFile="path" DataSourceMode="DataSet|DataReader" SelectCommand="SQL SELECT statement" />

Figure 3-8. General format for control.

An id property must be assigned in order to identify the returned recordset to other ASP.NET controls which display the returned records.

The DataFile property gives the directory path to the database. This can be the physical server path beginning with the drive letter, it can be a virtual path relative to the Web page containing the control, or it can be a path relative to the root Web directory. For the example eCommerce.mdb database its physical path is given in the DataFile property,

DataFile="c:eCommerceDatabaseseCommerce.mdb"

Alternately, it can be accessed by a relative path such as

DataFile="../Databases/eCommerce.mdb"

where the containing Web page is assumed to be in a directory parallel with the Databases directory. Another alternative is to specify a path relative to the root directory (the virtual Web directory) under which the page and database are stored. Thus, in the path

DataFile="~/Databases/eCommerce.mdb"

the character pair "~/" points to the root directory of the application (c:/eCommerce in this example) followed by the directory path to the database. As a general rule, relative path syntax is preferred since it is less likely to need changing when moving applications to different servers or different directory structures.

The SelectCommand property provides an SQL SELECT statement to identify the fields to retrieve from specified tables in the database. Any valid SELECT statement can be issued, either for a single table or for joined tables.

There are two DataSourceMode settings that can be made. The default "DataSet" mode stores the extracted recordset in server memory where it is available for further processing beyond its initial assignment to an information display control. This setting is necessary, for instance, when the display control provides sorting and paging options to rearranged and redisplayed a retrieved recordset. The "DataReader" mode is used when only a single access to the database is needed. This mode is a read-once, forward-only access method to retrieve a recordset for one-time-only display. It is more efficient than the DataSet method to retrieve a single set of records. The DataSourceMode need not be coded; using the DataSet mode is adequate for most retrieval situations unless server efficiencies associated with large databases come into play.

Assume, for instance, that a selected set of fields are to be extracted from the Products table of the example eCommerce.mdb database. An appropriately coded control is shown below.

Listing 3-5. Coding an AccessDataSource control.

An id is assigned so that a display control can point to this data source as the source for its information to display.

The DataFile property gives the path to the database, in this case expressed relative to the root directory of the Web application. In this and subsequent examples it is assumed that the Web page is in the c:eCommerceWebSite directory and the database is in the c:eCommerceDatabases directory. Therefore, the eCommerce directory is the root directory of the pages and the database. It is implied by the path prefix "~/". An alternate path specification gives the relative path from the page to the database, in this case "../Databases/eCommerce.mdb".

The SelectCommand property gives the SQL SELECT statement needed to retrieve four data fields from all records in the Products table. When the Web page is opened this data source control immediately connects to the database and retrieves the recordset, making it available for display on the page.

The Control

The AccessDataSource is a special case of the general > control used to access a variety of database products. An SqlDataSource requires ProviderName and ConnectionString properties to make a connection to a database.

id="string" Runat="Server" ProviderName="provider name" ConnectionString="connection string" SelectCommand="SQL SELECT statement" />

Figure 3-9. General format for control.

The ProviderName is "System.Data.OleDb" when connecting to an Access database. The ConnectionString includes Provider and Data Source parameters separated by a semicolon. For an Access database the Provider is "Microsoft.Jet.OLEDB.4.0" and the Data Source gives the physical path to the database. The following SqlDataSource retrieves the same recordset as the previous AccessDataSource.

Listing 3-6. Coding an SqlDataSource.

Binding to a Display Control

There are numerous ways to display information extracted from a database. The easiest way to display recordsets, however, is through several information display controls available through ASP.NET. One of the handiest and easy-to-use of these controls is the control introduced earlier. The way in which it binds to a data source control is similar to most other display controls.

A GridView in its default configuration automatically displays the rows and columns of a recordset returned from a data source control. It even uses the field names in a database table as column headings for its table display. A GridView binds to a data source by giving the id of the data source in its DataSourceID property. To bind to the AccessDataSource described above GridView coding is as simple as that shown below.

Listing 3-7. Coding for a GridView control bound to an AccessDataSource.

A display control binds to a data source control during the page-load process. Therefore, the display control is already populated with returned information when the page opens. The resulting display is shown below.

ItemNumber	ItemName	ItemPrice	ItemQuantity
BU1111	Microsoft Office Professional 2003	419.99	20
BU2222	WordPerfect Office 12	210.25	18
BU3333	Project 2003	519.99	10
DB1111	Access 2003	194.95	15
DB2222	SQL Server 2000 Standard	1989.95	10
DB3333	FileMaker Pro 6	275.95	8
DP1111	FrameMaker 7.0	799.95	10
DP2222	QuarkXPress 6.0	869.95	3
GR1111	Photoshop CS	589.95	17
GR2222	Illustrator CS	359.95	6
GR3333	Studio/MX 2004	969.95	13
GR4444	Flash/MX 2004	499.99	8
GR5555	Creative Suites Premier 1.1	1109.99	23
OS1111	Windows XP Home	49.95	20
OS2222	Windows XP Professional	139.95	15
OS3333	Windows Server 2003	949.95	7
WB1111	HomeSite 5.0	94.95	27
WB2222	Dreamweaver MX 2004	379.95	8
WB3333	FrontPage 2003	179.95	22
WB4444	Visual Studio .NET Professional	749.88	7

Figure 3-10. Page output produced by AccessDataSource and GridView controls.

You are likely to want more control over the aesthetics of the display than what is provided by the default GridView, although the default view is sufficient to test database connections. In later tutorials you learn the formatting tricks for the GridView.

Data Binding with Script

A Web site can be data driven in a second way in addition to reporting contents of external data sources. It can respond to user requests. Controls can be placed on the page to solicit user preferences about what information to display and how to display it. In order to demonstrate the ease with which users can become active in selecting page content the following rewrite of the previous application provides six buttons for selecting different table displays, each of which is produced dynamically in response to the choice.

ItemNumber	ItemName	ItemPrice	ItemQuantity
BU1111	Microsoft Office Professional 2003	419.99	20
BU2222	WordPerfect Office 12	210.25	18
BU3333	Project 2003	519.99	10

Figure 3-11. GridView output governed by user choices.

In this case a script is needed to respond to user clicks on the buttons. As shown in the listing below, subprogram Display_Product_Type is called to dynamically create an appropriate SQL SELECT statement to select all records of the chosen type. This SELECT statement is dynamically assigned to the AccessDataSource to return these records for display in a GridView.

Listing 3-8. Code for GridView output governed by user choices.

The AccessDataSource includes the required DataFile property to point to the database. Its SelectCommand retrieves an initial set of records for display, in this case all records which have the value "Business Office" in their ItemType field.

User choices are effected by assigning the six ItemType field values in the database as the Text properties of the buttons. The buttons' labels, then, match the item types found in the database. Recall that when a button calls a subprogram it identifies itself through the first item in the argument list, through argument Src in the example script. This button's Text property is given by the reference Src.Text, which translates as one of the ItemType values in the database. Therefore, this Src.Text property can be plugged into a SELECT statement to retrieve records of this type.

Assume, for instance, the button labeled "Graphics" is clicked. Therefore, the subprogram reference to Src.Text produces the value "Graphics". Since a SELECT statement is composed with the following declaration and assignment,

Dim SQLString As String = "SELECT ItemNumber, ItemName, ItemPrice, ItemQuantity FROM Products " & _
                          "WHERE ItemType = '" & Src.Text & "'"

when Src.Text ("Graphics") is concatenated inside this string the following statement is produced:

SELECT ItemNumber, ItemName, ItemPrice, ItemQuantity FROM Products WHERE ItemType = 'Graphics'

Now it is a matter of assigning this statement to the SelectCommand property of the AccessDataSource. This assignment is done programmatically with the statement

ProductsSource.SelectCommand = SQLString

The SelectCommand property of the control with id="ProductsSource" (the AccessDataSource) is assigned the SELECT statement stored in variable SQLString. Immediately upon this assignment the newly composed SQL command is issued and the AccessDataSource returns this set of records from the database. Its binding with the GridView automatically produces a new table display.

The above examples just scratch the surface of Web-based data access. Throughout these tutorials additional server controls demonstrate how to retrieve data sources and display their content to produce dynamic information for changing user needs. Working with databases is an exercise in using the SQL language to compose SELECT, INSERT, UPDATE, DELETE, and other SQL commands to carry out database processing. It is assumed you have basic facility with this language. If you need a review of SQL syntax check the appendix to these tutorials.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10771986/viewspace-969550/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10771986/viewspace-969550/