Filling Datasets with Local Data

In our first example, ch10_01, we accessed the data in the authors table in the pubs example database on SQL Server. But you don't have to connect to an external data source. You can fill a dataset locally, with entirely local datano external data connection needed.

We're going to create our own database table in this example and store that table in a dataset. You can see this new example, ch10_02 in the code for this book, at work in Figure 10.3, where the data grid is displaying data for a set of customers that we've stored in a data table, which itself is stored in a dataset.

Figure 10.3. The ch10_02 example.

To make this work, we'll start with a look at the DataTable class.

Working with the DataTable Class

The DataTable class represents a table of data, and datasets are made up of collections of data tables. You can find the significant public properties of DataTable objects in Table 10.19, their significant methods in Table 10.20, and their significant events in Table 10.21.

Table 10.19. Significant Public Properties of DataTable Objects

PROPERTY	PURPOSE
ChildRelations	Returns the child relations for this table.
Columns	Returns the columns in this table.
DataSet	Returns the DataSet object this table belongs to.
HasErrors	Returns True if there are errors in any of the rows in the table.
MinimumCapacity	Returns or sets the table's starting size.
Rows	Returns the rows in this table.
TableName	Returns or sets the name of the table.

Table 10.20. Significant Public Methods of DataTable Objects

METHOD	PURPOSE
AcceptChanges	Accepts (that is, commits) the changes made to the table.
Clear	Clears the data in the table.
Copy	Copies the table.
GetChanges	Returns a copy of the table including changes made to the table since AcceptChanges was last called.
GetErrors	Returns rows that contain errors.
ImportRow	Imports a row into a table.
LoadDataRow	Finds and updates a row.
NewRow	Creates a new row, with all the fields each row in the table has currently.
RejectChanges	Rolls back the changes made to the table since it was created or since AcceptChanges was called.
Select	Returns an array of rows.

Table 10.21. Significant Public Events of DataTable Objects

EVENT	MEANING
ColumnChanged	Happens after a value in a column was changed.
ColumnChanging	Happens while a column's value is being changed.
RowChanged	Happens after a row has been changed.
RowChanging	Happens while a row is being changed.
RowDeleted	Happens after a row was deleted.
RowDeleting	Happens while a row is about to be deleted.

