928 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

To get the data into a DataSet, you must first establish a connection to the database. The Connection object allows you to specify the database you want to work with, and it’s one of the simpler objects of ADO.NET. Between the database and the DataSet there’s another object, the DataAdapter object. While the client application works with the data in the DataSet most of the time, every now and then it must exchange information with the database (query the database, or update it). The communication between the database and the DataSet takes place through the DataAdapter. This object knows how to update the database, as well as how to move data from the database and store them into the DataSet. As you will see, the DataAdapter object contains four commands for retrieving rows from the database, updating and deleting existing rows, and inserting new rows. These commands are SQL statements, and this is all the information the DataAdapter object needs to move data between the DataSet and the underlying data source.

The first advantage of the DataSet object is that it doesn’t care where its data came from, as long as there’s a DataAdapter object that can move data to and from the DataSet. As a result, you can create DataSets in code, or from an XML file. You can even save (or persist) a DataSet object to a disk file and write a database application without the database.

How About XML?

ADO.NET and XML go hand in hand, so where does XML come into the picture? XML is a method of representing structured data, and ADO.NET uses XML to pass data between the server and the client. Fortunately, you don’t have to write any XML code yourself, neither do you have to parse XML documents to retrieve the information. ADO.NET uses XML for its own purposes, and you can take advantage of it and write XML code, if you want. It is possible, for example, to create a new DataSet with XML statements and store your data there. This DataSet is totally independent of a database and resides in the client computer’s memory. When you’re done using it, you can store it to a file and retrieve it from there later. In effect, this is a mechanism to create your own data store (it’s not a database, of course, but you can have related tables), without the overhead of setting up a SQL Server database.

Later in this chapter, you will see this technique in action. XML is an interesting technology, but it’s not required for learning the basics of programming with ADO.NET. After mastering simpler topics such as data binding and programming DataSets, you can explore XML on your own.

Creating a DataSet

We’ll start our exploration of database programming by creating a DataSet. In this chapter, you’ll see how to create a DataSet with visual tools and how to display its data on a grid. In the following chapter, you’ll learn how to create DataSets programmatically, but the visual tools are much simpler, and in most cases there’s no reason to write code to connect to a database and populate a DataSet.

To create a new database application, start a new project as usual. When the project’s form appears, open the Server Explorer and expand one of the databases. Select the Northwind database and expand its icon to see the objects of the database. In the Tables section, select the Customers table, drag it with the mouse, and drop it on the form. VB will add two new objects in the Components tray: SqlConnection1 and SqlDataAdapter1.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

CREATING A DATASET 929

The first object, SqlConnection1, is the application’s connection to the database. This object contains all the information needed to connect to the database. If you look at its properties, you will see that its ConnectionString property is:

data source=PowerToolkit;initial catalog=Northwind;integrated security=SSPI; persist security info=False;workstation id=POWERTOOLKIT;packet size=4096

SqlDataAdapter1 is the channel between your application and the database. The DataSet doesn’t know anything about the database—it’s not its job to know about the database. The application can request the data through the DataAdapter object, process them and then rely on the DataAdapter to update the database.

If you look up the properties of the SqlDataAdapter1 object (Figure 21.1), you’ll see that it has

a SelectCommand property, which is a Command object that retrieves the data from the table. The SelectCommand object has a property called CommandText, which is a SELECT SQL statement:

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers

Figure 21.1

The properties of the SqlDataAdapter object

This statement was generated automatically when you dropped the Customers table on the form. VB picked up the information from the table’s structure in the database and create a SELECT statement to retrieve all the columns of all rows.

If you select the SelectCommand item in the Properties window and then click the button with the ellipsis that appears next to the item’s setting, the Query Builder window will pop up and you can edit the SELECT statement (to exclude a few columns, or specify selection criteria to limit the number of rows returned by the query). You can also edit the SELECT statement by selecting the SqlDataAdapter1 object on the designer and clicking the Configure Data Adapter command at the bottom of the Properties window.

The SqlDataAdapter1 also has an InsertCommand property, which is shown next:

