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
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.
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.
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.
Tip:Using SqlDataReader is an easy way to print all rows from a table. It is
efficient and worth knowing.
