How do I read SQL row values of a column into an array?

Question

I have created a method that will get data from a SQL table and store the columns of data each in their own array. Right now, when working through the debugger, what I notice is that when I am assigning values to these arrays, they are null values.

I did check to see if my query returns values in SSMS and it indeed does. So null values should not be expected.

Here is the code to the method:

public static CommentsPageData getComments(string wcNum)
    {
        string[] prodTimeArray = new string[24];
        string[] crewsArray = new string[24];
        string[] commentsArray = new string[24];
        string[] redTimeArray = new string[24];
        string[] greenTimeArray = new string[24];

        string commandSql = "SELECT TOP 24 Production_Time, Crew, Red_Time, Green_Time, Comment FROM ************ WHERE Work_Center = @wc ORDER BY Production_Time DESC";

        SqlConnection con = new SqlConnection("Data Source=*******;Initial Catalog=********;Integrated Security=True");
        con.Open();

        SqlCommand cmd = new SqlCommand(commandSql, con);
        cmd.Parameters.AddWithValue("wc", wcNum);

        CommentsPageData commPageData = new CommentsPageData();

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    prodTimeArray.Append(reader["Production_Time"].ToString());
                    crewsArray.Append(reader["Crew"].ToString());
                    redTimeArray.Append(reader["Red_Time"].ToString());
                    greenTimeArray.Append(reader["Green_Time"].ToString());
                    commentsArray.Append(reader["Comment"].ToString());
                }
            }
            else
            {
                Console.WriteLine("No rows found");
            }
            reader.Close();
        }

        commPageData.ProdTimes = prodTimeArray;
        commPageData.Crews = crewsArray;
        commPageData.GreenTime = greenTimeArray;
        commPageData.RedTime = redTimeArray;
        commPageData.Comments = commentsArray;

        con.Close();

        return commPageData;
    }

Long story short, I have created a Class (CommentsPageData) which has an array for each column I'm returning 24 values from. However... The problem is in the while(reader.Read()){} section of the method. I can see it assigning values, but it is just assigning null values.

How can I actually get the values and assign them to my array correctly?

You're calling Enumerable.Append, which does not do what you think it does (in particular, it doesn't modify its input). C# arrays cannot change in size once declared. Your code would work (if inelegantly) if you used List<string> and .Add instead. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 7, 2023 at 14:38
@JeroenMostert why would it be considered inelegant? Genuinely curious and not refuting. — zackychan97
– zackychan97, Commented Feb 7, 2023 at 14:40
Mostly, this kind of manual boilerplate where you read a DataReader yourself is not necessary in favor of using something like Dapper, which cuts a lot of the boilerplate. If you do want to process a reader manually, then you don't need things like reader.HasRows (the loop already takes care of that), and reader["ColumnName"].ToString() is an inefficient way of working, since it looks up the column ordinal by name on every call and hides any issue with column types. Fixing this makes the code more tedious, though. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 7, 2023 at 14:45
@stuartd I can guarantee I will never need an array greater than 24. This method is ultimately just returning data for records submitted in the past 24 hours (1 per hour). — zackychan97
– zackychan97, Commented Feb 7, 2023 at 14:48

user21078216 · Accepted Answer · 2023-02-08 18:17:56Z

Its just like Jeroen Mostert said, Arrays in C# do not change in size after they are declared. You have declared your arrays inside the class which means the size you have initialized your arrays with is 0 which means no matter how many times you try to append elements to them, the arrays will be null. Use System.Collections.Generic List<Type> to hold your data as it can be updated to hold more elements.


    public static CommentsPageData getComments(string wcNum)
        {
            List<string> prodTimeArray = new List<string>();
            List<string> crewsArray = new List<string>();
            List<string> commentsArray = new List<string>();
            List<string> redTimeArray = new List<string>();
            List<string> greenTimeArray = new List<string>();

            string commandSql = "SELECT TOP 24 Production_Time, Crew, Red_Time, Green_Time, Comment FROM ************ WHERE Work_Center = @wc ORDER BY Production_Time DESC";

            SqlConnection con = new SqlConnection("Data Source=*******;Initial Catalog=********;Integrated Security=True");
            con.Open();

            SqlCommand cmd = new SqlCommand(commandSql, con);
            cmd.Parameters.AddWithValue("wc", wcNum);

            CommentsPageData commPageData = new CommentsPageData();

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        prodTimeArray.Add(reader["Production_Time"].ToString());
                        crewsArray.Add(reader["Crew"].ToString());
                        redTimeArray.Add(reader["Red_Time"].ToString());
                        greenTimeArray.Add(reader["Green_Time"].ToString());
                        commentsArray.Add(reader["Comment"].ToString());
                    }
                }
                else
                {
                    Console.WriteLine("No rows found");
                }
                reader.Close();
            }

            commPageData.ProdTimes = prodTimeArray.ToArray();
            commPageData.Crews = crewsArray.ToArray();
            commPageData.GreenTime = greenTimeArray.ToArray();
            commPageData.RedTime = redTimeArray.ToArray();
            commPageData.Comments = commentsArray.ToArray();

            con.Close();

            return commPageData;
        }

while your answer is acceptable, I'd like to add that you should probably rename the variables named "...Array" to avoid any confusion. Also looks like a Model would additionally improve the answer.

Serge · Accepted Answer · 2023-02-08 19:18:41Z

I would use this code

    reader = cmd.ExecuteReader()
    var dt=new DataTable;
    dt.Load(reader);

    Dictionary<string, string[]> dict = new();

    for (var i = 0; i < dt.Rows.Count; i++)
    {
        for (var j = 0; j < dt.Rows[i].ItemArray.Length; j++)
        {
            if (!dict.ContainsKey(dt.Columns[j].ColumnName)) 
                    dict.Add(dt.Columns[j].ColumnName, new string[dt.Rows.Count]);
            dict[dt.Columns[j].ColumnName][i] = dt.Rows[i].ItemArray[j].ToString();
        }
    }

and just use the dictionary

but if you want a name for each array

    string[] prodTimeArray;
    string[] crewsArray;
    string[] commentsArray;
    ....

    prodTimeArray = dict["Production_Time"];
    crewsArray = dict["Crew"];
    commentsArray = dict["Comment"];
    ....

AngryToddlers · Accepted Answer · 2023-02-07 14:36:41Z

0

If you really need to have this end result, I'd recommend just making an object representing a row in the table, use Dapper to return the rows into an IEnumerable of the objects, then to a LINQ select for each column into an array

For Example:

var results = await con.QueryAsync<MyRowObject>(commandSql, new {"wc"}, commandType: Text);

var productionTimeArray =  results.Select(x => x.ProductionTime).ToArray();

answered Feb 7, 2023 at 14:36

AngryToddlers

951 gold badge1 silver badge9 bronze badges

Collectives™ on Stack Overflow

How do I read SQL row values of a column into an array?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related