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", ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.