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 stackoverflow.com/questions/10409576/… — Alex K.
– Alex K., Commented Jan 16, 2017 at 12:42

Community · Accepted Answer · 2017-05-23 11:45:49Z

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, 2017 at 11:45

CommunityBot

11 silver badge

answered Jan 16, 2017 at 13:04

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

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

6 Comments

Norgul Over a year ago

Is this prone to SQL injection?

Zohar Peled Over a year ago

only if the value of tableName comes from user input.

Norgul Over a year ago

Is there a way to patch that part without hardcoding?

Zohar Peled Over a year ago

Not that I know of.

Norgul Over a year ago

Solution is great...you're just missing the increment at the end of the loop

|

wnutt · Accepted Answer · 2017-01-16 13:12:12Z

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, 2017 at 13:12

answered Jan 16, 2017 at 13:06

wnutt

5493 silver badges5 bronze badges

3 Comments

Norgul Over a year ago

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

wnutt Over a year ago

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

Norgul Over a year ago

If done like that, it can be exploited. I needed parameter solution :)

Dans · Accepted Answer · 2017-01-16 20:57:28Z

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, 2017 at 20:57

Dans

2361 silver badge7 bronze badges

Collectives™ on Stack Overflow

ADO.NET insert multiple values to SQL parameter

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related