Using Databases With Asp

18: 19: 20: <EditItemTemplate> 21: 24: 27: 30: 31:

This listing won’t work yet. You have to define the methods to handle its events first—you’ll get to that in a moment. When users click each item, they will be displayed a menu of choices, thanks to the EditItemTemplate on line 20. Let’s first examine the code in this listing.

ANALYSIS

On line 16, you change the “Select” command of the Linkbutton to “Edit”. This is a special command reserved by ASP.NET that automatically raises the DataList’s EditCommand event. You must then declare the handler for this event as follows:

9

26

Day 9

sub DataList1_EditCommand(obj as object, e as _ DataListCommandEventArgs) DataList1.EditItemIndex = e.Item.ItemIndex DataList1.DataBind() end sub

Add this method to the code declaration block. You simply set the EditItemIndex property of the DataList to the selected item, which then displays the EditItemTemplate, beginning on line 20, and turns on edit mode for that item. Typically, you allow an item to be put in edit more to enable the user to edit fields, which in turn updates the data store. You’ll examine this in more detail tomorrow. You built three new link buttons that contain three more special commands: Cancel, Update, and Delete. These link buttons are shown on lines 21–29. When these commands are bubbled up, they raise the DataList’s CancelCommand, UpdateCommand, and DeleteCommand commands, respectively. You build these handlers just as you did for EditCommand: sub DataList1_CancelCommand(obj as object, e as _ DataListCommandEventArgs) DataList1.EditItemIndex = -1 DataList1.DataBind() end sub sub DataList1_UpdateCommand(obj as object, e as _ DataListCommandEventArgs) ‘ update data store DataList1.DataBind() end sub sub DataList1_DeleteCommand(obj as object, e as _ DataListCommandEventArgs) ‘ delete from data store DataList1.DataBind() end sub

Add these methods to your code declaration block as well. To take an item out of edit mode, you simply set the EditItemIndex to -1. To actually update or delete data from the data store, you have to build methods manually. Remember to always call the DataBind method again, or else the DataList won’t be updated to reflect any events or changes. Finally, you can view this listing from the browser now that all your event handlers are defined. Figure 9.9 shows what happens when you click on an item and turn on edit mode. Clicking on the Update and Delete buttons currently don’t do anything, but clicking on Cancel takes the item out of edit mode and places it in select mode. This produces a figure similar to Figure 9.8.

Using Databases with ASP.NET

27

FIGURE 9.9 Placing an item in edit mode.

9

DataGrid Server Control The DataGrid control is similar to both the DataList and Repeater controls, except that it provides even more functionality. This control uses columns to display data in a grid layout. By default, the DataGrid generates a column for every field in the data store. However, you can specify the fields to display manually, as well as how to display them. You can define the following types of columns: • Bound columns—These allow you to specify which columns to display and in what order, and allow you to format style attributes. These columns are the default used by the DataGrid. • Button columns—These columns display buttons for all the items on the grid, for which you may define custom functionality. A typical example is an Add to Shopping Cart button. • Edit command columns—Allow you to edit items. Replace all bound columns with modifiable fields. • Hyperlink columns—Display data as hyperlinks. • Templated columns—You can use templates, as with Repeater and DataList controls, to define custom formats for the columns.

28

Day 9

The DataGrid control automatically chooses a type of column based on the data being presented, but it’s easy enough to change the default behavior. Listing 9.13 shows an example. LISTING 9.13

DataGrid Example

1: 15: 16: < Columns> 17: 18: 19: 24: 25: 26: 28: 29: 30: 31: 33: 34: 35: 36: 38: 39: 42: 43: 44: 45:

Using Databases with ASP.NET

29

Listing 9.13 demonstrates the various properties of the DataGrid and how to create your own columns for the data. Using the same Page_Load event as Listing 9.10 (with the name changed from the ListBox to the DataGrid) produces the result shown in Figure 9.10. FIGURE 9.10 The DataGrid control in action.

9

