Now that the DataSource control is providing the data you want, you need a way to display it. From the Data section of the Toolbox, drag a GridView control onto the page. The GridView control recognizes that a SqlDataSource is on the page and does not create its own.
If you had dragged the GridView onto the page first, it would have given you the opportunity to create a SqlDataSource rather than assuming you’d like to use one already in existence. It pretty much amounts to the same thing.
Click on the GridView’s Smart Tag (if it is not already open). Click the drop-down menu next to “Choose Data Source” and select the DataSource control you just created, as shown in Figure 4-12.
Figure 4-12. The Smart Tag of the GridView control lets you select the data source you want to use.
Once the data source is set, the data grid is redrawn, with a column for each field returned by the data source. The column headers are filled in for you based on the column names in the table that the data source represents.
You’ll have an opportunity to make the grid look much prettier, in just a short while.
Open the Smart Tag again and check “Enable Paging,” which allows the grid to show a limited number of entries on each page and provide links to the other pages providing access to all the data. Also check “Enable Sorting,” which allows the user to sort the grid by clicking on a column header.
Set the page to be the start page for the application (right-click the page in the Solution Explorer and select “Set As Start Page”) and then run the application. Figure 4-13 demonstrates how the screen should appear.
Notice that the MakeFlag column (which is a Boolean value of some obscure use to the AdventureWorks business model) is shown as a checkbox. Also note that each of the column headers are shown as links. Click on one of them now—you see that the grid is sorted by that column. Also notice that at the bottom of the grid are links to page through more data, 10 rows at a time. Click on some of those too, to see the various pages.
Figure 4-13. With the GridView in place and connected to the data source, you can see the data you asked for. Notice the clickable headings and the paging links.
Each time you click on one of the columns or one of the page numbers the entire page is posted back to the server, and you’ll encounter a noticeable delay and flicker. You know how to fix that!
Close the browser and return to Design view. Drag an UpdatePanel control onto the page from the AJAX Extensions section of the Toolbox. Drag both the SqlDataSource and GridView controls already on the page into the UpdatePanel.
Run the application again. Notice there are no visible postbacks when you page or sort, and consequently, no flicker.
The IDE has done a lot of work for you. It has examined the data source and created a BoundField for each column in the data. Further, it has set the HeaderText to the name of the column in the database, represented by the DataField attribute. It has set the AllowPaging and AllowSorting properties to true. In addition, it has also set the SortExpression to the name of the field. Finally, you’ll notice on the declaration of the GridView that it has set AutoGenerateColumns to False.
If you were creating the GridView by hand, and if you want to let the grid create all the columns directly from the retrieved data, you could simplify the code by setting AutoGenerateColumns to True. (If AutoGenerateColumns is set to true, and you also include explicitly bound columns, then you will display duplicate data.) To see this at work, create a second GridView by dragging another GridView control from the Toolbox inside the UpdatePanel, below the first.
In the Smart Tag, set the Data Source to the same source as that of the first, SqlDataSource1. Click on the “Enable Paging” and “Enable Sorting” checkboxes.
Now go to Source view. If necessary, delete the <columns> collection from the new grid, GridView2. Change AutoGenerateColumns to the default value: True. The declaration for this second GridView should look something like the following:
Run the page. Both grids behave identically and are visually indistinguishable. So why does the IDE create the more complex version? By turning off AutoGenerateColumns, the IDE gives you much greater control over the presentation of your data. For example, you can set the headings on the columns (such as changing ProductNumber to Product No.). You can change the order of the columns or remove columns you don’t need, and you can add new columns with controls for manipulating the rows.
You can make these changes by manually coding the HTML in the Source view, or by switching to Design View and clicking the Smart Tag for the GridView and choosing Edit Columns. Do that now for GridView1 and you’ll see the Fields dialog box, as shown in Figure 4-14.
This dialog box is divided into three main areas: the list of available fields, the list of selected fields (with buttons to remove fields or reorder the list), and the BoundField properties window on the right. When you click on a selected field (such as ProductID), you can set the way that field will be displayed in the data grid (such as changing the header to ID).
While you’re examining what you can do with the GridView, let’s make it look a little nicer. First, delete or comment out the second (simpler) grid (GridView2) you just created a few moments ago. Second, open the Smart Tag on the original grid. Click AutoFormat and choose one of the formatting options. Of course, you can format it by hand, but why work so hard for a simple example? We’ll choose “Brown Sugar” because it shows up well in the printed book. Run the application. The output should appear as in Figure 4-15.
Figure 4-14.The field editor dialog lets you change the properties of your data columns, without having to do it in Source view.
At this point, the SqlDataSource you’ve created has only a SELECT statement to extract data from the database:
SelectCommand="SELECT ProductID, Name, ProductNumber, MakeFlag, SafetyStockLevel, ReorderPoint FROM Production.Product" >
That’s fine, if all you want to do is display the data in the database. For a functional site, though, you probably want to be able to add new data, edit existing data, and even delete data. You can do all that just as easily as you did the SELECT statement, by asking your data source control to generate the remaining Create, Retrieve, Update, and Delete statements (fondly known as CRUD statements), using a wizard to make your work easier. To see this in action, switch to Design view, click on the SqlDataSource’s Smart Tag, and choose Configure Data Source. The Configure Data Source Wizard opens, displaying your current connection string. Click Next and the Configure Select Statement dialog box is displayed, as shown earlier in Figure 4-8.
Figure 4-15. The AutoFormat option in the GridView’s Smart Tag lets you choose the formatting option that best fits your site, and applies it automatically.
Recall the previous the Configure Data Source Wizard—it did not correctly identify the table in the autogenerated SELECT statement, omitting the schema name. You worked around that by specifying your own SQL statement. Since the SELECT statement you needed was relatively simple to type in, that was not a problem.
However, there is a lot of typing involved for all the CRUD statements. So for the rest of these statements, you will use the Wizard to generate the SQL code, and then just fix the table names.
Make sure the “Specify columns from a table or view” radio button is selected, and the Product table is selected. Check the columns you want returned by the SELECT statement (ProductID, Name, ProductNumber, MakeFlag, SafetyStockLevel, ReorderPoint). This will create a new SELECT statement.
Click the Advanced button to open the Advanced SQL Generation Options dialog box. Select the “Generate INSERT, UPDATE, and DELETE statements” checkbox, as shown in Figure 4-16.
Figure 4-16.You’ll use the Advanced SQL Options dialog box to automatically create the SQL statements to add, edit, and delete data from your data source.
Clicking this checkbox instructs the Wizard to create the remaining CRUD statements, and also enables the second checkbox, Use optimistic concurrency. This is a feature that safeguards your data in case another user makes a change to the database at the same time you do. Select this option as well, and Click OK. When you return to the Wizard, click Next then Finish. You may be asked to update your grid, which unfortunately will wipe out all your customization, but the good news is that you are now bound to a data source control that provides all four CRUD methods.
This Wizard breaks down if any of the fields in the grid can have null values. When a database table is created, you must specify if a column must have data or if null values (no data) are allowed. If you include fields in the GridView which are allowed to be null, then you must handcode the SqlDataSource declaration in Source view.
Open the Smart Tag on the GridView control again, and reapply the look and feel you want. Also—and this is important—select the checkboxes “Enable Editing” and “Enable Deleting.”
Switch to Source view. The SqlDataSource markup will appear similar to Example 4-2, except the new SQL commands have been added. You still need to modify the table names, or else you’ll get the error you saw earlier (see Figure 4-9). Add the schema name [Production] to each of the four statements highlighted in Example 4-2.
The following code does not include concurrency detection.
Switch back to Design view and notice the Edit and Delete buttons on each row. They are the result of checking the Enable Editing and Enable Deleting checkboxes.
Taking apart the code in Example 4-2, on the first line is the declaration for the SqlDataSource (and its corresponding closing tag at the bottom). After the ID, the obligatory runat="server", and the ConnectionString attribute, you see four attributes: the SelectCommand (which was there previously) and the new DeleteCommand, InsertCommand, and UpdateCommand.
The DeleteCommand takes a single parameter (@ProductID), which is specified in the DeleteParameters element:
A parameter to a SQL statement allows for parts of the statement to be replaced when it is actually run. SQL parameters are always preceded with the @ symbol. So, in the following SQL statement:
delete from Products where ReorderPoint > @ReorderPoint
all the records with a value of ReorderPoint greater than some specified value will be deleted from the Products table. One time the statement is run, that value may be 100, the next time it may be 5.
The UpdateCommand control requires more parameters, one for each column you’ll be updating, as well as a parameter for ProductID (to make sure the correct record is updated). Similarly, the InsertCommand takes parameters for each column for the new record. All of these parameters are within the definition of the SqlDataSource.
Now that your SqlDataSource object is ready to go, you only have to set up your GridView control. In Design view, click on the GridView Smart Tag and choose “Edit Columns.” Verify that the checkboxes to enable editing and deleting are selected, as shown in Figure 4-17.
If you prefer to have buttons for Edit and Delete, rather than links, click on the Smart Tag and select “Edit Columns....” When the Fields dialog box opens, click the Command Field entry in the Selected Fields area (lower-left corner). This brings up the Command Field Properties in the right-hand window. In the Appearance section of the Fields editor, choose ButtonType and then change Link to Button in the drop-down menu next to ButtonType, as shown in Figure 4-18.
The result is that the commands (Edit and Delete) are shown as buttons, as shown in Figure 4-19.
Figure 4-17. Select the Smart Tag on the GridView, and check the boxes to enable editing and deleting.
Figure 4-18. Click the Smart Tag of the GridView, then click Edit Columns to get this Fields dialog box where you can select and edit the columns in the GridView. Here, the CommandField button type is being changed.
Figure 4-19.You can change the Edit and Delete links in the GridView to buttons, if you prefer.
Please check back next week for the continuation of this article.
ASP.NET 
/ 15 
Saving and Retrieving Data with AJAX
