Walkthrough: Mapping Data Source Tables to Dataset Tables
The structure of tables in a dataset does not always match the structure of tables in a data source. There might be several reasons for this:
- The dataset was created from an existing schema that uses different names than those in the data source.
- You want to change data element names in the dataset for convenience, readability, translation to or from a foreign language, or other reason.
- You want to control the names of typed data members when you generate a dataset from the adapter.
To enable you to match up tables and columns in the data source with those in the dataset, you can set the TableMappings property of a data adapter. This property contains information that establishes a correspondence between data source table columns and dataset table columns. When the adapter reads data from the data source, TableMappings property is used to determine where in the dataset to write the data.
This walkthrough gives you brief overview of how to set up table mappings in a data adapter. The scenario for the walkthrough assumes that you are working with a schema provided to you by someone else. You will map the columns from a table in your database (in this case, the Customers table of the Northwind database) to the columns defined in the schema.
In order to complete this walkthrough, you will need:
- Access to a server with the Northwind SQL Server sample database.
The walkthrough is split into a number of smaller pieces:
- Creating a Windows Form. The form is used only to illustrate how to create the mapping; you could use any form or component.
- Creating a dataset with a pre-defined schema. The walkthrough contains a simple but complete schema that you can copy and paste into Visual Studio to simulate a schema you might have gotten from someone else.
- Creating and configuring the data adapter in which you will map the data source to the dataset.
- Adding the DataGrid control to show the effect of the mapping.
Creating the Project and Form
This walkthrough uses a Windows Form as the container on which you will create the data adapter and dataset. It is not necessary that you use a Windows Form — you could also use a Web Forms page or a component — but a Windows Form is easy to work with and allows you later to use a DataGrid control to see the effect of the mappings.
To create the project and form
- From the File menu, point to New, and then choose Project.
- In the Project Types pane, choose Visual Basic Projects or Visual C# Projects, and then in the Templates pane, choose Windows Application.
- Assign a name to the project that will be unique and will conform to the naming conventions you use. For example, you might name this project Walkthrough_Mappings. After you have assigned a name, click OK.
Visual Studio creates a new project and displays a new form in the Windows Form Designer.
Creating the Schema and Dataset
For the walkthrough, imagine that someone has provided you with a schema that defines a Customers table. The schema defines a Customers table that is slightly different from the one in your Northwind database.
In this section, you will add a schema in your project. The actual schema layout (the XML that defines the schema) is provided below. By default, the XML Designer generates a class file based on the schema and derived from the DataSet class. The resulting class file defines the typed dataset class that you use in your application.
For more information about dataset and how they are defined, see Introduction to Datasets.
To add the schema
- In Solution Explorer, right-click the name of your project, choose Add, and then choose Add New Item.
- In the Templates pane, choose DataSet, name the new item dsCustomers, and then click Open.
The XML Designer opens with a new, blank dataset schema.
- Click the XML tab at the bottom.
- Copy the following schema text and then paste it into the XML view of the designer, completely overwriting the schema already in the file.
Tip If the < and > symbols are replaced with<and>when you paste, remove the pasted text, and then re-paste the schema by right clicking the design surface and selecting Paste as HTML from the shortcut menu.
<?xml version="1.0" encoding="utf-8" ?> <xs:schema id="dsCustomers" targetNamespace="http://www.tempuri.org/dsCustomers.xsd" xmlns="http://www.tempuri.org/dsCustomers.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" elementFormDefault="qualified"> <xs:element name="dsCustomers" msdata:IsDataSet="true"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="Customers"> <xs:complexType> <xs:sequence> <xs:element name="CustID" type="xs:string" /> <xs:element name="CompName" type="xs:string" /> <xs:element name="ContName" type="xs:string" minOccurs="0" /> <xs:element name="ContTitle" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> <xs:key name="Constraint1" msdata:PrimaryKey="true"> <xs:selector xpath=".//Customers" /> <xs:field xpath="CustID" /> </xs:key> </xs:element> </xs:schema>
- Save the schema and close it.
- Check that you have a dataset class file based on the schema. In Solution Explorer, click the Show All Files button, and then expand the node for the new schema. You should see a class file (with extension of .vb or .cs) underneath the schema.
Now that you have a schema and related dataset class file, you can add an instance of the dataset to your form.
To add the dataset to the form
- Click the Form1 tab at the top to return to the Windows Form designer and your form.
- From the Data tab of the Toolbox, drag the DataSet element onto the form.
The Add Dataset dialog box is displayed.
- Select Typed Dataset and then from the Name list, choose the dataset named dsCustomers. The dataset name is prefixed with your project name.
An instance of the dataset is added to the form under the name dsCustomers1.
Creating a Data Adapter and Table Mapping
With the dataset defined, you can configure the other half of your data access story: the data adapter that will read data from the database and fill it into the dataset. The data adapter will contain the table mapping that matches the database columns to those in the dataset.
Adding the Data Adapter
The first step is to create the data adapter, which you can do using a wizard.
To add a data adapter
- From the Data tab of the Toolbox, drag the OleDbDataAdapter element onto the form.
The Data Adapter Configuration Wizard dialog box is launched.
- In the wizard, do the following:
- In the second pane, create or choose a connection pointing to the SQL Server Northwind database.
Security Note Storing connection-string details (such as the server name, user name, and password) can have implications for the security of your application. Using Windows Integrated Security is a more secure way to control access to a database. For more information, see Database Security.
- In the third pane, specify that you want to use an SQL statement to access the database.
- In the fourth pane, create the following SQL statement:
SELECT CustomerID, CompanyName, ContactName, ContactTitle FROM Customers
For assistance building the SQL statement, click Query Builder to launch the Query Builder.Note In this walkthrough, you will populate the dataset with all the rows from the Customers table. In production applications, you typically optimize data access by creating a query that returns only the columns and rows you need. For an example, see Walkthrough: Displaying Data in a Windows Form Using a Parameterized Query.
When the wizard is complete, you will have a connection (OleDbConnection1) containing information about how to access your database. You will also have a data adapter (OleDbDataAdapter1) that contains a query defining what table and columns in the database you want to access.
- In the second pane, create or choose a connection pointing to the SQL Server Northwind database.
Creating the Table Mapping
Now that you have a data adapter on the form, you can configure its table mappings.
To create the mappings
- Select the data adapter at the bottom of the form.
- In the Properties window, click the button in the TableMappings property.
The Table Mapping dialog box is displayed.
- Check Use a dataset to suggest table and column names and select Walkthrough_Mappings.dsCustomers from the drop-down list. (If you named your project something else, the prefix will be different in this list.)
- Select Customers from the Dataset table list.
- Change the names of columns in the Dataset Columns list individually by using the drop-down list that is available for each column. Create the following mappings:
Source Columns Dataset Columns CustomerID CustID CompanyName CompName ContactName ContName ContactTitle ContTitle
When you have finished, the Mappings dialog box will look like this:
- Click OK to close the Table Mappings dialog box.
Using Controls to Illustrate Mapped Names
In the final step of the walkthrough, you add a DataGrid control that illustrates how the mappings are used in the dataset. When you bind the grid to the dataset table, you will see that the dataset table contains the mapped names.
To add and bind a DataGrid control
- Add a DataGrid from the Windows Forms tab of the Toolbox.
- Select the DataGrid and open the Properties window.
- Set the following data-binding properties:
- Set the DataSource property to dsCustomers1.
- Set the DataMember property to Customers.
The column headings will match those in the schema.
To be able to run the form and see the mapped data in use, you will need to fill the dataset.
To fill the dataset
- Double-click the form (not the control) to create an event handler for the form's Load event and add the following line to it:
'Visual Basic OleDbDataAdapter1.Fill(dsCustomers1) // C# oleDbDataAdapter1.Fill(dsCustomers1);
Finally, you can test the form to see the mapping in action.
To test the form
- Press F5 to run the form.
- When the form is displayed, confirm that the data from the Northwind Customers table is displayed in the grid using the mapped column names.
This walkthrough has provided a brief introduction to table mapping in data adapters. Mapping can be used not just to change column names, but in more sophisticated ways as well, including:
- Map columns from one table to an entirely different column in the dataset.
- Use mapping to limit the number of columns created in a dataset. This is useful if your data adapter reads all the columns in a database table but you only need a few of them in a dataset table.
- Map an expression column in a dataset to a database column. This allows you to calculate values in the dataset and write them to the database.
- Raise errors under various circumstances, including mismatches.
For more information, see Table Mapping in Data Adapters.