This control has a lot of properties you can manipulate. Lines 2–13 simply set up properties that you should already be familiar with. Line 14 tells this DataGrid that you want to set up your own columns, which you’ll proceed to do in the next section. If you set AutoGenerateColumns=true, the DataGrid would generate its own columns and you wouldn’t have to specify custom ones. AutoGenerateColumns causes the DataGrid to use all data source columns available, so you might prefer to define your own columns; by doing so, you can limit what the user sees.

ANALYSIS

Note

If you set AutoGenerateColumns=true and still provide custom column definitions, the DataGrid will render both. It will display all columns plus the additional ones you define. This means you could end up with multiple copies of the same column. For example, using Listing 9.13 but changing AutoGenerateColumns to true on line 14 generates Figure 9.11.

30

Day 9

FIGURE 9.11 AutoGenerate= true causes all columns to be displayed, plus the custom columns you’ve defined.

To define your custom columns, you must add them to the Columns collection of the DataGrid control. This can be done either programmatically at runtime or at design time, as you’ve done here. Using the < Columns> tag, you start defining the custom columns on line 16. TemplateColumn, HyperlinkColumn, BoundColumn,

and ButtonColumn are all ASP.NET controls used specifically for the DataGrid control. In Figure 9.10, you saw the output of the different types of columns. These columns are fairly simple and only contain a few properties you haven’t seen yet. The BoundColumns control uses the DataField property to bind to a column in your data store. The ButtonColumn control displays a LinkButton control by default, but you can use the ButtonType property to specify a different type, as shown on line 41. Feel free to play with the settings on the DataGrid. You’ll find that this control is very useful for displaying data from databases, and it allows you to customize nearly every aspect of its display.

Editing Items The DataGrid control also makes it easy to allow users to edit entries. Simply create an EditCommandColumn, and ASP.NET handles much of the display mechanism on its own. Let’s modify Listing 9.13 a bit to include an edit command column. See Listing 9.14.

Using Databases with ASP.NET

LISTING 9.14

DataGrid Example with Editable Columns

1: 8: 9: 10: 11: ... 12: ... 13: 14: 20: 21: 22: 23:

Because you’ve created a new EditCommandColumn, you can delete the previous HyperLinkColumn on line 36 of Listing 9.13. You can also remove the delete ButtonColumn on line 39 because the EditCommandColumn will add one for you. Leaving everything else the same, you tell the DataGrid which methods will handle its Edit, Cancel, and Update events on lines 5–7. On line 14, you declare your EditCommandColumn, which will display a LinkButton with the text “Edit”. When the user clicks this link, you enter edit mode for the selected item, and each bound column changes to a text box that the user can modify. Figure 9.12 shows what happens when you click this new Edit button.

ANALYSIS

Note

31

Only BoundColumns change into modifiable fields! The rest, such as TemplateColumns, retain their original interface. Make sure that any columns you want the user to edit either are BoundColumns or contain textboxes for editing.

9

32

Day 9

FIGURE 9.12 The EditCommandColumn

allows you to modify bound columns in a DataGrid.

EditCommandColumn also automatically displays the Update and Cancel links. You can define methods to handle these events as well: sub DataGrid1_Edit(obj as object, e as DataGridCommandEventArgs) DataGrid1.EditItemIndex = e.Item.ItemIndex DataGrid1.DataBind() end sub sub DataGrid1_Update(obj as object, e as DataGridCommandEventArgs) ‘do updates DataGrid1.DataBind() end sub sub DataGrid1_Cancel(obj as object, e as DataGridCommandEventArgs) DataGrid1.EditItemIndex = -1 DataGrid1.DataBind() end sub

These methods are similar to those for the DataList control.

Sorting The DataGrid control doesn’t have intrinsic support for sorting data rows, but it enables you to build such functionality by providing built-in events and displays. When you turn sorting on, by default the DataGrid turns every column header into a LinkButton that users may click to sort by that column. You’ll have to build your own sorting mechanism,

Using Databases with ASP.NET

33

