Using the DataGrid Control

The first thing you need when retrieving data from a database is, of course, a database connection. Although you can use the Data Adapter Configuration Wizard to create a new database connection, the process is easier to understand if you do it yourself the first time. To do so, start a new Windows Application project, open the Toolbox window, and drag a new SqlConnection object onto the form.

You now have a sqlConnection1 object in the Forms Designer. Open the object’s Properties window, and in the ConnectionString drop-down list, select New Connection. In the Data Link Properties window that appears, enter the defaults for the Microsoft Data Engine (MSDE) that’s installed with the .NET Framework SDK samples, as follows:

• For the server name, select (local)\NetSDK.

• For the login information, select Use Windows NT Integrated Security.

• For the database, select Northwind.

The fully configured Connection tab is shown in Figure 16-1.

Figure 16-1.

Defining the connection properties.

Click OK to complete the database connection.

After the connection to the database is defined, we can use it to access any data in the Northwind database. To specify which data we’re interested in, we’ll use a data adapter, which enables us to define which data to retrieve. (You can also use a data adapter to update data; you’ll learn how to do this in Chapter 18.)

To create a new SqlDataAdapter object, drag one from the Toolbox window onto the form. The Data Adapter Configuration Wizard will appear and will guide you through the creation of the SqlDataAdapter object. Because we’ve already defined a SqlConnection object, the wizard proposes to use that connection, which is perfectly fine with us. The wizard next prompts for a query type—here we’ll stick with the default of using SQL statements.

The SQL statement you provide will specify what users will ultimately see in the data grid. To limit the number of columns in the data grid, enter the following SQL statement:

SELECT CustomerID, CompanyName, Address, City, PostalCode, Country 
    FROM Customers

Using this information, the wizard will generate everything necessary for populating a dataset based on the SQL statement.

After you’ve created the SqlDataAdapter object, name it sqldaCustomers.

Up to now, we haven’t yet dealt with the data itself—only with how to connect to the database and specify the data to retrieve. The data representation on the client is called the dataset. In contrast to the ADO recordset, a dataset is tightly bound to the data it’s representing. For this reason, you should create a specialized dataset for each SqlDataAdapter object. Because it’s just a click away, this extra step doesn’t really slow you down.

To create a DataSet object, simply select the SqlDataAdapter object you generated earlier and choose Generate Dataset from the Data menu. Give the dataset a descriptive name, such as dsCustomers, and then click OK. (Remember, ADO.NET is discussed at length in Chapter 18.)

Now that we’ve done the prerequisite work, we can concentrate on working with the data grid. To add a data grid to a form, simply drag the DataGrid control from the Toolbox window to the form. To bind the dataset you created in the previous section to the DataGrid control, you must set the following two properties of the control:

• DataSource  Specifies which DataSet object to bind to

• DataMember  Specifies which table (DataTable object) of the DataSet object the data grid should be bound to by default

Configuring the DataMember property of the DataGrid control is easy—your only choice is the Customers table.

The completed setup is shown in Figure 16-2.

Once the setup is complete, the DataGrid control immediately displays the columns it will have at run time. This neat feature is also useful in that it allows you to double-check your SQL commands.

Figure 16-2.

Configuring the DataSource and DataMember properties for a data grid.

At this point, the application can be compiled and started, but the data grid won’t yet contain any data. Although you’ve defined everything from the connection to the dataset, nothing is loaded automatically. You must populate the dataset using the Fill method of the corresponding data adapter, as shown here:

dsCustomers1.Clear();
sqldaCustomers.Fill(dsCustomers1);

This code can be placed either in the constructor of the form or in a button event. When Fill returns, both the dataset and the data grid are populated, as shown in Figure 16-3.

Figure 16-3.

The dataset displayed in the data grid.

The Update DB button shown in Figure 16-3 provides a clue as to what we’ll look at next—editing data in the data grid and writing those changes back to the database.

As you play around with the data in the data grid, you’ll notice that you can edit the data in a cell. But when you close the application or reload the dataset, all your changes are lost. This is because the dataset is a pure in-memory representation of the data and has no connection with the database from which you retrieved the data unless you explicitly tell the dataset to update the database with your changes.

The update operation is similar to filling a dataset—that is, you call a method on the corresponding data adapter. Actually, the update is a single line of code, as follows:

sqldaCustomers.Update(dsCustomers1);

To summarize, we’ve written a total of three lines of code for an application that can display and update customer data in a database. This is only the tip of the iceberg, however, with more to come in the following sections. If you want to familiarize yourself more with the current example, go to the DatagridDbconnSimple directory on the companion CD.

The prerequisites for showing hierarchical data in a data grid are basically the same as for viewing nonhierarchical data. You’ll need a database connection and a dataset, but here’s the difference: for every hierarchy level (for example, Customers and Orders), you need a separate data adapter.

In addition to the connection and data adapter you created earlier in this chapter, create an additional DataAdapter object using the following SQL command:

SELECT OrderID, CustomerID, OrderDate, Freight, ShipAddress, 
    ShipName, ShipCity, ShipPostalCode, ShipCountry 
    FROM Orders

Name the new DataAdapter object sqldaOrderHistory.

To create the dataset, select both data adapters, choose Generate DataSet from the Data menu, and name the dataset dsOrderHistory. Because we’re working with multiple tables in this example, we still have some work to do. The dataset still knows nothing about the relationship between the two tables—that is, which is the parent and which columns should be used to create the relationship.

To add this missing information, double-click dsOrderHistory.xsd in Solution Explorer. You’re now in the XML Schema Designer (more on XML in Chapter 19), which displays definitions for the Customers and Orders tables. To define the relationship between these two tables, drag a Relation object from the Toolbox window to the Orders table. In the Edit Relation dialog box that appears, make sure that Parent Element is set to Customers, Child Element is set to Orders, and Key Fields and Foreign Key Fields are both set to CustomerID. Click OK to close the dialog box. The design will look something like Figure 16-4.

