SQL Join to object assignment

I am very new to C# and just learning, I assume this is a pretty basic question but i need a bit of help.

I have a SQL join that I am putting in an object and passing to a viewModel but I seem to be having trouble when I change from select * to naming all the headers and assigning it to the object.

  Select a.Desc,a.ID, b.Desc, b.state  from a
  join a ON b.Id = a.Id

 
   using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = sqlCon;
                    sqlCon.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            someList.Add(new someModel
                            {
                                Desc_a = sdr["a.Desc"].ToString(),
                                Desc_b = sdr["b.Desc"].ToString(),
                                id_a = Convert.ToInt32(sdr["a.Id"]),
                                State = sdr["b.state"].ToString()


                            });

                        }
                        sqlCon.Close();


I seem to be getting "System.IndexOutOfRangeException: a.Desc" error. I assume Im not assigning the value from the sql into the object correctly but im not seeing why?

Thank you for your help
Justin HullAsked:
Who is Participating?
 
Nitin SontakkeConnect With a Mentor DeveloperCommented:
Furthermore, from performance perspective it had suggest you use:

  Desc_a = sdr.GetString(0),
  Desc_b = sdr.GetString(2),
  id_a = sdr.GetInt32(1),
  State = sdr.GetString(3)

Open in new window


This is the best approach to fetch data from recordset. Needless to mention, you will need to cater to NULL values as well with the help of IsDBNull function.
0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
first in your query, you need to give alias to your fields (because you have the same name twice):

 Select a.Desc AS DescA,a.ID, b.Desc as DescB, b.state  from a  join a ON b.Id = a.Id

Open in new window


Then you specify the alias in your code:
                                Desc_a = sdr["DescA"].ToString(),
                                Desc_b = sdr["DescB"].ToString(),
                                id_a = Convert.ToInt32(sdr["Id"]),
                                State = sdr["state"].ToString()

Open in new window

0
 
Justin HullAuthor Commented:
Thank you very much
0
 
Justin HullAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.