将自定义对象列表传递给SQL Server存储过程

本文关键字：SQL Server 存储过程 自定义 对象 列表 | 更新日期: 2023-09-27 18:18:17

我希望通过一个存储过程调用传递一个自定义对象列表。这里是对象

 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;}
}

这里是数据库关系

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

基本上，OGFormResponseVO与OGFormLabelVO有1对1的关系。我希望能够通过存储过程调用将OGFormResponseVO列表插入到数据库中。我已经看了表值参数，你不能有类型的列是另一种类型。是否有解决这个问题的方法，或者我最好只是将子对象的所有属性作为单独的参数传递，或者有更好的方法。我必须使用SP，因为它是一个更大项目的一部分，所以其他数据模型选项不可用。

将自定义对象列表传递给SQL Server存储过程

正如我在评论中所说的，您可以使用结构化参数来实现这一点。

您只需要重新建模一点，这样您就可以将这些映射到表值参数(开始为DataTable s)。

假设您希望同时插入表单标签和相关的响应，您需要定义它们之间的临时关系。另外，注意到表的列比模型的列多，所以我认为您缩小了最初的示例。

OGFormResponse类对应的结构化参数需要有以下字段:

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)
)

OGFormLabelVO的表值类型可以1:1映射到c#类，加上一个额外的列—SequenceId。

SP可以是这样的:

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
相关文章：