C# SqlDataReader

SqlDataReader reads database rows one-by-one. It reads in forward order from a SQL database. The SqlDataReader type can be used in a loop to read multiple rows from a SQL database. It provides good performance and strong typing.

Example

Several preliminary steps are required before using the SqlDataReader on your database table. Unfortunately, these steps will require some configuration on your part and you must target the correct database with a custom connection string and also target the proper table. To begin, you can create a new SqlConnection and open it; then you can create a new SqlCommand and call its ExecuteReader method, assigning the reference it returns to a SqlDataReader.

This C# program uses SqlDataReader. It requires System.Data.SqlClient.

Program that uses SqlDataReader with SqlClient [C#]

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
	//
	// You need to access the project's connection string here.
	//
	string connectionString = ConsoleApplication1.Properties.Settings.Default.ConnectionString;
	//
	// Create new SqlConnection object.
	//
	using (SqlConnection connection = new SqlConnection(connectionString))
	{
	    connection.Open();
	    //
	    // Create new SqlCommand object.
	    //
	    using (SqlCommand command = new SqlCommand("SELECT * FROM Dogs1", connection))
	    {
		//
		// Invoke ExecuteReader method.
		//
		SqlDataReader reader = command.ExecuteReader();
		while (reader.Read())
		{
		    int weight = reader.GetInt32(0);    // Weight int
		    string name = reader.GetString(1);  // Name string
		    string breed = reader.GetString(2); // Breed string
		    //
		    // Write the values read from the database to the screen.
		    //
		    Console.WriteLine("Weight = {0}, Name = {1}, Breed = {2}", weight, name, breed);
		}
	    }
	}
    }
}

Output

Weight = 57, Name = Koko, Breed = Shar Pei
Weight = 130, Name = Fido, Breed = Bullmastiff
Weight = 93, Name = Alex, Breed = Anatolian Shepherd Dog
Weight = 25, Name = Charles, Breed = Cavalier King Charles Spaniel
Weight = 7, Name = Candy, Breed = Yorkshire Terrier

Access connection string. The Main entry point defined in the program text first accesses a project-specific ConnectionString. Your project will have its own ConnectionString, which can be generated through the Add Data Source menu item in Visual Studio.

New SqlConnection object. You must always create a new SqlConnection before using the SqlDataReader code here. Note that SqlConnections use an advanced optimization called connection pooling, so creating these new objects will probably not have a highly adverse affect on overall program performance.

New SqlCommand with string parameter. Next you must create a new SqlCommand object with an SQL text query as its first parameter, and the SqlConnection object reference as its second parameter. This program does not use a stored procedure, which may reduce performance and maintainability. The text "SELECT * FROM Dogs1" simply selects all rows from a table called Dogs1 in the database.

SqlDataReader from ExecuteReader method. Finally the program creates a new SqlDataReader object from the result of the ExecuteReader() method. Note how the while loop condition is written. The loop continues iterating through the loop body as long as the Read() method does not return false. This makes it possible to query the SqlDataReader for integers, strings and other types with the GetInt32 and GetString methods.

SqlDataAdapter

A more object-oriented approach to database table reading can be achieved by using DataTable instances in the .NET Framework. You can directly populate a DataTable with the data from a SQL database table using the SqlDataAdapter class, which is also easily available from the System.Data.SqlClient namespace. This approach has worse performance if you are dealing with vast amounts of data, because all of it must be stored in memory at once.

Summary

We saw the SqlDataReader class in the System.Data.SqlClient namespace of the .NET Framework and used the C# programming language. The SqlDataReader provides an excellent way to query rows one-by-one from your database tables. It does not require the usage of a DataTable, which can improve performance and decrease memory usage in certain cases, and decrease it in others. Using SqlDataReader is an easy way to print all rows from a table.