ADO.NET Table and Column Mapping in VB.NET

In this article I will explain about Table and Column Mapping in ADO.NET.
  • 5008

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.

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.