In the ch10_02 example, we'll create our own data table, fill it with data, and install it in a dataset. When the user clicks the Create the Dataset button you see in Figure 10.3, we start by creating a new DataTable object. Note that we pass the name of this new table, "Customers" , to the DataTable constructor:

 private void button1_Click(object sender, System.EventArgs e) {  DataTable table1;   table1 = new DataTable("Customers");  .   .   . 

This creates a new data table, table1 . There's nothing in this table yet, so the next step is to configure it with the fields we want in each record. To do that, you add columns to the table using the DataColumn class. In this example, we're going to store data for various customers in the table, and then add first name, last name, phone number, and ID fields to each customer's record, using the DataColumn class.

Working with the DataColumn Class

DataColumn objects represent the columnsthat is, the fieldsin a data table. You can find the significant public properties of DataColumn objects in Table 10.22.

Table 10.22. Significant Public Properties of DataColumn Objects

PROPERTY	PURPOSE
AllowDBNull	Returns or sets whether null values are allowed.
Caption	Returns or sets the column's caption.
ColumnName	Returns or sets the column's name.
DataType	Returns or sets the column's data type.
DefaultValue	Returns or sets the column's default value used in new rows.
MaxLength	Returns or sets the maximum length of a text column.
Ordinal	Returns the column's position in the Columns collection.
ReadOnly	Returns or sets if the column is read-only.
Table	Returns the column's table.
Unique	Returns or sets if values in this column need to be unique.

When you create a column in a table, you name that column by passing its name to the DataColumn constructor. You should also set the columns data type, using the column's DataType property. Tables keep track of columns with their Columns collection, so you can add the new columns we'll create to the table with the Columns collection's Add method. In this example, we're going to create first name, last name, phone number, and ID fields for each customer's record:

 private void button1_Click(object sender, System.EventArgs e) {   DataTable table1;   DataRow row1, row2, row3, row4;   table1 = new DataTable("Customers");  DataColumn firstName = new DataColumn("First Name");   firstName.DataType = System.Type.GetType("System.String");   table1.Columns.Add(firstName);   DataColumn lastName = new DataColumn("Last Name");   lastName.DataType = System.Type.GetType("System.String");   table1.Columns.Add(lastName);   DataColumn phone = new DataColumn("Phone");   phone.DataType = System.Type.GetType("System.String");   table1.Columns.Add(phone);   DataColumn id = new DataColumn("ID");   id.DataType = System.Type.GetType("System.Int32");   table1.Columns.Add(id);  .   .   . 

This code has configured which fields will appear in each record in our data table. Next, we have to actually create those records, and for that, you use the DataRow class.

Working with the DataRow Class

DataRow objects correspond to the records in a data table. Using the methods of this class, you can gain access to, insert, delete, and update the records in a table. You can find the significant public properties of DataRow objects in Table 10.23, and their significant methods in Table 10.24.

Table 10.23. Significant Public Properties of DataRow Objects

PROPERTY	PURPOSE
HasErrors	Returns True if there are errors in the row.
Item	Returns or sets data in a specified column.
ItemArray	Returns or sets all of the data in a row.
RowError	Returns or sets a row's error description.
RowState	The current state of a row.
Table	The table that contains this row.

Table 10.24. Significant Public Methods of DataRow Objects

METHOD	PURPOSE
AcceptChanges	Accepts (that is, commits) the changes made to the row.
BeginEdit	Begins an edit operation.
CancelEdit	Cancels an edit operation.
ClearErrors	Clears the errors in the row.
Delete	Deletes the row.
EndEdit	Ends an edit operation.
GetChildRows	Returns the row's child rows, if any.
IsNull	Returns True if a column contains a null value.
RejectChanges	Rolls back the changes made to the table since it was created or since AcceptChanges was called.
SetColumnError	Sets the error description for a column.

You don't have to create DataRow objects from scratch and configure the columns in each such object before adding it to a table; you can call the table's NewRow method to create a DataRow object already configured with the correct columns for that table (the maximum number of rows a table can have, by the way, is 16,777,216). To access the data in a field in a row, you can refer to the field by name, rowName ["First Name"] , or by number, rowName [0] . After you place the data you want in this new record, you can add it to the table with the table's Rows collection's Add method. Here's how we install our data in the data table in the ch10_02 example:

 private void button1_Click(object sender, System.EventArgs e) {   DataTable table1;   DataRow row1, row2, row3, row4;   table1 = new DataTable("Customers");     .     .     .  row1 = table1.NewRow();   row1["First Name"] = "Earnest";   row1["Last Name"] = "Hemingway";   row1["Phone"] = "[phone]";   row1["ID"] = 1;   table1.Rows.Add(row1);   row2 = table1.NewRow();   row2["First Name"] = "Humphrey";   row2["Last Name"] = "Bogart";   row2["Phone"] = "[phone]";   row2["ID"] = 2;   table1.Rows.Add(row2);   row3 = table1.NewRow();   row3["First Name"] = "Myrna";   row3["Last Name"] = "Loy";   row3["Phone"] = "[phone]";   row3["ID"] = 3;   table1.Rows.Add(row3);   row4 = table1.NewRow();   row4["First Name"] = "Grace";   row4["Last Name"] = "Kelly";   row4["Phone"] = "[phone]";   row4["ID"] = 4;   table1.Rows.Add(row4);   DataSet dataset1 = new DataSet();   dataset1.Tables.Add(table1);   dataGrid1.SetDataBinding(dataset1, "Customers");  } 

At the end of this code, you can see how to add this new table to a dataset. You create a new DataSet object and add the new table to the dataset's Tables collection. Finally, the code binds the new dataset to a data grid. You can see the results in Figure 10.3we've added our own data to a table, installed that table in a dataset, and bound that dataset to a data grid.

We've started drilling down to the actual cell-by-cell data in code now, and we'll continue that in the next example. The next example illustrates how to access the cell-by- cell data in the authors table, including how to retrieve the name of each column.