C# 如何将数组传递给 SQL Server 存储过程

Question

提问by Sergey

How to pass an array into a SQL Server stored procedure?

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

For example, I have a list of employees. I want to use this list as a table and join it with another table. But the list of employees should be passed as parameter from C#.

例如，我有一个员工列表。我想将此列表用作表并将其与另一个表连接。但是员工列表应该作为参数从 C# 传递。

Answer 1

采纳答案by Aaron Bertrand

SQL Server 2008 (or newer)

SQL Server 2008（或更新版本）

First, in your database, create the following two objects:

首先，在您的数据库中，创建以下两个对象：

CREATE TYPE dbo.IDList
AS TABLE
(
  ID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ID FROM @List; 
END
GO

Now in your C# code:

现在在你的 C# 代码中：

// Obtain your list of ids to send, this is just an example call to a helper utility function
int[] employeeIds = GetEmployeeIds();

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));

// populate DataTable from your List here
foreach(var id in employeeIds)
    tvp.Rows.Add(id);

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    // these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
    tvparam.SqlDbType = SqlDbType.Structured;
    tvparam.TypeName = "dbo.IDList";
    // execute query, consume results, etc. here
}

SQL Server 2005

If you are using SQL Server 2005, I would still recommend a split function over XML. First, create a function:

如果您使用的是 SQL Server 2005，我仍然建议使用基于 XML 的拆分功能。首先，创建一个函数：

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );
GO

Now your stored procedure can just be:

现在您的存储过程可以是：

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); 
END
GO

And in your C# code you just have to pass the list as '1,2,3,12'...

在您的 C# 代码中，您只需将列表作为'1,2,3,12'...

I find the method of passing through table valued parameters simplifies the maintainability of a solution that uses it and often has increased performance compared to other implementations including XML and string splitting.

我发现传递表值参数的方法简化了使用它的解决方案的可维护性，并且与其他实现（包括 XML 和字符串拆分）相比，通常提高了性能。

The inputs are clearly defined (no one has to guess if the delimiter is a comma or a semi-colon) and we do not have dependencies on other processing functions that are not obvious without inspecting the code for the stored procedure.

输入是明确定义的（没有人必须猜测分隔符是逗号还是分号），并且我们不依赖其他不明显的处理函数，而无需检查存储过程的代码。

Compared to solutions involving user defined XML schema instead of UDTs, this involves a similar number of steps but in my experience is far simpler code to manage, maintain and read.

与涉及用户定义的 XML 模式而不是 UDT 的解决方案相比，这涉及相似数量的步骤，但根据我的经验，管理、维护和读取代码要简单得多。

In many solutions you may only need one or a few of these UDTs (User defined Types) that you re-use for many stored procedures. As with this example, the common requirement is to pass through a list of ID pointers, the function name describes what context those Ids should represent, the type name should be generic.

在许多解决方案中，您可能只需要这些 UDT（用户定义类型）中的一个或几个，这些 UDT（用户定义的类型）可以重用于许多存储过程。对于这个例子，常见的要求是传递一个 ID 指针列表，函数名称描述这些 ID 应该代表什么上下文，类型名称应该是通用的。

Answer 2

回答by Fedor Hajdu

You need to pass it as an XML parameter.

您需要将其作为 XML 参数传递。

Edit:quick code from my project to give you an idea:

编辑：我的项目中的快速代码给你一个想法：

CREATE PROCEDURE [dbo].[GetArrivalsReport]
    @DateTimeFrom AS DATETIME,
    @DateTimeTo AS DATETIME,
    @HostIds AS XML(xsdArrayOfULong)
AS
BEGIN
    DECLARE @hosts TABLE (HostId BIGINT)

    INSERT INTO @hosts
        SELECT arrayOfUlong.HostId.value('.','bigint') data
        FROM @HostIds.nodes('/arrayOfUlong/u') as arrayOfUlong(HostId)

Then you can use the temp table to join with your tables. We defined arrayOfUlong as a built in XML schema to maintain data integrity, but you don't have to do that. I'd recommend using it so here's a quick code for to make sure you always get an XML with longs.

然后您可以使用临时表来连接您的表。我们将 arrayOfUlong 定义为内置的 XML 模式以维护数据完整性，但您不必这样做。我建议使用它，所以这里有一个快速代码，以确保您始终获得带有 long 的 XML。

IF NOT EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'xsdArrayOfULong')
BEGIN
    CREATE XML SCHEMA COLLECTION [dbo].[xsdArrayOfULong]
    AS N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="arrayOfUlong">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded"
                            name="u"
                            type="xs:unsignedLong" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';
END
GO

Answer 3

回答by Levi W

Use a table-valued parameter for your stored procedure.

对存储过程使用表值参数。

