Copy multiple column values from SqlReader to array?

Question

How can I generate an array of string that will contain all the column info. this query will return a single row with multiple columns

var rowLines = new List<string>();

        try
        {
            using (SqlConnection connection = new SqlConnection(GetConnectionString()))
            {
                string query = "SELECT I1,I2,I3,I4,I5,I6,I7,I8,I9,I10,I11,I12,I13,I14,I15 FROM LABEL_OUT WHERE LABEL_NAME='" + labelName + "'";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    connection.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            rowLines.Add(reader[0].ToString());
                        }

                    }
                }
            }

        }
        catch (Exception ex)
        {
            System.Windows.MessageBox.Show(ex.Message);
        }

here rowLines will Contain all the column value such as I1,I2,.....I15

You should also put your `SqlConnection` and your `SqlCommand` [inside a `using` block](https://stackoverflow.com/q/23185990/107625) to ensure correct resource release. — Uwe Keim, Sep 04 '18 at 13:15
You're already adding the first column to your list. What is the issue? Add the other columns the same way. — , Sep 04 '18 at 13:16
You aren't handling your [IDisposable](https://learn.microsoft.com/en-us/dotnet/api/system.idisposable?view=netframework-4.7.2) objects, such as SqlConnection properly. You need to dispose of it in a finally block, or wrap it in a using statement to ensure it gets disposed properly. — mason, Sep 04 '18 at 13:18
Actually this should return your desired output. Maybe you meant that you want all the columns and this is returning many rows with a single column — Marco Salerno, Sep 04 '18 at 13:20
@DreamTeK the number of times `DataSet` is a *good* solution to any problem is *vanishingly* small... — Marc Gravell, Sep 04 '18 at 13:22

score 5 · Answer 1 · answered Sep 04 '18 at 13:22

Probably the easiest way to do this is to use DbDataReader.GetValues(object[]), which populates a pre-existing array with the values from each column:

var vals = new object[reader.FieldCount];
while (reader.Read())
{
    reader.GetValues(vals);
    // ... do something with the values
}

score 2 · Answer 2 · answered Sep 04 '18 at 13:21

If you are sure that you will take a single line you could loop on the reader using the FieldCount and add each element on a List<string>. Finally, you could just return it as an array.

var rowLines = new List<string>();

if (reader.Read())
{
   for (int i = 0; i < reader.FieldCount; i++)
   {
      rowLines.Add(reader.IsDBNull(i) ? string.Empty : reader[i].ToString());
   }
}

return rowLines.ToArray();

Copy multiple column values from SqlReader to array?

2 Answers2