but at least ASP.NET provides you with the basics. You can also specify custom sorting, in which you define the columns that the user can sort by, and even provide custom sorting links. To turn sorting on, simply add AllowSorting=”true” to the declaration of the DataGrid. Then build a method named SortCommand to handle the sorting events: sub DataGrid1_SortCommand(obj as Object, _ e as DataGridSortCommandEventArgs) ‘ sort the data using the SortField property ‘ of the eventargs DataGrid1.DataBind() End Sub

The SortField property of the DataGridSortCommandEventArgs parameter tells you which column was clicked. You can use this information to sort your data accordingly, whether by sorting a DataView (which will be covered tomorrow) or by changing your SQL command. You’ll look at sorting data programmatically in much more detail tomorrow.

Note

FIGURE 9.13 When AllowSorting=true,

all headers turn into links to sort by.

If you want to use the default sorting mechanism, you must allow the DataGrid to generate its columns automatically—set AutoGenerateColumns=True. Otherwise, sorting won’t work as expected. Figure 9.13 shows an example of turning on sorting with AutoGenerateColumns=True.

9

34

Day 9

To use custom sorting, turn on sorting and specify a SortField for each column that you want to make sortable. For example, if you want to allow users to sort the list by address, you could use the following code:

Paging Paging is the capability of the DataGrid to divide the data into multiple pages if there are too many records to display on one page. This was once a complex task that developers had to build themselves, but ASP.NET now handles it for you. When paging is set up, ASP.NET divides the returned results into the number of pages you specify and provides buttons for the user to navigate the list. It then uses the CurrentPageIndex property of the DataGrid to determine which page should be displayed currently. When the user clicks on a button to go to the next page, the entire data set is re-created and the process starts over. This can result in long load times for your pages if you have large amounts of data. Therefore, the DataGrid control allows you to define custom paging procedures, which may not suffer from such conditions. To turn paging on, set AllowPaging=True (this is true by default), and set the PageSize property to the number of records you want to display at once. You can also specify the style of the paging buttons to display by using the PagerStyle property. The two built-in styles are the Next and Previous buttons and the numeric page numbers. Let’s modify the previous listing to include paging, shown in Listing 9.15. LISTING 9.15 1: ➥e 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13:

Adding Paging Capabilities to the DataGrid

sub DataGrid1_PageIndexChanged(obj as Object, as DataGridPageChangedEventArgs) DataGrid1.CurrentPageIndex = e.NewPageIndex DataGrid1.DataBind() end sub ... ...

Using Databases with ASP.NET

35

You first define a new method, DataGrid1_PageIndexChanged, to handle the paging events. The DataGrid won’t automatically change the page for you, so you have to do so yourself as shown on line 2. The DataGridPageChangedEventArgs contains the property NewPageIndex, which tells you the page number the user has clicked on. Simply set the DataGrid’s CurrentPageIndex to this value, rebind the data to the control, and ASP.NET handles the rest. In your DataGrid control, you add five new properties. Line 9 turns paging on (this is the default), and line 10 specifies that you want to display two records per page. Line 11 sets the paging mode to display numeric page numbers. This property can be either “NumericPages” or “NextPrev”, which display the next and previous buttons. On line 12, you tell the grid to only display two page-navigation numbers at once—this property is only valid if the mode is NumericPages. If there are more pages than this value, the user will see a clickable ellipsis. Finally, you declare the handler for the PageIndexChanged event. Figure 9.14 shows the paging features in action. FIGURE 9.14 Paging with the DataGrid control.

If you don’t want to use the built-in paging buttons, you can create custom ones. Simply set the PagerStyle-Visible property to false and place your own buttons where you like (for instance, in the header or footer). You’ll have to create your own navigation methods to handle these events as well—move between pages by changing the CurrentPageIndex property in your code.

9

36

Day 9

The DataGrid also allows you to use manual paging for tighter control over the process. This involves getting deeper into the mechanisms of ADO.NET, so we’ll leave this topic for tomorrow.

Summary of Data Binding Controls Now you know why you skipped over these three controls on Day 5—they’re quite complex! These controls make it very easy to display dynamic data using any type of data source, which was once one of the greatest difficulties facing ASP developers. With so many options, and with all the controls sharing a lot of the same features, it may be confusing to decide which one to use and where. Table 9.2 summarizes the options and provides suggestions on which controls to use in your situation. TABLE 9.2

