CODEWITHSUNDEEP
c#sqlvisual-studioinsert
4est
4est

Reputation: 3168

Foreach with Insert SQL (C#, SQL)

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?

Upvotes: 0

Views: 643

Answers (1)

Rafal
Rafal

Reputation: 12639

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

Upvotes: 2

Related Questions