none
Oracle SELECT statement in C# returning only first row multiple times RRS feed

  • Question

  • -I have a simple select statement (with a join) of two tables.
    -While the reader is reading this, I'm creating a new instance, and then calling my classes that will contain the information from each of my table columns.
    -Then, I'm adding this instance at the end of the list.

    BUT after weeks of debugging, I've realized that for some reason, a new instance of 'd' in this case, isn't being created. So, each time it goes through retrieving the information from the classes, it simultaneously replaces the previous array in the list. This results in the same row being produced each time. Here is my code.

    Class

    public List<Donor> Get()
            {
                //Define fields
                List<Donor> donors = new List<Donor>();
                string query;
                OracleCommand cmd;
                OracleDataReader reader;
    
                query = "SELECT * FROM ldhc_accounts l JOIN donors d ON l.donor_id = d.id";
                conn.Open();
                cmd = new OracleCommand(query, conn);
                reader = cmd.ExecuteReader();
    
                while (reader.Read())
                {
                    Donor d = new Donor();
                    {
                        d.DonorId = Convert.ToInt32(reader["id"]);
                        d.FName = reader["fname"].ToString();
                        donors.Add(d);
                    }
    
                }
    
                conn.Close();
    
                return donors;
            }


    C#

    protected void get_donors_btn_Click(object sender, EventArgs e)
            {
                List<Donor> donors = a_oracle.Get();
                foreach(Donor item in donors)
                {
                    get_donors.InnerHtml += "<li>"
                        + item.DonorId + " " 
                        + item.FName + " "
                        + "</li>";
                }
            }

    What I have tried:

    -I've tried to rearrange where my new instance is created, I've triple checked all my other code, and have finally realized that there is something here that is wrong.

    -If I create a select statement and include a WHERE clause, it works perfectly and returns only a single row that I need.

    -I've removed some other columns I'm trying to retrieve to make this code a little cleaner, but when I run this select statement in SQL developer, it works perfectly to return 3 distinct rows.

    -I'm stumped. Stack Overflow is stumped (or it's too long winded of a question). 

    -Let me know if there's any other information you need.

    There are some more details in my previous post: 

    https://stackoverflow.com/questions/48122615/oracle-select-statement-in-c-sharp-returning-only-first-row-multiple-times

    Monday, January 22, 2018 10:23 PM

All replies

  • Your object initialization on Doner d is not correct.

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/object-and-collection-initializers

    public StudentViewModels GetStudents()
            {
                var dtos = studapi.GetStudents();
               
                var vm = new StudentViewModels {Students = new List<StudentViewModels.Student>()};
    
                foreach (var dto in dtos)
                {
                    var student = new StudentViewModels.Student
                    {
                        StudentID = dto.StudentID,
                        LastName = dto.LastName,
                        FirstName = dto.FirstName,
                        EnrollmentDate = dto.EnrollmentDate
                    };
    
                    vm.Students.Add(student);
                }
    
                return vm;
            }

    Monday, January 22, 2018 11:53 PM
  • Temporarily comment the Get function and try another one:

    public List<Donor> Get()

    {

        List<Donor> donors = new List<Donor>();

     

        for( int i = 1; i <= 3; ++i )

        {

            Donor d = new Donor();

            {

                d.DonorId = i;

                d.FName = "Donor " + i;

                donors.Add( d );

            }

        }

     

        return donors;

    }

    Do you see different lines in this case?

    Note that in case of certain JOIN and data it is possible to have some repeating values.

    Tuesday, January 23, 2018 8:04 AM