Summary of Data Binding ASP.NET Server Controls

Control

Features/When to Use

Repeater

Simple, read-only output with no inherent support for selecting or editing. No inherent look; must be defined manually using templates. No inherent paging mechanism. You should use this control when you only have very simple data to display. This control is very helpful for use with one-dimensional arrays and collections, and it’s highly efficient and lightweight for use just about anywhere.

DataList

Provides a default, customizable table display. Editable content. Single and multiple selections. Optional multiple columns. Paging not inherent. A more advanced version of the Repeater, this control provides more functionality, allowing you to select and edit items. Use this control if you need to display simple data that may require user interaction, or for any two-dimensional data in general. A typical use would be to display a Windows Explorer-type file hierarchy.

DataGrid

Provides a default, extensively customizable grid display. Editable content, including deletion. Single and multiple selections. Paging supported inherently. Support for customized functionality.

Using Databases with ASP.NET

TABLE 9.2

37

continued

Control

Features/When to Use This control is the powerhouse of the list controls. It provides a full-featured set of display capabilities, complete customization capabilities, and powerful paging, sorting, and editing functionality. Use this control when you want to allow user interaction with the data, or when you need its data organizing power. Usually recommended for data from complex databases. A typical use for this control would be as part of a shopping cart in an e-commerce application where users can update, remove, and add items directly. It might seem like a good idea to use this control all the time, but it carries a lot of overhead and might not be ideal for situations requiring small, fast display.

Let’s build an example to solidify your understanding of data binding and these controls. You’ll build a simple DataGrid application that sets its background colors to values stored in a DataSet—a “row-colorizer,” if you will. This may not sound like much, but it encapsulates a lot of what you learned today. Figure 9.15 shows you what this sample should look like. FIGURE 9.15 Your completed RowColorizer application.

The first step is figuring out how you’re going to get your data. Let’s use a custom DataSet so you don’t have to deal with any databases just yet. You can use this DataSet to fill your DataGrid. Let’s examine the code that creates the DataSet. Listing 9.16 shows a method that you’ll be adding to in a moment.

9

38

Day 9

LISTING 9.16

Creating Your DataSet with the Colors of the Rainbow

1: function CreateDataSet as DataSet 2: ‘create an array of colors 3: dim i as integer 4: dim arrColors() as String 5: if ViewState(“Colors”) is nothing then 6: arrColors = new String(7) {“red”, “orange”, _ 7: “yellow”, “green”, “blue”, “indigo”, “violet”} 8: ViewState(“Colors”) = arrColors 9: else 10: arrColors = ViewState(“Colors”) 11: end if 12: 13: ‘create an empty dataset 14: ds = new DataSet(“MyDataSet”) 15: 16: ‘create a new table and columns 17: dim dTable as New DataTable(“Colors”) 18: dTable.Columns.Add(“Color”, GetType(String)) 19: dTable.Columns.Add(“ID”, GetType(Int32)) 20: ‘add table 21: ds.Tables.Add(dTable) 22: 23: ‘add rows 24: for i = 0 to 6 25: dim dr as DataRow = dTable.NewRow() 26: dr(0) = arrColors(i).ToString 27: dr(1) = i 28: dTable.Rows.Add(dr) 29: next 30: 31: return ds 32: end function

Save this listing in the file summary.aspx, in the code declaration block (you’ll be adding other methods and UI elements to this file shortly). This should look similar to Listing 9.1—the first DataSet you built today. You’re using the colors of the rainbow (red, orange, yellow, green, blue, indigo, violet) for your data. Look at lines 5–11. It might look scary, but don’t worry: It’s not! Imagine a typical situation with the row colorizer. A user visits the page and sees several rows in a DataGrid, all different colors. These colors are stored in an array you’ve created. Each time the page loads, the array is created anew and the DataGrid uses it to set the colors. Then the user tries to edit the colors in the grid, and changes the first row to blue instead of red. However, the result won’t be as expected. When the user submits the form again, the array will be created anew, and the DataGrid will use it instead of the new value.

