How to retrieve column with multiple values for single record (one to many)

[rafuria]

I need some help understanding how to code for the following scenario: A page can have multiple attributes, by attributes I mean an instrument, a style, and a scoring.

In the database this looks like:

23  trumpet seo 7   meta    26  trumpet.jpg /store/trumpet.jpg  blurb   3   footer  1   506
23  trumpet seo 7   meta    26  trumpet.jpg /store/trumpet.jpg  blurb   3   footer  2   183
23  trumpet seo 7   meta    26  trumpet.jpg /store/trumpet.jpg  blurb   3   footer  3   45

In the C# code this is my class StorePage:

 public class StorePage
    {
        public int StorePageID { get; set; }
        public int SPPreambleID { get; set; }
        public string Title { get; set; }
        public string SEOTitle { get; set; }
        public int ParentStorePageID { get; set; }
        public string Meta { get; set; }
        public string Image { get; set; }
        public string ImageLink { get; set; }
        public string Blurb { get; set; }
        public int RegionID { get; set; }
        public string Footer { get; set; }       


        public int[] SPAttributeRefID { get; set; }
        public int[] AttributeID { get; set; }
    }

Then to retrieve the StorePage record I have this method:

public StorePage GetPage(int StorePageID, int SPPreambleID)
        {
            StorePage storepage = new StorePage();
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("mn_StorePage_GetPage", con))
                {
                    cmd.Parameters.AddWithValue("@StorePageID", SqlDbType.Int).Value = StorePageID;
                    cmd.Parameters.AddWithValue("@SPPreambleID", SqlDbType.Int).Value = SPPreambleID;
                    cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        storepage.StorePageID = reader.GetInt32(0);
                        storepage.Title = (reader.IsDBNull(1)) ? string.Empty : reader.GetString(1);
                        storepage.SEOTitle = (reader.IsDBNull(2)) ? string.Empty : reader.GetString(2);
                        storepage.ParentStorePageID = (reader.IsDBNull(3)) ? -1 : reader.GetInt32(3);
                        storepage.Meta = (reader.IsDBNull(4)) ? string.Empty : reader.GetString(4);
                        storepage.SPPreambleID = (reader.IsDBNull(5)) ? -1 : reader.GetInt32(5);
                        storepage.Image = (reader.IsDBNull(6)) ? string.Empty : reader.GetString(6);
                        storepage.ImageLink = (reader.IsDBNull(7)) ? string.Empty : reader.GetString(7);
                        storepage.Blurb = (reader.IsDBNull(8)) ? string.Empty : reader.GetString(8);
                        storepage.RegionID = (reader.IsDBNull(9)) ? -1 : reader.GetInt32(9);
                        storepage.Footer = (reader.IsDBNull(10)) ? string.Empty : reader.GetString(10);
                    }
                }
            }
            return storepage;
        }

My question is, how can I add the 2 columns that have multiple values for a single page record in my method GetPage? These are 2 new values, I declared them as arrays since one page can have many of each but I am not sure this is the correct way. I have not added them to the method GetPage because I am not sure how to do this.


public int[] SPAttributeRefID { get; set; }
public int[] AttributeID { get; set; }

 

[jmcilhinney]

Your design needs an overhaul, both in the database and in the application. What you should have is two tables and two entity types, with a foreign key relation between the tables and one entity having a collection of the other.

With regards to the database, you should break those last two columns out into a separate table. That table would be StorePageAttribute and it would have StorePageAttributeID (or AttributeID if you prefer), StorePageAttributeRefID (or SPAttributeRefID if you prefer) and StorePageID columns, with the last column being a foreign key from the StorePage table. That way, the data you have shown would result in only one row in the StorePage table and three in the StorePageAttributeID.

In your C# code, you would have a StorePageAttribute class and then your StorePage class would a StorePageAttributes property (or Attributes if you prefer) of type List<StorePageAttribute>. When it comes to retrieving the data, you could either use two separate queries or you can use a query with two SELECT statements. If you go the latter route, you use the data reader to read the first result set, then you call NextResult and then read the second result set the same way.