Table and Column Mapping in ADO.NET

This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

One of the important properties of the DataAdapter is the TableMapping property. This property contains a collection of the DataTableMapping objects that are found in the System.Data.Common namespace (because they're common to all providers). The DataAdapter uses the DataTableMapping object to map the table name of the Data source to the DataTable name of the DataSet. In general, the names for both sources can be the same.

For example, in listing 5-51, the Northwind database's Order Table Mapping is constructed and added to the DataAdapter.

Listing 5-51. Using DataTableMapping to map Orders table of Northwind database

   private void DataMapping1()
        {
            // create a connection object
            string ConnectionString = "Integrated Security = SSPI;" +
            " Initial Catalog = Northwind;" + "Data Source = localhost;";
            SqlConnection conn = new SqlConnection(ConnectionString);

            // open the connection
            conn.Open();

            // Create a DataTableMapping object
            DataTableMapping myMapping = new DataTableMapping("Orders", "mapOrders");
            SqlDataAdapter adapter = new SqlDataAdapter("Select * FROM Orders", conn);

            // Call DataAdapter's TableMappings.Add method
            adapter.TableMappings.Add(myMapping);

            // Create a DataSet object and Call DataAdapter's Fill method
            // Make sure you use new name od DataTableMapping i.e., MayOrders
            DataSet ds = new DataSet();
            adapter.Fill(ds, "mapOrders");
            dataGrid1.DataSource = ds.DefaultViewManager;
        }

The default mapping for a DataTable is the Table alias. If you use this mapping name, then you don't need to mention the table in the Fill method listing 5-52 shows an example using DataTableMapping with the Table option.

Listing 5-52. Using DataTable Mapping with the Table option

    private void DataMapping2()
        {
            // create connection object
            string ConnectionString = "Integrated Security = SSPI;" + "Initial Catalog = Northwind;" + " Data Source = localhost;";
            SqlConnection conn = new SqlConnection(ConnectionString);

            // open the connection
            conn.Open();

            // create a DataTableMapping object
            DataTableMapping myMapping = new DataTableMapping(" Table", " Orders");
            SqlDataAdapter adapter = new SqlDataAdapter("Select * From Orders", conn);

            // Call DataAdapter's TableMappings.Add method
            adapter.TableMappings.Add(myMapping);

            // Create a DataSet object and call DataAdapter's Fill method
            // Make sure you use new name od DataTableMapping i.e., MayOrders
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            dataGrid1.DataSource = ds.DefaultViewManager;
        }

DataTables are not only mapping things aliased in .NET. You can also alias the DataColumns using DataColumnMapping objects. The DataTableMapping's ColumnMapping property contains DataColumnMappings. You construct a ColumnMapping in much the same way you do a table mapping. Listing 5-53 shows an example of DataColumnMapping. The first order is in a message box using the alias ID :

Listing 5-53. Using DataColumnMapping

private void DataMapping3()
        {
            // Create a connection object
            string ConnectionString = "Integrated Security = SSPI;" +
            "Initial Catalog = Northwind;" + "Data Source = localhost;";
            SqlConnection conn = new SqlConnection(ConnectionString);

            // open the Connection
            conn.Open();

            // Create a DataTableMapping object
            DataTableMapping myMapping = new DataTableMapping("Table", "Orders");
            SqlDataAdapter adapter = new SqlDataAdapter("Select * From Orders", conn);

            // Call DataAdapter's TableMapping.Add method
            adapter.TableMappings.Add(myMapping);
            myMapping.ColumnMappings.Add(new DataColumnMapping("OrderID", "mapID"));

            // Create a DataSet object and call DataAdapter's Fill method
            // Make sure you use new name od DataTableMapping i.e., MayOrders
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            MessageBox.Show(ds.Tables["Orders"].Rows[0]["mapID"].ToString());
            dataGrid1.DataSource = ds.DefaultViewManager;
        }

The framework automatically generates much of the Mappings, so you don't have to worry about them. But, occasionally, you may want to choose your own schema names for your DataSet that map back to the data source.

Conclusion

Hope this article would have helped you in understanding Table and Column Mapping in ADO.NET. See my other articles on the website on ADO.NET.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.