ADO.NET Tutorialprovides basic and advanced concepts of C# for beginners and professionals.

  1. Home
  2. ADO.NET Tutorial

Read data using ADO.NET with a SqlDataReader

Back to: ADO.NET Tutorial

Using SqlDataReader in ADO.NET is a faster, forward-only way to read data directly from a database. Unlike the DataSet and DataAdapter, which work in a disconnected manner, the SqlDataReader requires an open connection to the database while reading data.

Steps to Read Data Using SqlDataReader

  1. Establish a Database Connection: Use a SqlConnection object to connect to the database.

  2. Create a Command: Use a SqlCommand to define the SQL query you want to execute.

  3. Execute the Reader: Use the ExecuteReader method of the SqlCommand to retrieve a SqlDataReader.

  4. Read Data: Use the Read method of SqlDataReader to loop through the rows of data.

  5. Close the Reader and Connection: Always close the SqlDataReader and the SqlConnection when done.

Example Code to Read Data Using SqlDataReader

using System;
using System.Data.SqlClient;
class Program { static void Main()
{
// Step 1: Define the connection string
string connectionString = "your_connection_string_here";
// Step 2: Define the query to fetch data
string query = "SELECT ID, Name, Age FROM YourTable";
// Step 3: Use a SqlConnection and SqlCommand
using (SqlConnection connection = new SqlConnection(connectionString))
{
try {
connection.Open();
// Open the connection
using (SqlCommand command = new SqlCommand(query, connection))
{
// Step 4: Execute the SqlDataReader
using (SqlDataReader reader = command.ExecuteReader())
{
// Step 5: Read and display data
Console.WriteLine("ID\tName\tAge");
Console.WriteLine("---------------------------");
while (reader.Read())
{
int id = reader.GetInt32(0);
// Get the first column (ID)
string name = reader.GetString(1);
// Get the second column (Name)
int age = reader.GetInt32(2);
// Get the third column (Age)
Console.WriteLine($"{id}\t{name}\t{age}");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
}
// Connection automatically closed when exiting using block
}
}

Explanation of the Code

  1. Connection String: Replace "your_connection_string_here" with the actual connection string to your database.

  2. Query: Replace "SELECT ID, Name, Age FROM YourTable" with your SQL query.

  3. Opening the Connection: The connection.Open() method establishes a connection to the database.

  4. Executing the Reader: The command.ExecuteReader() method retrieves the SqlDataReader for reading the query results.

  5. Reading Data:

    • Use the reader.Read() method in a while loop to move through the rows of the result set.
    • Access column values by their ordinal index (e.g., GetInt32(0) for the first column) or by column name (e.g., reader["ColumnName"]).

  6. Closing Resources:

    • The using statements ensure that both the SqlDataReader and SqlConnection are automatically closed and disposed of after use.

Sample Output

For a table YourTable with columns ID, Name, and Age, the output might look like:

ID Name Age
----------------------
1 John 25
2 Alice 30
3 Mark 22 




Advantages of SqlDataReader


    
    
  • Performance: It is lightweight and optimized for fast, forward-only reading.
    • 
    
  • Simple: Ideal for reading and displaying data quickly.
    • 
    
  • Low Memory Overhead: Works directly with the connection and does not require additional memory for data storage.
    • 





Considerations


    
    
  • The SqlDataReader requires an open connection while reading data.
    • 
    
  • It cannot be used for random access; data must be processed sequentially.
    • 
    
  • Use this when you need to read and display data quickly without extensive manipulation.
    • 







                        

                               
                                                            
                        


                    



                

            

        

    






        
        
Scroll to Top