传递一个SQL参数的IN（）子句中使用类型化数据集在.NET中 [英] Passing a SQL parameter to an IN() clause using typed datasets in .NET

问题描述

首先道歉，因为在这个网站类似的问题，但他们没有直接回答这个问题。

First apologies as there are similar questions on this site, but none of them answer this problem directly.

林创建DataSet中的一个TableAdapter像的查询：

Im using typed datasets in VS 2010. I create a TableAdapter in a Dataset with a query like:

SELECT * from Table WHERE ID IN(@IDs)

现在如果我叫： TableAdapter.Fill（MyDataTable，1,2,3）时发生错误，指出VS不能转换为1,2,3型int类型。很公平。

Now if I call: TableAdapter.Fill(MyDataTable,"1,2,3") an error occurs stating that VS cannot convert 1,2,3 to type int. Fair enough.

于是我决定改变参数（即@IDs）参数集合中键入字符串。再次尝试 - 仍然是同样的错误消息。

So then i decide to change the Parameter (i.e. @IDs) type to string in the Parameter collection. Try again - still the same error message.

那么，有没有办法这种类型化的DataSet能接受我的1,2,3参数？目前，我只有几个参数来传递，所以我可以很容易地只创建5左右的参数，并分别通过他们，但如果有几百个？有什么办法，我可以叫填写（）方法与我的逗号分隔的参数？

So is there any way this typed dataset can accept my "1,2,3" parameter? At the moment i only have a few parameters to pass, so i could easily just create 5 or so parameters and pass them separately, but what if there are hundreds? Is there any way I can call the Fill() method with my comma separated parameter?

（我知道我可以使用动态SQL创建语句并执行它，但如果有另一种方式让我保持我的类型化的DataSet在如的ReportViewer / bindingsources使用将preFER）

(i know i can use Dynamic SQL to create the statement and execute it but would prefer if there is another way allowing me to keep my typed dataset for use in e.g. ReportViewer/bindingsources)

推荐答案

您不能使用一个单一的参数值，这样的清单。但也有可能是特定于数据库的方式来实现你想要什么。例如，与SQL Server 2005或更高版本，你可以创建一个表值函数来分割你的字符串参数，是这样的：

You can't use a single parameter for a list of values in this way. But there may be database-specific ways to achieve what you want. For example, with SQL Server 2005 or later you could create a table-valued function to split your string parameter, something like:

CREATE FUNCTION dbo.F_Split
(
@InputString VARCHAR(MAX)
,@Separator VARCHAR(MAX)
)
RETURNS @ValueTable TABLE (Value VARCHAR(MAX))
AS
BEGIN

    DECLARE @SeparatorIndex INT, @TotalLength INT, @StartIndex INT, @Value VARCHAR(MAX)
    SET @TotalLength=LEN(@InputString)
    SET @StartIndex = 1

    IF @Separator IS NULL RETURN

    WHILE @StartIndex <= @TotalLength
    BEGIN
        SET @SeparatorIndex = CHARINDEX(@Separator, @InputString, @StartIndex)
        IF @SeparatorIndex > 0
        BEGIN
            SET @Value = SUBSTRING(@InputString, @StartIndex, @SeparatorIndex-@StartIndex)
            SET @StartIndex = @SeparatorIndex + 1
        END
        ELSE
        BEGIN
            Set @Value = SUBSTRING(@InputString, @StartIndex, @TotalLength-@StartIndex+1)
            SET @StartIndex = @TotalLength+1
        END
        INSERT INTO @ValueTable
        (Value)
        VALUES
        (@Value)
    END

    RETURN
END

您会再使用它，如下所示：

You would then use it as follows:

SELECT * from Table WHERE ID IN (SELECT CAST(Value AS INT) FROM F_Split(@IDs, ','))

这篇关于传递一个SQL参数的IN（）子句中使用类型化数据集在.NET中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！