[Solved] C# Inserting values into database using switch case

Question

Let’s clean up the code a little bit and make it a little more reusable first:

foreach (int thisColor in allColors)
{
    List<Subject> subjectsForThisColor = subjects.Where(x => x.Color == thisColor).ToList();
    foreach (Subject s in subjectsForThisColor)
    {
        test += s.SubjectName + " -" + s.Color + "\n";
        switch (s.Color)
        {
            case 1:
                updateOneRow("Monday", "first", s.SubjectName, null);
                break;
            case 2:
                updateOneRow("Monday", "second", s.SubjectName, null);
                break;
            case 6:
                updateOneRow("Monday", "sixth", s.SubjectName, null);
                break;
        }
    }
}

SQL Method

public void updateOneRow(string tableName, string columnName, string value){        
    con.Open();
    query = "SELECT count ("+columnName+") FROM "+tableName;
    SqlCommand cmd = New SqlCommand(query, con);
    Int32 count = (Int32)cmd.ExecuteScalar();
    if(count == 0){
        cmd = new SqlCommand("INSERT INTO " + tableName + "("+columnName+") values (@value)"),con);
    }
    else{
        query = "SELECT top 1 "+columnName+" FROM "+tableName;
        cmd = New SqlCommand(query, con);
        string dbValue = (string)cmd.ExecuteScalar();
        if (dbValue != null && dbValue != string.Empty)
            value += ";"+dbValue;
        cmd = new SqlCommand("UPDATE  " + tableName + " set ["+columnName+"] = @value", con);
    }
    cmd.Parameters.AddWithValue("value",value);
    cmd.ExecuteNonQuery();
    con.Close();
}

That’ll help clear up your code, as to why you are getting Elective-5, it is because you were using an UPDATE SQL statement on your case 6 instead of an INSERT. Update will overwrite previous values that were in the table and replace all current values that match your where clause, of which you had none. So all values in the table would have been overwritten. I think you should have written INSERT and not UPDATE

[Edit]
I don’t like it, but updated the code to do what you are expecting. You may need to debug it, but should give you the idea. It checks to see if a row already exists, if not it does an insert. If it does, it checks the column for a value and if one exists, adds it to the new value to insert. Then it does the update.

Hope that helps!

Final warning
This code won’t be perfect, far from it, but I hope it will guide you in the right direction of some slightly better coding practices. Good luck!
Also this is not the best design for a database, you should really look at doing this a different way!

[OP’s CODE]

case 6:
                            con.Open();
                            string query = "SELECT count (  sixth  ) FROM  Monday  ";
                            SqlCommand cmd3 = new SqlCommand(query, con);
                            Int32 count = (Int32)cmd3.ExecuteScalar();
                            if (count == 0)
                            {
                                cmd = new SqlCommand("INSERT INTO Monday (sixth) values (@value)", con);

                            }
                            else
                            {
                                query = "SELECT top 1 sixth FROM Monday";
                                cmd = new SqlCommand(query, con);
                                string dbValue = (string)cmd.ExecuteScalar();
                                if (dbValue != null && dbValue != string.Empty)
                                {
                                    value += ";" + dbValue;
                                    //value += s.SubjectName;
                                    //cmd = new SqlCommand("UPDATE  Monday set [sixth] = @value", con); <-This is in the wrong place

                                }
                                cmd = new SqlCommand("UPDATE  Monday set [sixth] = @value", con);//Should be here
                            }
                            cmd.Parameters.AddWithValue("value",value);
                            cmd.ExecuteNonQuery();
                            con.Close();
                            break;

After making the necessary changes and executing it, I am getting the following error.

The parameterized query '(@value nvarchar(4000))INSERT INTO Monday (sixth) values (@value' expects the parameter '@value', which was not supplied.

Accepted Answer