DataReader

In ADO.NET, a DataReader is a broad category of objects used to sequentially read data from a data source. DataReaders provide a very efficient way to access data, and can be thought of as a Firehose cursor from ASP Classic, except that no server-side cursor is used. A DataReader parses a Tabular Data Stream from Microsoft SQL Server, and other methods of retrieving data from other sources.

A DataReader is usually accompanied by a Command object that contains the query, optionally any parameters, and the connection object to run the query on.

DataReader types

There is no DataReader class in ADO.NET, but there are a number of classes that implement the IDataReader interface:

• System.Data.SqlClient.SqlDataReader
• System.Data.OleDb.OleDbDataReader
• Oracle.OracleClient.OracleDataReader

DataReaders have a small footprint and good performance because each is tailor-made to the task at hand, however this makes it more difficult to write an application that can be moved from one backend data source to another. Some provider-specific DataReaders expose types used by the underlying database - for example, int values can be null in Microsoft SQL Server, but not in the .NET Framework prior to version 2.0.

Strong vs weak typing

When using a DataReader to retrieve data, the developer can choose to read field values in strongly typed manner ( example: myReader.GetString(12) ) or a weakly typed manner, returning then as System.Objects ( example: myReader.GetValue(12) ). Both approaches have their pros and cons.

Using the strongly typed retrieval methods can be more cumbersome, especially without specific knowledge of the underlying data. Numeric values in the database can translate to several .NET types: Int16, Int32, Int64, Float, Decimal, or Currency. Trying to retrieve a value using the wrong type results in an exception being thrown, which stops code from running further, and slows the application down. This is also true when you use the right type, but encounter a DbNull value ( this can be avoided by using the IsDbNull boolean function of the DataReader class ). The benefit to this retrieval method is that data validation is performed sooner, improving the probability of data correction being possible.

Weakly typed data retrieval allows for quick code writing, and allows for the data to be used in some fashion when the developer doesn't know beforehand what types will be returned. Further, with some effort, the programmer can extract the value into a variable of the proper type by using the GetFieldType or GetDataTypeName methods of the DataReader.

Common errors

A DataReader can in some cases be used in place of a DataTable, however many programmers have experienced connection bloat when following this approach. A DataReader can only be used against an (already) open database connection; that connection isn't closed until the DataReader's Dispose method is called. If an exception is thrown while the data is being processed, for example as described in Strong and weak typing, above, the Dispose method will never be called if the developer writes code explicitly declaring and disposing the DataReader without the use of a try-finally block. The C# using construct is a good way to avoid this problem, as shown below in the code example.

Sample code

Sample of accessing SQL Data using DataReader

void DataTest()
{
    using (SqlConnection conn1 = new SqlConnection(...))
    {
        conn1.Open();
        SqlCommand mycommand = new SqlCommand("select * from someTable", conn1);
        using (SqlDataReader myreader = mycommand.ExecuteReader())
        {
            if (myreader != null)
                while (myreader.Read())
                    Console.WriteLine(myreader.GetValue(0).ToString() + ":" + myreader.GetTypeName(0));

        }
        mycommand.Dispose();
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using MySql.Data.MySqlClient;

namespace ConsoleApplication1;

class Program
{
    static void Main(string[] args)
    {
        string Conn = "Server=localhost;Uid=root;Pwd=thiru;Database=Employee";
        MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(Conn);
      
        MySqlCommand comm = new MySqlCommand("select * from emp", conn);
        conn.Open();

        MySqlDataReader Ada = comm.ExecuteReader();
        while (Ada.Read())
        {
            Console.WriteLine(Ada[0]);
        }
    }
}