none
Reading one row multiple times

    Question

  • I'm creating an object list from a table.  After the table read has finished I inspected each object in the list.  The list count is correct, however each object in the list is the same, the last row in the table, all the primary keys are the same in the list.  Has anyone seen this behaviour before?  My select statement is select * from tablename.   
    Tuesday, April 18, 2017 10:51 PM

All replies

  • VS is showing some weird behaviour.  when examining a object list at a break point, I examined each object created from a table read.  Each object has its own primary key.  When the read is finished, I examined the first object again.  It has the same primary key as the last object, they all do.  Even though I'm looking at object[0] in the list<>, expecting to see the data in the first object, I think VS is somehow displaying data from the last object.  Doing this with each object.  This to me is a bug.  I'm open to any other logical explanations for this behaviour.
    Wednesday, April 19, 2017 12:34 AM
  • Can we see your code?
    Wednesday, April 19, 2017 12:46 AM
  • The bones of it, I have replaced names with generic ones, the syntax is what is important.  

    using (SqlConnection Connection = new SqlConnection(connectionString))
                {
                    string selectFrom= "select * from Table";
                    SqlCommand Query = new SqlCommand(selectFrom, Connection);
                    Connection.Open();
                    SqlDataReader Reader = Query.ExecuteReader();
                    try
                    {
                        while (Reader.Read())
                        {
                            Object object = new Object();
                            object.SetMem1(Reader["Column1"].ToString());
                            object.SetMem2(Reader["Column2"].ToString());
                            object.SetMen3(Reader["Column3"].ToString());
                            object.SetMem4(int.Parse(Reader["Column4"].ToString()));
                            object.SetMem5(Reader["Column5"].ToString());
                            object.SetMem6(Reader["Column6"].ToString());
                            object.SetMem7(Reader["Column7"].ToString());
                            object.SetMem8(Reader["Column8"].ToString());
                            List.Add(object);
                        }
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }
                    Reader.Close();
                    Connection.Close();
                }

    The code executes without errors.  To compound the behaviour I described, I'm displaying the object list in a datagridview (see below).  Its creating a row for each object, but its only displaying the last object, in the last row. Empty rows except the last.  What I think is happening (guessing) is VS is only reading/seeing the last object in the list, and displaying that objects data regardless of which actual object the user is examining in the list.  So if its only seeing the last object, that could/might account for the empty rows(objects).   

    Again, I'm open to any other logical/rational explanations.

    Richard.

    Wednesday, April 19, 2017 1:36 AM
  • Hi RichardDunneBSc,

    Since the issue is more related to Visual C#, I’m moving this thread to Visual C# forum for better response.

    Thank you for your understanding and support.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 19, 2017 7:19 AM
  • Here is a suggested method to try (of course you will need to change the fields, table and server stuff). The idea is to approach this slightly different. 

    using System;
    using System.Windows.Forms;
    
    namespace WindowsFormsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                var ops = new DataOperations();
                dataGridView1.DataSource = ops.Demo();
            }
        }
    }
    
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    
    public class DataOperations
    {
        private string Server = "Your Server name";
        private string Catalog = "Your catalog";
        /// <summary>
        /// Connection string for connecting to the database
        /// </summary>
        private string ConnectionString = "";
    
    
        public DataOperations()
        {
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True";
        }
    
        public List<Sample> Demo()
        {
            var results = new List<Sample>();
    
            using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
            {
                using (SqlCommand cmd = new SqlCommand { Connection = cn })
                {
                    cmd.CommandText = "SELECT Identifier,RoomNumber,CheckIn,CheckOut FROM Table1";
    
                    cn.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            results.Add(new Sample()
                            {
                                Identifier = reader.GetInt32(0),
                                RoomNumber = reader.GetString(1),
                                CheckIn = reader.GetDateTime(2),
                                CheckOut = reader.GetDateTime(3)
                            });
                        }
                    }
                }
            }
    
            return results;
        }
    
    }
    public class Sample
    {
        public long Identifier { get; set; }
        public string RoomNumber { get; set; }
        public DateTime CheckIn { get; set; }
        public DateTime CheckOut { get; set; }
    
    }
    
    

    Results from running the code in the IDE and also the SQL-Server table

    Form at run time


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, April 19, 2017 10:43 PM
    Moderator