代码之家 › 专栏 › 技术社区 › graffic

通过C中的参数在sqlcommand中列出字符串列表#

sql-server sql c#

graffic · 技术社区 · 16 年前

使用C中的sqlcommand,我创建了一个查询,该查询在where子句中包含in(list…)部分。而不是循环通过我的字符串列表来生成查询所需的列表(如果您认为在SQLInjection中是危险的)。我想我可以创建一个参数,比如:

SELECT blahblahblah WHERE blahblahblah IN @LISTOFWORDS

然后在代码中,我尝试添加这样的参数:

DataTable dt = new DataTable();
dt.Columns.Add("word", typeof(string));
foreach (String word in listOfWords)
{
    dt.Rows.Add(word);
}
comm.Parameters.Add("LISTOFWORDS", System.Data.SqlDbType.Structured).Value = dt;

但这不管用。

问题:

我是在尝试不可能的事情吗?
我采取了错误的方法吗?
我在这方面有错误吗?

感谢您的时间:)

8 回复 | 直到 16 年前

Mark Lindell 16 年前

This link shows some approaches for SQL Server 2000/2005

SQL Server 2008 supports passing a table value parameter.

Joel Coehoorn 16 年前

SELECT * FROM [Table] WHERE ID IN (1,2,3)

SELECT * FROM [Table] WHERE ID IN ( SELECT TableID FROM [OtherTable] WHERE OtherTableID= @OtherTableID )

Judah Gabriel Himango 16 年前

Passing Arrays to Stored Procedures

Arrays and Lists in SQL 2005

Passing Array of Values to SQL Server without String Manipulation

Using MS SQL 2005's XML capabilities to pass a list of values to a command

Dani 16 年前

StringBuilder sb = new StringBuilder();
for (int i=0; i< listOfWords.Count; i++)
{
    sb.AppendFormat("p{0},",i);
    comm.Parameters.AddWithValue("p"+i.ToString(), listOfWords[i]);
}

comm.CommandText = string.Format(""SELECT blahblahblah WHERE blahblahblah IN ({0})", 
sb.ToString().TrimEnd(','));

SELECT blah WHERE blah IN (p0,p1,p2,p3...)...p0='aaa',p1='bbb'

Vyrotek 16 年前

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Dana 16 年前

Arno 16 年前

aarona 13 年前

FUNCTION

ALTER FUNCTION [dbo].[Split]
(
    @RowData nvarchar(max),
    @SplitOn nvarchar(5) = ','
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(100)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

Select Id, Data from dbo.Split('123,234,345,456',',')

CREATE PROCEDURE [dbo].[findDuplicates]
    @ids nvarchar(max)
as
begin
    select ID
      from SomeTable with (nolock)
     where ID in (select Data from dbo.Split(@ids,','))
end

public void SomeFunction(List<int> ids)
{
    var idsAsDelimitedString = string.Join(",", ids.Select(id => id.ToString()).ToArray());

    // ... or however you make your connection
    var con = GetConnection();

    try
    {
        con.Open();

        var cmd = new SqlCommand("findDuplicates", con);

        cmd.Parameters.Add(new SqlParameter("@ids", idsAsDelimitedString));

        var reader = cmd.ExecuteReader();

        // .... do something here.

    }
    catch (Exception)
    {
        // catch an exception?
    }
    finally
    {
        con.Close();
    }
}