C# SqlDataReader

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

SqlDataReader: SQL table in Visual Studio

Example

Note

Preliminary steps are required before using SqlDataReader on a database table. These steps require configuration on your part. 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 an SqlDataReader.

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
Main method

Access connection string. Main() 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.

Tip:You must always create a new SqlConnection before using the SqlDataReader code here.

SqlConnection

Note:SqlConnection objects use an advanced optimization called connection pooling.

So:Creating these new objects will probably not have a highly adverse affect on overall program performance.

Steps

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.

Note:The text "SELECT * FROM Dogs1" simply selects all rows from a table called Dogs1 in the database.

Finally, the program creates a new SqlDataReader object from the result of the ExecuteReader() method. The while-loop continues iterating through its loop body as long as the Read() method does not return false.

Tip:This makes it possible to query the SqlDataReader for integers, strings and other types with the GetInt32 and GetString methods.

SqlDataAdapter

Note

A more object-oriented approach to database table reading can be achieved by using DataTables. You can directly populate a DataTable with the data from an SQL database table using the SqlDataAdapter class.

SqlDataAdapter

Warning:This approach is slower if you are dealing with vast amounts of data, because all of it must be stored in memory at once.

Summary

C# programming language

We saw the SqlDataReader class. It 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.

SqlClient Tutorial: SqlConnection, SqlCommand

Tip:Using SqlDataReader is an easy way to print all rows from a table. It is efficient and worth knowing.


C#: Data