Foreach with Insert SQL (C#, SQL)

Question

I have list of values:

string[] DataValues

now I want to put all the value into insert:

string myInsert = "Insert into table1(col1, col2, col3....col4) values (DataValues[0],DataValues [1], DataValues[2]....)

It's posssible to use somehow foreach?

@MohammedNoureldin Doesnt even make that huge difference in code, since you can easily convert the code with a for loop to code with a foreach loop, or the other way round. — L. Guthardt
– L. Guthardt, Commented Dec 18, 2017 at 13:02
foreach (string s in DataValues) { //insert s into database; } — Jasper
– Jasper, Commented Dec 18, 2017 at 13:03
If you are creating SQL like this, you are going to open yourself up to SQL injection and bugs. Please be very careful... — DavidG
– DavidG, Commented Dec 18, 2017 at 13:09

Rafal · Accepted Answer · 2017-12-18 13:28:30Z

2

To get exactly what your want you can format your DataValues like so:

var paramList = string.Join(",",DataValues.Select(x=>string.Format("'{0}'",x)).ToArray()

I presume you have the same for columns list.

BUT DO NOT DO THAT

this will make your code prone to sql injection you should declare parameter for each value and use it via parameter.

var parameters = string.Join(",",DataValues.Select((x,i)=>"@param"+i));
var myInsert = string.Format("Insert into table1({0}) values ({1})", columns, parameters);

using (SqlConnection connection = new SqlConnection(/* connection info */))
using (SqlCommand command = new SqlCommand(sql, connection))
{
    for(var i=0; i< DataValues.Length ; i++)
    {
       var param = cmd.Parameters.Add("@param"+i,  SqlDbType.NVarChar, DataValues[i].Length);
       param.Value = DataValues[i];
    }
    command.ExecuteNonQuery();
}

edited Dec 18, 2017 at 13:28

answered Dec 18, 2017 at 13:14

Rafal

12.7k34 silver badges56 bronze badges

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

2 Comments

Rafal Over a year ago

@DavidG yes ok, fixed.

4est Over a year ago

thx Rafal, the insert command will be saved inside file.sql, so "myInsert" I can easly write to file

Collectives™ on Stack Overflow

Foreach with Insert SQL (C#, SQL)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related