Returning multiple results from database query in C#

Question

I have the following code on my C# WinForm, which builds a query and passes it and a parameter to a function that queries the PostgreSQL database:

DataTable dt = ((DataTable)dgvCategories.DataSource);

List<string> validationInfoResult = new List<string>();
for (intRowIndex = 0; intRowIndex <= dt.Rows.Count - 1; intRowIndex++)
{
    strNewValueCat = dt.Rows[intRowIndex][1].ToString();
    db getValidationInfo = new db();

    strGetValidationInfoQuery = "SELECT validationdb, validationtable, validationfield, validationfield2, validationvalue2" +
                                " FROM masterfiles.categories" +
                                " WHERE category = @category";
    validationInfoResult = getValidationInfo.GetValidationInfo(strGetValidationInfoQuery, strNewValueCat);
}

As you can see, I'm iterating through a DGV's DataTable, grabbing a value from a particular cell in the current row and passing that as a parmaeter to the db query. I need to return values from 5 fields and my intention is to put the query result into a List<> and then get each of the List<> items into their own variable for use in the form.

My db class function looks like this:

public List<string> GetValidationInfo(string strQuery, string strCategory)
{
    List<string> strResult = new List<string>();

    using (NpgsqlConnection conn = new NpgsqlConnection(connString))
    using (NpgsqlCommand cmd = new NpgsqlCommand(strQuery, conn))
    {
        cmd.Parameters.Add(new NpgsqlParameter("category", strCategory));
        conn.Open();

        using (NpgsqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                strResult.Add(reader.GetString(0));
                Console.WriteLine("reader: " + reader.GetString(0));
            }
        }
    }

    return strResult;
}

I built the above code based on this SO question's answer.

When I run it, Console.WriteLine() only ever outputs the first query field, validationdb and then moves on to the next DataTable row in the calling for loop. Stepping through the code at runtime, after the first time it hits Console.WriteLine() it goes back to while (reader.Read()) and then steps out of the while loop and returns to the calling function.

What is wrong in my loop that it's only putting the first query field result into the List<>?

You are only iterating through each ROW returned, not each COLUMN(field) — Kevin, Apr 08 '20 at 16:54
@Kevin, I only need the value in one cell at the same index of each row to pass to the query each time. — marky, Apr 08 '20 at 16:59
@AnoopRDesai, How do I return all five query result fields to the calling function, then? — marky, Apr 08 '20 at 17:00
You could use a for loop within the while that goes from 0 to `reader.FieldCount`. — Anoop R Desai, Apr 08 '20 at 17:01
@mark I was talking about the same thing Anoop was. You were only outputting field 0 — Kevin, Apr 08 '20 at 17:15
@Kevin - okay. sorry. I misunderstood. Thanks for clarifying. — marky, Apr 08 '20 at 17:23

score 0 · Answer 1 · answered Apr 08 '20 at 17:02

Following code would output all fields:

while (reader.Read())
{
    // strResult.Add(reader.GetString(0));
    Console.WriteLine("Column 1: " + reader.GetString(0));
    Console.WriteLine("Column 2: " + reader.GetString(1));
    Console.WriteLine("Column 3: " + reader.GetString(2));
    Console.WriteLine("Column 4: " + reader.GetString(3));
    Console.WriteLine("Column 5: " + reader.GetString(4));
}

So you need to modify your output List<string> to accommodate 5 strings per item.

Anoop R Desai · Answer 2 · 2020-04-08T17:12:01.137

The problem is GetString(0) which fetches only the first column of a row (index 0). One way of getting around this would be by looping through the columns. From the docs, FieldCount can be used to fetch the number of columns:

while (reader.Read())
{
    for(int i = 0; i < reader.FieldCount; i++)
    {
        strResult.Add(reader.GetString(i));
        Console.WriteLine("reader: " + reader.GetString(i));
    }
}

Returning multiple results from database query in C#

2 Answers2