List of strings in SqlCommand via parameters in C #

Question

Working with SqlCommand in C # I created a query containing the IN part (list ...) in the where clause. Instead of iterating over my string list generating the list I need for the query (dangerous if you think in sqlInjection). I thought I could create a parameter like:

SELECT blahblahblah WHERE blahblahblah IN @LISTOFWORDS 

Then in the code I try to add a parameter like this:

 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; 

But that does not work.

Questions:

• Am I trying to do something impossible?
• Did I take the wrong approach?
• Do I have errors in this approach?

Thank you for your time:)

Answer 1

What you are trying to do is possible, but does not use your current approach. This is a very common problem with all possible solutions prior to SQL Server 2008, with tradeoffs in performance, security, and memory usage.

This link shows some approaches to SQL Server 2000/2005

SQL Server 2008 supports passing a parameter to a table value.

Hope this helps.

Answer 2

You want to think about where this list comes from. Typically, this information is located in a database somewhere. For example, instead:

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

You can use a subquery like this:

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

Answer 3

If I understand correctly, you are trying to pass a list as an SQL parameter.

Some people have tried to do this with limited success:

Passing arrays to stored procedures

Arrays and Lists in SQL 2005

Passing an array of SQL Server values ​​without string manipulation

Using the XML features of MS SQL 2005 to pass a list of values ​​to a command

Answer 4

• Am I trying to do something impossible?

No, It is Immpossible.

• Did I take the wrong approach?

Your approach does not work (at least in .net 2)

• Do I have errors in this approach?

I would try the solution "Joel Coehoorn" (2 answers), if possible. Otherwise, another parameter should send the string parameter with all values ​​separated by a separator. Write a dynamic query (build it based on values ​​from a string) and execute it with "exec".

Another solution would be to create a query directly from the code. Something like that:

 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(',')); 

The command should look like this:

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

In MsSql2005, "IN" only works with 256 values.

Answer 5

I would recommend setting this parameter as a comma-separated value string and using the Split function in SQL to turn it into a single table of value columns, and then you can use the IN function.

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

Answer 6

If you want to pass the list as a string in a parameter, you can simply build the query dynamically.

DECLARE @query varchar (500) SET @query = 'SELECT blah blah WHERE blahblah in (' + @list + ')' EXECUTE (@query)

Answer 7

I had the same problem, I think there is now a way to do this directly over the ADO.NET API.

You might consider inserting words into a seductive one (plus queryid or something else), and then refer to what is redeeming from the query. Or dynamically create a query string and avoid sql injection in other ways (e.g. checking regular expressions).

Answer 8

This is an old question, but I came up with an elegant solution for this that I like to reuse, and I think everyone else will find it useful.

First of all, you need to create a FUNCTION in SqlServer, which takes delimited input and returns a table with elements divided by records.

Here is the code for this:

 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 

Now you can do something like this:

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

And fear not, it may not be susceptible to Sql injection attacks.

Then write a stored procedure that takes comma-delimited data, and then you can write a sql statement that uses this Split function:

 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 

Now you can write a C # wrapper around:

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