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;

//  . . . 

// Create the DataAdapter.
String sqlText = "SELECT CategoryID, CategoryName, Description " +
    "FROM Categories";
SqlDataAdapter da = new SqlDataAdapter(sqlText,
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);

// Create the table mapping to map the default table name 'Table'.
DataTableMapping dtm = da.TableMappings.Add("Table", "tblmapCategories");
// Create the column mappings for the Categories table.
dtm.ColumnMappings.Add("CategoryID", "colmapCategoryID");
dtm.ColumnMappings.Add("CategoryName", "colmapCategoryName");
dtm.ColumnMappings.Add("Description", "colmapDescription");

// Create the DataSet and fill.
DataSet ds = new DataSet( );
da.Fill(ds);

// Retrieve and display the mapped name of the table as grid caption.
dataGrid.CaptionText = "TableName: " + ds.Tables[0].ToString( );

// Bind the default view of the Categories table to the grid.
dataGrid.DataSource = ds.Tables["tblmapCategories"].DefaultView;

Discussion

When the Fill( ) method of the DataAdapter is used to fill a DataSet, the column names used in the DataSet default to the column names defined in the data source.

A DataAdapter has a collection of DataTableMapping objects in its DataTableMappingCollection accessed through its TableMappings property. These objects map the name of a table in the data source to a DataTable with different name in the DataSet. When a batch query is used to fill multiple tables within a DataSet, the table names default to Table, Table1, Table2, and so on. You can use table mapping to rename 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.