EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login

DataReader C#

By Priya PedamkarPriya Pedamkar

Home » Software Development » Software Development Tutorials » C# Tutorial » DataReader C#

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:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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.

Popular Course in this category
C# Training Program (6 Courses, 17 Projects)6 Online Courses | 17 Hands-on Project | 89+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.6 (8,847 ratings)
Course Price

View Course

Related Courses
ASP.NET Training (8 Courses, 19 Projects).NET Training Program (4 Courses, 19 Projects)

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:

datareader c# Example 1

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:

datareader c# Example 2

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#

C# Training Program (6 Courses, 17 Projects)

6 Online Courses

17 Hands-on Project

89+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
C sharp Tutorial
  • Advanced
    • Inheritance in C#
    • Exception Handling in C#
    • Types of Exception in C#
    • C# FileNotFoundException
    • C# NullReferenceException
    • C# OutOfMemoryException
    • C# StackOverflowException
    • Custom Exception in C#
    • What is Multithreading in C#
    • C# finally
    • C# System.IO
    • What is StringBuilder in C#
    • DataReader C#
    • BinaryWriter in C#
    • C# BinaryReader
    • TextWriter in C#
    • TextReader in C#
    • C# StringReader
    • C# StringWriter
    • C# StreamReader
    • C# StreamWriter
    • C# FileInfo
    • What is Design Pattern in C#?
    • Multithreading in C#
    • Sorting in C#
    • Bubble Sort in C#
    • C# SortedList
    • C# SortedSet
    • C# SortedDictionary
    • Abstract Class in C#
    • Access Modifiers in C#
    • C# Generics
    • Deserialization in C#
    • C# Thread
    • C# Thread Join
    • C# Thread Sleep
    • C# Thread Synchronization
    • C# Class
    • Sealed in C#
    • Sealed Class in C#
    • Polymorphism in C#
    • C# Call By Reference
    • Virtual Keyword in C# 
    • Yield Keyword in C#
    • Regular Expression in C#
    • C# Lambda Expression
    • C# Predicate
    • Convert Object to JSON C#
    • Checkbox in C#
    • C# MessageBox
    • Collections in C#
    • List in C#
    • C# LinkedList
    • Listbox in C#
    • Protected in C#
    • C# EventHandler
    • Private in C#
    • this Keyword in C#
    • Static Keyword in C#
    • C# Out Parameter
    • Assert in C#
    • C# Delegates
    • C# Interface
    • Generics in C#
    • Timer in C#
    • C# Serialization
    • Metadata in C#
    • C# Stack
    • C# Using Static
    • Queue in C#
    • C# File.Exists
    • C# Tuples
    • C# Create JSON Object
    • Partial in C#
    • C# readonly
    • C# Action Delegate
    • C# Await Async
    • C# Dictionary
    • IEnumerable C#
    • C# Data Grid View
    • C# Dynamic
    • Web Services in C#
    • C# Pattern Matching
    • C# Extension Methods
    • C# XmlSerializer
  • Basic
    • Uses Of C#
    • C# Versions
    • C# Data Types
    • Variables in C#
    • Namespaces in C#
    • C# Compilers
    • C# Keywords
    • Iterators in C#
    • Objects in C#
    • C# Object Dispose
    • C# object to XML
    • C# check object type
    • C# Object Serialization
    • Pointers in C#
    • C# Literals
    • C# Commands
    • C# Custom Attribute
    • Type Casting in C#
    • String vs String C#
    • C# Struct vs Class
  • Operators
    • Logical Operators in C#
    • Conditional Operators in C#
    • Bitwise Operators in C#
    • C# OR Operator
    • C# Ternary Operators
    • Operator Precedence in C#
  • Control Statement
    • C# if Statement
    • Else If in C#
    • Continue in C#
    • Break in C#
    • Switch Statement in C#
    • Goto Statement in C#
  • Loops
    • C# For Loop
    • C# While Loop
    • C# do-while loop
    • C# foreach Loop
  • Arrays
    • Arrays in C#
    • 2D Arrays in C#
    • C# Jagged Arrays
    • String Array in C#
    • C# Multidimensional Arrays
  • Constructor and Destructor
    • Constructor in C#
    • Copy Constructor in C#
    • Static Constructor in C#
    • Destructor in C#
  • overloading and overrideing
    • Overloading and Overriding in C#
    • Overloading in C#
    • Overriding in C#
    • Method Overloading in C#
    • Method Overriding in C#
    • Operator Overloading in C#
  • Functions
    • C# Functions
    • C# String Functions
    • Math Functions in C#
    • Recursive Function in C#
    • C# Anonymous Functions
    • C# Local Functions
    • Enum in C#
    • Trim() in C#
    • clone() in C#
    • C# random
    • C# String Format()
    • C# String Interpolation
    • C# StartsWith()
    • C# String IndexOf()
    • DateTime in C#
    • C# Nullable
    • C# nameof
    • C# checked
    • C# String PadLeft
    • Convert String to Double in C#
    • Convert int to String C#
    • String to Date C#
    • C# intern()
    • C# Stopwatch
    • C# DirectoryInfo
    • C# Compare()
    • C# Base
    • C# SOAP
    • Lock in C#
  • Programs
    • Patterns in C#
    • Swapping in C#
    • Palindrome in C#
    • Factorial in C#
    • Fibonacci Series in C#
    • Random Number Generator in C#
    • Prime Numbers in C#
    • Armstrong Number in C#
    • Reverse String in C#
  • Interview questions
    • C# Interview Questions and Answers
    • C# OOP Interview Questions
    • C# Design Pattern Interview Questions

Related Courses

C# Certification Training

ASP.NET Course

.NET Course

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - C# Training Program (6 Courses, 17 Projects) Learn More