Using Databases with ASP.NET

39

This is because the Page_Load event, which is responsible for creating the array, executes before the method that handles the DataGrid’s update event. Therefore, your update method doesn’t add new data; it only reinserts the old. Figure 9.16 illustrates this process. FIGURE 9.16 Your update event won’t receive the correct data.

1. Page_Load event, array created, data bound, page displayed

2. User clicks edits button, page reloads, edit mode turned on

ID

Color

Change

ID

Color

Change

0

red

Edit

0

red

Change Cancel

1

orange

Edit

1

orange

Edit

2

yellow

Edit

2

yellow

Edit

3

green

Edit

3

green

Edit

4

blue

Edit

4

blue

Edit

5

indigo

Edit

5

indigo

Edit

6

violet

Edit

6

violet

Edit

4. Page_Load event, array recreated, data bound (all rows reset to original values)

5. Update method retrieves value from edited row, rebinds data, page displayed

3. User changes value to blue and submits form

9

blue

6. Original data displayed ID

Color

Change

0

red

Edit

1

orange

Edit

2

yellow

Edit

3

green

Edit

4

blue

Edit

5

indigo

Edit

6

violet

Edit

You might be wondering whether you could just not bind the data in step 4. Wouldn’t that solve the problem? Unfortunately, no. If the data is not bound, the DataGrid won’t contain any data, and subsequent methods won’t be able to access the values. Consequently, not binding the data in step 4 would cause an error to occur. Therefore, you need to store your color array in a place where it won’t be reinitialized to the original values each time the page is submitted. The State Bag is the answer (see Day 5, “Beginning Web Forms,” for more information). The first time you create your array, you store it in the State Bag. Subsequent page loads and method calls will now refer to this array, which is initialized only once. Your update method can modify values in the array in the State Bag, and everything will work beautifully. Let’s look at the code, and this will make more sense. Line 5 checks whether there is already an array in the State Bag. If there isn’t—as should be the case upon first viewing of the page—you instantiate your array on lines 6 and 7. If the State Bag already contains an array, meaning that the array was already created (and perhaps modified), you simply retrieve it. On lines 13–29, you create a DataSet and add DataTables and the information from the array to it. You should be familiar with this task by now. Rather than creating seven rows manually, you instantiate an array with the values on line 2 and use a for loop to add them to the DataSet, as shown on lines 24–29.

40

Day 9

Finally, you return the DataSet to whatever method called this function. Presumably, this calling method will bind the DataSet to the DataGrid. Let’s take a look at the declaration for the DataGrid, shown in Listing 9.17. LISTING 9.17

Your Rainbow DataGrid

1: 2: 3: 10: 11: 12: 13: 14: ’/> 16: 17: 18: 19: 21: 22: 26: 27: 28: 29:

Add this listing to the end of summary.aspx. This is a standard DataGrid with handlers defined for the Edit, Cancel, Update, and ItemCreated events. Note that the second column, the BoundColumn, contains the color from the DataSet that will be editable. The EditCommandColumn will automatically display all the links to allow the user to edit, update, and cancel. Listing 9.18 shows the rest of the code declaration block for this page.

Using Databases with ASP.NET

LISTING 9.18 1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48:

41

The Completed summary.aspx

<%@ Page Language=”VB” %> <%@ Import Namespace=”System.Data” %> <%@ Import Namespace=”System.Data.OleDb” %> <script runat=”server”> dim ds as DataSet dim blnSet as Boolean = false sub Page_Load(obj as Object, e as EventArgs) ds = CreateDataSet blnSet = true if not Page.IsPostBack then BindGrid end if end sub sub BindGrid() dgColors.DataSource = ds dgColors.DataMember = “Colors” DataBind() end sub sub ChangeColor(obj as object, e as DataGridItemEventargs) dim intIndex as Integer = e.Item.ItemIndex if blnSet then if intIndex > 0 then dgColors.Items(intIndex - 1).BackColor = _ Drawing.Color.FromName(ds.Tables(“Colors”). _ Rows(intIndex-1)(“Color”)) dgColors.Items(intIndex - 1).ForeColor = _ Drawing.Color.FromName(ds.Tables(“Colors”). _ Rows(6-intIndex)(“Color”)) end if end if end sub sub dgColors_Edit(obj as object, e as DataGridCommandEventargs) dgColors.EditItemIndex = e.Item.ItemIndex BindGrid end sub sub dgColors_Cancel(obj as object, e as DataGridCommandEventargs) dgColors.EditItemIndex = -1 BindGrid() end sub sub dgColors_Update(obj as object, e as DataGridCommandEventargs)

