Visual Basic .NET - ADO .Net - SqlDataReader

The SqlDataReader class in ADO.NET is used to read the data from a SQL Server database. It provides a forward-only, read-only stream of data from a SQL Server database.

Creation and Usage: To create a SqlDataReader object, you first need to execute a SQL command or stored procedure using the ExecuteReader method of the SqlCommand object. The ExecuteReader method returns a SqlDataReader object that can be used to retrieve data. You can then use the Read method of the SqlDataReader to move to the next row and read the data.

Reading Data: The SqlDataReader provides methods to retrieve data from the result set, such as GetString, GetInt32, GetDateTime, etc., corresponding to the data type of the column being read. These methods allow you to retrieve values from specific columns based on their index or name.

Null Values: The SqlDataReader handles null values gracefully. You can use the IsDBNull method to check if a column value is null before retrieving it. Alternatively, you can use the GetNullableXXX methods to retrieve nullable types from the SqlDataReader.

Field Access: The SqlDataReader allows access to the fields by using either the column index or the column name. You can use the GetOrdinal method to retrieve the index of a column by its name, and then use the index to access the field value.

Data Types: The SqlDataReader supports reading data of various data types, including string, integer, decimal, date/time, boolean, etc. It provides methods to retrieve values as the appropriate data type, ensuring type safety and compatibility.

Data Retrieval Efficiency: The SqlDataReader is designed for forward-only, read-only access, which makes it highly efficient for retrieving large result sets. It uses a stream-based approach and keeps the memory footprint low, allowing you to process data efficiently even when dealing with large datasets.

The following is an example of how to use the SqlDataReader class to read data from a SQL Server database in VB.NET:

Imports System.Data.SqlClient
' Create a SqlConnection object
Dim connectionString As String = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
Dim connection As New SqlConnection(connectionString)
' Create a SqlCommand object
Dim commandText As String = "SELECT * FROM Customers"
Dim command As New SqlCommand(commandText, connection)
' Open the connection
connection.Open()
' Execute the command and create a SqlDataReader object
Dim reader As SqlDataReader = command.ExecuteReader()
' Loop through the data and output to the console
While reader.Read()
    Console.WriteLine(reader("CustomerID").ToString() & " " & reader("CompanyName").ToString())
End While
' Close the reader and the connection
reader.Close()
connection.Close()

In the above example, we first create a SqlConnection object with the connection string to the database. We then create a SqlCommand object with the SQL query to retrieve data from the Customers table.

Next, we open the connection and execute the command with the ExecuteReader() method, which returns a SqlDataReader object. We then loop through the data using the Read() method of the SqlDataReader, which reads the next row of data. We access the values in each column by calling the Item property of the SqlDataReader object, passing the column name or index as an argument.

Finally, we close the reader and the connection using the Close() method.