Use values from int[] in SQL statement in C#

Question

I have the following SQL statement that I want to run:

string sql = @"DECLARE @a udt;" +
              "INSERT INTO @a (id) VALUES @params;";

I have the following array of integers:

int[] array1 = {10,20,30,40,50,60};

It is important to not that the above array size is dynamic in my program.

I would like to add the values from the array to the @params in the SQL statement so that the SQL being executed in the SqlCommand looks like this:

sql = @"DECLARE @a udt;" +
       "INSERT INTO @a (id) VALUES (10),(20),(30),(40),(50),(60);" +
       "EXEC sp @a;";

I have tried the following amongst others and keep getting exceptions:

SqlConnection con = new SqlConnection("connectionString");
SqlCommand cmnd = new SqlCommand(sql, con);

for (int i = 0; i < array1.Count; i++)
{
    cmnd.Parameters.AddWithValue("@params" , array1[i]);
}

con.open().
cmnd.ExecuteNonQuery();

May somebody please explain what I am doing wrong. I also need to make sure to prevent SQL injection.

You can't do that, basically. You will have to generate the SQL dynamically, or execute the same (single) insert multiple times with different parameter values. — Blorgbeard
– Blorgbeard, Commented Nov 2, 2015 at 3:17
@Blorgbeard could I do something like ...VALUES @param1, @param2... instead of ...VALUES (10), (20)... ? — J.S.Orris
– J.S.Orris, Commented Nov 2, 2015 at 3:24
Is there a reason you can't just run the insert X number of times? — Ron Beyer
– Ron Beyer, Commented Nov 2, 2015 at 3:26
@Ron Beyer no...but how would I do that with the DECLARE @a udt; there? — J.S.Orris
– J.S.Orris, Commented Nov 2, 2015 at 3:28

ChrisC73 · Accepted Answer · 2015-11-02 05:51:28Z

2

You are close with your solution, but you need to explicitly reference each array index parameter in the sql string. eg:

    List<int> tests = new List<int>() { 10, 11, 12};
    var command = new MySqlCommand();
    List<string> parameterNames = new List<string>();
    for (int i = 0; i < tests.Count; i++)
    {
            string parameterName = String.Format("@test{0}", i);
            command.Parameters.AddWithValue(parameterName, tests[i]);
            parameterNames.Add("(" + parameterName + ")");
    }
    string insertSql = @"insert into test(id) values {0}";
    command.CommandText = String.Format(insertSql, String.Join(", ", parameterNames));
    command.Connection = connection;

    int result = command.ExecuteNonQuery();

The resulting sql command text will be:

"insert into test(id) values (@test0), (@test1), (@test2)"

answered Nov 2, 2015 at 5:51

ChrisC73

1,87315 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nikhil Vartak Over a year ago

Why to loop when it can be done in single statement. See my answer.

Antonín Lejsek Over a year ago

Problem with this approach is, that You can not have more than 2100 parameters.

Nikhil Vartak · Accepted Answer · 2015-11-02 03:39:38Z

1

Join the Int array to format according to @params parameter:

int[] intArr = { 10, 20, 30 };
string intStr = string.Join(",", intArr.Select(i => "(" + i.ToString() + ")"));

intStr is in the form (10),(20),(30) which you pass to sql. Create dynamic statement and execute it.

-- declared here for sample
declare @params varchar(50) = '(10),(20),(30)'

declare @sql varchar(max) = 
    'declare @a table (id int)' + ';' +
    'insert into @a (id) values ' + @params + ';' +
    'exec sp @a'

exec (@sql)

answered Nov 2, 2015 at 3:39

Nikhil Vartak

5,1473 gold badges29 silver badges36 bronze badges

1 Comment

J.S.Orris Over a year ago

Will this prevent sql injection?

CElliott · Accepted Answer · 2015-11-02 03:52:43Z

1

You could use generate dynamic SQL for your values clause. This will cause some performance overhead depending on how often this code is run due to poor plan cache reuse.

I would pass the array in as a delimited string to the procedure and use a SQL string splitter to convert the string into a table. This allows you to pass in a single parameter to the procedure and possibly bypass the UDT.

There are a variety of string splitters available with a web search. Any you find that use a loop would be slow, but would work. I personally prefer Jeff Moden's inline table valued function [DelimitedSplit8K] (requires free signup, but I think it is well worth it) which is probably the fastest TSQL string splitter.

Usage with any splitter would be similar to this:

DECLARE @a udt;
INSERT INTO @a([id])
SELECT [Item]
FROM [dbo].[DelimitedSplit8K](@params)

This would convert {10,20,30,40,50,60} to a table with one value per row.

Another usage is for joining:

SELECT 
    [col1],
    [col2],
    [ItemNumber] -- position in array
FROM [dbo].[MyTable] A
INNER JOIN [dbo].[DelimitedSplit8K](@params) B
ON       A.[col3] = B.[Item] -- join on array value

edited Nov 2, 2015 at 3:52

answered Nov 2, 2015 at 3:42

CElliott

4522 silver badges8 bronze badges

1 Comment

Nikhil Vartak Over a year ago

Another Split function - dotnetprof.blogspot.in/2012/08/split-function-sql.html

Collectives™ on Stack Overflow

Use values from int[] in SQL statement in C#

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related