The step-by-step article demonstrates how to use the
DataAdapter TableMappings collection to map the generic
DataTable object names to the schema that is already defined in the typed
DataSet object.
When you use a
DataAdapter object to fill a
DataSet from a stored procedure that returns multiple resultsets, the
DataAdapter creates
DataTable objects named Table, Table1, Table2, and so on in the
DataSet. To use more meaningful names, you can rename the
DataTable objects. However, you cannot use this solution with a typed
DataSet.
Back to the top
Create the Stored Procedure
The stored procedure in this example uses data from the Microsoft SQL Server
Northwind sample database. The stored procedure selects all of the records from the
Customers table and then selects all of the records from the
Orders table.
| 1. | Start Microsoft Visual Studio .NET. |
| 2. | On the View menu, click Server Explorer. |
| 3. | In Server Explorer, connect to your SQL Server Northwind database. |
| 4. | In the tree view, right-click the Stored Procedure node under your database connection, and then click New Stored Procedure. |
| 5. | Add the following code to the stored procedure:
CREATE PROCEDURE dbo.sp_GetCustOrd
AS
SELECT * FROM Customers;
SELECT * FROM Orders;
|
| 6. | On the File menu, click Save to save the stored procedure. |
Back to the top
Create the Typed DataSet
Although you can create the typed
DataSet schema manually by adding a new
DataSet object to the project (on the
Project menu, click
Add New Item, and then click
DataSet), this example uses Server Explorer to build the
DataSet schema automatically.
| 1. | In Visual Studio .NET, on the File menu, point to New, and then click Project. |
| 2. | Click Visual Basic Projects under Project Types, and then click Windows Application under Templates. |
| 3. | On the View menu, click Server Explorer. |
| 4. | In Server Explorer, click Connect to Database, and then connect to your SQL Server Northwind database. |
| 5. | Drag the Customers and the Orders tables from Server Explorer to the current project. Notice that a SqlConnection object and two SqlDataAdapter objects are added to the project. |
| 6. | In the Properties window, click Generate Dataset, and then add both tables to the DataSet. Notice that an .xsd file is added to the project. The .xsd file is named according to the name that you chose for the DataSet class. |
Back to the top
Add the DataRelation
| 1. | In Solution Explorer, double-click the .xsd file that you created in the previous section. |
| 2. | Right-click the Customers table in the designer, point to Add, and then click New Relation. |
| 3. | In the Edit Relation dialog box, select the Orders table as the child element, and then click OK. This creates a new DataRelation named CustomersOrders in the DataSet schema. |
| 4. | On the File menu, click Save to save the changes. |
Back to the top
Fill the Typed DataSet
This section adds code to fill the typed
DataSet and to map the table names. This section also uses the
DataGrid control to display the filled
DataSet.
| 1. | Drag a DataGrid control from the toolbox to the form. |
| 2. | Double-click the form, and then add the following code to the Load event of the form:
Dim da As New SqlDataAdapter("sp_GetCustOrd", SqlConnection1)
da.SelectCommand.CommandType = CommandType.StoredProcedure
da.TableMappings.Add("Table", "Customers")
da.TableMappings.Add("Table1", "Orders")
Dim ds As New CustOrd() ' Change this name to match .xsd file name.
da.Fill(ds)
DataGrid1.DataSource = ds
DataGrid1.DataMember = "Customers"
|
| 3. | Add the following namespace reference at the top of the code window:
Imports System.Data.SqlClient
|
| 4. | On the File menu, click Save to save the changes. |
| 5. | Press F5 to run the application. |
Back to the top
Troubleshooting
| • | You cannot have multistatement stored procedures when you use Microsoft Jet databases. |
| • | Stored procedure syntax may vary with relational database systems other than Microsoft SQL Server. |
| • | The SqlClient .NET Data Provider only works with Microsoft SQL Server 7.0 and later. For any other relational database system, use the OleDb .NET Data Provider or other suitable provider, and then include the appropriate connection information. |
| • | Typically, the System and the System.Data namespaces are imported at a project level for Visual Basic projects. If you are compiling from the command prompt, or if you have changed your project settings, you may have to explicitly add Imports statements for these namespaces at the top of the code window. |
Back to the top