Export (0) Print
Expand All

Setting Up DataTable and DataColumn Mappings

A DataAdapter contains a collection of zero or more DataTableMapping objects in its TableMappings property. A DataTableMapping provides a master mapping between the data returned from a query against a data source, and a DataTable. The DataTableMapping name can be passed in place of the DataTable name to the Fill method of the DataAdapter. The following example creates a DataTableMapping named AuthorsMapping for the MyAuthors table.

workAdapter.TableMappings.Add("AuthorsMapping", "MyAuthors")
[C#]
workAdapter.TableMappings.Add("AuthorsMapping", "MyAuthors");

A DataTableMapping enables you to use column names in a DataTable that are different from those in the database. The DataAdapter uses the mapping to match the columns when the table is updated.

If you do not specify a TableName or a DataTableMapping name when calling the Fill or Update method of the DataAdapter, the DataAdapter will look for a DataTableMapping named "Table". If that DataTableMapping does not exist, the TableName of the DataTable will be "Table". You can specify a default DataTableMapping by creating a DataTableMapping with the name of "Table".

The following code example creates a DataTableMapping (from the System.Data.Common namespace) and makes it the default mapping for the specified DataAdapter by naming it "Table". The example then maps the columns from the first table in the query result (the Customers table of the Northwind database) to a set of more user-friendly names in the Northwind Customers table in the DataSet. For columns that are not mapped, the name of the column from the data source is used.

Dim custMap As DataTableMapping = custDA.TableMappings.Add("Table", "NorthwindCustomers")
custMap.ColumnMappings.Add( "CompanyName", "Company")
custMap.ColumnMappings.Add( "ContactName", "Contact")
custMap.ColumnMappings.Add( "PostalCode", "ZIPCode")

custDA.Fill(custDS)
[C#]
DataTableMapping custMap = custDA.TableMappings.Add("Table", "NorthwindCustomers");
custMap.ColumnMappings.Add( "CompanyName", "Company");
custMap.ColumnMappings.Add( "ContactName", "Contact");
custMap.ColumnMappings.Add( "PostalCode", "ZIPCode");

custDA.Fill(custDS);

In more advanced situations, you may decide that you want the same DataAdapter to support loading different tables with different mappings. To do this, simply add additional DataTableMapping objects.

When the Fill method is passed an instance of a DataSet and a DataTableMapping name, if a mapping with that name exists it is used, otherwise a DataTable with that name is used.

The following examples create a DataTableMapping with a name of Customers and a DataTable name of BizTalkSchema. The example then maps the rows returned by the SELECT statement to the BizTalkSchema DataTable.

Dim bizMap As ITableMapping = custDA.TableMappings.Add("Customers", "BizTalkSchema")
bizMap.ColumnMappings.Add( "CustomerID", "ClientID")
bizMap.ColumnMappings.Add( "CompanyName", "ClientName")
bizMap.ColumnMappings.Add( "ContactName", "Contact")
bizMap.ColumnMappings.Add( "PostalCode", "ZIP")

custDA.Fill(custDS, "Customers")
[C#]
ITableMapping bizMap = custDA.TableMappings.Add("Customers", "BizTalkSchema");
bizMap.ColumnMappings.Add( "CustomerID", "ClientID");
bizMap.ColumnMappings.Add( "CompanyName", "ClientName");
bizMap.ColumnMappings.Add( "ContactName", "Contact");
bizMap.ColumnMappings.Add( "PostalCode", "ZIP");

custDA.Fill(custDS, "Customers");
Note   If a source column name is not supplied for a column mapping or a source table name is not supplied for a table mapping, default names will be automatically generated. If no source column is supplied for a column mapping, the column mapping is given an incremental default name of SourceColumnN, starting with "SourceColumn1". If no source table name is supplied for a table mapping, the table mapping is given an incremental default name of SourceTableN, starting with "SourceTable1".
It is recommended that you avoid the naming convention of "SourceColumnN" when you supply a source column name for a column mapping, or "SourceTableN" when you supply a source table name for a table mapping, because the name you supply may conflict with an existing default column mapping name in the ColumnMappingCollection or table mapping name in the DataTableMappingCollection. If the supplied name already exists, an exception will be thrown.

Multiple Result Sets

If your SelectCommand returns multiple tables, Fill will automatically generate table names with incremental values for the tables in the DataSet, starting with the specified table name and continuing on in the form TableNameN, starting with "TableName1". You can use table mappings to map the automatically generated table name to a name you want specified for the table in the DataSet. For example, for a SelectCommand that returns two tables, Customers and Orders, issue the following call to Fill.

custDA.Fill(custDS, "Customers")

Two tables are created in the DataSet: Customers and Customers1. You can use table mappings to ensure that the second table is named Orders instead of Customers1. To do this, map the source table of Customers1 to the DataSet table Orders, as shown in the following example.

custDA.TableMappings.Add("Customers1", "Orders")
custDA.Fill(custDS, "Customers")

See Also

Using .NET Framework Data Providers to Access Data | DataSet Class | DataTable Class | DataTableCollection Class | DataTableMappingCollection Class | OleDbDataAdapter Class | OdbcDataAdapter Class | SqlDataAdapter Class

Show:
© 2014 Microsoft