Using DataReader

SqlDataReader objects provide an efficient means of reading data, however, they do not provide a means of writing data.

They are often described as accelerated firehose data streams. Data reads occur forward-only and sequentially. After a data read, data disappears unless saved. This design allows DataReader to deliver accelerated performance by avoiding the overhead of traversing and writing data. Employ it in situations requiring a single read to exploit its power. It also proves the best option in operations involving large data sets better left out of memory beyond a single call. Other options involve caching, an area discussed later in the guide.

CREATION

Instantiation differs slightly from other ADO.NET objects because it requires a call to ExecuteReader on a command object. The example below demonstrates instantiation: 

SqlDataReader rdr = cmd.ExecuteReader();

ExecuteReader returns the instance. It avoids the new keyword because it uses the connection and command objects needed for the DataReader operation.

DATA READS

DataReader returns data with a sequential stream, and extracts data from tables, row-by-row. After reading a row, the row disappears, and only creation of a new instance of DataReader allows access. A common way to read from the stream uses a while loop to iterate through rows. Review an example of this operation below: 

while (rdr.Read())
	{
		// Retrieve column results
		string teammbr = (string)rdr["EmployeeName"];
		string org = (string)rdr["OrganizationName"];
		string loc = (string)rdr["Region"];

		// Print the results
		Console.Write("{0,-15}", teammbr);
		Console.Write("{0,-10}", org);
		Console.Write("{0,-15}", loc);
		Console.WriteLine();
	}

MULTIPLE SET RETRIEVAL

DataReader provides the NextResult method for operating on multiple result sets by iterating through result sets in order. The example below demonstrates operating on the results of two SELECT statements: 

static void RetrieveMultis(SqlConnection connection)
{
	using (connection)
	{
		SqlCommand command = new SqlCommand(
			"SELECT GroupID, GroupName FROM dbo.Groups;" +
			"SELECT TeamMbrID, SurName FROM dbo.TeamMbrs",
			connection);
		connection.Open();

		SqlDataReader rdr = command.ExecuteReader();

		while (rdr.HasRows)
		{
			Console.WriteLine("\t{0}\t{1}", rdr.GetName(0),
				rdr.GetName(1));
			while (rdr.Read())
			{
				Console.WriteLine("\t{0}\t{1}", rdr.GetInt32(0),
					rdr.GetString(1));
			}
			rdr.NextResult();
		}
	}
}

CLOSING DATAREADER

Always close both connection objects and DataReader objects after operation completion. Utilize a try-finally block to perform the close. Review an example below: 

try
	{
		.
		.
	}
	finally
	{
		if (rdr != null)
		{
			rdr.Close();
		}
	}
« Previous
Next »