I have table SelectedType with columns type_1 ... type_2_3 of datatype bit:

P_ID   type_1 type_1_1 type_1_2 type_1_3 type_2 type_2_1 type_2_2 type_2_3

 1        0      1         1       0       0       1        0        1       
 2        1      0         1       0       0       1        0        0
 3        1      0         1       0       0       1        0        0
 4        0      0         0       1       1       1        1        1
...and so on 

And JSON Array

[
  {"value": "type_1_1"}, {"value": "type_2_1"}, {"value": "type_2_3"}
]

How can I update the SQL Server table using for loop and if {"value": "type_1_1"} equals to table Column name type_1_1 then set value to 1 or if value is 1 then set to 0?

Here is what I'm trying so far:

public bool UpdatePredictSubGoalType(int id, string _selectedTypes)
{
        string a = "[]";
        string item = _selectedTypes;

        if (!item.Contains('[') && !item.Contains(']'))
        {
            string c = a.Insert(1, _selectedTypes);
            item = c;
        }

        bool res = false;

        JArray Ja = JArray.Parse(item);
        string sqlstr = "";

        try
        {
            using (conn = new SqlConnection(CommonDA.GetConnectionString()))
            {
                conn.Open();
                trans = conn.BeginTransaction();

                try
                {
                    for (int i = 0; i <= Ja.Count - 1; i  )
                    {
                        string x = Ja[i]["value"].ToString();

                        SqlCommand cmd = conn.CreateCommand();
                        sqlstr = @"Update SelectedType set "  x  " = (0 or 1, i don't know how to do) where id = @id AND "   x   " = @"   x;
                        cmd.CommandText = sqlstr;
                        cmd.Transaction = trans;
                        cmd.Parameters.AddWithValue("@id", id);                            

                        cmd.ExecuteNonQuery();

                        trans.Commit();
                        conn.Close();

                        res = true;
                    }
                }
                catch (Exception Err)
                {
                    trans.Rollback();
                    CommonLB.SystemError(Err.Message);
                    CommonLB.SystemError("Data", "SQL:"   sqlstr);
                }
            }
        }
        catch (Exception Err)
        {
            throw new ApplicationException(Err.Message, Err);
        }

        return res;
    }

CodePudding user response：

Assuming what I mentioned in the comments is correct (that presence of a value defines 1, absence defines 0) I think, before you set your 1s you'll need to set everything to 0, or get into having a collection of all your columns so C# can work out what should be 0..

Something like this (install Dapper to make ExecuteAsync on a connection "a thing", or expand it to "the long way"):

    using var conn = new SqlConnection(CommonDA.GetConnectionString()));

    var sql = "UPDATE t SET type_1=0, type_1_1=0, type_1_2=0, type_1_3=0, type_2=0, type_2_1=0, type_2_2=0, type_2_3=0 WHERE ID = @id";

    for (int i = 0; i <= Ja.Count - 1; i  ){
      var c = Ja[i]["value"].ToString();

      if(!Regex.IsMatch(c, @"^type_\d(_\d)?$")
        continue;
      sql = sql.Replace($"{c}=0", $"{c}=1");
    }

    await conn.ExecuteAsync(sql, new { id });

We start off by naming very column in the table, and setting it type_x_y=0. Then for every column name present in the json, we change that part of the SQL to type_x_y=1 with a bit of string replacement

The Regex is in there to guard against SQL injection attacking by the json supplier

CodePudding user response：

You can just pass the whole JSON array into SQL, and have SQL Server break it down.

• Use OPENJSON to bring out separate rows
• Pivot it back into a single row, each column having a 1 or 0
• Use a combination of ISNULL and NULLIF to keep the same value
  • If you want to overwrite all columns then you can simply do type_1 = j.type_1

UPDATE st
SET
  type_1   = IIF(j.type_1   = 1, 1, st.type_1  ),
  type_1_1 = IIF(j.type_1_1 = 1, 1, st.type_1_1),
  type_1_2 = IIF(j.type_1_2 = 1, 1, st.type_1_2),
  type_1_3 = IIF(j.type_1_3 = 1, 1, st.type_1_3),
  type_2   = IIF(j.type_2   = 1, 1, st.type_2  ),
  type_2_1 = IIF(j.type_2_1 = 1, 1, st.type_2_1),
  type_2_2 = IIF(j.type_2_2 = 1, 1, st.type_2_2),
  type_2_3 = IIF(j.type_2_3 = 1, 1, st.type_2_3)
FROM SelectedType st
CROSS APPLY (
    SELECT *
    FROM OPENJSON(@json)
      WITH (value sysname) j
    PIVOT (COUNT(value) FOR value IN
        (type_1, type_1_1, type_1_2, type_1_3, type_2, type_2_1, type_2_2, type_2_3)
    ) pvt
) j
WHERE st.P_ID = @id;

Then your application code becomes very simple

public bool UpdatePredictSubGoalType(int id, string _selectedTypes)
{
    if (!item.StartsWith('['))
        _selectedTypes = '['   _selectedTypes;
    if (!item.EndsWith(']'))
        _selectedTypes = _selectedTypes   ']';

    try
    {
        const string query = @"
THE ABOVE QUERY
";
        using (var conn = new SqlConnection(CommonDA.GetConnectionString()))
        using (var cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;                            
            cmd.Parameters.Add("@json", SqlDbType.NVarChar, -1).Value = _selectedTypes;
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
    catch (Exception Err)
    {
        throw new ApplicationException(Err.Message, Err);
    }

    return true;
}

CodePudding user response：

Change your sql string:

str = $"Update SelectedType set [{x}] = CASE WHEN [{x}] = 0 then 1 else 0 end where id = {@id}";