How to make an "if" based on value retrieved from Query?

Question

I need to insert into a table "pass" or "fail", if from the query done there is a single fail instead of 'RProva' I have to put "fail" if the query can't find a single fail instead of 'RProva' I have to insert "pass", the query is working but I can't figure out how to do the if with the results of a query, maybe I have to use a "for"? Don't know. It is the attempt to make it, the second is the query where I have to insert the result of the possbile "IF"

1.

    SqlCommand cmdRD = new SqlCommand("SELECT ResItem AS RD FROM tSE JOIN tL ON tSE.idSE=tL.idL WHERE tL.Selection=1");
    var RD = cmdRD.ExecuteScalar();

    var values = new List<string>();
    using (cmdRD,sqliteCon)
    {
        using (SqlDataReader reader = cmdRD.ExecuteReader())
         {
            while (reader.Read())
            {
                values.Add(reader[0].ToString());
            }
        }
    }

2.

    SqlCommand cmd1 = new SqlCommand("INSERT INTO tSD(NomeItem,ResItemDet,DateStartDet,DateEndDet) OUTPUT inserted.Id VALUES (@NI,@RProva,@DATESE,@DATEED)");
    cmd1.Parameters.AddWithValue("@DATESE", DATESE);
    cmd1.Parameters.AddWithValue("@DATEED", DATEED);
    cmd1.Parameters.AddWithValue("@NI", NI);


    using (cmd1,sqliteCon)
    {
          foreach (var value in values)
          {
             if (value.Equals(pass))
             {
                        cmd1.Parameters.AddWithValue("@RProva", value);
             }
             else
             {
                        cmd1.Parameters.AddWithValue("@RProva", fail);
             }
                    cmd1.ExecuteNonQuery();
          }
    }

    int generatedId = Convert.ToInt32(cmd1.ExecuteScalar());
    cmd1.Parameters.Clear();


    SqlCommand cmd2 = new SqlCommand("UPDATE tSE SET FK_TSD_id = @tsdId FROM tL JOIN tSE ON tL.idL = tSE.idSE WHERE tL.Selection=1 ", sqliteCon);
    cmd2.Parameters.AddWithValue("@tsdId", generatedId);
    cmd2.ExecuteNonQuery();

    MessageBox.Show("Dato Aggiunto");

  }
  sqliteCon.Close();

1.1

    SqlCommand cmdRD = new SqlCommand("SELECT ResItem AS RD FROM tSE JOIN tL ON tSE.idSE=tL.idL WHERE tL.Selection=1", sqliteCon);
    var RD = cmdRD.ExecuteScalar();

    var tot =pass;
    using (cmdRD)
    {
        using (SqlDataReader reader = cmdRD.ExecuteReader())
         {
            while (reader.Read())
            {
                if(reader[0].ToString()==fail)
                {
                            tot = fail;
                            break;
                }
            }
            MessageBox.Show(tot);
        }
    }

i'm arrived to that(1.1) it works but i've to insert the TOT into RProva

Answer 1

Based on your question and comment I think you misunderstood ExecuteScalar:
ExecuteScalar executes the query, and returns the first column of the first row in the result set returned by the query. learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar

I dont know the Type of "RD" or "PASS", but the IF in your example (when using ExecuteScalar) seems OK. Else when you are using ExecuteReader you should use some kind of an loop to iterate through the Items. Like this:

string pass = "abc"; // guessing types and values
string fail = "failed";
string sqliteCon = "Data Source=(localdb)\\MSSQLLocalDB;Database=BooksDb";

using (SqlConnection connection = new SqlConnection(sqliteCon))
{
    connection.Open();

    var queryString = @"SELECT ResItem AS RD 
                FROM tSE 
                JOIN tL ON tSE.idSE = tL.idL 
                WHERE tL.Selection=1";

    var values = new List<string>();
    using (SqlCommand command = new SqlCommand(queryString, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                values.Add(reader[0].ToString());
            }
        }
    }

    var queryInsert = @"INSERT INTO tSD (NomeItem,ResItemDet,DateStartDet,DateEndDet) 
                                VALUES (@NI, @RProva, @DATESE, @DATEED)";
    using (SqlCommand command2 = new SqlCommand(queryInsert, connection))
    {
        foreach(var value in values)
        {
            command2.Parameters.Clear();
            if (value.Equals(pass))
            {
                command2.Parameters.AddWithValue("@RProva", value);
            }
            else
            {
                command2.Parameters.AddWithValue("@RProva", fail);
            }
            command2.ExecuteNonQuery();
        }
    }
}

Theres for sure a better way, but maybe this helps you out.

EDIT: Here is how I would implement your code example:

using (SqlConnection sqliteCon = new SqlConnection(connection))
{
    sqliteCon.Open();
    var values = new List<string>();
    var query = "SELECT ResItem AS RD FROM tSE JOIN tL ON tSE.idSE=tL.idL WHERE tL.Selection=1";
    using (SqlCommand cmdRD = new SqlCommand(query, sqliteCon))
    {
        var RD = cmdRD.ExecuteScalar();
        using (SqlDataReader reader = cmdRD.ExecuteReader())
        {
            while (reader.Read())
            {
                values.Add(reader[0].ToString());
            }
        }
    }

    int generatedId = 0;
    var query2 = "INSERT INTO tSD(NomeItem,ResItemDet,DateStartDet,DateEndDet) OUTPUT inserted.Id VALUES (@NI,@RProva,@DATESE,@DATEED)";
    using (SqlCommand cmd1 = new SqlCommand(query2, sqliteCon))
    {
        foreach (var value in values)
        {
            cmd1.Parameters.Clear();
            cmd1.Parameters.AddWithValue("@DATESE", DATESE);
            cmd1.Parameters.AddWithValue("@DATEED", DATEED);
            cmd1.Parameters.AddWithValue("@NI", NI);

            if (value.Equals(pass))
            {
                cmd1.Parameters.AddWithValue("@RProva", value);
            }
            else
            {
                cmd1.Parameters.AddWithValue("@RProva", fail);
            }
            cmd1.ExecuteNonQuery();
        }

        generatedId = Convert.ToInt32(cmd1.ExecuteScalar());
    }

    var query3 = "UPDATE tSE SET FK_TSD_id = @tsdId FROM tL JOIN tSE ON tL.idL = tSE.idSE WHERE tL.Selection=1 ";
    using (SqlCommand cmd2 = new SqlCommand(query3, sqliteCon))
    {
        cmd2.Parameters.AddWithValue("@tsdId", generatedId);
        cmd2.ExecuteNonQuery();
    }
}