Populating array from a database C#

Question

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnnFinalProject"].ToString());
SqlCommand cmd = new SqlCommand(@"SELECT c.partID, p.name, p.categoryID, p.price, p.image, p.subCategoryID, 
                                        (CASE WHEN categoryID = 1 THEN 'Y' ELSE 'N' END) AS casesYN,
                                        (CASE WHEN categoryID = 3 THEN 'Y' ELSE 'N' END) AS OSYN,
                                        (CASE WHEN categoryID = 7 THEN 'Y' ELSE 'N' END) AS HDDYN,
                                        (CASE WHEN subCategoryID = 1 THEN 'Y' ELSE 'N' END) AS powerYN,
                                        (CASE WHEN subCategoryID = 2 THEN 'Y' ELSE 'N' END) AS processorYN,
                                        (CASE WHEN subCategoryID = 3 THEN 'Y' ELSE 'N' END) AS moboYN,
                                        (CASE WHEN subCategoryID = 4 THEN 'Y' ELSE 'N' END) AS memoryYN,
                                        (CASE WHEN subCategoryID = 5 THEN 'Y' ELSE 'N' END) AS graphicsYN,
                                        (CASE WHEN subCategoryID = 6 THEN 'Y' ELSE 'N' END) AS opticalYN,
                                        (CASE WHEN subCategoryID = 7 THEN 'Y' ELSE 'N' END) AS soundYN
                                        FROM configuration c JOIN parts p ON p.partID = c.partID
                                        WHERE c.customID = @ID", conn);
      conn.Open();
      SqlDataReader dr = cmd.ExecuteReader();
      while (dr.Read())
      {
        string[] cases = { dr["partID"].ToString() }; 
      }

How can I insert into the cases array only the 'partID's that have 'casesYN' value set to Y, as it is now it would insert every 'partID' in the sql query.

TylerH · Accepted Answer · 2020-02-17 20:00:53Z

6

I assumed you don't want to change your SQL query.

Use List<string> (requires using System.Collections.Generic) instead of string[] because it will be easier to add new item into it:

var cases = new List<string>();
while (dr.Read())
{
    if(dr["casesYN"].ToString() == "Y")
        cases.Add(dr["partID"].ToString()); 
}

If you really need an array, you can always call ToArray() method after the loop is finished:

string[] casesArray = cases.ToArray();

But it will require using System.Linq at the top of your file.

edited Feb 17, 2020 at 20:00

TylerH

21.3k84 gold badges84 silver badges122 bronze badges

answered Apr 13, 2013 at 13:22

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Populating array from a database C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related