如何在Select语句的IN子句中提供一串ID，即使该列是整数

I'm paging data using an ObjectDataSource and I have the following method:

我使用ObjectDataSource分页数据,我有以下方法:

public int GetNumberOfArticles(string employeeIds)
{
    System.Data.DataTable dataTable;
    System.Data.SqlClient.SqlDataAdapter dataAdapter;
    System.Data.SqlClient.SqlCommand command;

    int numberOfArticles = 0;

    command = new System.Data.SqlClient.SqlCommand();
    command.Connection = Classes.Database.SQLServer.SqlConnection;

    command.CommandText = @"SELECT COUNT(*)
                            FROM 
                                  [Articles]
                            WHERE 
                                  [Articles].[EmployeeID] IN (@EmployeeIds)";

    command.Parameters.AddWithValue("@EmployeeIds", employeeIds);
    numberOfArticles = (int)command.ExecuteScalar();
    return numberOfArticles;
}

EmployeeID is an integer and because of that, anything I place inside employeeIds will be converted to an integer. However, because I'm using the IN keyword, it is obvious that i want to replace employeeIds with a list of ids separated by commas:

EmployeeID是一个整数,因此,我放在employeeIds中的任何内容都将转换为整数。但是,因为我使用的是IN关键字,所以很明显我想用一个用逗号分隔的id列表替换employeeIds:

1, 2, 3

But when I replace the line:

但是当我更换线路时:

command.Parameters.AddWithValue("@EmployeeIds", employeeIds);

with something like:

有类似的东西:

command.Parameters.AddWithValue("@EmployeeIds", "1, 2, 3");

I receive an exception because I provided a string while EmployeeIds is an integer. So, how would i go about doing that?

我收到一个异常,因为我提供了一个字符串,而EmployeeIds是一个整数。那么,我该怎么做呢?

thanks.

Edit:

From the replies, I understand that this has to be done manually, however the class which contains this method would be created automatically by the ObjectDataSource. So how can i provide the values of employeeIds at runtime?

从回复中,我知道这必须手动完成,但是包含此方法的类将由ObjectDataSource自动创建。那么如何在运行时提供employeeIds的值呢?

4 个解决方案

#1

The only way to go about doing this would be to parse your string manually in your query and insert the values into a memory table, then join the memory table in your query rather than using the IN clause.

执行此操作的唯一方法是在查询中手动解析字符串并将值插入内存表,然后在查询中加入内存表而不是使用IN子句。

As an example, this page on CodeProject presents this function:

例如,CodeProject上的这个页面提供了这个功能:

IF EXISTS(SELECT * FROM sysobjects WHERE ID = OBJECT_ID(’UF_CSVToTable’))
 DROP FUNCTION UF_CSVToTable
GO

CREATE FUNCTION UF_CSVToTable
(
 @psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
 DECLARE @sTemp VARCHAR(10)

 WHILE LEN(@psCSString) > 0
 BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp VALUES (@sTemp)
 END

RETURN
END
Go

You could then use it like this:

然后你可以像这样使用它:

SELECT                          
    COUNT(*)
FROM 
    [Articles]

JOIN dbo.UF_CSVToTable(@EmployeeIds) ids on ids.sID = [Articles].[EmployeeID]

In the end, though, it's generally not a great practice to take up. But if it's necessary, then this approach should give it to you in a pretty straightforward manner.

但最终,这通常不是一个很好的做法。但如果有必要,那么这种方法应该以非常简单的方式给你。

#2

You can create your SQL-statement like this using string-concatenation:

您可以使用字符串连接创建这样的SQL语句:

"SELECT ... IN (@id1,@id2,@id3)"

“SELECT ... IN(@ id1,@ id2,@ id3)”

and then:

command.Parameters.AddWithValue("@id1", employeeIds[0]); command.Parameters.AddWithValue("@id2", employeeIds[1]); command.Parameters.AddWithValue("@id3", employeeIds[2]);

command.Parameters.AddWithValue(“@ id1”,employeeIds [0]); command.Parameters.AddWithValue(“@ id2”,employeeIds [1]); command.Parameters.AddWithValue(“@ id3”,employeeIds [2]);

(using for-loops of course).

(当然使用for循环)。

This way you still insert values using parameters.

这样,您仍然可以使用参数插入值。

#3

Similar to this question, you need to add a parameter for each value at runtime:

与此问题类似,您需要在运行时为每个值添加一个参数:

public int GetNumberOfArticles(string employeeIds)
{
    System.Data.DataTable dataTable;
    System.Data.SqlClient.SqlDataAdapter dataAdapter;
    System.Data.SqlClient.SqlCommand command;

    int numberOfArticles = 0;

    command = new System.Data.SqlClient.SqlCommand();
    command.Connection = Classes.Database.SQLServer.SqlConnection;

    string params = string.Join(",", employeeIds.Select((e, i)=> "@employeeId" + i.ToString()).ToArray());
    command.CommandText = @"SELECT                          
                               COUNT(*)
                            FROM 
                               [Articles]
                            WHERE 
                               [Articles].[EmployeeID] IN (" + params + ")";

    for (int i = 0; i < employeeIds.Length; i++) {
       command.Parameters.AddWithValue("@employeeId" + i.ToString(), employeeIds[i]);
    }

    numberOfArticles = (int)command.ExecuteScalar();

    return numberOfArticles;
}

#4

A parameter is a distinct value and it can't be used to dynamically create a SQL statement as you're trying to do. You will need to create the SQL statement using string concatenation which could be as simple as:

参数是一个不同的值,它不能用于动态创建SQL语句,就像您尝试的那样。您将需要使用字符串连接创建SQL语句,这可能很简单:

command.CommandText = @"SELECT COUNT(*) FROM [Articles] " +
    "WHERE [Articles].[EmployeeID] IN (" + employeeIds + ")";

However, you need to sanitize the employeeIds string if it comes from user input to avoid SQL injection vulnerability. You could do that by splitting the string into an array and parsing each element to check that it's an int. If it passes, then you can re-join the array into a string before concatenating.

但是,如果来自用户输入以避免SQL注入漏洞,则需要清理employeeIds字符串。您可以通过将字符串拆分为数组并解析每个元素来检查它是否为int来实现。如果它通过,那么您可以在连接之前将数组重新连接到字符串中。

#1