Figure 16-4.

Customers and Orders tables linked with a Relation object.

Save the changes to the schema, define the DataGrid control’s DataSource and DataMember properties as you did in the previous example, and populate the dsOrderHistory dataset using the following code:

dsOrderHistory1.Clear();
sqldaCustomers.Fill(dsOrderHistory1);
sqldaOrderHistory.Fill(dsOrderHistory1);

Finally, compile and run the application.

The plus signs (+) displayed next to each record in the data grid enable you to drill down in the hierarchy that resides below the Customers table. Our example contains only one dependent child table, named Orders, accessible via the Customers­Orders relationship (unless you changed the default name of the relationship in the previous section). Clicking the relationship name changes your data grid to a view similar to that shown in Figure 16-5.

Figure 16-5.

The Orders table expanded, with the Customers table information shown in the top row.

To return to the Customers table, click the left arrow in the top row. Although this drill-down capability can come in handy occasionally, it gets tiring when you need to view child records more frequently. In that case, you might consider linking multiple data grids.

The relationship you specify in the XML Schema Designer not only allows for hierarchical display of data in a single data grid, but it also lets you link two separate data grids in such a way that when the active row is changed in the parent grid, the child grid automatically reflects the change by displaying the corresponding rows from the child table.

To see how easy this feature is to implement, add one more data grid to our existing form. Set the data grid’s DataSource property to the existing dataset, and for the DataMember property, enter the following code:

Customers.CustomersOrders

This is the relationship between the parent and child tables of our dataset. Now whenever the row is changed in the parent data grid, the newly created child data grid will display the corresponding Orders table data, as shown in Figure 16-6.

Figure 16-6.

Selecting a customer in the parent data grid to automatically display the associated order information in the child grid.

You can verify that the child data displayed is correct by drilling down in the parent data grid—remember, it’s still a hierarchical data grid.

As you’ll see in Chapter 18 and Chapter 19, ADO.NET and XML are deeply intertwined. You can easily treat a dataset as XML data, and you can load XML data into a dataset.

To see how this works, take a look at this small XML data file, appropriately named SampleData.xml:

<?xml version="1.0" encoding="utf-8" ?> 
<SnowReports Date="2/25/2002">
<Resort>
    <Name>Meribel</Name>
    <SnowHigh>120</SnowHigh>
    <SnowLow>43</SnowLow>
</Resort>

    

</SnowReports>

You’ll find the complete listing of this file on the companion CD in the DatagridXml directory. The tags describe what’s contained in this XML file: snow reports from various ski resorts, with the date of the report in the root node and resort-specific information contained in the Resort node.

To import the XML file’s contents into the data grid, the file first needs to be read and the data put into a dataset. Then the data binding of the data grid must be defined, which can be done using the SetDataBinding method. The code for these operations is shown here:

XmlDataDocument xdocSnowReports= new XmlDataDocument();
xdocSnowReports.DataSet.ReadXml(
    new StreamReader("SampleData.xml"), XmlReadMode.InferSchema);
dgLateXmlBound.SetDataBinding(xdocSnowReports.DataSet, "Resort");

The workhorse here is an instance of XmlDataDocument, which resides in the System.Xml namespace. The dataset of the XmlDataDocument object supports reading from an XML document, as well as the important task of automatically inferring a schema for the XML document. (We didn’t create an .xsd file.) Once all loading is complete, the Resort table is bound to the data grid.

The dataset in this example contains multiple tables because the root node and the Resort nodes define separate levels of the hierarchy of the XML document—the root node is the topmost parent table, and the Resort nodes are records in the first child table. The other nodes in the Resort nodes are leaf nodes, and therefore they form columns in the Resort table, not tables of their own.

Being able to access the root node’s attributes enables us to customize the data grid a bit more. One practical use for the date of the snow report would be to create a descriptive caption in the data grid, as shown here:

DataRow dr = xdocSnowReports.DataSet.Tables["SnowReports"].Rows[0];
string strCaption = dr["Date"].ToString();
this.dgLateXmlBound.CaptionText = "Snow report on " + strCaption;

You can customize the look and feel of your data grid in a number of ways. Probably the most important customization is defining table and column styles, which is covered next.

There are a number of good reasons for defining table and column styles. For example, you might not want to display all the columns that a table in a dataset offers; instead, you might want to show only a defined subset of columns. Another interesting possibility is to change how columns are rendered—for example, you can display check boxes for the True/False fields instead of text boxes. You can also adjust pure user interface settings, such as column width, background color, or font selection.

Working with column styles

To gain full control over how your data grid is displayed, you need to define each column with a column style. You can access the DataGridColumnStyle Collection Editor by clicking the GridColumnStyles collection in the respective DataGridTableStyle object. After you manually add all three columns of the snow report, the Collection Editor should look like Figure 16-7.

All three columns are defined as DataGridTextBoxColumn objects, with the MappingName property set to the respective XML tag name. Also modified are the Width and HeaderText properties. The sample application, including XML saving functionality, can be found on the companion CD in the directory DatagridXml.

Before we move on, we need to take a quick look at the DataGrid­Column­Style-derived classes. The DataGridColumnStyle class provides you with a simple text box column and a Boolean column represented by a check box. However, nothing prevents you from creating your own column styles—for example, a drop-down list for lookup values from other tables. Sample implementations for such custom DataGridColumnStyle-derived classes can be found at http://www.gotdotnet.com/.

Figure 16-7.

The DataGridColumnStyle Collection Editor, in which you can add text box and Boolean columns.