What is the difference between a DataReader and a DataTable in C# (.NET)?
Introduction
Both methods (DataTable and DataReader) allow a C# application to access and retrieve data from a database. The main difference between the two is that the DataTable is a disconnected recordset, in that it will retrieve all the records from the source and then close the recordset. A DataReader will only read a record at a time so you can then process the data.
Code Examples of DataReader and DataTable
DataReader
using (SqlConnection conn = new SqlConnection(Misc.GetConn()))
{
conn.Open();
using (SqlCommand command = new SqlCommand("Select Id, Name from stars", conn))
{
command.CommandType = CommandType.Text;
using (SqlDataReader reader = command.ExecuteReader())
{
while(reader.Read())
{
Console.WriteLine(reader.GetString(1));
}
}
}
}
DataTable
using (StreamWriter oFS = new StreamWriter("C:\\temp\\Stars.txt"))
{
using (SqlConnection conn = new SqlConnection(Misc.GetConn()))
{
using (SqlCommand command = new SqlCommand("Select Id, Name from stars", conn))
{
command.CommandType = CommandType.Text;
SqlDataAdapter oAdapt = new SqlDataAdapter(command);
oAdapt.Fill(oTbl);
foreach(DataRow oDR in oTbl.Rows)
{
oFS.WriteLine(oDR[1]);
}
}
}
}
When to use Datatable or DataReader
Use a DataTable when :-
- The data is a fairly small amount of records so that they are not all read into memory.
- A DataTable whilst being disconnected, it can be written back to the database.
Use a DataReader when :-
- When the recordset is large and that its not able to be read all into memory.
- Datareaders are read only, you can't amend the data, if you wanted to, you'd have to use a procedure or inline sql to save the data.
When I added timings, I found the datatable method to be surpriisingly quicked than the datareader so where possible, you should use the datatable method except in the case of large recordsets where you should use a datareader The number of records that were used in determining the timings were over 130,000.
Last Modified : June 2023