DataReader C#

Introduction to DataReader C#

A Data reader is an object that is used to read data from the data sources. This can only perform read operation and not update operation on the data source. The data is retrieved as a data stream from the data source. Though the data reader is restricted in terms of only reading operation, it is highly effective and optimized as it is read only and forward only. There are two types of providers in the .Net Framework, they are SQLDataReader and OleDbDataReader. The data reader increases the application performance by reducing system overhead as it stores the only row in memory at a given point of time. This article will cover in detail the data reader in c# along with appropriate examples.

Syntax:

The sql data reader is available in the namespace System.Data.SqlClient and the corresponding assembly is System.Data.SqlClient.dll. A SQL data reader is initialized as follows.

SqlDataReadersqlReader = sqlCmd.ExecuteReader();

The execute reader is used to pass the SQL statements or procedure to the sqlconnection object and the corresponding result is stored in the sqlreader object of SqlDataReader. Before reading from any data reader, it should always be open and should point to the first record. The read() method of data reader is used to read and it moves forward to the next row.

The oledb data reader also behaves in the same way. It is available in the name space System.Data.OleDb and the corresponding assembly is System.Data.OleDb.dll. An oledbDataReader object is used to connect to OLEDB data sources and fetch data from them. Like SQLDataReader, oledbdata reader should also be open before performing read operation. An oledb data reader is initialized as follows.

OleDbDataReaderoledbReader = oledbCmd.ExecuteReader();

Where the executereader is used to carry out the SQL statements or procedures.

Accessing Data Reader Results

The data reader object has a read method that can be used to access rows from the result set. Each column value can be accessed either using their name or using their column order in which they appear on the result set.  To improve efficiency, a set of pre-defined type conversion methods to access values as such. Data reader is advised to use when working large sets of data as the data is not stored in cache memory. It is always advisable to close the data reader object once it is used. If the command has some return values or output parameters, it is only available once the data reader is closed. Another important thing to keep in mind while using data reader is that when a connection is used by a data reader another data reader or any other operation can’t be performed on that connection. Also, one important thing to remember is only forward accessing the dataset is possible. We can iterate to the next record only and not to the previous one.

Working with Multiple Sets

If data reader returns multiple result sets, the NextResult method of data reader can be used to access them. During implementation, it should be noted and taken care that all the result sets are being iterated and each column inside the result set is accessible.

Examples to Implement of DataReader C#

Below are the examples of DataReader C#:

Example #1

Typically, data is read from the result set returned by the data reader is to iterate each row using a while loop. The read method return value is of bool type, if the next row is present then true is returned and for the last record, false is returned. The while loop will be executed until the condition becomes false.

Syntax:

while(rdr.Read())
{
// operation to be performed
// Access columns
// Data manipulation
}

Like closing a SQL connection, it is always a best practice to close the Data reader. All the while part can be in enclosed in a try block and the data reader connection can be closed in the finally block.

Syntax:

try
{
// operation to be performed
// Access columns
// Data manipulation
}
Catch
{
//Handle exception here
}
finally
{
//close the data reader connection
if (reader != null)
{
reader.Close();
}
// close thesql connection
}

Code:

using System;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace test
{
public partial class test1 : Form
{
public test1()
{
InitializeComponent();
}
Public static void main()
{
string constr = null;
SqlConnectionscon ;
SqlCommandscmd ;
string sstat = null;
constr = "Data Source=testserver;Initial Catalog=testdb;User ID=test;Password=test";
sstat = "Select * from test";
scon = new SqlConnection(constr);
try
{
scon.Open();
scmd = new SqlCommand(sstat, scon);
SqlDataReadersstatReader = scmd.ExecuteReader();
while (sstatReader.Read())
{
Console.WriteLine("Name:" sstatReader.GetValue(0)  + "age:"  sstatReader.GetValue(1) );
}
sstatReader.Close();
scmd.Dispose();
scon.Close();
}
catch (Exception ex)
{
}
}
}
}

Output:

Example #2

Code:

using System;
using System.Windows.Forms;
using System.Data.OleDb;
namespace test
{
public partial class test : Form
{
public test()
{
InitializeComponent();
}
Public static void main()
{
string constr = null;
OleDbConnectionocon ;
OleDbCommandocmd ;
string sql = null;
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb";
sql = "select * from emp";
ocon = new OleDbConnection(constr);
try
{
ocon.Open();
ocmd = new OleDbCommand(sql, ocon);
OleDbDataReaderordr = ocmd.ExecuteReader();
while (ordr.Read ())
{
Console.WriteLine("EmpName:" ordr.GetValue(0) + "Empage:"  ordr.GetValue(1) + "Esalary" ordr.GetValue(2) );
}
ordr.Close();
ocmd.Dispose();
ocon.Close();
}
catch (Exception ex)
{
Console.WriteLine("Connection Failed");
}
}
}
}

Output:

Conclusion

Thus, the article covered in detail about data readers in c#. It also showed how data readers can be read only in a forward manner from the data stream. The article also explained in detail about the two types of data readers, I .esql data reader and oledb data reader. It showed the working of both data readers along with appropriate examples. The article also covered on how to work with multiple result sets using the NextResult method and how each data row can be iterated using the for a loop. To learn more in detail it is advisable to write sample programs and practice them.

Recommended Article

This is a guide to DataReader C#. Here we discuss the Introduction to DataReader in C# and its examples along with Code Implementation and Output. You can also go through our other suggested articles to learn more –

1. C# SortedDictionary
2. C# String Format()
3. TextWriter in C#
4. Static Constructor in C#