ADO - SqlDataReader Class

The ADO.NET SqlDataReader class in C# is a part of the ADO.NET library and is used to read data in a forward-only manner from a database. It provides a way to efficiently retrieve and process data returned by a SELECT query.

What is ADO.NET SqlDataReader Class in C#?

The ADO.NET SqlDataReader class allows you to efficiently read and process data returned from a database. It provides a fast, forward-only, read-only access to the result set. It is specifically designed for retrieving large amounts of data from the database in a streaming fashion.

ADO.NET SqlDataReader Class Properties in C#:

The SqlDataReader class has various properties that provide access to the columns and values of the current row in the result set. Some important properties include:

• FieldCount: Gets the number of columns in the result set.
• HasRows: Gets a value indicating whether the SqlDataReader contains one or more rows.
• IsClosed: Gets a value indicating whether the SqlDataReader is closed.
• Item[string]: Gets the value of a column specified by column name.
• Item[int]: Gets the value of a column specified by column index.

ADO.NET SqlDataReader Class Methods in C#:

The SqlDataReader class provides methods for reading data from the result set. Some important methods include:

• Read(): Advances the reader to the next record in the result set.
• GetDataTypeName(int): Returns the name of the data type of the specified column.
• GetFieldValue<T>(int): Returns the value of the specified column as the specified type.
• GetName(int): Returns the name of the specified column.
• GetOrdinal(string): Returns the index of the specified column by column name.

How to create an instance of the ADO.NET SqlDataReader class in C#?

To create an instance of the SqlDataReader class, you need to call the ExecuteReader() method of a SqlCommand object. This method returns a SqlDataReader object that you can use to read the data from the result set.

SqlDataReader reader = command.ExecuteReader();

Example to understand the C# SqlDataReader Object in ADO.NET:

Here's an example that demonstrates how to use the SqlDataReader class to read data from a result set:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    string query = "SELECT Id, Name, Age FROM YourTableName";
    SqlCommand command = new SqlCommand(query, connection);

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

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            int id = reader.GetInt32(0);
            string name = reader.GetString(1);
            int age = reader.GetInt32(2);

            Console.WriteLine($"Id: {id}, Name: {name}, Age: {age}");
        }
    }
    else
    {
        Console.WriteLine("No rows found.");
    }

    reader.Close();
}

In the above code, make sure to replace "YourTableName" with the actual name of the table you want to retrieve data from. The example assumes that the query returns three columns: Id, Name, and Age.

Here's an explanation of the code:

We create a SqlConnection and SqlCommand objects as usual, providing the query and connection details.

After opening the connection and executing the query, we obtain a SqlDataReader object using the ExecuteReader() method.

We check if the SqlDataReader has rows using the HasRows property. If there are rows, we iterate over them using the Read() method.

Inside the loop, we use the appropriate GetXXX() methods (e.g., GetInt32(), GetString()) to retrieve values from the current row based on the column index.

We then process the retrieved data as needed.

Remember to close the SqlDataReader object and handle any exceptions that may occur during the reading process.

The SqlDataReader class provides an efficient and lightweight way to retrieve and process large result sets from a database in a forward-only manner, making it suitable for scenarios where you need to read and process data efficiently.