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
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.
-
ExecuteReader and Read method
Read the record set available in datareader object.
-
NextResult
Read the next record set.
-
ExecuteReaderAsync and ReadAsync method
Read data asynchronously from datasource.
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;
}
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;
In this example you will see how Ado.net datareader return multiple result sets, you will learn following things
- how to return multiple result sets from Ado.net Datareader
- how to use stored procedure for reading data with SqlCommand
- how to fill Ado.net DataTable from DataReader
- 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
Async ExecuteReader Example
To use Async ExecuteReader method there is a built-in method ExecuteReaderAsync directly associated with SqlCommand object.
As we know whenever we want to consume any async method, we need to have to use await keyword, below is an example of how exactly you consume ExecuteReaderAsync and ReadAsync method, execution and reading both jobs will be done asynchronously.
using (SqlConnection con = new SqlConnection(Util.DBConnectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand("select * from dbo.tbStock", con);
cmd.CommandTimeout = 1;
SqlDataReader reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
_stock = new Stock();
_stock.Price = Convert.ToDecimal(reader["price"]);
_stock.Qty = Convert.ToInt32(reader["quantity"]);
_stock.Product = Convert.ToString(reader["productName"]);
stockDetails.Add(_stock);
}
con.Close();
}
As you can see in above example, we are reading data into some custom object called Stock, so the method that returns stock list, also needs to be an async method.
So your async task code will look like below.
public static async Task<List<Stock>> GetStockDetails()
{
var stockDetails = new List<Stock>();
Stock _stock = null;
return stockDetails;
}
