How to use SQL Data Reader

Question

I'd like to perform a select that returns 3 rows, each row with a code, and then use a sql data reader in C#. Do you have a snippet that does that? Im not sure how to submit my code example without this app blowing up on me! I'm using VS 2022, and a console app, with Microsoft.Data.SqlClient 5.1.3 or something around that version.

Answer 1

Use SqlCommand.ExecuteReader() and call Read() in a loop; each successful call advances the SqlDataReader to the next returned row.^1^

using Microsoft.Data.SqlClient;

string connectionString =
    "Server=YOUR_SERVER;Database=YOUR_DATABASE;Integrated Security=True;TrustServerCertificate=True;";

const string sql = """
    SELECT TOP (3) Code
    FROM dbo.YourTable
    ORDER BY Code;
    """;

using var connection = new SqlConnection(connectionString);
using var command = new SqlCommand(sql, connection);

connection.Open();

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

Replace:

• YOUR_SERVER and YOUR_DATABASE with your SQL Server details.
• dbo.YourTable with the table name.
• Code with the actual column name.
• The connection string with your authentication settings if you are not using Windows authentication.

GetString(0) means “read the first selected column as a string.” If Code is an integer, use reader.GetInt32(0) instead. Typed accessors reduce unnecessary type conversion.^1^

The reader starts before the first row, so Read() must be called before accessing column values. It returns false when there are no more rows.^2^

The using declarations dispose the reader, command, and connection even if an exception occurs. Do not execute another command on the same connection while this reader remains open; the connection is occupied by that reader until it is closed.^1^

For posting C# in Q&A, place three backticks followed by csharp before the code and three backticks after it, as shown above.

---

References

1. Retrieve data by a DataReader
2. SqlDataReader.Read Method-Definition

Answer 2

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