9

42

Day 9

LISTING 9.18 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59:

continued dim strColor as String = Ctype(e.Item.Cells(1). _ Controls(0), TextBox).Text ds.Tables(“Colors”).Rows(e.Item.ItemIndex) _ (“Color”) = strColor ViewState(“Colors”)(e.Item.ItemIndex) = strColor

dgColors.EditItemIndex = -1 BindGrid end sub

This listing contains all the code for summary.aspx except the HTML and CreateDataSet function. On lines 6–7, you declare two variables that will be used throughout your page: a DataSet and a Boolean value, which you’ll get to in a moment. The Page_Load method, beginning on line 9, fills the DataGrid from line 6 with data from the CreateDataSet function. If the page has not been posted back on itself, the controls on the page are bound with a custom function, BindGrid, which you’ll examine soon. This method also does one more thing: It sets the blnSet variable to true. This variable tells you when the data has been created—it is initially set to false on line 7 because the CreateDataSet method hasn’t been executed. After this method executes, you set this variable to true. You’ll come back to it in a moment. Next, on line 17, is the BindGrid method, which simply assigns the DataSource and DataMember properties of the DataGrid and calls DataBind. Let’s examine the methods on lines 38, 43, and 48 before returning to the ChangeColor method on line 23. The first two methods, dgColors_Edit and dbColors_Cancel, simply set the turn on and off edit mode, and rebind the data. The first method sets the EditItemIndex property to the item the user selected, whereas the second method sets it to -1 to turn off edit mode. The data is then rebound so that the changes will take effect. The dgColors_Update method on line 48 is a bit more complex. First, you grab the changed value from the event arguments of the DataGrid on line 49. The value is stored in the first control, in the second cell of each row, written as Item.Cells(1).Controls(0). Because this returns a generic control, you must cast it as a text box and then use the Text property to grab the value. On line 52, you update the value stored in the corresponding row of the DataSet. Literally, ds.Tables(“Colors”).Rows(e.Item.ItemIndex)(“Color”) means the value stored in the “Color” field at the data row corresponding to the row the user selected on the DataGrid, in the table “Colors”, in the variable ds, which happens to be your DataSet. (That’s long-winded, but it proceeds in a logical manner.)

Using Databases with ASP.NET

43

You set this value to the changed value from the DataGrid, and do the same thing for the array of colors you’ve stored in the State Bag on line 54. This is so that the next time the DataGrid is created, it will use the updated value instead of the original one. Then you take the item out of edit mode, and finally rebind the DataGrid. Now the changes that users make to the values will be persisted, but you’re not done yet! You have to color each row according to the corresponding value in the DataSet. To do this, you’ll use the Item_Created event of the DataGrid, which fires immediately before every item on the grid is created. The event handler for this method is described next. The ChangeColor method on line 23 is executed every time a new row is created in the DataGrid. Therefore, with seven items in the colors array, this method will be executed at least seven times each time the page is requested. Line 24 retrieves the index number of the row in the DataGrid in question, for easier access later. Lines 26–35 are responsible for changing the color of each row to the corresponding value in the DataSet. There are a few caveats to this section, so let’s examine it more closely. On line 26, you examine the blnSet variable again. The reason is simple: Recall that this variable indicates whether or not the DataSet has been created. The code on lines 27–34 accesses rows in the DataSet, and if the DataSet hasn’t been created yet, you’ll have some problems. Therefore, you must check whether the data is ready for access. Wait a second. Doesn’t the Page_Load method always execute before this event? And isn’t the Page_Load method responsible for creating the data? In that case, theoretically, the data should always be created before the ChangeColor method executes, so why the check? Normally, this would be true, but DataGrids aren’t average server controls—they do things a bit differently. When you first view a DataGrid, or when you click on the Edit button, things work the same as with other server controls: The Page_Load method executes first, followed by event handlers in no particular order, including the handler for the ItemCreated event. At this stage, you won’t have any problems. However, when you click the Update, Cancel, or Delete button, the data in the DataGrid is bound again before the Page_Load method executes. This means that, in this case, the ChangeColor method executes seven times before the Page_Load method executes and creates the DataSet. During these seven executions, you can’t access the data because it hasn’t been created yet. Thus, the check on blnSet on line 26 is to handle just this scenario. Using some well-placed Response.Write methods, Figures 9.17 and 9.18 show the order of execution when the edit and cancel command buttons have been clicked, respectively.

