DataReader in ADO.NET with C#

A DataReader in ADO.NET is a data retrieval component used in C# to efficiently read data from a database. It provides a forward-only, read-only access to the result set returned by a database query. The key advantage of using a DataReader is that it allows you to retrieve data from the database one row at a time, which can be very efficient for processing large result sets without loading the entire dataset into memory.

Here's a simple C# example of how to use a DataReader to fetch data from a SQL Server database and display it:

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

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            string sqlQuery = "SELECT FirstName, LastName FROM Customers";
            using (SqlCommand command = new SqlCommand(sqlQuery, connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string firstName = reader["FirstName"].ToString();
                        string lastName = reader["LastName"].ToString();
                        
                        Console.WriteLine($"First Name: {firstName}, Last Name: {lastName}");
                    }
                }
            }
        }
    }
}
    

Explanation of the code:

1. We start by importing the necessary namespaces for ADO.NET components.
2. We define a connection string, which contains the database server, database name, username, and password.
3. Inside a using block, we create a SqlConnection object and open the database connection.
4. We define a SQL query to select data from a "Customers" table.
5. Next, we create a SqlCommand object with the SQL query and the database connection.
6. Within another using block, we execute the command using ExecuteReader(), which returns a DataReader object.
7. Inside a while loop, we use the Read() method of the DataReader to iterate through the result set row by row.
8. We retrieve data from the DataReader using the column names (e.g., "FirstName" and "LastName") and display it in the console.
9. The using blocks ensure that resources are properly disposed of when we're done with them.

Output

First Name: John, Last Name: Doe
First Name: Jane, Last Name: Smith
First Name: Bob, Last Name: Johnson

This code connects to a SQL Server database, fetches data from the "Customers" table, and displays the first name and last name of each customer. You can modify the SQL query and connection string to suit your specific database and table.