SqlDataReader. This type 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.
Type notes. Using SqlDataReader is an easy way to print all rows from a table. It provides good performance and strong typing. It is efficient and worth knowing.
Example. Preliminary steps are required before using SqlDataReader on a database table. You must target the correct database with a custom connection string and also target the proper table.
Tip To begin, you can create a new SqlConnection and open it. We do this with the SqlConnection constructor.
Then You can create a new SqlCommand and call its ExecuteReader method, assigning the reference it returns to an SqlDataReader.
Finally The program creates a new SqlDataReader object from the result of the ExecuteReader() method.
Info The while-loop continues iterating through its loop body as long as the Read() method does not return false.
Important This makes it possible to query the SqlDataReader for integers, strings and other types with the GetInt32 and GetString methods.
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);
}
}
}
}
}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. 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.
Next You must create a new SqlCommand object with an SQL text query as its first parameter, and the SqlConnection as its second parameter.
Info The text "SELECT * FROM Dogs1" simply selects all rows from a table called Dogs1 in the database.
SqlDataAdapter. A more object-oriented approach uses DataTables. You can directly populate a DataTable with the data from an SQL database table using 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. SqlDataReader provides an excellent way to query rows one-by-one from your database tables. It does not require a DataTable, which can improve performance and decrease memory usage.
Dot Net Perls is a collection of tested code examples. Pages are continually updated to stay current, with code correctness a top priority.
Sam Allen is passionate about computer languages. In the past, his work has been recommended by Apple and Microsoft and he has studied computers at a selective university in the United States.
This page was last updated on Apr 20, 2023 (edit).
