如何将数组传递给存储过程

I have a list ClaimData in C# and it has three items Date, Type and Description There can be multiple rows in this as below,

我在c#中有一个列表ClaimData它有三个项目日期，类型和描述可以有多个行，如下所示，

ClaimData

ClaimData

Date         Type      Description

01/02/2012  "Medical" "Its a medical"
05/02/2013  "Theft"   "Its a Theft"
01/02/2014  "Test"    "Its a Test"

I want to pass this whole data to a stored procedure in one go to the sql server, so that I can reduce the database hits. I have written stored procedure which can iterate through this list and insert them in a table.

我想把整个数据传递给一个存储过程，一次传到sql服务器，这样我就可以减少对数据库的访问。我已经编写了存储过程，它可以遍历这个列表并将它们插入到一个表中。

How to achieve by manipulating the list object could be passed to the stored procedure as a parameter?

如何通过操作列表对象来实现，可以将其传递给存储过程作为参数?

2 个解决方案

#1

You will need to do a couple of things to get this going, since your parameter is getting multiple values you need to create a Table Type and make your store procedure accept a parameter of that type.

您将需要做一些事情来实现这一点，因为您的参数将获得多个值，您需要创建一个表类型并使存储过程接受该类型的参数。

Since you are passing a TABLE as a parameter you will need to create a TABLE TYPE something as follows

由于将表作为参数传递，所以需要创建一个表类型，如下所示

TABLE TYPE

表类型

CREATE TYPE dbo.ClaimData AS TABLE 
 (
    [Date]         DATE
    [Type]         VARCHAR(50)
    [Description]  VARCHAR(100)
  )
 GO

Stored Procedure to Accept That Type Param

接受该类型参数的存储过程

 CREATE PROCEDURE mainValues 
 @TableParam ClaimData READONLY   --<-- Accepts a parameter of that type 
 AS                                  -- Note it is ReadOnly 
 BEGIN
    SET NOCOUNT ON;

  --Temp table to store the passed values 
  -- since the passed parameter is only Read only and you
  -- cannot make any changes to the parameter so if you need to
  -- manipulate the data inside parameter you will need to get it
  -- into a Table vaiable.


  -- Declare a Table variable
  DECLARE @tmp_values table(
          [Date]         DATE
          [Type]         VARCHAR(50)
          [Description]  VARCHAR(100)
                            );   

   --Get values into that Table variable 
   INSERT INTO @tmp_values ([Date],[Type],[Description])
   SELECT [Date],[Type],[Description] FROM @TableParam

   -- Do other cool stuff with your passed data 

   SELECT * FROM @tmp_values  --<-- For testing purpose
END

EXECUTE PROC

执行过程

Declare a variable of that type and populate it with your values.

声明该类型的变量，并用值填充它。

 DECLARE @Table ClaimData(      --<-- Declare a variable of your type
          [Date]         DATE
          [Type]         VARCHAR(50)
          [Description]  VARCHAR(100)
                            ); 
 -- Populate the variable
   INSERT INTO @Table ([Date],[Type],[Description])
   SELECT [Date],[Type],[Description] FROM Source_Table

EXECUTE mainValues @Table   --<-- Stored Procedure Executed

#2

I sent all three column as a string using string builder and delimeter '|'

我用字符串构建器和delimeter '|'将三列作为字符串发送

    DateString = '01/02/2012|05/02/2013|01/02/2014'
    TypeString = 'Medical|Theft|Test'
    DescString = "Its a medical|..."

On database side I used a function to delimit these strings and inserted all these values in a temp table. This solved my problem.

在数据库端，我使用了一个函数来分隔这些字符串，并将所有这些值插入到临时表中。这解决了我的问题。

#1