14.5 Mapping Tables and Columns

By default, when you use the DataAdapter to fill a DataSet, the column names that are used in the DataSet correspond to the column names defined in the data source.

The data adapter has a collection of table-mapping objects that are accessed through the TableMappings property. A table-mapping object maps a table in the data source to a table with a different name in the DataSet. Table mappings are perhaps most commonly used to map default table names that are created as a result of filling a DataSet from a query that returns multiple result sets. When multiple result sets are added to a DataSet using the Fill( ) method of the DataAdapter, they are assigned the default names Table, Table1, Table2, and so on. Mapping the table names ensures that the data is updated to the correct tables in the data source. Of course, the table objects can also be renamed with the same result.

Each table-mapping object has a collection of column-mapping objects that are accessed through the ColumnMappings property. A column-mapping object maps a column in the data source to a column with a different name in the DataSet, within the table defined by the containing table-mapping object. Figure 14-3 shows how tables and columns in the DataSet and data source are mapped to each other using mapping classes in the DataAdapter.

Figure 14-3. Table and column mapping classes

Both table and column mappings can be used by the Fill( ) and FillSchema( ) methods when retrieving data, and by the Update( ) method when submitting DataSet changes back to the data source. The Fill( ) method always uses mapping information, if present. The FillSchema( ) method lets you choose whether to use mapping information.

The following example shows how to set up a table mapping and a column mapping:

SqlDataAdapter da;

// ... code to set up the data adapter

// map the DataSet table MyOrders to the data source table Orders
DataTableMapping dtm = da.TableMappings.Add("Orders", "MyOrders");

// map the DataSet column MyOrderID (in the DataSet MyOrders table)
// to the data source column OrderID (in the data source Orders table)
dtm.ColumnMappings.Add("MyOrderID", "OrderID");

If incoming data source table and column names don't match DataSet object names and mapping can't be performed, the MissingMappingAction property of the DataAdapter determines what action is taken when data is retrieved using the Fill( ) method. The default value Passthrough results in the creation of the missing objects in the DataSet. The MissingMappingAction property can also be set to ignore missing objects or to raise an exception if missing objects are encountered.

Similarly, the MissingSchemaAction property of the DataAdapter determines what action is taken when the schema is retrieved using the FillSchema( ) or Fill( ) method. As with the MissingMappingAction property, options allow the missing objects to be added, ignored, or to cause an exception to be raised. Additionally, the MissingSchemaAction can be set to add the missing objects along with the primary key information.