C# DataReader ADO.NET |Trickcode

How To Read Data From Database in C# using DataReader

Each data provider also has its own DataReader object. A DataReader object allows forward-only, read-only access to a database. This is great for querying multiple records in a table. Each DataReader implements the IDbDataReader. The DataReader only loads one row within the memory at a time to make sure the minimum use of memory. DataReader can only be used when a connection is open, so you would like to open a connection first and as soon as you're overusing it, then you want to close the connection.

The following program shows how we can use the DataReader to query the records of each employee in the Employees table of the Northwind database.

using System;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;

namespace DatabaseConnection
{
    class Employee
    {
        public int EmployeeId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    class Program
    {
        static void Main()
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = @"Data Source=localhost;" +
                       "Initial Catalog=Northwind;Integrated Security=SSPI";
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT EmployeeId, FirstName, LastName " + 
                                  "FROM Employees";
            command.Connection = connection;

            SqlDataReader reader;

            List Employees = new List();

            try
            {
                connection.Open();
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Employee newEmployee = new Employee();
                    newEmployee.EmployeeId = (int)reader["EmployeeId"];
                    newEmployee.FirstName = reader["FirstName"].ToString();
                    newEmployee.LastName = reader["LastName"].ToString();

                    Employees.Add(newEmployee);
                }

                reader.Close();
            }
            catch (SqlException)
            {
              Console.WriteLine("An error occured while connecting to the database");
            }
            finally
            {
                connection.Close();
            }

            //Print headers
            Console.WriteLine("{0,-15}{1,-15}{2,-15}",
                    "Employee ID", "First Name", "Last Name");

            foreach (Employee Employee in Employees)
            {
                Console.WriteLine("{0,-15}{1,-15}{2,-15}",
                    Employee.EmployeeId, Employee.FirstName, Employee.LastName);
            }
        }
    }
}

Employee ID    First Name     Last Name
1              Nancy          Davolio
2              Andrew         Fuller
3              Janet          Leverling
4              Margaret       Peacock
5              Steven         Buchanan
6              Michael        Suyama
7              Robert         King
8              Laura          Callahan
9              Anne           Dodsworth

The program first creates a class named Employee that will hold the values of each that will hold the details of the FirstName, LastName, and EmployeeId of each employee. Those details are declared as the properties of the class. We then initialize our connection and command and give them the details such as connection string and the SQL command to be used. We declared a SqlCommandReader. CommandReader doesn’t have a constructor so you cannot initialize them. We also defined a collection of Person objects. Be sure to import the System.Collections.Generic namespace. We entered the try block and there, we opened the connection. The code below shows how we can construct a DataReader object.

reader = command.ExecuteReader();

 We used the method ExecuteReader that creates a reader based on the CommandText and connection properties of the Command object.

while (reader.Read())
{
    Employee newEmployee = new Employee();
    newEmployee.EmployeeId = (int)reader["EmployeeId"];
    newEmployee.FirstName = reader["FirstName"].ToString();
    newEmployee.LastName = reader["LastName"].ToString();

    Employees.Add(newEmployee);
}

We entered a while loop. We called the Read method of the DataReader which gets the current row of the table from the database. After reading the data, the current row is moved to the next row. The Read method returns true if it successfully receives a row or record and false if it is not. Inside the loop, we created a new Employee object that will hold the data that was retrieved from the database. We assigned each of the properties with their respective columns in the table. To access the columns of the row retrieved by the DataReader, we can use the name of the column as indexers. Alternatively, we can use their index position in the table. The following does the same thing:

newEmployee.EmployeeId = (int)reader[0]; newEmployee.FirstName = reader[1].ToString(); newEmployee.LastName = reader[2].ToString();

If you don’t know the index/ordinal of the column, you can use the GetOrdinal method. The
GetOrdinal method accepts a string argument which is the name of the column.

Console.WriteLine(reader.GetOrdinal("EmployeeID");
Console.WriteLine(reader.GetOrdinal("FirstName");
Console.WriteLine(reader.GetOrdinal("LastName");

You can use the FieldCount property to of the DataReader to get the number of columns of the current row. Notice that we have converted each data receive to their appropriate data types. This is because the DataReader returns the data as an object so it can support any kind of data. Alternatively, you can use a number of methods offered by DataReader for getting data of specific data types as seen in the following.

newPerson.EmployeeId = reader.GetInt32(0);
newPerson.FirstName = reader.GetString(1);
newPerson.LastName = reader.GetString(2);

Those methods accept an argument which is the 0-based index of the column in the table. After retrieving the values, we add the new Employee to the employees' collection.

Employees.Add(newEmployee);

We then repeat the loop and executed the Read method again but this time, it reads the new row. Remember that last time we used the Read method, the cursor moved to the next row, so using the Read method now reads that brand new row. The loop repeats until it reaches the last record where Read will return false because no records can be retrieved anymore. The Read method will also return false if no records or rows actually exist in the table.

You can use the HasRows property of the DataReader to check if it contains at least one row. After leaving the loop, we need to close the DataReader to free up the resources used. We then leave the try block and since no error occurred, the program proceeds to the final block, and there, the connection was closed.

  The last codes prints the data in a table. We used for matters to add the padding so the data will align properly. Another version of the ExecuteReader method can accept a System.Data.CommandBehavior enumeration value. The following table shows some of the values you can use.

Value	Description
CloseConnection	Immediately closes the connection when the Close method of the DataReader is called.
Default	The default behavior of the DataReader.
single result	The query returns a single result set.
SingleRow	The query is expected to return only a single row.

The following example shows you how to apply the CommandBehavior enumeration. The constructor of the Parameter class used above accepts the name and the data type of the value to be stored in the parameter. If you are using a variable-length data type such as nvarchar(x), then you can use a different constructor.

SqlParameter parameter2 = new SqlParameter("@FirstName", SqlDbType.NVarChar, 100);

 Once you created the Parameter object, you can then specify the value it will contain using the Value property.

parameter1.Value = 10;

You can now add the created Parameter object to the Parameters property of the Command object.

command.Parameters.Add(parameter1);
The following code demonstrates the use of parameters. The following program accepts an employee ID from the user and prints the full name of the employee with that ID.

int id;
SqlConnection connection = new SqlConnection();
SqlCommand command = new SqlCommand();
SqlDataReader reader;

connection.ConnectionString =
    @"Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;" +
    "Integrated Security=SSPI";
         
command.Connection = connection;

Console.Write("Enter an EmployeeId: ");
id = Int32.Parse(Console.ReadLine());

command.CommandText = "SELECT * FROM Employees WHERE EmployeeId = " + id;

try
{
    connection.Open();

    reader = command.ExecuteReader(CommandBehavior.SingleRow);
    reader.Read();
    Console.WriteLine(reader["FirstName"].ToString() + " " +
        reader["LastName"].ToString());
    reader.Close();
}
catch
{
    Console.WriteLine("An error occured.");
}
finally
{
    connection.Close();
}

OutPut:
 Enter an EmployeeId: 3
Janet Leverling

The program above asks a user an employee id. The program prints the full name of the employee with the specified employee id. Since each employee has a unique employee id (EmployeeId is the primary key), then the query is expected to return one row of data. Therefore, we passed the CommandBahavior.SingleRow value to only retrieve the first row that matches the WHERE clause of the SQL command. You can also combine behaviors using bitwise operation as seen by this example:

reader = command.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.CloseConnection);

For example, you can retrieve a single row and at the same time, close the connection once the reader has been closed. We used the | (bitwise OR) operator to combine the two enumeration values.