Passing List of Custom object to SQL Server Stored Procedure

+1 vote
asked Aug 15, 2014 by isaac-levin

I am looking to pass a list of custom objects with just one stored procedure call. Here is the object

 public class OGFormResponse
{ public string Response {get; set;} public OGFormLabelVO FormLabel {get; set;}
} public class OGFormLabelVO
{ public int OGFormLabelKey {get; set;} public string FormType {get; set;} public string LabelText {get; set;} public string ControlName {get; set;} public string DisplayStatus {get; set;} public string LabelType = {get; set;} public bool IsActive {get; set;} public string LabelParentControlName {get; set;}
}

Here is the Database Relationship

CREATE TABLE [dbo].[OGFormLabels](
[OGFormLabelKey] [int] IDENTITY(1,1) NOT NULL,
[OGFLText] [nvarchar](max) NULL,
[OGFLControlName] [nvarchar](50) NOT NULL,
[OGFLIsActive] [bit] NOT NULL,
[OGFLDisplayStatusKey] [int] NOT NULL,
[OGFLFormTypeKey] [int] NOT NULL,
[OGFLLabelTypeKey] [int] NOT NULL,
[OGFLParentKey] [int] NULL,
[OGFLBeginDate] [datetime] NOT NULL,
[OGFLBeginUser] [varchar](40) NOT NULL,
[OGFLUpdateDate] [datetime] NOT NULL,
[OGFLUpdateUser] [varchar](40) NOT NULL,
CONSTRAINT [PK_OGFormLabel] PRIMARY KEY CLUSTERED
(
[OGFormLabelKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[OGFormLabels] ADD CONSTRAINT [DF_OGFormLabel_OGFLBeginDate] DEFAULT (getdate()) FOR [OGFLBeginDate]
GO
ALTER TABLE [dbo].[OGFormLabels] ADD CONSTRAINT [DF_OGFormLabel_OGFLBeginUser] DEFAULT ('dbo') FOR [OGFLBeginUser]
GO
ALTER TABLE [dbo].[OGFormLabels] ADD CONSTRAINT [DF_OGFormLabel_OGFLUpdateDate] DEFAULT (getdate()) FOR [OGFLUpdateDate]
GO
ALTER TABLE [dbo].[OGFormLabels] ADD CONSTRAINT [DF_OGFormLabel_OGFLUpdateUser] DEFAULT ('dbo') FOR [OGFLUpdateUser]
GO
ALTER TABLE [dbo].[OGFormLabels] WITH CHECK ADD CONSTRAINT [FK_OGFormLabel_OGFormStatus] FOREIGN KEY([OGFLFormTypeKey])
REFERENCES [dbo].[OGDisplayStatus] ([OGDisplayStatusKey])
GO
ALTER TABLE [dbo].[OGFormLabels] CHECK CONSTRAINT [FK_OGFormLabel_OGFormStatus]
GO
ALTER TABLE [dbo].[OGFormLabels] WITH CHECK ADD CONSTRAINT [FK_OGFormLabel_OGFormType] FOREIGN KEY([OGFLFormTypeKey])
REFERENCES [dbo].[OGFormType] ([OGFormTypeKey])
GO
ALTER TABLE [dbo].[OGFormLabels] CHECK CONSTRAINT [FK_OGFormLabel_OGFormType]
GO
ALTER TABLE [dbo].[OGFormLabels] WITH CHECK ADD CONSTRAINT [FK_OGFormLabel_OGLabelType] FOREIGN KEY([OGFLLabelTypeKey])
REFERENCES [dbo].[OGLabelType] ([OGLabelTypeKey])
GO
ALTER TABLE [dbo].[OGFormLabels] CHECK CONSTRAINT [FK_OGFormLabel_OGLabelType]
GO
CREATE TABLE [dbo].[OGFormResponses](
[OGFormResponseKey] [int] IDENTITY(1,1) NOT NULL,
[OGRFormKey] [int] NOT NULL,
[OGRFormLabelKey] [int] NOT NULL,
[OGRResponse] [nvarchar](max) NOT NULL,
[OGRBeginDate] [datetime] NOT NULL,
[OGRBeginUser] [varchar](40) NOT NULL,
[OGRUpdateDate] [datetime] NOT NULL,
[OGRUpdateUser] [varchar](40) NOT NULL,
CONSTRAINT [PK_OGFormResponse] PRIMARY KEY CLUSTERED
(
[OGFormResponseKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[OGFormResponses] ADD CONSTRAINT [DF_OGFormResponse_OGRBeginDate] DEFAULT (getdate()) FOR [OGRBeginDate]
GO
ALTER TABLE [dbo].[OGFormResponses] ADD CONSTRAINT [DF_OGFormResponse_OGRBeginUser] DEFAULT ('dbo') FOR [OGRBeginUser]
GO
ALTER TABLE [dbo].[OGFormResponses] ADD CONSTRAINT [DF_OGFormResponse_OGRUpdateDate] DEFAULT (getdate()) FOR [OGRUpdateDate]
GO
ALTER TABLE [dbo].[OGFormResponses] ADD CONSTRAINT [DF_OGFormResponse_OGRUpdateUser] DEFAULT ('dbo') FOR [OGRUpdateUser]
GO
ALTER TABLE [dbo].[OGFormResponses] WITH CHECK ADD CONSTRAINT [FK_OGFormResponse_OGForm] FOREIGN KEY([OGRFormKey])
REFERENCES [dbo].[OGForm] ([OGFormKey])
GO
ALTER TABLE [dbo].[OGFormResponses] CHECK CONSTRAINT [FK_OGFormResponse_OGForm]
GO
ALTER TABLE [dbo].[OGFormResponses] WITH CHECK ADD CONSTRAINT [FK_OGFormResponse_OGFormLabel] FOREIGN KEY([OGRFormLabelKey])
REFERENCES [dbo].[OGFormLabels] ([OGFormLabelKey])
GO
ALTER TABLE [dbo].[OGFormResponses] CHECK CONSTRAINT [FK_OGFormResponse_OGFormLabel]
GO

So basically, OGFormResponseVO has a 1 to 1 relationship with OGFormLabelVO. I want to be able to insert a list of OGFormResponseVO into a database via a Stored Procedure call. I have looked into table valued parameters and you cannot have a Column of the Type be another type. Is there a workaround for this, or am I better off just passing all the properties of the child object as separate parameters, or is there a better way. I have to use an SP as it is part of a larger project, so other Data Model options aren't available.

1 Answer

+2 votes
answered Nov 8 by marcel-n

As I said in the comments, you can do this with structured parameters.

You just need to re-model a bit so you can map these to table-valued parameters (which start off as DataTables).

Assuming that you want to insert both form labels and associated responses in the same go, you need to define a temporary relationship between them. Also, noticed that the tables have more columns than the models, so I think you downsized the initial example.

The structured parameter corresponding to class OGFormResponse needs to have the following fields:

CREATE TYPE [dbo].[OGFormResponse] AS TABLE( [Response] VARCHAR(256), [SequenceId] INT --This is just a temporary sequence (1..N) you can use to map to form labels (see below)
)

The table-valued type for OGFormLabelVO can be mapped 1:1 to the C# class, plus one extra column - the SequenceId.

The SP could look something like this:

CREATE PROCEDURE [dbo].[SaveFormStuff] @FormResponses AS [dbo].[OGFormResponse] READONLY, @FormLabels AS [dbo].[OGFormLabelVO] READONLY
AS SET NOCOUNT ON; //This stores the PKs of the inserted form labels DECLARE @InsertedFormLabels AS TABLE ( Id INT NOT NULL, SequenceId INT NOT NULL ) INSERT INTO [dbo].[OGFormLabels] (...) SELECT (...) FROM @FormLabels FL OUTPUT inserted.OGFormLabelKey, FL.SequenceId INTO @InsertedFormLabels -- Now you have the newly inserted form label ID mapped to sequence IDs -- Time to insert responses INSERT INTO [dbo].[OGFormResponses] (...) SELECT (...), OGRFormLabelKey = IFL.Id FROM @FormResponses FR INNER JOIN @InsertedFormLabels IFL ON IFL.SequenceId = FR.SequenceId
END
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
...