SqlDataReader Object: Used to retrieve a read-only, forward-only stream of data from a database. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. Using the DataReader can increase application performance both by retrieving data as soon as it is available, and (by default) storing only one row at a time in memory, reducing system overhead.
How to Use SqlDataReader:
To create a SqlDataReader, we must call the ExecuteReader method of the SqlCommand object, instead of directly using a constructor.
While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.
Changes made to a result set by another process or thread while data is being read may be visible to the user of the SqlDataReader. However, the precise behavior is timing dependent.
IsClose and RecordAffected are the only properties that you can call after the SqlDataReader is closed. Although the RecordAffected property may be accessed while the SqlDataReader exists, always call Close before returning the value of RecordAffected to guarantee an accurate return value.
Tip:
For optimal performance, SqlDataReader avoids creating unnecessary objects or making unnecessary copies of data. Therefore, multiple calls to methods such as GetValue return a reference to the same object. Use caution if you are modifying the underlying value of the objects returned by methods such as GetValue.
e.g.
string queryString = "SELECT OrderID, CustomerID FROM Orders";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Call Read before accessing data.
while (reader.Read())
{
Console.WriteLine(String.Format("{0}, {1}",
reader[0], reader[1]));
//or we can use we have to cast into proper type. IsDBNull(ordinal position of the column) is used to check whether the column is null.
If(reader.IsDBNull(0))
long orderID = Convert.ToInt64(reader[“OrderID”]);
long customerID = Convert.ToInt64(reader[“CustomerID”]);
}
// Call Close when done reading.
reader.Close();
}
Summary:
SqlDataReader is read-only forward only way to access the database in the connected way. It is the fasted way to access the database. It is GetValue method for each .NET provided basic types (such as int, long, string etc). IsDBNull is used to check in any column value is System.DBNull.