Tutorial :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?


Solution: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.


Solution:2

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.


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


Solution: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:

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »