Wiley 978-0-7645-5708-8 Datasheet

8Reading from DatabasesSo far, you've learnt a lot about programming, and seen those techniques in use in a variety of Webpages. Now it's ti

❑ PagerStyle: Defines the style of the pager section. In our grid, this is the last row showing thepage numbers, but it appears before the footer if a

Try It Out Creating a Data Page1.Create a new page using the Data Pages templates. Pick the Data Report with Paging and Sorting,and call it SortPage.

Figure 8-6This isn't much different from the drag and drop approach we used in the first example, but it uses the.NET data classes and a DataGrid

❑ AllowSorting: Allows the grid to sort the rows on the basis of column selections. Setting thisto true enables links on the column headings.❑OnSortCo

one attached to. Well, since it hasn't got a named object, ASP.NET takes this as being a property of thecurrent Page. By default, a Page doesn&ap

if (SortField == String.Empty)CommandText = "select * from Suppliers order by CompanyName";elseCommandText = "select * from Suppliers o

Displaying Data Using the Code WizardsThere are times where both the drag and drop from the Data Explorer and the template pages cannotprovide you wit

4. The drop-down list shows configured data sources (from the Data Explorer) as well as an optionto create a new connection. Pick the existing connect

Figure 8-118. Click OK and you'll see the WHERE clause part of the window is filled in as shown in Figure 8-12:Figure 8-129. Press the Next butto

You can see just the required columns in Figure 8-13.10. Press Next.11. From the Name Method window, change the name textbox to GetProductsDataSet. Ma

plain text. Spreadsheets, on the other hand, are complex files containing not only the entered text andnumbers, but also details about the data, such

This is where (pun intended) we add the WHERE part of the SQL statement, and this is what filters therows and joins tables together. We've select

Now when you look at the WHERE clause section you see two tables joined together as in Figure 8-17:Figure 8-17The WHERE Clause Builder can also be use

Next, we have the connection string that simply points to our existing database:string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; &quo

We mentioned earlier that the data adapter is the link between our page and the data. As part of thislink, the adapter provides not only data fetching

❑ System.Data.OleDb: Contains the objects used to communicate to databases via OLEDB.OLEDB provides a common set of features to connect to multiple da

On the left we have the database and the connection, in the middle we have four Command objects, andon the right a DataAdapter and a DataSet. Notice t

The three main methods of the command you'll use are the execute methods:In the examples so far, we haven't used these methods as the execut

of products, with a drop-down list showing the product categories. The user could select a category sothat only those categories are shown.The Paramet

6. Double-click the Fetch button to switch to the Click event procedure. Add the following code:void Button1_Click(object sender, EventArgs e) {DataGr

13. Save the file and run it.14. Select a category and then click Fetch to see only the products for that category shown in Figure8-22:Figure 8-22What

This is the sort of thing you'd see in a spreadsheet, but there are a couple of big problems with this. For astart, we have repeated information.

lstCategory.DataValueField = "CategoryID";lstCategory.DataTextField = "CategoryName";lstCategory.DataBind();}}When the Fetch butto

Once the query string is set, we define our command to run the query, as follows:System.Data.IDbCommand dbCommand = new System.Data.OleDb.OleDbCommand

string queryString = "SELECT [Products].[ProductName], ""[Products].[QuantityPerUnit], [Products].[UnitPrice]," +"[Products].

Many times, when fetching data we simply want to display it as it is, perhaps by binding it to a grid. TheDataSet provides a local store of the data,

"[Products].[UnitsInStock] FROM [Products]";System.Data.IDbCommand dbCommand = new System.Data.OleDb.OleDbCommand();dbCommand.CommandText =

System.Data.OleDbDataReader dataReader = new _dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);if (!dataReader.HasRows)Response.Wr

57084_08.qxp 30/01/2004 8:03 PM Page 282

The OrderDetails table is as follows:We now have three tables that can be linked together by their ID fields as shown in Figure 8-1:Figure 8-1We now h

foreign key is simply a column that is the primary key in another table. Because the values of theprimary key and the foreign key will be the same, we

Figure 8-22. Select Access Database from the window that appears and press OK.3. Enter the following into the Data File text area (use a central locat

You can double-click on these to open the table, and see and change the data. One thing you mightnotice is that you don't see any queries – that&

3. Save the page and run it as shown in Figure 8-5:Figure 8-5Amazing! A sortable grid full of data and you didn't have to write even a single lin

some database specific features. You don't need to know about these specifically (they are fullydocumented in the .NET help files); just copy the