C# DataReader: Read Query Results

In this tutorial, we'll demonstrate how to use the ADO.NET SqlDataReader to read query results from a SQL Server database in C#. We'll use a simple example of an Employee table in a database.

Set up the database

Create a new database and a table called 'Employees':

CREATE DATABASE EmployeeDB;

USE EmployeeDB;

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

Insert some sample data into the 'Employees' table:

INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Doe');
INSERT INTO Employees (FirstName, LastName) VALUES ('Jane', 'Smith');

Create a C# Console Application

Create a new C# Console Application project in Visual Studio and add the following namespaces:

using System;
using System.Data;
using System.Data.SqlClient;

Set up the connection string

Create a connection string with the necessary credentials to connect to your SQL Server instance:

string connectionString = "Server=localhost;Database=EmployeeDB;User Id=myUser;Password=myPassword;";
SqlConnection connection = new SqlConnection(connectionString);

Read query results using SqlDataReader

Create a method called ReadEmployees to read the data from the 'Employees' table using SqlDataReader:

static void ReadEmployees(SqlConnection connection)
{
    string selectQuery = "SELECT * FROM Employees";
    SqlCommand selectCommand = new SqlCommand(selectQuery, connection);

    connection.Open();
    SqlDataReader reader = selectCommand.ExecuteReader();

    while (reader.Read())
    {
        Console.WriteLine($"ID: {reader["EmployeeID"]}, Name: {reader["FirstName"]} {reader["LastName"]}");
    }
    reader.Close();
    connection.Close();
}

Test the SqlDataReader

In the Main method, call the ReadEmployees method to test the SqlDataReader:

static void Main(string[] args)
{
    SqlConnection connection = new SqlConnection(connectionString);
    ReadEmployees(connection);
    Console.ReadLine();
}

When you run the Console Application, you should see the list of employees displayed in the console.

In this tutorial, we've shown you how to use the SqlDataReader to read query results from a SQL Server database in C#. SqlDataReader provides a fast and efficient way to read data from a database, but it is a forward-only, read-only cursor. If you need to work with data in a more flexible way, you might want to consider using other ADO.NET components like DataSet and SqlDataAdapter.

C# DataReader Example:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // SQL query
            string query = "SELECT Column1, Column2 FROM TableName";

            // Create a SqlCommand
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                // Execute the query and obtain a SqlDataReader
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    // Read data using SqlDataReader
                    while (reader.Read())
                    {
                        // Access data using reader
                        string column1Value = reader["Column1"].ToString();
                        string column2Value = reader["Column2"].ToString();
                        Console.WriteLine($"Column1: {column1Value}, Column2: {column2Value}");
                    }
                }
            }

            connection.Close();
        }
    }
}

Handling NULL Values with DataReader in C#:

Check for NULL values using reader.IsDBNull() method.

if (!reader.IsDBNull(reader.GetOrdinal("ColumnName")))
{
    // Value is not NULL
    string value = reader["ColumnName"].ToString();
}
else
{
    // Value is NULL
}

C# DataReader Get Column Values:
- Access column values using reader["ColumnName"].ToString() or appropriate data type conversion.
```
string columnValue = reader["ColumnName"].ToString();
```

Reading Data from a Stored Procedure with DataReader in C#:

Execute a stored procedure using SqlCommand and read the results with SqlDataReader.

using (SqlCommand command = new SqlCommand("StoredProcedureName", connection))
{
    command.CommandType = CommandType.StoredProcedure;

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Access stored procedure results
        }
    }
}