How do I provide a string of Ids in a Select statement's IN clause even though the column is an integer

Question

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

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:

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?

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?

Answer 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.

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

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.

Answer 2

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:

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.

Answer 3

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

"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]);

(using for-loops of course).

This way you still insert values using parameters.

Answer 4

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;
}