Database Access with Visual Basic .NET (3rd Edition)

The DataAdapter object maintains a collection of DataTableMapping objects, which it exposes as the TableMappings property. The purpose of these mappings is to allow the mapping of table names and/or column names from their names in the data source to their names in the DataSet. Of course, once set up, this method works in both directions both when reading from the data source to the DataSet and when writing from the DataSet to the data source.

In our examples featuring use of the Fill method of the DataAdapter, we would specify both the DataSet and the name of the table in the DataSet to be filled with the data being read, such as:

daEmployees.Fill(dsEmployeeInfo, "Employees")

However, the second argument to the Fill method is really the name of a TableMapping. The DataAdapter looks to see if it has a defined mapping with that name and, if it does, it uses the information there to complete the Fill operation. However, if it doesn't have such a mapping, it creates a table with the name of the passed parameter and fills that with the data.

What that means is that, if we add a mapping named MappingName and map it to a table named empDataSetTable with the line

daEmployees.TableMappings.Add("MappingName", "empDataSetTable")

we could then call the Fill method with the line

daEmployees.Fill(dsEmployeeInfo, "MappingName")

Doing so would cause the data being read to be filled into empDataSetTable in the DataSet dsEmployeeInfo.

Once we have defined a table mapping, we can add column mappings to it. This approach is most useful when you want your application code to use column names different from those used in the data source. When using a table mapping to fill a DataSet, the DataAdapter will look for any column mappings for that table mapping and use them to map the column names. Any columns that don't have a column mapping defined will use the data source column names for the names in the DataSet.

For example, did you ever wonder what year it was when the SQL Server sample database pubs was first designed and what the naming limitations were that caused them to come up with such abbreviated names? Column mappings allow loading the pubs database tables into our DataSet but with more readable column names. If your chief DBA and chief software designer insist on conflicting naming conventions, column mappings could make them both (and you) happy.

Let's continue with our table mapping example. In addition to mapping the table, we want to map all the column names to comply with the demand of our chief software designer that all object names begin with a three-letter prefix indicating to whom they belong. Our mapping code would now look like Listing 7.4.

Listing 7.4 Mapping the table and column names

daEmployees.TableMappings.Add("MappingName", _ "empDataSetTable") With daEmployees.TableMappings("MappingName").ColumnMappings .Add("ID", "empEmployeeID") .Add("FirstName", "empFirstName") .Add("LastName", "empLastName") .Add("DepartmentID", "empDepartmentID") .Add("Salary", "empSalary") End With daEmployees.Fill(dsEmployeeInfo, "MappingName")

Previously, in Chapter 6 (Listings 6.1 and 6.2), we wrote a function ReadData that filled a DataSet with data from database table and then displayed the contents of that DataSet in the listbox on frmDataSets. If we have btnDataAdapterFill_Click call a modified version of that function named ReadDataMapped, which contains the code shown in Listing 7.5, we can run the DataSetCode project and see the results of the table and column mappings. These results are shown in Figure 7.1.

Listing 7.5 ReadData modified to use table and column mappings

Private Sub ReadDataMapped() Dim daEmployees As SqlDataAdapter = New _ SqlDataAdapter("select * from tblEmployee", _ "server=localhost;uid=sa;database=novelty") dsEmployeeInfo = New DataSet() 'Configure Table and Column mappings daEmployees.TableMappings.Add("MappingName", "empDataSetTable") With daEmployees.TableMappings("MappingName").ColumnMappings .Add("ID", "empEmployeeID") .Add("FirstName", "empFirstName") .Add("LastName", "empLastName") .Add("DepartmentID", "empDepartmentID") .Add("Salary", "empSalary") End With daEmployees.Fill(dsEmployeeInfo, "MappingName") DisplayDataSet(dsEmployeeInfo) End Sub

Note

The default table mapping is named Table. This mapping is used if only the DataSet name is specified in the call to the Fill (or Update) method. That's why by default a table filled from such a call to Fill will be named Table in the DataSet. However, you can explicitly define a mapping named Table and specify the table name that you want. Therefore the following lines of code

daEmployees.TableMappings.Add("Table", "MyTableName") daEmployees.Fill(dsEmployeeInfo)

will result in the creation and filling of a table named MyTableName.