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

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...... — marc_s
– marc_s, Commented Feb 17, 2018 at 10:11

marc_s · Accepted Answer · 2018-02-17 11:27:53Z

2

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.

edited Feb 17, 2018 at 11:27

answered Feb 17, 2018 at 10:14

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user9373061 Over a year ago

That was an example, so i know how to example retrive adding ids to students by compare the names

marc_s Over a year ago

@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 Over a year ago

@PatrickArtner you are right,i did not think about it. Is the solution you posted down the best way to do it?

Patrick Artner Over a year ago

@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

Patrick Artner · Accepted Answer · 2018-02-17 11:07:49Z

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.

Does not associate a first name with a last. John Smith and Larry Johnson I don't want Larry Smith.

Collectives™ on Stack Overflow

C# SqlDataReader only retrieves one row

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related