Why does SqlDataReader populate with last record only

Question

I'm learning asp.net web forms, and I got stuck on a problem.

Instead of providing what I've tried, I'll tell you what I have that is working, and what I am trying to do that is not working! This is what I have and is working:

SqlConnection sqlConn = new SqlConnection(connstr);

sqlConn.Open();
string sqlComm = "SELECT * FROM bookShop";

SqlCommand comm = new SqlCommand(sqlComm, sqlConn);

SqlDataReader reader = comm.ExecuteReader();

while (reader.Read())

So after this, I just dynamically create some div's and other elements to populate my website, and this works fine, it reads the entire database and generates everything.

However, this was in my Page_Load function and now I am trying to modify it so that this becomes a method of a dataclass which will allow me to just call a method instead of typing all of this in page_load. So the idea here is to call a method that will retrieve all of my database records and put them in a List<T> from which I plan on creating the needed elements, and then the user should work with the string. After he/she finishes I will use the list to update the database. This is for making as little as possible reference to the actual base.

This is what I have in the method:

SqlDataReader reader;


Book temp = new Book();

connection.Open();
SqlCommand loading = new SqlCommand("SELECT * FROM bookShop", connection);
reader = loading.ExecuteReader();
while(reader.Read())
{
    temp.ID = int.Parse(reader[0].ToString());
    temp.bookName = reader[1].ToString();
    temp.bookAuthor = reader[2].ToString();
    temp.bookPrice = float.Parse(reader[3].ToString());
    temp.coverPath = reader[4].ToString();
    books.Add(temp);
}
connection.Close();

books is a List that I take as a parameter, but for some reason this code only populates the list with the last record of my database and I can't figure out why since its exactly the same as the above.

In your while loop, you iterate over the records of your select statement and assign some values to you'r temp object. since it's always the same object, the loop will run to the end and the last record will be the last one assigned. — Oddmar Dam
– Oddmar Dam, Commented Jun 15, 2019 at 10:08
ah now I see,thanks a lot for the clarification will clean it up now. — V.Rusanov
– V.Rusanov, Commented Jun 15, 2019 at 10:34

Marc Gravell · Accepted Answer · 2019-06-15 10:05:09Z

2

You're only creating one book instance, and constantly overwriting the values. Move the new, basically:

while(reader.Read())
{
    Book temp = new Book();
    temp.ID = int.Parse(reader[0].ToString());
    temp.bookName = reader[1].ToString();
    temp.bookAuthor = reader[2].ToString();
    temp.bookPrice = float.Parse(reader[3].ToString());
    temp.coverPath = reader[4].ToString();
    books.Add(temp);
}

Alternatively: use a tool like "Dapper" to make life easy:

var books = connection.Query<Book>("SELECT * FROM bookShop").AsList();

or...

string isbn = "978-1935182474";
var book = connection.QuerySingleOrDefault<Book>(
    "SELECT * FROM bookShop where isbn=@isbn", new {isbn});

answered Jun 15, 2019 at 10:05

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

2 Comments

V.Rusanov Over a year ago

now I see it does not work with rewriting the values obviously, but I want to ask why does it matter ? I mean I have one set of values,I add them to the list and even if I change the values of the object temp,since its previous values are already in the list it shouldnt be changed?

Marc Gravell Over a year ago

@V.Rusanov no, the object reference is in the list - multiple times in your case, but that's multiple times of the same object. The object has properties, but if there's only one object instance, then it can only have one value for the properties

Apoorva Asthana · Accepted Answer · 2019-06-15 11:01:21Z

0

You need to create new instance of class Book for every iteration of while loop and then add it to list like this :

while(reader.Read())
{
    Book temp = new Book();
    temp.ID = int.Parse(reader[0].ToString());
    temp.bookName = reader[1].ToString();
    temp.bookAuthor = reader[2].ToString();
    temp.bookPrice = float.Parse(reader[3].ToString());
    temp.coverPath = reader[4].ToString();
    books.Add(temp);
}

answered Jun 15, 2019 at 11:01

Apoorva Asthana

769 bronze badges

Collectives™ on Stack Overflow

Why does SqlDataReader populate with last record only

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related