Populate array with numbers from database

Question

I'm trying to figure out the best way to populate an array with numerical values pulled from a database. This is the function I have set up so far:

    protected int[] GetBoxes()
    {
        int[] boxes;
        string query = "SELECT DISTINCT Length FROM Products INNER JOIN Baird_Ground_Boxes_Products ON uidBox = ProductId";
        using (SqlConnection cn = new SqlConnection(AbleConnectionStr))
        {
            SqlCommand cmd = new SqlCommand(query, cn);
            cmd.CommandType = CommandType.Text;
            cn.Open();
            using (IDataReader reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                { 
                     if (reader["Length"].ToString() !="")
                     {
                          //populate array with the length values returned
                     }
                }                     
            }
        }
        return boxes;
    }

There is never a single set of values that could be returned (could be 3, could be 2). I'm getting confused on how to populate the array, like I'm making it too difficult or something. Any suggestions would be helpful!

do you know how to access the fields of the reader .. for example reader["FieldName"].ToString() or var someInt = (int)reader["FieldName"], there are seriously 100's of examples online on how to do this.. — MethodMan
– MethodMan, Commented Oct 8, 2014 at 20:39

Will Marcouiller · Accepted Answer · 2014-10-08 20:45:10Z

2

At first sight, you may have multiple rows returned from your query.

select distinct Length 
    from Products 
        join Baird_Ground_Boxes_Products 
            on  uidBox = ProductId

So, you may as well read out all of your returned rows with a while loop.

var lengths = new List<string>();

if (reader.HasRows)
    while (reader.Read()) lengths.Add(reader.GetString(0));

return lengths.ToArray();

And I wonder whether your field Length is really a string. Perhaps should you consider using an integer, since lengths are generally expressed as numbers. If so, than wave the GetString method in favour of GetInt32, and change your type of list.

var length = new List<int>();
lengths.Add(reader.GetInt32(0));

answered Oct 8, 2014 at 20:45

Will Marcouiller

24.3k26 gold badges100 silver badges166 bronze badges

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

1 Comment

Tim Schmelter Over a year ago

+1 I think that it's already an int column since he's returning an int[]. I assume he wants to skip null-values with the string conversion. Of course reader.IsDBNull is the better choice.

Tim Schmelter · Accepted Answer · 2014-10-08 21:04:04Z

2

You should fill a List<T> and finally use it's ToArray method to create the array:

var lengths = new List<int>();
// ...

using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
    if(!reader.IsDBNull(0)) 
        lengths.Add(reader.GetInt32(0));
}

return lengths.ToArray();

I've also used reader.IsDBNull instead of reader["Length"].ToString() !="" to skip the nulls. You should consider to remove the nulls in the first place by using WHERE Length IS NOT NULL.

edited Oct 8, 2014 at 21:04

answered Oct 8, 2014 at 20:38

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

Collectives™ on Stack Overflow

Populate array with numbers from database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related