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());
 }
 }
}

• SteppingRazor: What are the parameter values? Show us executed sql...
• marc_s: Well, first you're overwriting the parameter values for each student - but the search is then only executed once, for the last values set - so of course the SQL only gets the last set values......

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.

• user9373061: That was an example, so i know how to example retrive adding ids to students by compare the names
• marc_s: @PatrickArtner: not the ideal solution - just trying to show the OP where the problem lies. Of course, I'd prefer to stick this code into a stored procedure and pass in a table-valued parameter to handle multiple students at once - with a single SqlDataReader .....
• user9373061: @PatrickArtner you are right,i did not think about it. Is the solution you posted down the best way to do it?
• Patrick Artner: @Printer It is "a" solution to your problem. It is far from "best" - I probably would not query 100 students by Name/Firstname at once - I either use a ORM mapper and filter with Linq to Sql or use Linq to Objects on the results of a dbContext. I might get all + filter or the one student I am interested in - depending on the size of Data I have. I think you have a X-Y-Problem at hand if you do what you do. Creating a stored procedure as marc_s proposed would not have occured to me as first solution but sounds intriguing

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: paramlist in debug mode

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.

• paparazzo: Does not find the pair
• paparazzo: Does not associate a first name with a last. John Smith and Larry Johnson I don't want Larry Smith.