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. |