[ Team LiB ]Recipe 2.16 Mapping Table and Column Names Between the Data Source and
DataSet
Problem
You want to control the names assigned to tables and columns when you fill a DataSet
using a DataAdapter.
Solution
Use DataTableMapping and DataColumnMapping objects to map the names of database
tables and columns in the data source to different names in a DataSet when using a
DataAdapter.
The sample code defines a SQL statement to retrieve the CategoryID, CategoryName,
and Description columns from the Categories table in Northwind. A DataAdapter is
created with a DataTableMapping object to map the database table name Categories to
the name tblmapCategories in the DataSet. Three DataColumnMapping objects are
created to map the database column names to different names in the table in the DataSet.
The DataAdapter is used to fill a new DataSet. Finally, the default view of the mapped
Categories table is bound to the data grid on the form.
The C# code is shown in Example 2-21
.
Example 2-21. File: MappingsForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

// . . .


tables created within the DataSet to match the table names in the data source or to map
the tables returned from a batch query to DataTable objects that already exist within the
DataSet.
Each table mapping object has a collection of DataColumnMapping objects in its
DataColumnMappingCollection that are accessed through its ColumnMappings property.
These objects map the name of a column in the data source to a column with a different
name in the DataSet for the table associated with the containing table mapping object.
The Fill( ) method of the DataAdapter always uses mapping information (if present) to
retrieve data from a data source. The FillSchema( ) method accepts an argument
specifying whether to use mapping information when retrieving schema information from
a data source. Like the Fill( ) method, the Update( ) method always uses mapping
information (if present) when submitting DataSet changes back to the data source.
In the solution, the Categories table retrieved by the query is mapped to a table in the
DataSet called tblmapCategories with the following code:
DataTableMapping dtm = da.TableMappings.Add("Table", "tblmapCategories");
Without the table mapping, a table named Table will be created when the Fill( ) method
is called. For a query returning a single table, the table mapping can also be specified by
using an overload of the Fill( ) method as shown:
da.Fill(ds, "tblmapCategories");
The solution also maps the three column names returned by the query, CategoryID,
CategoryName, and Description using the following code:
dtm.ColumnMappings.Add("CategoryID", "colmapCategoryID");
dtm.ColumnMappings.Add("CategoryName", "colmapCategoryName");
dtm.ColumnMappings.Add("Description", "colmapDescription");
The column mapping objects are added to the table mapping object for the table
containing the columns to be mapped.

[ Team LiB ]