C# SqlDataReader only retrieves one row

Question

I have a problem, I have list over 100 students but reader only retrieves the last student in the table.

string selectQuery = "SELECT * FROM Students WHERE firstName = @first AND lastName = @last";

using (SqlCommand sqlCommand = new SqlCommand(selectQuery, sqlConnection))
{
    sqlCommand.Parameters.Add("@first", SqlDbType.VarChar);
    sqlCommand.Parameters.Add("@last", SqlDbType.VarChar);

    foreach (Student student in studentList)
    {
        sqlCommand.Parameters["@first"].Value = student.first;
        sqlCommand.Parameters["@last"].Value = student.last;
    }

    using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
    {
        while (sqlReader.Read())
        {
            Console.WriteLine(sqlReader["firstName"].ToString());
            Console.WriteLine(sqlReader["lastName"].ToString());
        }
    }
}

Answer 1

If you want to retrieve the values for all students, then you need to execute the SQL inside the foreach Student loop:

string selectQuery = "SELECT * FROM Students WHERE firstName = @first AND lastName = @last";

using (SqlCommand sqlCommand = new SqlCommand(selectQuery, sqlConnection))
{
    sqlCommand.Parameters.Add("@first", SqlDbType.VarChar);
    sqlCommand.Parameters.Add("@last", SqlDbType.VarChar);

    foreach (Student student in studentList)
    {
        sqlCommand.Parameters["@first"].Value = student.first;
        sqlCommand.Parameters["@last"].Value = student.last;

        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
        {
             while (sqlReader.Read())
             {
                 Console.WriteLine(sqlReader["firstName"].ToString());
                 Console.WriteLine(sqlReader["lastName"].ToString());
             }
        }
    }
}

but this really doesn't make a lot of sense.... what are you really trying to achieve?? You search by first and last name - which you provide from the studentList - but then you also output only the first and last name you've retrieved - which are the same as you've passed in - so why even going to the database?

Update: as @PatrickArtner correctly comments - creating and disposing of 100 SqlDataReader instances is less than ideal. It would be much better to stick this selection process into a stored procedure with a single SELECT, and a table-valued parameter that would hold the 100 (or more) student ID's or information, based on which you want to select your students, and then iterate over the result set from that stored procedure in C# using a single SqlDataReader object.

Answer 2

Reason for your problem is you overwrite your single 2 parameters over and over. See comments in code:

string selectQuery = "SELECT * FROM Students WHERE firstName=@first and lastName=@last";
using (SqlCommand sqlCommand = new SqlCommand(selectQuery, sqlConnection))
{
    sqlCommand.Parameters.Add("@first", SqlDbType.VarChar);
    sqlCommand.Parameters.Add("@last", SqlDbType.VarChar);
    foreach (Student student in studentList) // addds 100 students
    {
        // overwrites the same parameter again and again.... so its only the last one
        sqlCommand.Parameters["@first"].Value = student.first;
        sqlCommand.Parameters["@last"].Value = student.last;
    }

    using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) 
    {// the query uses only 2 params so you need to change the query and the adding
        while (sqlReader.Read())
        {
            Console.WriteLine(sqlReader["firstName"].ToString());
            Console.WriteLine(sqlReader["lastName"].ToString());
        }
    }
 } 

---

Solution search for all of them with one query using a complexer WHERE condition build from your data and as many SqlParameters as needed for your data:

// create all the variable names
var firstVarName = Enumerable.Range(1, studentList.Count)
    .Select(i => $"@first_{i}")
    .ToList();
var lastVarName = Enumerable.Range(1, studentList.Count)
    .Select(i => $"@last_{i}")
    .ToList();

var cond = "firstName = {0} and lastName = {1}";

var whereOred = new StringBuilder("WHERE 1 = 0  -- false, just ease of formatting\n");

for (int i = 0; i < firstVarName.Count; i++)
{
    whereOred.AppendLine("   OR " + string.Format(cond, firstVarName[i], lastVarName[i]));
}

// adapt query to search all variable names
string selectQuery = $@"
SELECT *
FROM Students
{whereOred.ToString()}";

Console.WriteLine(selectQuery);
using (SqlCommand sqlCommand = new SqlCommand(selectQuery, sqlConnection))
{
    // add all the variable names with values from studentList
    for(int i=0;i<studentList.Count;i++) 
    {
        sqlCommand.Parameters.Add(firstVarName[i], SqlDbType.VarChar);
        sqlCommand.Parameters.Add(lastVarName[i], SqlDbType.VarChar);
        sqlCommand.Parameters[firstVarName[i]].Value = studentList[i].first;
        sqlCommand.Parameters[lastVarName[i]].Value = studentList[i].last;
    }

    using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
    { 
        if (sqlReader.HasRows)
            while (sqlReader.Read())
            {
                Console.WriteLine(sqlReader["firstName"].ToString());
                Console.WriteLine(sqlReader["lastName"].ToString());
            }
        else Console.WriteLine("No match.");
    }
}

SqlStatement created for my demodata:

SELECT *
FROM Students
WHERE 1 = 0  -- false, just ease of formatting
   OR firstName = @first_1 and lastName = @last_1
   OR firstName = @first_2 and lastName = @last_2
   OR firstName = @first_3 and lastName = @last_3
   OR firstName = @first_4 and lastName = @last_4
   OR firstName = @first_5 and lastName = @last_5
   OR firstName = @first_6 and lastName = @last_6
   OR firstName = @first_7 and lastName = @last_7
   OR firstName = @first_8 and lastName = @last_8
   OR firstName = @first_9 and lastName = @last_9
   OR firstName = @first_10 and lastName = @last_10
   OR firstName = @first_11 and lastName = @last_11
   OR firstName = @first_12 and lastName = @last_12
   OR firstName = @first_13 and lastName = @last_13
   OR firstName = @first_14 and lastName = @last_14
   OR firstName = @first_15 and lastName = @last_15
   OR firstName = @first_16 and lastName = @last_16
   OR firstName = @first_17 and lastName = @last_17
   OR firstName = @first_18 and lastName = @last_18
   OR firstName = @first_19 and lastName = @last_19
   OR firstName = @first_20 and lastName = @last_20

SqlParameters added for execution for my demodata:

I used

public class Student { public string first; public string last; }

and

var studentList = Enumerable.Range(1, 20)
    .Select(i => new Student { first = $"firstname {i}", last = $"lastname {i}" })
    .ToList();

to create some demo studen list.