Passing List of Custom object to SQL Server Stored Procedure

Question

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.

Are OGFormResponse and OGFormLabelVO being stored in the same table? Seperate tables? If separate table, how is the relationship defined? Key constraints? Common field? — Brandon
– Brandon, Commented Aug 15, 2014 at 20:49
Look into ADO.NET structured parameters. You need to defined corresponding table types for them in the database. Dapper also supports them in the latest version. — Marcel N.
– Marcel N., Commented Aug 15, 2014 at 20:54
@Brandon they are seperate tables with key constraints, I have added the DDL — Isaac Levin
– Isaac Levin, Commented Aug 15, 2014 at 21:04
@MarcelN. Thanks for the link, I will take a look. Is there any noticeable difference between 4.0 and 4.5? My application is 4.0 — Isaac Levin
– Isaac Levin, Commented Aug 15, 2014 at 21:28

Community · Accepted Answer · 2017-05-23 11:49:31Z

5

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

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Aug 15, 2014 at 22:02

Marcel N.

14k5 gold badges50 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ProgrammingLlama Over a year ago

Exactly what I would have said had I not been on a tablet :-) +1

ProgrammingLlama Over a year ago

To add to this, you can declare the type in c# using an array of SqlMetadata and then build a list of SqlDataRecord from it and pass that using AsEnumerable() on the list (if you don't want to use datatables)

Marcel N. Over a year ago

@john: Yes, I think that's what Dapper uses by default. The other alternative is to use DataTables. Anyway, tried to focus more on the SQL part.

Collectives™ on Stack Overflow

Passing List of Custom object to SQL Server Stored Procedure

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related