CODEWITHSUNDEEP
c#sqlsql-serverparameterssql-injection
J.S.Orris
J.S.Orris

Reputation: 4821

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

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.

Upvotes: 0

Views: 1390

Answers (3)

ChrisC73
ChrisC73

Reputation: 1853

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

Upvotes: 2

CElliott
CElliott

Reputation: 452

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

Upvotes: 1

Nikhil Vartak
Nikhil Vartak

Reputation: 5127

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)

Upvotes: 1

Related Questions