1. What is
DataReaders?2. How to use
DataReaders in ADO.NET for retrieving data?3. Programming Examples
What is DataReaders?
The DataReader object in C# ADO.NET allows you to retrieve data from database in read-only and forward-only mode. It means you can only read and display data but can’t update or delete data. If you want to make modification in retrieved data you need to use DataAdapter instead of DataReader.
Why and When to Use DataReader?
When you want to only display information or search result, you can use DataReader. There are various advantages of using DataReader like:
1. The retrieved data is stored in the network buffer in the client and then the client can read data using Read method. As data gets stored in the client network buffer it increases application performance significantly.
2. By default DataReader stores only one row at a time in memory. It reduces system overhead.
Methods and Properties of DataReader
Properties
| PROPERTY | DESCRIPTION |
|---|---|
| Depth | Indicates the depth of nesting for row |
| FieldCount | Returns number of columns in a row |
| IsClosed | Indicates whether a data reader is closed |
| Item | Gets the value of a column in native format |
| RecordsAffected | Number of row affected after a transaction |
Methods
| METHOD | DESCRIPTION |
|---|---|
| Close | Closes a DataRaeder object. |
| Read | Reads next record in the data reader. |
| NextResult | Advances the data reader to the next result during batch transactions. |
| Getxxx | There are dozens of Getxxx methods. These methods read a specific data type value from a column. For example. GetChar will return a column value as a character and GetString as a string. |
How to use DataReaders in ADO.NET for retrieving data?
using System;
using System.Data.SqlClient;
namespace DataReader_Examples
{
class Program
{
static void Main(string[] args)
{
string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
SqlConnection con = new SqlConnection(ConString);
string querystring = "Select * from Items";
try
{
con.Open();
SqlCommand cmd = new SqlCommand(querystring, con);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader[0].ToString() + " " + reader[1].ToString() + " " + reader[2].ToString());
}
}
catch(SqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
con.Close();
Console.ReadKey();
}
}
}
}
Output
Summary
In this tutorial, you learned how to use DataReader in ADO.NET for accessing data from database table. In the next chapter, you will learn about DataAdapters and DataSets.