9

44

Day 9

FIGURE 9.17 With the edit method, the Page_Load method executes first.

FIGURE 9.18 With the cancel, update, and delete commands, the data is rebound before the Page_Load event.

Using Databases with ASP.NET

Let’s move onto line 27. This check ensures that you are manipulating valid items in the DataGrid—you’ll examine this in more detail later. Line 28 sets the background color for each row in the DataGrid using the BackColor property. Essentially, what you want to do is grab the color stored in the row that the user has selected. Unfortunately, the process isn’t as simple as it sounds because the BackColor property accepts only a System.Drawing.Color object, and the value in the data store is a string. Therefore, you must cast appropriately. Unfortunately, you cannot cast directly from a string to a color— you have to go about it in a different way.

Note

e.Item.ItemIndex will return -1 for headers and footers, and the corresponding index number for all other rows.

The Color object has a method, FromName, that takes a string that represents a valid color name, such as “Red” or “Green”. It will convert this string into a Color object for you. Therefore, on line 29, you pass the FromName method the color name stored in your DataSet, and get back a valid Color object. Now you have your color for the row that the user selected. On line 28, you set the BackColor property of the current row to that color. When you view this output, the words tend to blend into this colored background, so you decide to change the foreground color as well. On lines 31–33, you do the same thing to return the color for the foreground. You know that a contrasting color will show up well, so you use the formula 6 - ItemIndex to return the composite color. (Note that you also could have used e.Item.Count - e.Item.ItemIndex, but because you know the count and it will never change, you hard-code the value.) You set the foreground color on line 31, and you’re done! You now have a beautiful rainbow-colored DataGrid (shown in Figure 9.12) that users can manipulate. They can change the values in the data store, and the corresponding row’s color change as well.

Note

If you specify a color not contained in the Color object, such as “bluegreenviolet” or “reddd”, the application will throw an error. Later, you might

want to add a routine to verify that the user’s entry is an actual color by using the IsKnownColor method of the Color object.

You might notice something peculiar about this control, however. The last row of the DataGrid isn’t colored violet as it should be.

45

9

46

Day 9

Let’s examine the evidence. The ItemCreated event fires before the item is actually created. You can verify this by examining DataGrid1.Items.Count during the ChangeColor method. This property returns the actual number of items in the grid. The first value it returns, which is when the ItemCreated event fires for the first time, is 0. Therefore, you can’t actually set the color of the row during the ItemCreated event because it hasn’t been created yet. Trying to do so will produce an error. Going strictly by e.Item.ItemIndex to determine which row was created won’t be accurate—especially because it returns -1 twice in this example. This is the reason for the if statement on line 27 in Listing 9.18: You want to make sure that you don’t try to set the color for a row that isn’t even there. Because this event fires before the item is created, you have to set the color of the row created the last time the event fired. This is the reason you use e.Item.ItemIndex - 1 when referencing your values. Consequently, you can’t color the last row because when the last ItemCreated event fired, you colored the row before the last one. All is not lost, however. Your last row can know the joys of coloration. It takes clever manipulation of the row being created and an examination of the properties of the row, but this will be left as an exercise for you.

