CODEWITHSUNDEEP
c#sql-server
crippa
crippa

Reputation: 3

Append value in sql server

I have written an c# foreach function that updates an column in the sql server. I have 2 values that i would like to append in the same column. In the foreach function, it updates the values in the column, but it overwrites the value on the next "loop".

Example:

string value = "Blue";
SqlCommand cmdUpdate = new SqlCommand("UPDATE Colors SET color=@value WHERE color_id = 11", connection);

and then the next foreach loop the string value is "Red"

string value = "Red";
SqlCommand cmdUpdate = new SqlCommand("UPDATE Colors SET color=@value WHERE color_id = 11", connection);

How should i write my code to get both values in the sql column. "Blue, Red"

Thanks in advance

Upvotes: 0

Views: 93

Answers (2)

Soner Gönül
Soner Gönül

Reputation: 98740

You can create a logic like that;

  • Move your all strings in a List<string>
  • Generate a temperory empty string to insert your commmand.
  • For the first value, check your temp string is empty, if it is, add it to current string, if it is not, format it with the current string and old strings.

As an example;

var list = new List<string>(){"Blue", "Red"};
var tempStr = "";
foreach(var s in list)
{
    if(tempStr == "")
        tempStr = s;
    else
        tempStr = string.Format("{0}, {1}", tempStr, s);
    SqlCommand cmdUpdate = new SqlCommand("UPDATE Colors SET color=@value WHERE color_id = 11", connection);
    cmdUpdate.Parameters.Add("@value", SqlDbType.NVarChar).Value = tempStr;
    // I assume your color column is NVarChar.
    cmdUpdate.ExecuteNonQuery();
    cmdUpdate.Parameters.Clear();
}

This will update your color as Blue and Blue, Red with order.

Upvotes: 1

leonard_deutsch
leonard_deutsch

Reputation: 361

Concat your string first and then update.

string value = "Blue";
value += ", Red";
SqlCommand cmdUpdate = new SqlCommand("UPDATE Colors SET color=@value WHERE color_id = 11", connection);

And of course do this in your foreach clause.

Edit: You could also do this if you only want to use sql:

SqlCommand cmdUpdate = new SqlCommand("UPDATE Colors SET color='' WHERE color_id = 11", connection);
foreach(...)
{
    value = "[Your Value]";
    SqlCommand cmdUpdate = new SqlCommand("UPDATE Colors SET color= color + ', ' + @value WHERE color_id = 11", connection);
}

Upvotes: 0

Related Questions