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.

Example

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

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.

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

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

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.