email article printer friendly get the code more resources

Implement ADO.NET Mapping
Use the ADO.NET mapping mechanism to bind a data source to a DataSet that uses different table and column names.
by Fabio Claudio Ferracchiati

September 2003 Issue

Technology Toolbox: VB.NET, ADO.NET

Imagine that you need to build a system that retrieves data from an external data source (such as a Web service) and imports it into your database. However, the data source's table and column names are different from the ones your database uses. Changing your database's structure (tables, columns, and so on), stored procedures, and relations to match those of the data source isn't a viable solution. It's smarter to use middleware code that maps the original data-source names to the destination data-source names. The code manages every naming conversion when you must retrieve data from the original source and insert it into the destination source, and vice versa.

You don't need to create this middleware code yourself, because the ADO.NET DataTableMappings and DataColumnMappings classes provide it. These classes aren't well known, but they can be useful in many situations. I'll show you how they work, and how you can use them to integrate data from differently structured data sources (see Figure 1).

In the days before ADO.NET, you could use the SQL AS keyword when you needed to manage a database with terse column names—such as ln for last name column or st for state column. The AS keyword defines aliases you can use in both the database and your code:

SELECT fn AS FirstName, ln AS LastName 
FROM authors

Then, you can use the aliases in the code instead of the original column names:

Dim da As New OleDbDataAdapter
Dim ds As New DataSet
da.Fill(ds, "authors")
Console.WriteLine("{0} {1}", _
   ds.Tables("authors").Rows(0) _
   "FirstName"), ds.Tables( _
   "authors").Rows(0) ("LastName")

Using the AS keyword can still be a good solution when you can operate directly on the original data source. However, you might need to retrieve data from a remote database that uses foreign table and column names. You can't change the database structure manually, and your database account probably doesn't have appropriate privileges. You'll find ADO.NET mapping classes to be extremely useful in this situation.

The DataAdapter class uses the mapping classes when you perform an operation against the database. For example, when your code invokes the Fill method, the DataAdapter object checks its TableMappings collection to look for a relation between the original and destination table and column names. This allows you to use your custom names in the code. The DataAdapter transforms them into the original names each time you perform an operation against the data source, such as retrieving data or inserting a new record. You can apply this feature to any kind of data source, not only databases. For example, you can map DataSet objects you retrieve from a Web service or a BizTalk schema.