Stored Procedure with Multiple Resultsets

This example shows how to use a stored procedure that executes inside itself a parameterized SELECT query to tables and returns table records that satisfy the query condition as the list of corresponding complex type objects.

The script for creating a stored procedure and a table for the SQL Server DBMS is as follows:

CREATE TABLE dbo.Department (
  DeptNo int NOT NULL,
  DName varchar(14),
  Loc varchar(13),
  PRIMARY KEY (DeptNo)
)
GO
CREATE TABLE dbo.Employee (
  EmployeeID int IDENTITY PRIMARY KEY,
  LastName nvarchar(20) NOT NULL,
  FirstName nvarchar(10) NOT NULL,
  Address nvarchar(60),
  City nvarchar(15),
  Country nvarchar(15),
  DeptNO int NOT NULL,
  FOREIGN KEY (DeptNO) REFERENCES dbo.Department(DeptNO)
)
GO
CREATE PROCEDURE dbo.SelectDeptsAndEmps
AS
SELECT *
FROM dbo.Department;
SELECT *
FROM dbo.Employee;
GO

First, we create a model and drag the required stored procedure from the Database Explorer window to the design area; in the displayed message box we click Yes to obtain metadata of procedure resultsets.

As a result we get 2 complex types with default names SelectDeptsAndEmpResult and SelectDeptsAndEmpResult1 and a method corresponding to the stored procedure. For convenience, we rename the complex types to DeptsResult and EmpsResult and get the following method:

images_SP-with-multi-Resultsets-General-TDA

As a result of code generation for the model, the corresponding method of the model context will be generated having a signature close to the relevant stored procedure:

C#:

        public SelectDeptsAndEmpMultipleResult SelectDeptsAndEmp ()
        {
            SelectDeptsAndEmpMultipleResult result = new SelectDeptsAndEmpMultipleResult();
            OAConnection connection = this.Connection;
            bool needClose = false;
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
                needClose = true;
            }
            try
            {
                using (OACommand cmd = connection.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = @"dbo.SelectDeptsAndEmps";
                    using (OADataReader reader = cmd.ExecuteReader())
                    {
                        result.SelectDeptsAndEmpResults.AddRange(this.Translate<SelectDeptsAndEmpResult>(reader));
                        reader.NextResult();
                        result.SelectDeptsAndEmpResult1s.AddRange(this.Translate<SelectDeptsAndEmpResult1>(reader));
                        reader.NextResult();
                    }
                }
            }
            finally
            {
                if (needClose)
                    connection.Close();
            }
            return result;
        }
    public class SelectDeptsAndEmpMultipleResult
    {
        public SelectDeptsAndEmpMultipleResult()
        {
          SelectDeptsAndEmpResults = new List<SelectDeptsAndEmpResult>();
          SelectDeptsAndEmpResult1s = new List<SelectDeptsAndEmpResult1>();
        }
        public List<SelectDeptsAndEmpResult> SelectDeptsAndEmpResults { get; private set; }
        public List<SelectDeptsAndEmpResult1> SelectDeptsAndEmpResult1s { get; private set; }
    }

If we need to retrieve the whole tables, we do the following: we drag the required stored procedure from the Database Explorer window to the design area; in the displayed message box we click No not to obtain metadata of procedure result set; then we drag the required tables from the Database Explorer window to the design area.

Then we double-click the method and in the Value Types drop-down list select the Department and Employee tables:

images_SP-with-multi-resultsets-General2-TDA

As a result of code generation for the model, the corresponding method of the model context will be generated having a signature close to the relevant stored procedure:

C#:

        public SelectDeptsAndEmpMultipleResult SelectDeptsAndEmp ()
        {
            SelectDeptsAndEmpMultipleResult result = new SelectDeptsAndEmpMultipleResult();
            OAConnection connection = this.Connection;
            bool needClose = false;
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
                needClose = true;
            }
            try
            {
                using (OACommand cmd = connection.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = @"dbo.SelectDeptsAndEmps";
                    using (OADataReader reader = cmd.ExecuteReader())
                    {
                        result.Departments.AddRange(this.Translate<Department>(reader));
                        reader.NextResult();
                        result.Employees.AddRange(this.Translate<Employee>(reader));
                        reader.NextResult();
                    }
                }
            }
            finally
            {
                if (needClose)
                    connection.Close();
            }
            return result;
        }
    public class SelectDeptsAndEmpMultipleResult
    {
        public SelectDeptsAndEmpMultipleResult()
        {
          Departments = new List<Department>();
          Employees = new List<Employee>();
        }
        public List<Department> Departments { get; private set; }
        public List<Employee> Employees { get; private set; }
    }

Complex types and tables can be combined in one method.

Stored Procedure with Multiple Resultsets

See Also