When you pass it in from C# you'll add the parameter with the data type of SqlDb.Structured.

当您从 C# 传入它时，您将添加数据类型为 SqlDb.Structured 的参数。

See here: http://msdn.microsoft.com/en-us/library/bb675163.aspx

请参阅此处：http: //msdn.microsoft.com/en-us/library/bb675163.aspx

Example:

例子：

// Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();
}

Answer 4

回答by praveen

There is no support for array in sql server but there are several ways by which you can pass collection to a stored proc .

sql server 中不支持数组，但有几种方法可以将集合传递给存储过程。

By using datatable
By using XML.Try converting your collection in an xml format and then pass it as an input to a stored procedure

通过使用数据表
通过使用 XML。尝试以 xml 格式转换您的集合，然后将其作为输入传递给存储过程

The below link may help you

下面的链接可能对你有帮助

passing collection to a stored procedure

将集合传递给存储过程

Answer 5

回答by eselk

It took me a long time to figure this out, so in case anyone needs it...

我花了很长时间才弄清楚这一点，所以万一有人需要它......

This is based on the SQL 2005 method in Aaron's answer, and using his SplitInts function (I just removed the delim param since I'll always use commas). I'm using SQL 2008 but I wanted something that works with typed datasets (XSD, TableAdapters) and I know string params work with those.

这是基于 Aaron 回答中的 SQL 2005 方法，并使用他的 SplitInts 函数（我刚刚删除了 delim 参数，因为我将始终使用逗号）。我正在使用 SQL 2008，但我想要一些适用于类型化数据集（XSD、TableAdapters）的东西，我知道字符串参数适用于这些。

I was trying to get his function to work in a "where in (1,2,3)" type clause, and having no luck the straight-forward way. So I created a temp table first, and then did an inner join instead of the "where in". Here is my example usage, in my case I wanted to get a list of recipes that don't contain certain ingredients:

我试图让他的函数在“where in (1,2,3)”类型子句中工作，并且没有直接的方法。所以我首先创建了一个临时表，然后做了一个内部连接而不是“在哪里”。这是我的示例用法，就我而言，我想获取不包含某些成分的食谱列表：

CREATE PROCEDURE dbo.SOExample1
    (
    @excludeIngredientsString varchar(MAX) = ''
    )
AS
    /* Convert string to table of ints */
    DECLARE @excludeIngredients TABLE (ID int)
    insert into @excludeIngredients
    select ID = Item from dbo.SplitInts(@excludeIngredientsString)

    /* Select recipies that don't contain any ingredients in our excluded table */
   SELECT        r.Name, r.Slug
FROM            Recipes AS r LEFT OUTER JOIN
                         RecipeIngredients as ri inner join
                         @excludeIngredients as ei on ri.IngredientID = ei.ID
                         ON r.ID = ri.RecipeID
WHERE        (ri.RecipeID IS NULL)

Answer 6

回答by Adam

I've been searching through all the examples and answers of how to pass any array to sql server without the hassle of creating new Table type,till i found this linK, below is how I applied it to my project:

我一直在搜索有关如何将任何数组传递给 sql server 而无需创建新表类型的麻烦的所有示例和答案，直到我找到了这个链接，下面是我如何将它应用于我的项目：

--The following code is going to get an Array as Parameter and insert the values of that --array into another table

--以下代码将获取一个数组作为参数并将该 --array 的值插入另一个表中

Create Procedure Proc1 


@UserId int, //just an Id param
@s nvarchar(max)  //this is the array your going to pass from C# code to your Sproc

AS

    declare @xml xml

    set @xml = N'<root><r>' + replace(@s,',','</r><r>') + '</r></root>'

    Insert into UserRole (UserID,RoleID)
    select 
       @UserId [UserId], t.value('.','varchar(max)') as [RoleId]


    from @xml.nodes('//root/r') as a(t)
END

Hope you enjoy it

希望你喜欢它

Answer 7

回答by Solomon Rutzky

Context is always important, such as the sizeand complexityof the array. For small to mid-size lists, several of the answers posted here are just fine, though some clarifications should be made:

上下文始终很重要，例如数组的大小和复杂性。对于中小型列表，这里发布的几个答案都很好，但应该做出一些澄清：

