skip to Main Content

I have a model named StorePage that has 2 properties that can contain multiple attribute values, I declared them as arrays to later on a method in the controller iterate through a data reader, get the values from the database/ table column put them in these arrays and make them accessible to my front end app.

public class StorePage
{
    public int ActionTypeID { get; set; }
    public int ResultTypeID { get; set; }
    public string ResultMessage { get; set; }
    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; }

    // these are the arrays
    public int[] SPAttributeRefID { get; set; }
    public int[] AttributeID { get; set; }

}

// This below is the method that gets the values from the database.

    public StorePage GetPage(int StorePageID, int SPPreambleID)
    {
        StorePage storepage = new StorePage();
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DEV_BI01_LVT"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("mn_StorePage_GetPage", con))
            {
                cmd.Parameters.AddWithValue("@StorePageID", SqlDbType.Int).Value = StorePageID;

                if (SPPreambleID != -1)                    
                {
                    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);

                        for (int i = 0; i <= 3; i++)
                        {
                            storepage.SPAttributeRefID = reader.GetInt32(11);
                        }

                    }
            }
        }
        return storepage;
    }

The line within the For loop is giving me an error:

Cannot implicitly convert type ‘int’ to ‘int[]’

storepage.SPAttributeRefID = reader.GetInt32(11);

Is it possible someone can try to help me resolve this problem? I am probably missing a cast or perhaps there is a better way to get and store the values. The relationship between the page and the attribute values is of one to many. A page can have multiple attributes in my case this are instruments, and styles, and scoring.

Here is an example of what I am trying to retrieve:

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

The last column is the attributes column. You can see these are 3 different values pertaining to a single page record.

For your help, thanks in advance.

2

Answers


  1. The error is self-explanatory. The property SPAttributeRefID is an int[]:

    public int[] SPAttributeRefID { get; set; }
    

    The result of reader.GetInt32(11); is an int.

    If you want to fill that array with the value then:

    storepage.SPAttributeRefID = new int[] { reader.GetInt32(11) };
    

    By this code:

    for (int i = 0; i <= 3; i++)
    {
        storepage.SPAttributeRefID = reader.GetInt32(11);
    }
    

    I think you want to fill the array with 3 items from different columns. You can do:

    storepage.SPAttributeRefID = new int[] 
    {  
        reader.GetInt32(11),  
        reader.GetInt32(12),  
        reader.GetInt32(13)
    };
    
    //Or:
    storepage.SPAttributeRefID = Enumerable.Range(11,3)
                                           .Select(x => reader.GetInt32(x));
    

    After your update: What you should do is use the first suggestion I gave and then later on use Linq to GroupBy the results. Each iteration of the while loop processes 1 record and what you are trying to achieve is between records. Another option is to use linq to entities via some ORM and to do this GroupBy in the database itself.

    Login or Signup to reply.
  2. You could do it this way. This assumes that the last 2 columns (12,13) are the items that belong to the arrays. Note that you now have a list instead of an array as these are easier to grow where as an array has a fixed size.

    public class StorePage
    {
        public StorePage(){
            SPAttributeRefID = new List<int>();
            AttributeID = new List<int>();
        }
       // these are now Lists
        public List<int> SPAttributeRefID { get; set; }
        public List<int> AttributeID { get; set; }
    
    }
    
    public StorePage GetPage(int StorePageID, int SPPreambleID)
    {
        StorePage storepage = null;
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DEV_BI01_LVT"].ConnectionString))
        using (SqlCommand cmd = new SqlCommand("mn_StorePage_GetPage", con))
        {
            cmd.Parameters.AddWithValue("@StorePageID", SqlDbType.Int).Value = StorePageID;
    
            if (SPPreambleID != -1)                    
            {
                cmd.Parameters.AddWithValue("@SPPreambleID", SqlDbType.Int).Value = SPPreambleID;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
    
            using(SqlDataReader reader = cmd.ExecuteReader())
            while (reader.Read())
            {
                // create a new instance if not created yet
                if(storepage == null)
                {
                    storepage = new StorePage();
                    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);
                }
                // only read the columns 12,13 and add each respective member to the corresponding list
                if(!reader.IsDBNull(11))
                    storepage.SPAttributeRefID.Add(reader.GetInt32(11));
                if(!reader.IsDBNull(12))
                    storepage.AttributeID.Add(reader.GetInt32(12));
            }
        }
        return storepage;
    }
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search