CODEWITHSUNDEEP
asp.net-mvc-4
swinaz
swinaz

Reputation: 21

Returning multiple recordsets in an mvc4 model when using a stored procedure

I am in the process of learning MVC4.

My question is this. Is it possible for the MVC model to provide multiple recordsets when the data is being queried from a stored procedure?

So let me add some context. In my database I have 2 tables and a stored procedure defined as follows:

CREATE TABLE [dbo].[RnDParent](
[ParentID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_RnDParent] PRIMARY KEY CLUSTERED 
(
[ParentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[RnDChild](
[ChildID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL,
[Label] [varchar](50) NOT NULL,
[Value] [money] NOT NULL,
CONSTRAINT [PK_RnDChild] PRIMARY KEY CLUSTERED 
(
[ChildID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE PROCEDURE Proc_RnDProcedure_GetParentByID 
@ParentID int
AS
BEGIN

SELECT [ParentID]
,[Name]
FROM [PriceCal3].[dbo].[RnDParent]
WHERE [ParentID] = @ParentID

SELECT [ChildID]
,[ParentID]
,[Label]
,[Value]
FROM [PriceCal3].[dbo].[RnDChild]
WHERE [ParentID] = @ParentID

END
GO

I have then created an MVC4 project and used the ADO.Net Entity Data Model control to create the model selecting the stored procedure Proc_RnDProcedure_GetParentByID as the object I want to use.

The connection string has the multipleactiveresultsets key set to True;

When I look at the code for the model for the stored procedure it is defined as follows.

namespace RnDMVC4Project.Models
{
using System;

public partial class Proc_RnDProcedure_GetParentByID_Result
{
public int ParentID { get; set; }
public string Name { get; set; }
}
}

There is no reference to the recordset for the results from the Child table.

Since this class is auto generetated, is it possible to include the records from the child table? If yes what piece of the jigsaw am I missing?

Thank you in advance for any help.

Upvotes: 1

Views: 800

Answers (1)

Ema.H
Ema.H

Reputation: 2878

For have just one object, can you do this ?

SELECT c.[ChildID]
,c.[ParentID]
,c.[Label]
,c.[Value]
,p.[Name]
FROM [PriceCal3].[dbo].[RnDChild] as c
INNER JOIN [PriceCal3].[dbo].[RnDParent] ON c.[ParentID] = p.[ParentID]
WHERE c.[ParentID] = @ParentID

Upvotes: 1

Related Questions