ADO.NET 3.5 Cookbook, 2nd Edition

2.3. Mapping Table and Column Names Between a 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 filled using a DataAdapter.

The solution defines a SQL statement to retrieve the Title, FirstName, and LastName columns from the Person.Contact table in AdventureWorks. A DataAdapter is created with a DataTableMapping object to map the DataSet default table name Table to the name mappedContact. 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. The table and column names are output together with the result set to the console.

The C# code in Program.cs in the project MappingTableAndColumnNames is shown in Example 2-3.

Example 2-3. File: Program.cs for MappingTableAndColumnNames solution

using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; namespace MappingTableAndColumnNames { class Program { static void Main(string[] args) { // Create the connection string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 Title, FirstName, LastName " + "FROM Person.Contact"; SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); ...