For splitting a delimited list, a SQLCLR-based splitter is the fastest. There are numerous examples around if you want to write your own, or you can just download the free SQL#library of CLR functions (which I wrote, but the String_Split function, and many others, are completely free).
Splitting XML-based arrays canbe fast, but you need to use attribute-based XML, not element-based XML (which is the only type shown in the answers here, though @AaronBertrand's XML example is the best as his code is using the text()XML function. For more info (i.e. performance analysis) on using XML to split lists, check out "Using XML to pass lists as parameters in SQL Server"by Phil Factor.
Using TVPs is great (assuming you are using at least SQL Server 2008, or newer) as the data is streamed to the proc and shows up pre-parsed and strongly-typed as a table variable. HOWEVER, in most cases, storing all of the data in DataTablemeans duplicating the data in memory as it is copied from the original collection. Hence using the DataTablemethod of passing in TVPs does not work well for larger sets of data (i.e. does not scale well).
XML, unlike simple delimited lists of Ints or Strings, can handle more than one-dimensional arrays, just like TVPs. But also just like the DataTableTVP method, XML does not scale well as it more than doubles the datasize in memory as it needs to additionally account for the overhead of the XML document.

对于拆分分隔列表，基于 SQLCLR 的拆分器是最快的。如果您想自己编写，有很多示例，或者您可以下载免费的SQL#CLR 函数库（这是我编写的，但 String_Split 函数和许多其他函数是完全免费的）。
拆分基于 XML 的数组可能很快，但您需要使用基于属性的 XML，而不是基于元素的 XML（这是此处答案中显示的唯一类型，尽管 @AaronBertrand 的 XML 示例是最好的，因为他的代码使用的是text()XML 函数。有关使用 XML 拆分列表的更多信息（即性能分析），请查看Phil Factor 的“使用 XML 在 SQL Server 中将列表作为参数传递”。
使用 TVP 非常棒（假设您至少使用 SQL Server 2008 或更新版本），因为数据会流式传输到 proc 并显示为预解析和强类型的表变量。然而，在大多数情况下，将所有数据存储在DataTable意味着将数据复制到内存中，因为它是从原始集合中复制的。因此，使用DataTable传入 TVP的方法不适用于较大的数据集（即不能很好地扩展）。
与简单的整数或字符串分隔列表不同，XML 可以处理多于一维的数组，就像 TVP 一样。但与DataTableTVP 方法一样，XML 的伸缩性不佳，因为它使内存中的数据大小增加了一倍以上，因为它需要额外考虑 XML 文档的开销。

With all of that said, IF the data you are using is large or is not very large yet but consistently growing, then the IEnumerableTVP method is the best choice as it streams the data to SQL Server (like the DataTablemethod), BUT doesn't require any duplication of the collection in memory (unlike any of the other methods). I posted an example of the SQL and C# code in this answer:

综上所述，如果您使用的数据很大或不是很大但一直在增长，那么IEnumerableTVP 方法是最佳选择，因为它将数据流式传输到 SQL Server（如该DataTable方法），但不会需要在内存中复制集合（与任何其他方法不同）。我在此答案中发布了 SQL 和 C# 代码示例：

Pass Dictionary to Stored Procedure T-SQL

将字典传递给存储过程 T-SQL

Answer 8

回答by Hamed Nazaktabar

Based on my experience, by creating a delimited expression from the employeeIDs, there is a tricky and nice solution for this problem. You should only create an string expression like ';123;434;365;'in-which 123, 434and 365are some employeeIDs. By calling the below procedure and passing this expression to it, you can fetch your desired records. Easily you can join the "another table" into this query. This solution is suitable in all versions of SQL server. Also, in comparison with using table variable or temp table, it is very faster and optimized solution.

根据我的经验，通过从employeeID 创建一个分隔表达式，这个问题有一个棘手而好的解决方案。您应该只创建像一个字符串表达式';123;434;365;'中，其中123，434和365一些employeeIDs。通过调用以下过程并将此表达式传递给它，您可以获取所需的记录。您可以轻松地将“另一个表”加入此查询中。此解决方案适用于所有版本的 SQL Server。此外，与使用表变量或临时表相比，它是非常快速和优化的解决方案。

CREATE PROCEDURE dbo.DoSomethingOnSomeEmployees  @List AS varchar(max)
AS
BEGIN
  SELECT EmployeeID 
  FROM EmployeesTable
  -- inner join AnotherTable on ...
  where @List like '%;'+cast(employeeID as varchar(20))+';%'
END
GO

Answer 9

回答by Charan Ghate

This will help you. :) Follow the next steps,

这会帮助你。:) 按照以下步骤操作，

Open the Query Designer

Copy Paste the Following code as it is,it will create the Function which convert the String to Int

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );
GO

Create the Following stored procedure

 CREATE PROCEDURE dbo.sp_DeleteMultipleId
 @List VARCHAR(MAX)
 AS
 BEGIN
      SET NOCOUNT ON;
      DELETE FROM TableName WHERE Id IN( SELECT Id = Item FROM dbo.SplitInts(@List, ',')); 
 END
 GO

Execute this SP Using exec sp_DeleteId '1,2,3,12'this is a string of Id's which you want to delete,

You convert your array to string in C# and pass it as a Stored Procedure parameter

