How To Fill a Typed DataSet from a Stored Procedure That Returns Multiple Resultsets by Using Visual Basic .NET (322793)

SUMMARY

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"
   						

   NOTE: You must change "CustOrd" to match the name of the .xsd file that you created in the Create the Typed DataSet section.
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