ADO.NET insert multiple values to SQL parameter

Question

I am receiving a Dictionary<string, string> and would like to forward its values to the DB inside SqlParameter. Is that even possible? This is the way I did it, and I am getting an error that column name doesn't match table definition.

SqlParameter param = new SqlParameter();
param.ParameterName = "@Values";

var sb = new StringBuilder();
foreach (var item in data)
{
    sb.Append("'" + item.Value + "', ");
}
param.Value = sb.ToString().TrimEnd(',');

string insertString = $"insert into {tableName} values (@Values)";
SqlCommand command = new SqlCommand(insertString, connection);

command.Parameters.Add(param);
command.ExecuteNonQuery();

Yes, you could use a *table valued parameter* on the server and a *data table* in C#, see http://stackoverflow.com/questions/10409576/pass-table-valued-parameter-using-ado-net — Alex K., Jan 16 '17 at 12:42

score 2 · Accepted Answer · edited May 23 '17 at 11:45

2

Sql server can't interpret the single variable you are passing as multiple values.
You can either generate your query with multiple variables, or use a table valued parameter.
For the first option, you must change the way you build your query:

var command = new SqlCommand();

var insertString = $"insert into {tableName} values (";
var sb = new StringBuilder(insertString);
int i = 0;
foreach (var item in data)
{
    sb.Append("@P").Append(i).Append(",");
    command.Parameters.Add("@P" + i, SqlDbType.VarChar).Value = item.Value;
    i++;
}
command.Connection = connection;
command.CommandText = sb.ToString().TrimEnd(",") + ");";
command.ExecuteNonQuery();

Note: Code was not tested, there might be some errors.

For the second option, You must use a stored procedure. I've never tried to pass table valued parameters to an inline query and I don't think it's possible.
This post (also linked in Alex K's comment) explains how to do that.

edited May 23 '17 at 11:45

Community

1
1

answered Jan 16 '17 at 13:04

Zohar Peled

79,642
10
69
121

Is this prone to SQL injection? – Norgul Jan 16 '17 at 13:20
only if the value of `tableName` comes from user input. – Zohar Peled Jan 16 '17 at 13:21
Is there a way to patch that part without hardcoding? – Norgul Jan 16 '17 at 13:22
1

Not that I know of. – Zohar Peled Jan 16 '17 at 13:24
Solution is great...you're just missing the increment at the end of the loop – Norgul Jan 16 '17 at 13:26
This has the advantage that there will only be one round trip to the database, because there is only one command that is executed. However, be advised that the number of parameters for an SqlCommand is limited to 2100. – Martin Aug 23 '21 at 14:33

wnutt · Answer 2 · 2017-01-16T13:12:12.743

0

Each value in the "Values" part of you t-SQL must be enclosed with parenthesis.

So, just change this line:

    sb.Append("'" + item.Value + "', ");

to:

    sb.Append("('" + item.Value + "'),");  // note: no space after the ,

Your tSQL would look something like this:

    insert into myTable values ('A', 'B', 'C',)

It needs to look like this (assuming you've only got 1 column in the table):

    insert into myTable values ('A'), ('B'), ('C')

And if your table contains multiple columns:

    insert into myTable (myColumn) values ('A'), ('B'), ('C')

edited Jan 16 '17 at 13:12

answered Jan 16 '17 at 13:06

wnutt

519
3
5

It's a good idea, but it doesn't work. I am getting `Incorrect syntax near '@Values'.` ...I suppose because it expects values to be enclosed in brackets in the first place – Norgul Jan 16 '17 at 13:20
Guess I should have specified specifically that you need to change your insertString to (no need for the parameter if you're not using a stored proc): string insertString = $"insert into {tableName} values {sb.ToString().TrimEnd(',')}"; – wnutt Jan 16 '17 at 13:36
If done like that, it can be exploited. I needed parameter solution :) – Norgul Jan 16 '17 at 13:44

score 0 · Answer 3 · answered Jan 16 '17 at 20:57

0

I think the best is create a split function in mssql (million of example in internet)and a stored. Pass a string comma(for example) separated to the stored Who call the function. Sorry for no example but i'm with my smartphone

answered Jan 16 '17 at 20:57

Dans

216
1
7

ADO.NET insert multiple values to SQL parameter

3 Answers3

Linked