This tip explains how to populate a Typed Dataset from a Stored Procedure, which returns multiple result sets.

We need to use the DataAdapter TableMappings collection to map the generic tables to the schema, which is defined in the typed Dataset. When the Data Adapter is used to fill a dataset, the dataset is filled with tables named as Table, Table1, Table2 and so on. So typed Datasets can’t be populated normally.

The Stored Procedure

CREATE PROCEDURE getData

AS

   SELECT * FROM Employees;

   SELECT * FROM Departments;

Shown above is the Stored Procedure, which will be used to populate two tables in the Typed Dataset given below.

1. Employees
2. Departments
   
   

The Typed Dataset

Create a Typed Dataset by dragging and dropping the two tables from the Server Explorer in Design Mode.

Code

The following piece of code will help us to populate a typed Dataset directly with the Fill method of the Data Adapter.

Dim sStr as String = “Your DB Connection String”
Dim oConn as New SqlConnection(sStr)

Dim oDA as New SqlDataAdapter("getData", oConn)

oDA.SelectCommand.CommandType = CommandType.StoredProcedure

oDA.TableMappings.Add("Table", " Employees")

oDA.TableMappings.Add("Table1", " Departments")

 Dim oDs As New EmpDept()       

oDA.Fill(oDs)

Hope this is useful. Happy Coding!!!!