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

Article translations Article translations
Article ID: 322793 - View products that this article applies to.
This article was previously published under Q322793
Expand all | Collapse all

On This Page

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.

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.

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.

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.

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.

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.

Properties

Article ID: 322793 - Last Review: August 30, 2004 - Revision: 2.3
APPLIES TO
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET 1.1
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
Keywords: 
kbdataadapter kbhowto kbsqlclient kbsystemdata KB322793

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com