Sending an Email while reading rows from database

Question

I'm writing a script that will output data using Console.WriteLine as well as send en email out for each "set" of this data. My issue is that in using:

for (int i = 0; i < reader.FieldCount; i++)
                            {
                                Console.WriteLine(reader.GetValue(i));
                            }

PROBLEM: I'm unable to actually save the values so that I can also use them in my email script that is below.

I think that I need to save these values in a list of some sort but I'm unsure if that's the appropriate way to go with this.

        try
        {
            cnn.Open();
            //Console.WriteLine("Connection Open!");

            string sql = "SQLQUERY";
            using (var command = new SqlCommand(sql, cnn))
            {
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            Console.WriteLine(reader.GetValue(i));
                        }

                        SmtpClient client = new SmtpClient();
                        client.Port = 587;
                        client.Host = "HOST";
                        client.EnableSsl = true;
                        client.Timeout = 10000;
                        client.DeliveryMethod = SmtpDeliveryMethod.Network;
                        client.UseDefaultCredentials = false;
                        client.Credentials = new System.Net.NetworkCredential("EMAIL", "PW");

                        MailMessage mm = new MailMessage("donotreply@domain.com", "EMAIL", "TEST", "TEST ");
                        mm.BodyEncoding = UTF8Encoding.UTF8;
                        mm.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;

                        client.Send(mm);

                        Console.WriteLine();
                    }
                }
            }

            Console.WriteLine("A ticket has been generated for the devices listed above.");


            cnn.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Can not open connection!   " + ex);
        }
    }

Answer 1

You can always use a List<List<object>> and store each row in a List<object> of fields.

// At the top:
List<List<object>> data = new List<List<object>>();

// Lower:
while (reader.Read())
{
    List<object> row = new List<object>();
    for (int i = 0; i < reader.FieldCount; i++)
    {
        object val = reader.GetValue(i);
        Console.WriteLine(val);
        row.Add(val);
    }
    data.Add(row);

    // You can now use the row variable here if you want, or you can
    //    wait until after looping through the reader and send the
    //    emails at the end, using the data variable instead.

    Console.WriteLine();
}