How to use SQL Data Reader

Yep - with Microsoft.Data.SqlClient in a Visual Studio 2022 C# console application, you can execute a SELECT query that returns three rows and use SqlDataReader to read each row individually. You do not need to know the number of rows in advance; reader.Read() advances to the next row and returns false when there are no more rows.

Try the following:

using Microsoft.Data.SqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString =
            "Server=YOUR_SERVER;Database=YOUR_DATABASE;" +
            "Integrated Security=True;TrustServerCertificate=True;";

        string sql = @"
            SELECT Code
            FROM (VALUES
                ('ABC'),
                ('DEF'),
                ('GHI')
            ) AS Codes(Code);";

        using SqlConnection connection =
            new SqlConnection(connectionString);

        using SqlCommand command =
            new SqlCommand(sql, connection);

        connection.Open();

        using SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            string code = reader.GetString(0);
            Console.WriteLine(code);
        }
    }
}

The output should be:

ABC
DEF
GHI

In this example, the VALUES clause creates three sample rows, each containing one code. Replace that query with your actual SELECT statement and update the connection string to match your environment. reader.GetString(0) retrieves the first column, using a zero-based column index. If your column can contain NULL, check reader.IsDBNull(0) before calling GetString(0).

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Hi @Corey Fleig , thank you for your question.

I have at least 2 interpretations of what you mean by "each row with a code" here: you want 3 query commands each returning a row; or you want the rows to return one additional column value, a "code".

In either case, you simply want to import the Microsoft.Data.SqlClient library and use its SqlDataReader class to declare a reader object. You can first establish and open your connection, for example:

using Microsoft.Data.SqlClient;
string connectionString = "your connection string";
using SqlConnection connection = new(connectionString);
connection.Open();

Then use either of the following examples:

string[] queries =
{
    "SELECT 'ABC' AS Code;",
    "SELECT 'DEF' AS Code;",
    "SELECT 'XYZ' AS Code;"
};
foreach (string query in queries)
{
    using SqlCommand command = new(query, connection);
    using SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        string code = reader.GetString(reader.GetOrdinal("Code"));
        Console.WriteLine(code);
    }
}

(if you mean "3 query commands, each returning a row")

string query = """
    SELECT Code
    FROM (VALUES
        ('ABC'),
        ('DEF'),
        ('XYZ')
    ) AS Codes(Code);
    """;
using SqlCommand command = new(query, connection);
using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    string code = reader.GetString(reader.GetOrdinal("Code"));
    Console.WriteLine(code);
}

(if you mean "one query returns 3 rows, each with a code")

Please let me know if this is what you need or clarify more information otherwise so I can better help you. Also, feel free to ask if you have any further question.

If my response provides you what you need, I would greatly appreciate it if you could follow this guide for your confirmation. Thank you.