Free Web Development Online Tutorials, Learn to Code
Learn ADO.Net DataReader with C#, ado.net executereader c#, executereader method with stored procedure

Ado.net DataReader C# Example

This Ado.net DataReader Tutorial with C# for Beginners will guide you to learn Ado.net DataReader with some real-time examples. Please feel free to ask question, I will keep updating this tutorial with answer of your query

Ado.net executereader c# example

In this article you will learn how to use Ado.Net DataReader for accessing data from Database, You will learn how to read single and multiple record set using ado.net dataReader.

ADO.Net DataReader provides a connection oriented data access, DataReader supports forward-only and read-only data retrieval from Data Sources.

using System.Data;
using System.Data.SqlClient;
SqlCommand cmd = new SqlCommand(querystring, con);
SqlDataReader reader = cmd.ExecuteReader();

Here we show you how to work with SqlDataReader, similar way you can use OleDbDataReader under System.Data.OleDb namespace, both reader objects are inherited from DbDataReader and IDataReader

Before we start with DataReader example, we want you to know few DataReader characteristic and important methods

ADO.net DataReader Characteristic

  • SqlDataReader is the best for reading data in the most efficient manner possible.
  • SqlDataReader is forward-only, that means once you have read the data from SqlDataReader, then you must save it in some object or use it wherever you want, you won't be able to go back and read data from same DataReader instance.
  • We can read multiple record set using DataReader, and need to read them one by one
  • Using DataReader you cannot write data back to datasource
  • You must have an open connection (associated with dataReader) while reading the data.

ADO.net DataReader Methods

  • Read()

    Read the record set available in datareader object.

  • ReadAsync()

    Read data asynchronously from datasource.

  • NextResult()

    Read the next record set.

Ado.net datareader to object list

You will learn how to use ADO.net DataReader with Cutomobject, As we know that after reading the data we have to save it in some object, so here in example we save the data into a student object collection. So, following method will return a List<WTRStudent> object.

List<WTRStudent> getStudents()
{
    SqlDataReader _reader = null;
    SqlConnection _conn = new SqlConnection(connectionString);
     SqlCommand _cmd = new SqlCommand("select StudentName, Address, ContactNumber from tbStudent", _conn);
    List<WTRStudent> _studentList = new List<WTRStudent>();
    WTRStudent _student = null;
try
{            
//open the connection
_conn.Open();
// read the data into reader
_reader = _cmd.ExecuteReader();
while (_reader.Read())
{
    // get the results of each column
    _student.FullName =  _reader["StudentName"] as string;
    _student.Address =  _reader["Address"] as string;
    _student.ContactNumber =  _reader["ContactNumber"] as string;        
    _studentList.Add(_student);
}
// incase you have another record set to read, you can read this way
while (_reader.NextResult())
{            
    // read the next record set.
}
}
catch (Exception ex)
{
    // do the exception handling rightly
}
return _studentList;
}

ado.net datareader get column name

You can get column value from datareader either by name or by index value , here is an example of how you can get ado net datareader get column name.

SqlDataReader _reader = _cmd.ExecuteReader();

string FullName =  _reader["StudentName"] as string;
string Address =  _reader["Address"] as string;

Ado.net sqlcommand executereader multiple result sets

In this example you will see how Ado.net datareader return multiple result sets, you will learn following things

  1. how to return multiple result sets from Ado.net Datareader
  2. how to use stored procedure for reading data with SqlCommand
  3. how to fill Ado.net DataTable from DataReader
  4. how to use NextResult method in ado.net datareader

Following method will return a DataTable collection object.

List<DataTable> getMultileDataTables()            
{
    SqlDataReader _reader = null;
    SqlConnection _conn = new SqlConnection(connectionString);
    SqlCommand _cmd = new SqlCommand();
    _cmd.CommandType = CommandType.StoredProcedure;
    _cmd.CommandText = "usp_getStudentTeacher";
    _cmd.Connection = _conn;
            
    List<DataTable> _resultList = new List<DataTable>();
            
    DataTable _dtStudent = new DataTable();
    _dtStudent.Columns.Add(new DataColumn("FullName"));
    _dtStudent.Columns.Add(new DataColumn("Address"));
    _dtStudent.Columns.Add(new DataColumn("ContactNumber"));
            
    DataTable _dtTeacher = _dtStudent.Clone();
    _dtTeacher.Clear();
            
DataRow _dRow = null;
try
{
    //open the connection
    _conn.Open();
            
    // read the data into reader
    _reader = _cmd.ExecuteReader();
            
    // read the first recordset of student
            
while (_reader.Read())
{
    _dRow = _dtStudent.NewRow();
    // get the results of each column
    _dRow["FullName"] = _reader["StudentName"] as string;
    _dRow["Address"] = _reader["Address"] as string;
    _dRow["ContactNumber"] =  _reader["ContactNumber"] as string;
            
    _dtStudent.Rows.Add(_dRow);
}
            
 // read the second recordset of teacher
         
while (_reader.NextResult())
{
    _dRow = _dtTeacher.NewRow();
    // get the results of each column
    _dRow["FullName"] = _reader["StudentName"] as string;
    _dRow["Address"] =  _reader["Address"] as string;
    _dRow["ContactNumber"] =  _reader["ContactNumber"] as string;
            
    _dtTeacher.Rows.Add(_dRow);
}
            
    // fianlly add both DataTable into collection
    _resultList.Add(_dtStudent);
    _resultList.Add(_dtTeacher);
            
}
catch (Exception ex)
{
    // do the exception handling rightly
}
finally
{
    //close the connection
    _conn.Close();
}
    return _resultList;
}

In above example we have a used a stored procedure to return multiple result sets

Comment
Name
Email
Website
Subscribe
 
learn ado.net
Ado.Net DataReader C# Example
Ado.net Interview Questions Answers

Ado.Net C# Examples | Join Asp.Net MVC Course