Runing ExecuteNonQuery in C# for each different row

Question

So I have a database that has student information and I want to add a randomly generated grade for each one of them. I created a method that generates a random grade letter between A-F.

However, when I update my database table all the courses that the students are taking get the same grade I want to give each different course a different grade .

 command.CommandText = "UPDATE CurrentCourses SET CurrenteGrade ='" + 
                       RandomLetter(grades) + "'";

 command.ExecuteNonQuery();

I believe that if there is a way to run this query for each different row it would solve my problem. But I couldn't really get it to work. I used Microsoft Access to create my database.

Thank you

Answer 1

Please put the where condition in your update query.Without where condition it will update all data in the table.

Answer 2

Using this UPDATE command without any WHERE-clause will affect every record each time. Note that the random letter is generated before the query is executed. Therfore the query will run with a single grade letter.

You could run this query for each course in turn with an appropriate WHERE-clause that selects one course each time. But this is not efficient.

Or, much better, you could apply a random function in SQL itself, that is evaluated for each record (i.e. let MySQL choose a random grade).

UPDATE CurrentCourses
SET CurrenteGrade = SUBSTRING('ABCDEF', FLOOR(RAND() * 6) + 1, 1)

In your code

command.CommandText = @"UPDATE CurrentCourses
    SET CurrenteGrade = SUBSTRING('ABCDEF', FLOOR(RAND() * 6) + 1, 1)";
command.ExecuteNonQuery();

This requires no loops and no command parameters.

Note that RAND() returns a random number between 0.0 and 1.0 (including 0.0 but excluding 1.0). Therefore FLOOR(RAND() * 6) generates a whole number in the range [0 .. 5]. 1 is added to get a number in the range [1 .. 6] used as index in the string 'ABCDEF' for the SUBSTRING function that cuts out one letter.

Answer 3

You can do it with pure T-SQL:

declare @idColumn int
DECLARE @MIN INT=1; --We define minimum value, it can be generated.
DECLARE @MAX INT=100; --We define maximum value, it can be generated.

select @idColumn = min( Id ) from CurrentCourses 

while @idColumn is not null
begin
Update CurrentCourses 
SET CurrenteGrade  = @MIN+FLOOR((@MAX-@MIN+1)*RAND(CONVERT(VARBINARY,NEWID())));
    select @idColumn = min( Id ) from CurrentCourses  where Id > @idColumn
end

the code above is looping over all the records (replace Id with your primary key) and generates random number between 1-100 (see comments where you can set new values) and updates the random number in each CurrentGrade record.

format and concatenate that command into your CommandText and execute ExecuteNonQuery()

Answer 4

The update statement affects all the rows in the database table:

"UPDATE CurrentCourses SET CurrenteGrade ='" + 
                   RandomLetter(grades) + "'"

Without a WHERE clause, this code could be run in a loop 100 times with a random grade each time, but every row in the table will say whatever grade was randomized last. If the last loop picked 'E' as the random grade, then all rows in the table will be graded E, depite the fact that they have, in the previous 5 seconds, changed grade 99 times already (all rows change each time the code is run. They only stop changing when the code stops being executed)

If you want to change all course rows to the same grade:

sqlCommand.CommandText = "UPDATE CurrentCourses SET CurrenteGrade = ? WHERE Course_ID = ?";

And then populate the parameters of the SqlCommand:

sqlCommand.Parameters.AddWithValue("grade", RandomLetter(grades));
sqlCommand.Parameters.AddWithValue("course", "SoftwareEngineering101");

This is the sort of thing you'd run many times (in a loop maybe) with a different course ID each time. The idea is that you just change the parameter values, then re-run the query:

sqlCommand.CommandText = "UPDATE CurrentCourses SET CurrenteGrade = ? WHERE Course_ID = ?";
sqlCommand.Parameters.AddWithValue("grade", "a"); //dummy values
sqlCommand.Parameters.AddWithValue("course", "a"); //dummy values
//the loop does the real work, repeatedly overwiting param values and running:
foreach(var course in myCoursesArray){
  sqlCommand.Parameters["grade"] = RandomLetter(grades);
  sqlCommand.Parameters["course"] = course;
  sqlCommand.ExecuteNonQuery()
}

With access, using ? for parameter placeholders in the SQL, it is important that you then add your paramters in the same order as the ? marks appear in the sql. The names are irrelevant - this is not so in more powerful DB systems like sqlserver, where the SQL has named parameters and the names given in the c# code do matter. In our Access based code though, the only thing that matters about the name is to use it when overwriting the parameter value with a new one in the loop

Note; there are good reasons to avoid using .AddWithValue, but I won't get into those here. It's more important to avoid using string concatenation to build values into your SQLs. See bobbytables.com for more info

Answer 5

Try something like;

UPDATE CurrentCourses SET CurrenteGrade = (select top 1 gradeName from grades ORDER BY NEWID())

In your code, you are not setting different courses for students. Because before executing the query, your query takes just one grade and update all the rows.

You can perform it using SQL easily.

Answer 6

You could first query for each row in the table(s) that contain the students and the course.

And then for each row in the data set execute your method to update that row with a random letter grade.

for example

foreach (DataRow dr in ds.tables[0].rows)  
{
  command.CommandText = "UPDATE CurrentCourses SET CurrenteGrade ='" + 
  RandomLetter(grades) + "'" + "WHERE PRIMARYKEY = dr.id"
  command.ExecuteNonQuery();
}

Answer 7

First your exact question: You need a WHERE statement that filters the update down to just a single row. This is usually done with an ID number or other unique identifier for the specific row(student in this case).

Second: Concatenating strings together with raw data can lead to errors and also security issues. For example, things like having a single quote in your string data will cause havoc. You should use up SqlParameters. https://www.dotnetperls.com/sqlparameter