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.
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.
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.
SqlConnection
and open it. We do this with the SqlConnection
constructor.SqlCommand
and call its ExecuteReader
method, assigning the reference it returns to an SqlDataReader
.SqlDataReader
object from the result of the ExecuteReader()
method.while
-loop continues iterating through its loop body as long as the Read()
method does not return false.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
Your project will have its own ConnectionString
, which can be generated through the Add Data Source menu item in Visual Studio.
SqlConnection
before using the SqlDataReader
code here.SqlCommand
object with an SQL text query as its first parameter, and the SqlConnection
as its second parameter.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
.
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.