INSERT INTO dbo.Customers(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

930 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers

WHERE (CustomerID = @Select_CustomerID)

Any string that starts with the @ symbol is a variable. The DataAdapter sets the values of all these variables to the values of the new row to be inserted and then executes the InsertCommand against the database. The INSERT statement will add a new row to the Customers table. The SELECT statement following the INSERT statement selects the newly added row from the table and returns it to the application. There are two more commands in the SqlDataAdapter object, UpdateCommand and DeleteCommand. These two commands update a row in the Customers table and delete a row, respectively. We’ll return to the DataAdapter object and look at its properties, as well as how to set it up manually, later in this chapter. For now, keep in mind that the action of adding a table to a form creates and configures a DataAdapter object.

Note If you’re working with an Access database, you’ll follow the same steps, but the objects will have different names. The Connection object’s default name will be OleDbConnection, and the DataAdapter object’s default name will be OleDbDataAdapter. The SQL statements that move data in and out of an Access database may use a slightly different syntax, but they’re equally simple statements.

So far, you’ve created and configured a DataAdapter object that knows how to access the database and retrieve the desired data. The next step is to tell the DataAdapter where to leave the data it retrieves from the database, and where the changes reside, so that it can update the database. This is the DataSet object. Select the DataAdapter object on the designer and locate the Generate Dataset link at the bottom of the Properties window. Alternatively, you can open the Data menu and select the Generate Dataset command. (The Data menu isn’t available unless the form is visible.) You will see the Generate Dataset dialog box, which is shown in Figure 21.2. This dialog box proposes to create a new DataSet object, named DataSet1, that will contain the table Customers. As you will see in the following example, a DataSet may contain multiple tables—that’s why you’re given the option to select the table(s) you want to add to the DataSet. Click OK to create the new DataSet object. In the following examples we’ll use more descriptive names, but for this introductory example I’m going to use the default names.

Two new items will be added to the project: the DataSet1.xsd item in the Solution Explorer and the DataSet11 object on the design surface. DataSet1 is a class that describes the structure of the data you’ll retrieve from the database. DataSet11 is an instance of this class—this is where the data will be stored.

Let’s see what we have done so far. Open the Data menu and select Preview Data. Alternatively, you can right-click the DataAdapter’s icon in the Components tray and select Preview Data. This command will open the Data Adapter Preview window, which is shown in Figure 21.3. When the Data Adapter Preview dialog box comes up, it will be initially empty. Click the Fill Dataset button to execute the query, populate the DataSet, and preview the Customers table. You can see that the query retrieved the desired data from the database. If the DataSet contains multiple tables, you’ll have to select the name of the table you want to preview in the Data Tables list, and its rows will appear in the preview pane.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

CREATING A DATASET 931

Figure 21.2

To generate a DataSet, specify its name and the tables you want to add to it.

Figure 21.3

Previewing the Customers table

The last step is to display the information to the user, and this is what we’ll do next. There are many ways to present the information on a front-end application, but we’ll start with a tool designed specifically for this purpose.

The DataGrid Control

One of the most common tasks in programming database applications is to present data to the users. We have created the DataSet that will hold our data, so we can now design an interface to present the data residing in the DataSet to the user. At design time, the DataSet is empty, of course. The DataSet object contains information about the structure of the table(s) it will hold at runtime, but no actual data.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

932 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

The primary control for displaying DataSets is the DataGrid control, which is similar to the grid you use to edit the rows of a table with SQL Server’s Enterprise Manager or even Access. The application you’ll develop in this section is called Customers (it’s available in this chapter’s folder on the companion CD), and its main form is shown in Figure 21.4. The DataGrid control can display not only single tables, but it also allows you to navigate through the rows of related tables. For example, you can display categories, select a category, and view the products under the selected category. Let’s start by displaying a single table on the DataGrid control.

Figure 21.4

Displaying the Customers table on a DataGrid control

Note By the way, the DataGrid control isn’t appropriate for all types of interfaces, and you shouldn’t give users free access to all the rows of a DataTable. However, it’s the best tool for visualizing the structure of a DataSet, and you will find it convenient at the beginning. As you learn more about the objects of ADO.NET and how to program them, you’ll start using the more traditional controls to build your interfaces. The DataGrid control, however, remains a powerful tool, especially for displaying DataSets.

Switch the project’s form and place a DataGrid control on it. To specify where the data will come from, you must set the control’s DataSource property to a DataSet. Locate the DataSource property in the Properties window and expand the list of possible settings for the property. You will see two settings: DataSet11 and DataSet11.Customers. If you set the DataSource property to a DataSet object, then you will have to specify which of the tables in the DataSet you want to display on the grid. Since our DataSet contains a single table, set the DataSource property to DataSet11.Customers. Alternatively, you can set the DataSource property to DataSet11 and the DataMember property Customers.

The names of all the fields will appear at the header of the DataGrid control. The data is still at the server; the control must be populated when the user requests it. That’s why you see only the field names and no data. By default, all columns have the same length. Clearly, you must customize the appearance of the DataGrid control. But first, let’s see what our data looks like on the control.

If you run the application now, you won’t see any data on the control. The DataSet must be populated explicitly by calling the Fill method of the associated DataAdapter. Place a button on the form, name it Load Data, and insert the following code in its Click event handler:

Private Sub Button1_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles Button1.Click

DataSet11.Clear()

SqlDataAdapter1.Fill(DataSet11, “Customers”)

End Sub

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

CREATING A DATASET 933

The first statement clears the current contents of the DataSet11 object. The Fill method of the DataAdapter accepts as arguments the names of a DataSet and of a table in the DataSet and populates the specified table. You can omit the second argument if the DataSet contains a single table.

Run the application, click the Load Data button, and the grid will be populated with the rows of the Customers table. You can edit the fields on the grid, but the changes are local to the DataSet and they’re not automatically submitted to the database. To update the underlying tables, you must explicitly call the Update method of the DataAdapter object. The Update method accepts two arguments, just like the Fill method. You specify the names of the DataSet and of the table to be updated. Enter the following statement behind the Update Table button:

Private Sub Button2_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles Button2.Click

SqlDataAdapter1.Update(DataSet11, “Customers”)

End Sub

Updating the rows in the underlying tables is not a trivial task, and it takes much more code than a single call to the Update method. For this example, we’ll assume that you’re updating simple tables and no other users are accessing the same tables at the same time. A more practical, robust approach requires quite a bit of code, and we’ll discuss it in the following chapter. The Update method updates one row at a time, and it stops as soon as it encounters an error. If the first edited row can’t be written to the underlying table, the DataAdapter object will not attempt to update the remaining edited rows. To continue the update process even if one or more rows fail, set the DataAdapter object’s ContinueUpdateOnError to True.

Customizing the DataGrid Control

The default appearance of the DataGrid control is rather blunt, and you’ll always have to customize it. The simplest customization tool for the DataGrid control is the AutoFormat command. Rightclick the DataGrid control and, from the context menu, select AutoFormat. On the dialog box that appears, you will see a list of available styles, such as Professional, Simple, and Classic. You can select each style on the dialog box to preview it and apply any style to the control by selecting it with the mouse and clicking OK to close the dialog box.

You can also customize each individual element of the control. Select the DataGrid control on the form and locate its TableStyles property in the Properties window. Click the button with the ellipsis and you will see the DataGridTableStyle Collection Editor, shown in Figure 21.5. The DataGrid control can display one or more tables, and each table can have its own style. Since our DataSet contains a single table, we must add a single DataGridTableStyle object to the collection. Click Add and the DataGridTableStyle1 object will be added to the collection. You can change its name too, but let’s leave the default for the first example.

Here you can set properties like the HeaderFont, the background color of the rows, as well as an alternate background color (the AlternatingBackColor value will be used for even-numbered rows). The DataGridTableStyle object won’t be automatically associated to any of the tables; you must set the name of the table explicitly with the MappingName property. Locate this property in the Properties section of the dialog box, expand the list of possible values (which are the names of the tables in the DataSet), and select the Customers table.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

934 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

Figure 21.5

Specifying the appearance of each table in the DataGrid control

Right above the MappingName property, you’ll see the GridColumnStyles property, which is also a collection. Click the button with the ellipsis, and you will see the GridColumnStyles Collection Editor, shown in Figure 21.6. This collection, which is initially empty, contains one member for each column, and each member determines the appearance of a different column in the parent table.

Figure 21.6

Specifying the appearance of each column on the DataGrid control

In the dialog box of Figure 21.6, you can set the widths, captions, and alignments of the individual columns. Don’t forget to set the MappingName property of each DataGridColumnStyle item to the appropriate field of the table. Any DataGridColumnStyle item that’s not mapped to a column will be ignored. The NullText property is a string that will appear in every field that’s Null.

Displaying Related Tables

In this section, we’ll build a project that involves two related tables, the Products and Categories tables. Most of the applications you’ll write will handle related rows from multiple tables, as isolated tables are quite rare. We’ll display the data on a DataGrid control again, but in a hierarchical way. The DataGrid allows you to display the rows of the parent table (in our case, the Categories table) and navigate to each parent row’s child rows. If a parent row has child rows, a plus sign is displayed in front of its name. You can click this symbol to view the related rows. At any point you can return

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

CREATING A DATASET 935

to the parent table, select another row, and view its child rows. The project you’ll build in this section is called RelatedTables, and you can find it on the CD. The project’s form is shown in Figure 21.7.

Figure 21.7

Displaying a table with related rows on the DataGrid control

The first step is to create a DataSet with two related tables. Start a new project, open the Server Explorer, and drop the Categories and Products tables of the Northwind database onto the form. VB will add automatically a Connection object to the form, as well as two DataAdapters. Since both DataAdapters see the same database, a single Connection object will suffice. Now you must configure the two DataAdapters. First, rename them to DACategories and DAProducts—no reason to work with names that differ in the last digit. To configure a DataAdapter object, select it with the mouse and click the Configure Data Adapter link at the bottom of the Properties window (or select the same command from the DataAdapter’s context menu). The default DataAdapter object is configured with a SQL statement that retrieves all columns and all rows of a table. In most cases you’ll have to edit this statement to retrieve a subset of a table.

To configure a DataAdapter, you can use the Data Adapter Configuration Wizard. In the following section, I will describe all the options offered by the wizard, and then we’ll use it to configure the

DACategories DataAdapter.

The Data Adapter Configuration Wizard

The Configure Data Adapter command will start the Data Adapter Configuration Wizard, and the first screen of the wizard is a welcome screen. Click Next to view the next one, which prompts you to select a Connection object. Accept the default connection, which was established when you dropped the tables on the form.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

936 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

Click Next to see the next screen of the wizard, which is the Choose A Query Type screen. Here you can specify the method that will be used to retrieve the data from the database. You can use SQL statements, use existing stored procedures, or create new stored procedures. When you’ll be working with large application or in a team environment, you will have to design the stored procedures first, and then you’ll start building database applications. The wizard can generate both the SQL statements and stored procedures needed to access the database. I suggest you familiarize yourself with SQL statements first and use stored procedures later. Let’s see how

each option works.

Select the SQL statements option and click Next. The Wizard will display the Generate The SQL Statements screen, where it will display a SELECT statement that retrieves all rows and all columns of the database. You can either edit the default SELECT statement or type a new statement. You can also click the Query Builder button to start Query Builder. Use this tool to visually specify the data you want to retrieve from one or more tables, as explained in Chapter 20. When you close the Query Builder, you’ll be returned to the Generate the SQL Statements screen and the new SELECT statement will appear on the this screen.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

CREATING A DATASET 937

By default, the wizard will create all the SQL statements for retrieving and editing the table (the INSERT, DELETE, and UPDATE statements). If you don’t plan to update the table from within the application—you only want to present data to the user—click the Advanced Options buttons. A dialog box will pop up where you can disable the generation of the UPDATE/INSERT/DELETE statements. On the same dialog box, you can set two more options to control how the statements will be generated.

The option Use Optimistic Concurrency applies to the UPDATE and DELETE statements. If you leave this option checked, the resulting statements will not modify any rows in the underlying table(s) in the database if these rows have been edited since your application read them. “Optimistic concurrency” means that we don’t anticipate multiple users editing the same row at the same time. If this happens rarely, then you can check the Optimistic Concurrency option. In the few cases that this happens, your application won’t be able to update the rows that have already been edited.

If you clear this option, the wizard will generate SELECT statements that update the underlying tables even if they have changed since they were read into the local DataSet. In this situation, the last user to commit his changes to the database wins.

The last option, Refresh The DataSet, generates SQL statements that update the database and then retrieve the rows they changed with a SQL statement. Check this option so that the application can immediately retrieve the new rows and display them on your form.

Click OK to close the Advanced Options dialog box and return to the wizard. Click the Next button to view the last screen that summarizes the results. Click Finish on this screen and the wizard will generate the SQL statements according to the options you specified on the wizard.

If you choose Create New Stored Procedures in the Choose A Query Type screen of the wizard, the next screen will display the SQL statement that retrieves all the data from the specified table. You can click the Query Builder to specify the query with visual tools. After specifying the SQL SELECT statement on which the stored procedures will be based, click Next to see the Create The Stored Procedures screen.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

938 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

On this screen, you can specify the names of the stored procedures for each action your application will perform against the database (select rows, insert new rows, editing existing rows, and delete rows). You can also specify whether the wizard should generate the stored procedures and add them to the database, or whether you want to write the stored procedures to the database yourself. You can click the Preview SQL Script button to see the stored procedures that the wizard will generate. You should let the wizard generate the stored procedures and edit them from within Server Explorer if you have to. Click Next to see the last screen of the wizard that summarizes the results.

If you choose Use Existing Stored Procedures in the Choose A Query Type screen of the wizard, the next screen will prompt you to select the four stored procedures for the actions you want to perform against the database. If a procedure requires parameters, you must specify the columns that contain the parameter values as well.

Configuring the DACategories DataAdapter

Let’s return to the RelatedTables project and configure the DACategories DataAdapter. The Categories table contains an Image field, which we won’t display on our form. Accept the default settings

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

CREATING A DATASET 939

on the Data Adapter Configuration Wizard, and change the SQL statement by removing the name of the Picture field. Here is the edited SQL statement you will see on the Generate the SQL Statements screen of the wizard:

SELECT CategoryID, CategoryName, Description

FROM dbo.Categories

Then configure the DAProducts DataAdapter by accepting all the defaults. Use the SQL statement generated by the wizard, which retrieves all the fields of the Products table (unless you want to omit a few columns). Once the two DataAdapters have been configured, you can generate the DataSet. Click the link Generate Dataset (or select the command Generate Dataset from the Data menu) and you will see the Generate Dataset dialog box (shown earlier in Figure 21.2). The wizard suggests that you create a DataSet with a single table. Check both table names and then change the default name of the DataSet to CategoriesProducts. You have just created a single DataSet with two tables.

When the CategoriesProducts.xsd file appears on the Solution Explorer, double-click it. The XSD file contains the schema of the two tables in the DataSet, but not the relationship between them. Even though the relationship between the two tables exists in the database, the wizard didn’t relate the tables in the DataSet to one another. You must do so by establishing a relationship manually. With the two tables on the designer’s surface, open the Toolbox. The Toolbox is a new one and contains the tools for editing XML schemas. Drag the Relation icon from the Toolbox and drop it on the Products table. The Edit Relation window, shown in Figure 21.8, will pop up. Here, you can specify the characteristics of the relationship between the two tables. Specifying a relationship between two tables in the DataSet is no different than specifying a relationship in the database. The Parent Element is the primary table, and the Child Element is the foreign table. After you have specified the primary and foreign tables, you must set the key fields of each table. Notice that the primary key can’t be changed: it’s always the parent table’s primary key. Finally, you can specify the rules for updating, deleting, and inserting rows. For now, leave the Default option in these boxes.

Figure 21.8

Establishing a relation between two tables in a DataSet

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

940 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

Click the OK button to return to the XML Designer. The relationship you created is depicted with a line between the two tables as shown in Figure 21.9. You can click the diamond-shaped icon at the middle of this line and select Edit Relation to see a dialog box where you can modify the relation.

Figure 21.9

How a relation is depicted on the XML Designer

If you click the XML tab at the bottom of the Designer, you will see the XML description of the relationship:

<xsd:unique name=”Constraint1” msdata:PrimaryKey=”true”> <xsd:selector xpath=”.//Categories” />

<xsd:field xpath=”CategoryID” /> </xsd:unique>

<xsd:unique name=”Products_Constraint1” msdata:ConstraintName=”Constraint1” msdata:PrimaryKey=”true”>

<xsd:selector xpath=”.//Products” /> <xsd:field xpath=”ProductID” />

</xsd:unique>

<xsd:keyref name=”CategoriesProducts” refer=”Constraint1”> <xsd:selector xpath=”.//Products” />

<xsd:field xpath=”CategoryID” /> </xsd:keyref>

I can’t get into the details of XML here, but it’s easy to see the definitions of the primary and foreign keys. keyref is XML’s term for a relation. The CategoriesProducts relation is between Constraint1 (the Categories table’s primary key) and the CategoryID field of the Products table.

We now have a DataSet with two related tables, just as they appear in the database. The DataSet will be populated with a copy of the two tables the moment it’s created. You can work with the copies of the tables and update the underlying tables in the database whenever you see fit (if you edit the DataSet). The catch here is that the DataSet must be populated at once. Should you be working with a table of half a million book titles, you’d download an enormous amount of information to the client. If the user wants to view the first few titles in a couple of categories only, you’re wasting system resources. In your applications, you should limit the number of rows downloaded to the client, and you’ll see how to do this later in this chapter. Never download more data to the client than you’re going to use. Dumping thousands of rows on a DataGrid control isn’t going to be of much help to the user either.

To load the data to the grid, enter the statements shown in Listing 21.1 in the form’s Load event.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

CREATING A DATASET 941

Listing 21.1: Populating a DataSet with Two Tables

Private Sub Form1_Load(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles MyBase.Load

DACategories.Fill(CategoriesProducts1, “Categories”)

DAProducts.Fill(CategoriesProducts1, “Products”)

End Sub

Run the application now. When the form comes up, it will be empty, displaying a plus symbol where the first row should appear. Click it and you will see the names of the two tables: Categories and Products. Select the parent table, Categories, and the grid will be populated with the rows of the Categories table. In front of each row, you’ll see the plus symbol, which indicates that all the rows in the Categories table are parent rows and you can expand them to see their child rows from the Products table.

If you click one of the plus signs, you’ll see the names of all relations that use the Categories table as the parent table. In our example, there’s only one such relation, the CategoriesProducts relation. Click this link and the grid will be populated with the selected category’s child rows, as shown in Figure 21.10. At the top of the grid, you see the name of the parent table and the selected row. This is the parent row of all the rows currently on the grid (it’s the category to which all the products belong). At the top-left corner of the grid are two icons: The back arrow icons takes you back to the parent row. The other icon is a toggle that hides/displays the details of the parent row.

Figure 21.10

Viewing child rows on the DataGrid control

The headers of each column are clickable. When a header is clicked, the rows on the grid are sorted alphabetically according to the selected column. If you sort a column, a little arrow appears next to its header, indicating the order in which the rows are sorted (ascending or descending). To disable the sorting of the columns, set the DataGrid control’s AllowSorting property to False (its default value is True).

As you can see, the DataGrid control can handle related tables without any code. It picks up all the information about relations from the schema (the XSD file created for you by the wizard) and uses it to build an elaborate interface. The DataGrid is fine for building an interface to browse one or more tables, but when it comes to updating the underling tables, you must provide additional code. You’re going to learn how to write robust code for updating the underlying tables in the following chapter. In the following section, we’ll look at another simple technique for displaying data on a form, using the familiar Windows controls.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com