int[] intarray = { 1, 2, 3, 4, 5 };  
string[] result = intarray.Select(x=>x.ToString()).ToArray();

?

SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "sp_DeleteMultipleId";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Id",SqlDbType.VARCHAR).Value=result ;

打开查询设计器

按原样复制粘贴以下代码，它将创建将字符串转换为 Int 的函数

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );
GO

创建以下存储过程

 CREATE PROCEDURE dbo.sp_DeleteMultipleId
 @List VARCHAR(MAX)
 AS
 BEGIN
      SET NOCOUNT ON;
      DELETE FROM TableName WHERE Id IN( SELECT Id = Item FROM dbo.SplitInts(@List, ',')); 
 END
 GO

执行此 SP 使用exec sp_DeleteId '1,2,3,12'这是要删除的 Id 字符串，

您在 C# 中将数组转换为字符串并将其作为存储过程参数传递

int[] intarray = { 1, 2, 3, 4, 5 };  
string[] result = intarray.Select(x=>x.ToString()).ToArray();

?

SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "sp_DeleteMultipleId";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Id",SqlDbType.VARCHAR).Value=result ;

This will delete multiple rows, All the best

这将删除多行，一切顺利

Answer 10

回答by Patrick Chu

As others have noted above, one way to do this is to convert your array to a string and then split the string inside SQL Server.

正如上面其他人所指出的，一种方法是将数组转换为字符串，然后在 SQL Server 中拆分字符串。

As of SQL Server 2016, there's a built-in way to split strings called

从 SQL Server 2016 开始，有一种内置的方法来拆分字符串，称为

STRING_SPLIT()

It returns a set of rows that you can insert into your temp table (or real table).

它返回一组可以插入到临时表（或真实表）中的行。

DECLARE @str varchar(200)
SET @str = "123;456;789;246;22;33;44;55;66"
SELECT value FROM STRING_SPLIT(@str, ';')

would yield:

会产生：

If you want to get fancier:

如果你想变得更狂热：

DECLARE @tt TABLE (
    thenumber int
)
DECLARE @str varchar(200)
SET @str = "123;456;789;246;22;33;44;55;66"

INSERT INTO @tt
SELECT value FROM STRING_SPLIT(@str, ';')

SELECT * FROM @tt
ORDER BY thenumber

would give you the same results as above (except the column name is "thenumber"), but sorted. You can use the table variable like any other table, so you can easily join it with other tables in the DB if you want.

会给你与上面相同的结果（除了列名是“thenumber”），但排序。您可以像使用任何其他表一样使用表变量，因此您可以根据需要轻松地将它与数据库中的其他表连接起来。

Note that your SQL Server install has to be at compatibility level 130 or higher in order for the STRING_SPLIT()function to be recognized. You can check your compatibility level with the following query:

请注意，您的 SQL Server 安装必须在兼容级别 130 或更高级别STRING_SPLIT()才能识别该功能。您可以使用以下查询检查您的兼容性级别：

SELECT compatibility_level
FROM sys.databases WHERE name = 'yourdatabasename';

Most languages (including C#) have a "join" function you can use to create a string from an array.

大多数语言（包括 C#）都有一个“join”函数，你可以用它从数组中创建一个字符串。

int[] myarray = {22, 33, 44};
string sqlparam = string.Join(";", myarray);

Then you pass sqlparamas your parameter to the stored procedure above.

然后将sqlparam您的参数作为参数传递给上面的存储过程。

C# 如何将数组传递给 SQL Server 存储过程

提问by Sergey

采纳答案by Aaron Bertrand

SQL Server 2008 (or newer)

SQL Server 2008（或更新版本）

SQL Server 2005

SQL Server 2005

回答by Fedor Hajdu

回答by Levi W

回答by praveen

回答by eselk

回答by Adam

回答by Solomon Rutzky

回答by Hamed Nazaktabar

回答by Charan Ghate

回答by Patrick Chu

相关推荐

最近更新

标签

C# 如何将数组传递给 SQL Server 存储过程

提问by Sergey

采纳答案by Aaron Bertrand

SQL Server 2008 (or newer)

SQL Server 2008（或更新版本）

SQL Server 2005

SQL Server 2005

回答by Fedor Hajdu

回答by Levi W

回答by praveen

回答by eselk

回答by Adam

回答by Solomon Rutzky

回答by Hamed Nazaktabar

回答by Charan Ghate

回答by Patrick Chu

相关推荐

C# WCF 服务 maxReceivedMessageSize basicHttpBinding 问题

将 C# 中的预处理语句与 Mysql 结合使用

C# 命名空间“System.Web.Mvc”中不存在类型或命名空间名称

C# 检查目录是否没有文件，但可能包含子文件夹

相关推荐

最近更新

标签