ADO.NET Cookbook

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", ...