That’s Not ASP! Today’s lesson illustrates two of the greatest improvements of ASP.NET over classic ASP: the DataSet and data binding. The first is somewhat similar to the older Recordset object, but provides much more functionality. It can represent complete sets of data, including relationships between tables and hierarchical data sets. Data binding allows you to easily display content in your pages. In classic ASP, you had to manually loop through any data store, such as the Recordset shown in the following code snippet: <% Do While Not objRS.EOF Response.write “” & objRS(“FirstName”) & “ “ & _ objRS(“LastName”) & “
” & _ objRS(“Address”) & “
” & objRS(“City”) & _ “
” & objRS(“State”) &”
”& objRS(“Zip”) & _ objRS(“Phone”) & “

” objRS.Movenext Loop %>

Developers had to build the code to handle the display as well as the HTML for it. Thus, code and UI elements were interspersed together throughout a page, which was prone to error and often led to confusion (this problem was known as “spaghetti code”).

Using Databases with ASP.NET

47

With data binding and server controls, that is no longer necessary. These controls will automatically loop through any object that can be iterated, such as a DataSet, array, or even other listing server controls! In addition, they provide a plethora of capabilities to control the display of the data. After you’ve experienced data binding and the listing server controls, you’ll wonder how you ever got by without them! They can easily cut you development time in half, at the same time providing more functionality than with classic ASP.

Summary Yesterday you looked at the fundamentals of databases and how to use them in your ASP.NET Web pages. Today’s lesson expanded on that topic immensely and showed you complex new ways of adding, editing, sorting, and paging data on your pages. Specifically, you learned about data binding and three new server controls, which provide tremendous functionality, and shorten the work of developers. First you examined the DataSet, an integral part of ADO.NET’s new architecture. You learned how the DataSet is used to represent heterogeneous data while maintaining a consistent interface. Tomorrow you’ll learn about the entire ADO.NET toolset and how it works within the new distributed data paradigm. You spent a lot of time today on the three primary data-binding controls—the Repeater, DataList, and DataGrid ASP.NET server controls—and how easily they promote data presentation and manipulation. These controls provide a lot of complex functionality for very little work on the developer’s part. Each of these controls provide similar functionality, and they share a lot of common properties, methods, and events. After you learn how to use one, you can easily apply your knowledge to the others. Learning these controls is a big step toward becoming an ASP.NET database expert—and you’re almost there! So far, you’ve been lightly skimming over ADO.NET and its mechanisms for accessing and modifying data. Tomorrow, you’ll make up for that by taking an in-depth look at how ADO.NET functions with ASP.NET. You’ll also learn how to allow users to update your data stores using ADO.NET—a very important topic. In the meantime, start playing with these data binding server controls!

9

48

Day 9

Q&A Q Can a DataSet hold XML data? A Absolutely. In fact, when the DataSet is traveling from the server to the client and back, it’s first transformed into XML. This allows it to move data into places that normally wouldn’t be accessible, such as behind restricted firewalls, which are designed to stop unauthorized data. Because XML is plain text, firewalls ignore it and allow it through. Q What does the MinimumCapacity property in Table 9.1 do? A This property sets the minimum number of rows that you expect will reside within this table. Setting this property in no way restricts you from adding more rows. Instead, it sets aside memory resources ahead of time so that access to these rows will be faster during runtime. You’ll get to this and other specific ADO.NET properties tomorrow.

Workshop This workshop will help reinforce the concepts covered in today’s lesson. Answers can be found in Appendix A.

Quiz 1. What namespaces must you import to use data mechanisms in your ASP.NET pages? 2. True or False: The DataSet can contain multiple tables. 3. What are the three collections of the DataSet, and what do they do? 4. What is the syntax for data-binding expressions? 5. Does the DataList control support paging? 6. What are the two properties of a DataGrid that you must specify to turn on sorting and paging, respectively?

Exercises 1. Enhance the planner application from exercise 2 in Day 6 to use data binding. Bind the DayLabel to the Calendar’s selectedDate, and format it properly.