ADO.NET Table and Column Mapping in VB.NET
In this article I will explain about Table and Column Mapping in ADO.NET.
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 Sub DataMapping1()
' create a connection object
Dim ConnectionString As String = "Integrated Security = SSPI;" & " Initial Catalog = Northwind;" & "Data Source = localhost;"
Dim conn As New SqlConnection(ConnectionString)
' open the connection
conn.Open()
' Create a DataTableMapping object
Dim myMapping As New DataTableMapping("Orders", "mapOrders")
Dim adapter As 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
Dim ds As New DataSet()
adapter.Fill(ds, "mapOrders")
dataGrid1.DataSource = ds.DefaultViewManager
End Sub
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 Sub DataMapping2()
' create connection object
Dim ConnectionString As String = "Integrated Security = SSPI;" & "Initial Catalog = Northwind;" & " Data Source = localhost;"
Dim conn As New SqlConnection(ConnectionString)
' open the connection
conn.Open()
' create a DataTableMapping object
Dim myMapping As New DataTableMapping(" Table", " Orders")
Dim adapter As 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
Dim ds As New DataSet()
adapter.Fill(ds)
dataGrid1.DataSource = ds.DefaultViewManager
End Sub
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 Sub DataMapping3()
' Create a connection object
Dim ConnectionString As String = "Integrated Security = SSPI;" & "Initial Catalog = Northwind;" & "Data Source = localhost;"
Dim conn As New SqlConnection(ConnectionString)
' open the Connection
conn.Open()
' Create a DataTableMapping object
Dim myMapping As New DataTableMapping("Table", "Orders")
Dim adapter As 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
Dim ds As New DataSet()
adapter.Fill(ds)
MessageBox.Show(ds.Tables("Orders").Rows(0)("mapID").ToString())
dataGrid1.DataSource = ds.DefaultViewManager
End Sub
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.