DataReader Class in .NET provides efficient way to retrieve data from the database. DataReader can be used when to simply display the result set. It cannot be used to  DataReader is forward-only which means that when we read some data it should be saved in some means as we cannot go back and read it again.

     The DataReader is of much use where data need not be updatable nor it should be available for multiple requests.

Coding a DataReader

To illustrate it with an example

 private void btLoadDt_Click(object sender, EventArgs e)
        {
            
            SqlConnection conn = new SqlConnection();
            SqlDataReader reader;
 
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
 
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = @"SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP 
                                WHERE EMPNO =@EMPLOYEENO and JOB = @JOBDESC";
            cmd.Connection = conn;
 
 
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@EMPLOYEENO", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(8957);
            sqlParameters[1] = new SqlParameter("@JOBDESC", SqlDbType.VarChar);
            sqlParameters[1].Value = Convert.ToString("ENGINEER");
            cmd.Parameters.AddRange(sqlParameters);
 
            cmd.Connection.Open();
 
            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 
            DataTable dt = new DataTable();
            dt.Load(reader); 
 
        }

The connection object provides the connection to the DataSource which we have defined in the App config file.

SqlCommand cmd = new SqlCommand();

 

The command object enables us to access database commands to return data (Select), modify (Insert, Update or Delete) data, run stored procedures, and send or retrieve parameter information (Usually through the properties associated with the parameter such as ParameterName, SqlDbType, Size, Direction and Value)

Creating SqlDataReader Object

   Creating an instance of SqlDataReader is different from other way we instantiate the ADO .NET components like DataSet, Connection, Command and DataAdapter objects

     We must call the ExecuteReader Method on the command Object like,

         SqlDataReader reader = cmd.ExecuteReader(); 

Loading a DataTable

  To load the DataTable with the DataReader is simple by calling the Load Method of the DataTable.

   DataTable dt = new DataTable();
   dt.Load(reader); 

Once the DataTable is loaded with the DataReader we can assign the DataTable to the Datagrid’s DataSource property

Thanks for reading.