How DataReader Works and How is it different from Dataset ?

Introduction ADO.NET offers two data classes for query results: the DataReader and the DataSet.
Determining which is best for your application involves careful analysis. Consider these factors before making a decision. If you use the SqlDataReader, note the following:

The underlying connection to the database remains open and cannot be used for any other purpose while the data reader is active. Call Close on the SqlDataReader as soon as possible.
There can be only one data reader per connection.
You can close the connection explicitly when you finish with the data reader, or tie the lifetime of the connection to the SqlDataReader object, by passing the CommandBehavior.CloseConnection enumerated value to the ExecuteReader method. This indicates that the connection should be closed when the SqlDataReader is closed.
When accessing data by using the reader, use the typed accessor methods (such as GetInt32 and GetString) if you know the column's underlying data type because they reduce the amount of type conversion required when you read column data.
To avoid unnecessary data being pulled from server to client, if you want to close the reader and discard any remaining results, call the command object's Cancel method before calling Close on the reader. Cancel ensures that the results are discarded on the server and are not pulled unnecessarily to the client. Conversely, calling Close on the data reader causes the reader to unnecessarily pull the remaining results to empty the data stream.
If you want to obtain output or return values returned from a stored procedure and you are using the ExecuteReader method of the SqlCommand object, you must call the Close method on the reader before the output and return values are available.

What is a DataReader?

DataReaders are different–they provide forward-only, read-only, connected access to data sources, and they don't support data manipulation. So why would you want to use something that limits you so much? Performance, for starters: DataReaders are much faster. Another benefit is a smaller memory footprint–DataReaders allow you to work with data as you get it, a row at a time. So DataReaders are particularly well-suited for working with data that's too large to fit in memory comfortably. A DataReader is a read-only stream of data returned from the database as the query executes. It only contains one row of data in memory at a time and is restricted to navigating forward only in the results one record at a time. The DataReader does support access to multiple result sets, but only one at a time and in the order retrieved. Just as in the original version of ADO, the data is no longer available through the DataReader once the connection to the data source is closed, which means a DataReader requires a connection to the database throughout its usage. Output parameters or return values are only available through the DataReader once the connection is closed.

DataReaders all implement the same IDataReader interface, which means that all of the DataReaders should provide the same set of functionality. Each DataReader implementation is optimized for a specific data provider such as SQL Server or Oracle. Version 1.0 of the Microsoft .NET Framework includes a System.Data.SqlClient.SqlDataReader that is specific to SQL Server and a System.Data.OleDb.OleDbDataReader that is more generic for OLEDB providers. Version 1.1 of the .NET Framework introduced the SqlCeDataReader for connecting to the SQL Server edition for Windows CE, OdbcDataReader for ODBC data sources, and OracleDataReader for connecting to Oracle databases. It isn''t required that you use the DataReaders from Microsoft, and each database vendor often has its own .NET provider. In addition, you can implement a DataReader of your own through the IDataReader interface.

Retrieving a Single Item

In this scenario, you want to retrieve a single item of data. For example, you might want to look up a single product name, given its ID, or a single customer credit rating, given the customer's name. In such scenarios, you will generally not want to incur the overhead of a DataSet or even a DataTable when retrieving a single item. You might also want simply to check whether a particular row exists in the database. For example, as a new user registers on a Web site, you need to check whether or not the chosen user name already exists. This is a special case of the single item lookup, but in this case, a simple Boolean return is sufficient.

Comparing the Options

Consider the following options when you retrieve a single item of data from a data source:

Use the ExecuteScalar method of a SqlCommand object with a stored procedure.
Use a stored procedure output or return parameter.
Use a SqlDataReader object.

The ExecuteScalar method returns the data item directly because it is designed for queries that only return a single value. It requires less code than either the stored procedure output parameter or SqlDataReader approaches require.. From a performance perspective, you should use a stored procedure output or return parameter because tests have shown that the stored procedure approach offers consistent performance across low and high-stress conditions (from fewer than 100 simultaneous browser connections to 200 browser connections).

Sample DataReader C# code

The C# sample code in Listing 1 demonstrates how to use a DataReader to retrieve data from the Northwind sample database included with Microsoft SQL Server. A connection is set up, the command object is created to retrieve the data, the connection is opened, and the data is retrieved and printed to the console window. The connection will automatically be closed when the DataReader is closed by using the appropriate CommandBehavior options on the Command object, which is a good habit to follow to ensure connections are not accidentally left open. ExecuteReader - Simply executes the SQL query against the database, using the Read() method to traverse through data, as illustrated below ExecuteNonQuery - Used whenever you work with SQL stored procedures with parameters, as illustrated in the Stored Procedures section below ExecuteScalar - Returns a lightning fast single value as an object from your database Ex. object val = Command.ExecuteScalar(); Then check if != null. ExecuteXmlReader - Executes the SQL query against SQL Server only,while returning an XmlReader object. Prepare - Equivalent to ADO's Command.Prepared = True property. Useful in caching the SQL command so it runs faster when called more than once.
Ex. Command.Prepare();
Dispose - Releases the resources on the Command object. Used to force garbage collecting, ensuring no resources are being held after our connection is used. Incidentally, by using the Dispose method you automatically call the Connection object's Close method as well.

c#

CSharp Source Code

SqlCommand objCmd = new SqlCommand(Sql , objConnect);
SqlDataReader ObjDr =objCmd.ExecuteReader();
while (ObjDr.Read() == true)
{
Response.Write (objDataReader[0].ToString() + "");
}
// closing Reader object:
objDataReader.Close();
objDataReader = null; objCommand.Dispose();
objCommand = null; objConnect.Close();
objConnect= null;

Vb.net

Vb.net Source Code

Dim objCmd As New SqlCommand(Sql, ObjConn)
Dim ObjDr As Data.SqlClient.SqlDataReader = objCmd.ExecuteReader()
while ObjDr.Read() = True
Response.Write(ObjDr(0).ToString() & "")
End while ' closing
    Reader object:
ObjDr.Close()
ObjDr = Nothing

objCmd.Dispose()
objCmd = Nothing
ObjConn.Close()
ObjConn = Nothing

When not to use Sql Data Reader ?

Don’t use Sql Data Reader when you don’t need to unecessarily keep a database connection open. It’s more efficient to use a disconnected data source model, meaning that you fetch all the data that is required at the beginning, and then close the connection. This is already implemented for you in SqlHelper.ExecuteDataSet().. If you are doing a big loop and outside the loop it has while (dr.read()) you will start to use up all your database connections pretty fast if your site gets moderately busy. In most cases you won’t need this, so just use ExecuteDataSet instead. If you do need to grab the data as you are going along and use it, for example when your data source is very large, or when you want to do asynchronous work on the data, then its a good idea to use SqlDataReader so you can process the data as it comes (rather than waiting until the entire data results are available) As well, DON’T FORGET, you need to explicitly close your Database connections, best to have a try { } catch { } and in the finally { } block you can put conn.Close(), otherwise if there is an exception you will have a connection that is going to stay open until .NET decides to dispose of it
How do I know which one to use?
You can use the following information to help you decide which data retrieval is right for your purposes.

When to consider using a DataReader:

The DataReader is a better choice for applications that require optimised read-only and forward-only access to data such as binding to a DataGrid control. The sooner the data is off-loaded from the DataReader and the connection is closed the better the application performance.
The DataReader is a better choice when you are planning to make repeated calls to retrieve small amounts of information or the data you are retrieving must be as up to date as possible each time it is used.

This is my first article focusing on working with DataReader, stored procedures in ADO.NET. In this article we mainly focus on retrieving values from stored procedures, retrieving by using "ExecuteReader," and retrieving an entire row from a stored procedure. To work with below given example. you need the simple extra tables “UserLogin_Tbl” with database name of your Choice. Let the structures of this table be like shown below

FieldName	Datatype
User_ID	varchar (30)
User_Password	varchar (30)

Retrieving Data, that we can display or process the data in some other way, we have two basic choices. We can use a DataSet object or a DataReader object.
The DataReader approach is generally quicker because it avoids the overhead that is associated with creating a DataSet object. The overhead that is associated with a DataSet object includes creating DataSet subobjects such as DataTables, DataRows, and DataColumns. However, the DataReader provides less flexibility, and is less suited to situations where you have to cache data and pass the data to components in an application that has multiple tiers or we can say if data modification is required, DataReader is not the choice. So DataReader is best choice when we have to display data.
Note The DataAdapter used to fill the DataSet uses a DataReader internally. Use a DataReader when the following conditions are true:
Now Question arises what made me to explain DataReader Again when it is simple object of ADO.NET as other. I agree it is .But fact is that when it comes to using DataReader or returning DataReader, it is bit tricky, where most of the developers get caught. I have seen most of the developers have raised Issue’s regarding returning DataReader from class doesn’t work. Let me make it simple and explain it in simple steps.
Example of Login Control where we need to fetch username, password to compare it with existing record. I shall look into the same concept, but with any data type and without using RETURN anymore. Before going to the ADO.NET code, we need to create a simple stored procedure in SQL Server which returns some value. Using “Query Analyzer,” execute the following script
[Note: Don’t forget to create Table [UserLogin_Tbl] within database]

Important Point: Use of Datareader is worth when we just need to Retrieve and display the data.

1. Create Stored procedure "proc_ValidateUser"

CREATEPROCEDURE proc_ValidateUser(@UserID int@User_Password varchar(50))AS SELECT User_ID, User_Password FROM UserLogin_Tbl WHERE User_ID = @ User_ID AND User_Password=@User_Password RETURN

The above stored procedure simply uses a SELECT statement to retrieve User_ID and User_Password from UserLogin_Tbl based on the “USERID” and “USERPASSWORD” we send through the parameter “@User_ID & @User_Password.” The stored procedure is named “proc_ValidateUser.” Now, we need to go to ADO.NET to access the same in ASP.NET.

2. Create Class "ClsLogin"

c#

CSharp Source Code

using System;
using System.Data;
 using System.Data.SqlClient;
 using System.Web;
 //Author:Shabir
 //Dated: 23/01/2009
 public class 
ClsLogin
{
//Your SQL Connection string which is already in the webconfig
 private string DBConstr = ConfigurationManager.ConnectionStrings["FAITHConnStr"].ConnectionString;
 public string homepageURL= "HomePage.aspx";
public string LoginpageURL=   
     "Login.aspx";
public ClsLogin()
 { }
 public string getConnectionString
 {
  get
 {
 return DBConstr; 
    
 }
}
public boolLogin(string DBConstr,string Userid, string password)
 {
 try 
 {
SqlConnection objConn= new SqlConnection(getConnectionString);
SqlCommand objcmd=new SqlCommand("proc_ValidateUser", objConn);
objcmd.CommandType=CommandType.StoredProcedure;
/// open  connection
objConn.Open();
objcmd.Parameters.AddWithValue("@User_ID",UserName);
objcmd.Parameters.AddWithValue("@User_Password", password);
 SqlDataReader  ObjDr= objcmd.ExecuteReader(); 
 while (ObjDr.Read())
 {
 if (ObjDr[0].ToString() == Userid 
        &&  ObjDr[1].ToString() == password)
{                   
    HttpContext.Current.Response.Redirect(homepageURL);
}
  }        
 // Close the reader and the connection.  
ObjDr.Close();
return true;
 }
 catch (Exception ex)
  {
 //if exception was raised then redirect to
    loginpage where he has to relogin
 HttpContext.Current.Response.Redirect("Login.aspx?error="+ex.Message);
   }
   finally
   { objConn.Close();}
 }
}

vb.net

vb.net Source Code

Imports System
Imports System.Data 
Imports System.Data.SqlClient
Imports System.Web
'Author:Shabir 
'Dated: 23/01/2009 
Public Class ClsLogin
 'Your SQL Connection string which is already in the webconfig
Private DBConstr    As  String = ConfigurationManager.ConnectionStrings("FAITHConnStr").ConnectionString  
    Public homepageURL As String =  
        "HomePage.aspx"     Public LoginpageURL As String   = "Login.aspx"
    Public Sub  New()
    End Sub
   Public ReadOnly  Property getConnectionString()    As String 
      Get
    Return DBConstr
   End Get
   End Property
   Public Function  Login(ByVal DBConstr   As String,  ByVal Userid As String, ByVal password  As  String) As Boolean
   Try
   Dim objConn As   New 
        SqlConnection(getConnectionString)
   Dim objcmd As  New 
        SqlCommand("proc_ValidateUser", objConn)             
   objcmd.CommandType = CommandType.StoredProcedure             
    ' open connection.         
   objConn.Open()
   objcmd.Parameters.AddWithValue("@User_ID", UserName)            
    objcmd.Parameters.AddWithValue("@User_Password", password)  
      Dim ObjDr As 
        SqlDataReader = objcmd.ExecuteReader()
   While ObjDr.Read()
      If ObjDr(0).ToString()  = Userid AndAlso ObjDr(1).ToString() = password Then
          HttpContext.Current.Response.Redirect(homepageURL)           
     End  If 
   End  While          
    ' Close the reader and the connection.           
    ObjDr.Close()           
    Return 
        True
   Catch ex As
        Exception             
    'if exception was raised then redirect to loginpage where he has
        to relogin             
    HttpContext.Current.Response.Redirect("Login.aspx?error=" & ex.Message)    
        Finally
   objConn.Close()
   End Try
End Function 
End Class

3 Call Login Method Login on Login Button Click

protected void btnlogin_Click(object sender,EventArgs e)
{
ClsLogin objlogin=new ClsLogin();
Objlogin.Login(StrConn, txtUsername.Text, txtPassword.Text);
}

Protected sub btnlogin_Click(object sender,EventArgs e)
Dim  objlogin 
        as new ClsLogin()

    Objlogin.Login(StrConn, txtUsername.Text, txtPassword.Text)    
End sub

Important thing to notice is that while calling Login Method ,I am passing Class Defined Connection as parameter,if new Connection will be opened and tried to login,you will receive Error ExecuteReader requires an open and available Connection. The connection's current state is closed.. As shown in code , “ExecuteReader” is a method which returns a “DataReader” object to pull the information from the database (in this case it is stored procedure) continuously, in a forward only and read-only manner. In the next article, we shall work with DataReader in Deep, Next we will understand data tables, data sets, and so forth using stored procedures. If possible, we shall also look into stored functions and compare both.

Now let me take a simple example where we will retrieve some fields of the table by using datareader Object.
The DataReadThe DataReader provides the fastest but least flexible way to retrieve data from the database. Data is returned as a read-only, forward-only stream of data that is returned one record at a time. If you need to retrieve many records rapidly, using a DataReader would require less memory than a DataSet, which would need to create a large table to hold the results. The following code example, which requires the User table (see below) , shows how to execute a simple query on SQL database and read the results using a DataReader:

// Create a SQL command                ace="" ns="urn:schemas-microsoft-com:office:office" prefix="o" ?>
 string strSQL    = "SELECT UserID,User_FName,User_LName FROM User_Tbl";
 SqlConnection  objConn = new SqlConnection(getConnectionString);
 SqlCommand objcmd = new SqlCommand(strSQL , objConn);
 objcmd.CommandType =CommandType.Text;
//open  connection.   
objConn.Open();
SqlDataReader  ObjDr;
ObjDr = ObjCmd.ExecuteReader(); 
while (ObjDr.Read())
{ 
Console.WriteLine(ObjDr["UserID"].ToString());
Console.WriteLine(ObjDr["User_FName"].ToString()); 
Console.WriteLine(ObjDr["User_LName"].ToString()); 
}
 ObjDr.Close();
// Close the connection 
objConn.Close();

Retrieving a Scalar Value

You can use the ExecuteScalar method of the Command object to return a single value, such as a sum or a count, from the database. The ExecuteScalar method returns the value of the first column of the first row of the result set. The following code example, which requires the User table (see below) ,shows how to retrieve the count of a specified group

// Retrievethe number of Users who whose  age is more than 24pan>// from the user table // Open connection
string  strSQL = 
    "select count(sal) FROM User_Tbl where age >'24' ; 
SqlConnection objConn = new SqlConnection(getConnectionString);
SqlCommand objcmd = new SqlCommand(strSQL,  objConn); 
objcmd.CommandType = CommandType.Text;             
objConn.Open();
try          
{ 
int count = (int)objcmd.ExecuteScalar();
}   
catch (Exception   
        ex) { // Display any exceptions in a messagebox            
MessageBox.Show (ex.Message); 
} // 
 Close the connection   
objConn.Close();

Retrieving Large Data (ADO.NET)

By default, the DataReader loads incoming data as a row as soon as an entire row of data is available. Binary large objects (BLOBs) need different treatment, however, because they can contain gigabytes of data that cannot be contained in a single row. The Command.ExecuteReader method has an overload that will take a CommandBehavior argument to modify the default behavior of the DataReader. You can pass SequentialAccess to the ExecuteReader method to modify the default behavior of the DataReader so that instead of loading rows of data, it will load data sequentially as it is received.
This is ideal for loading BLOBs or other large data structures. Note that this behavior may depend on your data source. For example, returning a BLOB from Microsoft Access will load the entire BLOB being loaded into memory, rather than sequentially as it is received. When setting the DataReader to use SequentialAccess, it is important to note the sequence in which you access the fields returned. The default behavior of the DataReader, which loads an entire row as soon as it is available, allows you to access the fields returned in any order until the next row is read. When using SequentialAccess however, you must access the fields returned by the DataReader in order. For example, if your query returns three columns, the third of which is a BLOB, you must return the values of the first and second fields before accessing the BLOB data in the third field. If you access the third field before the first or second fields, the first and second field values are no longer available. This is because SequentialAccess has modified the DataReader to return data in sequence and the data is not available after the DataReader has read past it. When accessing the data in the BLOB field, use the GetBytes or GetChars typed accessors of the DataReader, which fill an array with data.
You can also use GetString for character data; however. to conserve system resources you might not want to load an entire BLOB value into a single string variable. You can instead specify a specific buffer size of data to be returned, and a starting location for the first byte or character to be read from the returned data. GetBytes and GetChars will return a long value, which represents the number of bytes or characters returned. If you pass a null array to GetBytes or GetChars, the long value returned will be the total number of bytes or characters in the BLOB.
You can optionally specify an index in the array as a starting position for the data being read.

Example

The following example returns the publisher ID and logo from the Users Table The User ID (userID) is a character field, and the logo is an image, which is a BLOB. Because the logo field is a bitmap, the example returns binary data using GetBytes. Notice that the publisher ID is accessed for the current row of data before the logo, because the fields must be accessed sequentially.

 // Assumes that connection is a valid
 SqlConnection object from above defined class. 
 SqlCommand ObjCmd = new SqlCommand("SELECT User_id, User_Photo  FROM User_Tbl",ObjConn);
 // Writes the BLOB to a file (*.bmp).
  FileStream stream;      
 // Streams the BLOB to the FileStream object.  
     BinaryWriter writer;       
// Size of the BLOB buffer.
int bufferSize = 100;  
 // The BLOB byte[] buffer to be filled by GetBytes.
 byte[] outByte = new byte[bufferSize];  
 // The bytes returned from GetBytes.
  long retval; 
// The starting position in the BLOB output.
 long startIndex = 0;     
 // The User ID  to use in the file name.
 string UserID= "";
 // Open the connection and read data into the DataReader.
 ObjConn.Open();
 SqlDataReader ObjRd = ObjCmd.ExecuteReader(CommandBehavior.SequentialAccess);
 while (ObjDr.Read()) { // Get the
 //publisher id, which must occur before getting the logo.
 UserID =ObjDr.GetString(0);
// Create a file to hold the output.
 stream = new FileStream("Photo" + UserID +".bmp",FileMode.OpenOrCreate, FileAccess.Write);     
 writer = new BinaryWriter(stream); 
 startIndex = 0;
retval = ObjDr.GetBytes(1, startIndex, outByte, 0, bufferSize);
 while (retval == bufferSize)
 {
 writer.Write(outByte);
 writer.Flush();
 // Reposition start index to end of last buffer and fill buffer.
 startIndex += bufferSize;
 retval = reader.GetBytes(1,startIndex, outByte, 0, bufferSize);
 } // Write the remaining buffer. 
     
 writer.Write(outByte, 0, (int)retval- 1); 
 writer.Flush(); writer.Close();
     stream.Close(); } 
 ObjDr.Close();
 ObjConn.Close();

' Assumes that connection is a valid SqlConnection   object from above defined class.     

Dim ObjCmd As  New SqlCommand("SELECT User_id, User_Photo FROM User_Tbl", ObjConn)        
  ' Writes the BLOB to a file (*.bmp).FileStream stream;
  ' Streams the BLOB to the FileStream object.BinaryWriter writer;
  outByte = New Byte(bufferSize - 1) {} 
  ' Open the connection and read data into the DataReader.
   ObjConn.Open()           
    Dim ObjRd As SqlDataReader = ObjCmd.ExecuteReader(CommandBehavior.SequentialAccess)
     While ObjDr.Read()
     'Get the USER id, which must occur before getting   the logo. 
     UserID = ObjDr.GetString(0)  
       ' Create a file to hold the output.          

  stream = New FileStream("Photo" & UserID & ".bmp", FileMode.OpenOrCreate, FileAccess.Write)             
        writer = New BinaryWriter(stream)
        startIndex = 0 
      retval = ObjDr.GetBytes(1, startIndex, outByte,  0, bufferSize)    
While retval = bufferSize             
  writer.Write(outByte)           
 writer.Flush()                         
     ' Reposition start index to end of last buffer and fill buffer. 
            
 startIndex += bufferSize;        
 retval = reader.GetBytes(1, startIndex, outByte,  0, bufferSize)  
 End While
   ' Write the    remaining buffer. writer.Write(outByte, 0, (int)retval - 1);             
writer.Flush();
    writer.Close();
stream.Close()
End While    
ObjDr.Close()  
ObjConnObjConn.Close()

The SqlDataReader class defines a lightweight yet powerful object that is used to read information from a SQL database. A SqlDataReader object has a small footprint, because it doesn't contain more than a single record in memory at any time. This makes it ideal for reading large amounts of data from a database.

Properties and Methods of the SqlDataReader Class

Item	Description
*Properties*
FieldCount	Contains the number of fields retrieved from the query
IsClosed	Contains True if the SqlDataReader object is closed
Item	Contains A collection of values that are accessible both by field name and by ordinal number
RecordsAffected	Returns the number of records affected by an executed query
*Methods*
Close()	Closes the SqlDataReader object
BooleanGetBoolean()	Retrieves a value of type Boolean
GetByte()	Retrieves a value of type Byte
GetBytes()	Retrieves values of type Byte
GetChar()	Retrieves a value of type Char
GetChars()	Retrieves values of type Char
GetDataTypeName()	Retrieves the data type of a field by ordinal number
GetDateTime()	Retrieves a value of type DateTime
GetDecimal()	Retrieves a value of type Decimal
GetDouble()	Retrieves a value of type Double
GetFieldType()	Retrieve the .NET data type of a field by ordinal number
GetFloat()	Retrieves a value of type Float
GetGuid()	Retrieves a value of type GUID
GetInt16()	Retrieves a value of type Int
GetInt32()	Retrieves a value of type Int
GetInt64()	Retrieves a value of type Int
GetName()	Returns a field name by ordinal number
GetOrdinal()	Returns an ordinal number by field name
GetSchemaTable()	Returns a data table that contains a database schema
GetSqlBinary()	Retrieves a value of type SqlBinary
GetSqlBit()	Retrieves a value of type SqlBit
GetSqlByte()	Retrieves a value of type SqlByte
GetSqlDateTime()	Retrieves a value of type SqlDateTime
GetSqlDecimal()	Retrieves a value of type SqlDecimal
GetSqlDouble()	Retrieves a value of type Double
GetSqlGuid()	Retrieves a value of type SqlGuid
GetSqlInt16()	Retrieves a value of type SqlInt16
GetSqlInt32()	Retrieves a value of type SqlInt32
GetSqlInt64()	Retrieves a value of type SqlInt64
GetSqlMoney()	Retrieves a value of type SqlMoney
GetSqlSingle()	Retrieves a value of type SqlSingle
GetSqlString()	Retrieves a value of type SqlString
GetSqlValue()	Returns a SQL field value by ordinal number
GetString()	Retrieves a value of type String
GetValue()	Returns the value of field data by ordinal number
IsDBNull()	Returns True if the SQL field contains Null
NextResult()	Reads the next result in the result set into memory when reading batch T-SQL results
Read()	Reads the next result in the result set into memory

SqlDataReader.FieldCount

Syntax

Int32 FieldCount

Description The Fieldcount property contains the number of fields in the current record. Example

int FieldCount;

//Instantiate and open connection object
SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select employee_id,
lastname, firstname from Employees", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

//Get FieldCount
FieldCount = reader.FieldCount;

SqlDataReader.IsClosed

Syntax

Boolean IsClosed

Description The IsClosed property contains True if the SqlDataReader object is closed. Example

//Instantiate and open connection object

  SqlConnection conn = new SqlConnection("Data Source=localhost; User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
//Get command object SqlCommand cmd = new SqlCommand("Select employee_id, lastname, firstname from Employees", conn);
//Execute reader SqlDataReader reader = cmd.ExecuteReader();
if(reader.IsClosed != true){
//work with reader
}

SqlDataReader.Item

Syntax

Object Item(String name)
Object Item(Int32 i)

Description The Item property retrieves the value of a column in its native data format. Example

string message = "";
//Instantiate and open connection object
SqlConnection conn = new SqlConnection("Data Source=localhost; User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
//Get command object
SqlCommand cmd = new SqlCommand("Select employee_id,
lastname, firstname from Employees", conn);
//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

//Loop through all records building a string
while(reader.Read()){
message = message + reader["employee_id"] + " " + reader["lastname"] +
", " + reader["firstname"] + "
";
}
reader.Close();

SqlDataReader.RecordsAffected

Syntax

Int32 RecordsAffected

Description The RecordsAffected property contains the number of records affected by the query. Example

int RecordsAffected;

//Instantiate and open connection object
SqlConnection conn = new SqlConnection("Data Source=localhost; User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select employee_id,
lastname, firstname from Employees", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

//Get RecordsAffected
RecordsAffected = reader.RecordsAffected;

SqlDataReader.Close()

Syntax

Void Close()

Description The Close() method closes the SqlDataReader object. Example

SqlDataReader reader = cmd.ExecuteReader();
reader.Close();

SqlDataReader.GetBoolean()

Syntax

Boolean GetBoolean(Int32 i)

Description The GetBoolean method returns the value of a specified column as type boolean. Example

SqlConnection conn = new SqlConnection("Data Source=localhost; User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname, from aTable", conn);
//Execute reader
SqlDataReader reader = cmd.ExecuteReader();
Boolean value;
while(reader.Read()){
value = reader.GetBoolean(0);
}
reader.Close();

SqlDataReader.GetByte

Syntax

Byte GetByte(Int32 i)

Description The GetByte() method returns the value of a specified column as type Byte. Example

SqlConnection conn = new SqlConnection("Data Source=localhost; User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);
//Execute reader
SqlDataReader reader = cmd.ExecuteReader();
Byte value;
while(reader.Read()){
value = reader.GetByte(0);
}
reader.Close();

SqlDataReader.GetBytes

Syntax

Int32 GetBytes(Int32 i, Int32 dataIndex, Byte[buffer],
Int32 bufferIndex, Int32 length)

Description The GetBytes() method returns the value of a specified column as type Bytes. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);
//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

Byte[] value = new Byte[10];
while(reader.Read()){
reader.GetBytes(0, 0, value, 0, 10);
}
reader.Close();

SqlDataReader.GetChar()

Syntax

Char GetChar( Int32 i )

Description The GetChar() method returns the value of a specified column as type Char. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);
//Execute reader
SqlDataReader reader = cmd.ExecuteReader();
char value;
while(reader.Read()){
value = reader.GetChar(0);
}
reader.Close();

SqlDataReader.GetChars()

Syntax

Int32 GetChars(Int32 i, Int32 dataIndex, Char[] buffer,
Int32 bufferIndex, Int32 length)

Description The GetChars() method returns the value of a specified column as type Char. Example

SqlConnection conn = new SqlConnection("Data Source=localhost; User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);
//Execute reader
SqlDataReader reader = cmd.ExecuteReader();
char[] value = new char[10];
while(reader.Read()){
reader.GetChars(0, 0, value, 0, 10);
}
reader.Close();

SqlDataReader.GetDataTypeName()

Syntax

String GetDataTypeName(Int32 i)

Description The GetDataTypeName() method returns a string that contains the data type of the specified field. Example

SqlConnection conn = new SqlConnection("Data Source=localhost; User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();
//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);
//Execute reader
SqlDataReader reader = cmd.ExecuteReader();
string datatype = reader.GetDataTypeName(0);

reader.Close();

SqlDataReader.GetDateTime()

Syntax

DateTime GetDateTime(Int32 i)

Description The GetDateTime() method returns the value of a specified column as type DateTime. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

DateTime value;
while(reader.Read()){
value = reader.GetDateTime(0);
}
reader.Close();

SqlDataReader.GetDecimal()

Syntax

Decimal GetDecimal(Int32 i)

Description The GetDecimal() method returns the value of a specified column as type Decimal. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

decimal value;
while(reader.Read()){
value = reader.GetDecimal(0);
}
reader.Close();

SqlDataReader.GetDouble()

Syntax

Double GetDouble(Int32 i)

Description The GetDouble() method returns the value of a specified column as type Double. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

double value;
while(reader.Read()){
value = reader.GetDouble(0);
}
reader.Close();

SqlDataReader.GetFieldType()

Syntax

Type GetFieldType(Int32 i)

Description The GetFieldType() method returns the type of the specified field. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

Type dataType = reader.GetFieldType(0);

reader.Close();

SqlDataReader.GetFloat()

Syntax

Single GetFloat(Int32 i)

Description The GetFloat() method returns the value of a specified column as type Float. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

float value;
while(reader.Read()){
value = reader.GetFloat(0);
}
reader.Close();

SqlDataReader.GetGuid()

Syntax

Guid GetGuid(Int32 i)

Description The GetGuid() method returns the value of a specified column as type Guid. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

Guid value;
while(reader.Read()){
value = reader.GetGuid(0);
}
reader.Close();

SqlDataReader.GetInt16()

Syntax

Int16 GetInt16(Int32 i)

Description The GetInt16() method returns the value of a specified column as type Int16. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

Int16 value;
while(reader.Read()){
value = reader.GetInt16(0);
}
reader.Close();

SqlDataReader.GetInt32()

Syntax

Int32 GetInt32(Int32 i)

Description The GetInt32() method returns the value of a specified column as type Int32. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

Int32 value;
while(reader.Read()){
value = reader.GetInt32(0);
}
reader.Close();

SqlDataReader.GetInt64()

Syntax

Int64 GetInt64(Int32 i)

Description The GetInt64() method returns the value of a specified column as type Int64. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
             conn.Open(); //Get
                 command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

Int64 value;
while(reader.Read()){
value = reader.GetInt64(0);
}
reader.Close();

SqlDataReader.GetName()

Syntax

String GetName(Int32 i)

Description The GetName() method returns a string that contains the name of the SQL field. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

string fieldname = reader.GetName(0);

reader.Close();

SqlDataReader.GetOrdinal()

Syntax

Int32 GetOrdinal(String name)

Description The GetOrdinal() method returns an integer that contains the ordinal number of the specified field. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

int fieldOrdinal = reader.GetOrdinal(fieldname);

reader.Close();

SqlDataReader.GetSchemaTable()

Syntax

DataTable GetSchemaTable()

Description The GetSchemaTable() method returns a data table that contains database schema for the SqlCommand object. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

DataTable aTable = reader.GetSchemaTable();

reader.Close();

SqlDataReader.GetSqlBinary()

Syntax

SqlBinary GetSqlBinary(Int32 i)

Description The GetSqlBinary() method returns the value of a specified column as type SqlBinary. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlBinary value;

while(reader.Read()){
value = reader.GetSqlBinary(0);
}
reader.Close();

SqlDataReader.GetSqlBit()

Syntax

SqlBit GetSqlBit(Int32 i)

Description The GetSqlBit() method returns the value of a specified column as type SqlBit. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlBit value;
while(reader.Read()){
value = reader.GetSqlBit(0);
}
reader.Close();

SqlDataReader.GetSqlByte()

Syntax

SqlByte GetSqlByte(Int32 i)

Description The GetSqlByte() method returns the value of a specified column as type SqlByte. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlByte value;
while(reader.Read()){
value = reader.GetSqlByte(0);
}
reader.Close();

SqlDataReader.GetSqlDateTime()

Syntax

SqlDateTime GetSqlDateTime(Int32 i)

Description The GetSqlDateTime() method returns the value of a specified column as type SqlDateTime. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlDateTime value;
while(reader.Read()){
value = reader.GetSqlDateTime(0);
}
reader.Close();

SqlDataReader.GetSqlDecimal()

Syntax

SqlDecimal GetSqlDecimal(Int32 i)

Description The GetSqlDecimal() method returns the value of a specified column as type SqlDecimal. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlDecimal value;
while(reader.Read()){
value = reader.GetSqlDecimal(0);
}
reader.Close();

SqlDataReader.GetSqlDouble()

Syntax

SqlDouble GetSqlDouble(Int32 i)

Description The GetSqlDouble() method returns the value of a specified column as type SqlDouble. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlDouble value;
while(reader.Read()){
value = reader.GetSqlDouble(0);
}
reader.Close();

SqlDataReader.GetSqlGuid()

Syntax

SqlGuid GetSqlGuid(Int32 i)

Description The GetSqlGuid() method returns the value of a specified column as type SqlGuid. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlGuid value;
while(reader.Read()){
value = reader.GetSqlGuid(0);
}
reader.Close();

SqlDataReader.GetSqlInt16()

Syntax

SqlInt16 GetSqlInt16(Int32 i)

Description The GetSqlInt16() method returns the value of a specified column as type SqlInt16. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlInt16 value;
while(reader.Read()){
value = reader.GetSqlInt16(0);
}
reader.Close();

SqlDataReader.GetSqlInt32()

Syntax

SqlInt32 GetSqlInt32(Int32 i)

Description The GetSqlInt32() method returns the value of a specified column as type SqlInt32. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlInt32 value;
while(reader.Read()){
value = reader.GetSqlInt32(0);
}
reader.Close();

SqlDataReader.GetSqlInt64()

Syntax

SqlInt64 GetSqlInt64(Int32 i)

Description The GetSqlInt64() method returns the value of a specified column as type SqlInt64. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlInt64 value;
while(reader.Read()){
value = reader.GetSqlInt64(0);
}
reader.Close();

SqlDataReader.GetSqlMoney()

Syntax

SqlMoney GetSqlMoney(Int32 i)

Description The GetSqlMoney() method returns the value of a specified column as type SqlMoney. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlMoney value;
while(reader.Read()){
value = reader.GetSqlMoney(0);
}
reader.Close();

SqlDataReader.GetSqlSingle()

Syntax

SqlSingle GetSqlSingle(Int32 i)

Description The GetSqlSingle() method returns the value of a specified column as type SqlSingle. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlSingle value;
while(reader.Read()){
value = reader.GetSqlSingle(0);
}
reader.Close();

SqlDataReader.GetSqlString()

Syntax

SqlString GetSqlString(Int32 i)

Description The GetSqlString() method returns the value of a specified column as type SqlString. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlString value;
while(reader.Read()){
value = reader.GetSqlString(0);
}
reader.Close();

SqlDataReader.GetSqlValue()

Syntax

Object GetSqlValue(Int32 i)

Description The GetSqlValue() method returns a value of type Object, by using its native SQL data type. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

System.Data.SqlTypes.SqlString value;
while(reader.Read()){
value = reader.GetSqlValue(0);
}
reader.Close();

SqlDataReader.GetString()

Syntax

String GetString(Int32 i)

Description The GetString() method returns the value of a specified column as type String. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

string value;
while(reader.Read()){
value = reader.GetString(0);
}
reader.Close();

SqlDataReader.GetValue()

Syntax

Object GetValue(Int32 i)

Description The GetValue() method returns the value of type Object, by using the Microsoft .NET framework types. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

string value;
while(reader.Read()){
value = reader.GetValue(0);
}
reader.Close();

SqlDataReader.IsDBNull()

Syntax

Boolean IsDBNull(Int32 i)

Description The IsDBNull() method returns True if the specified column is null and False otherwise. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

string value;
while(reader.Read()){
if(reader.IsDBNull(0) != true)
value = reader.GetValue(0);
}
reader.Close();

SqlDataReader.NextResult()

Syntax

Boolean NextResult()

Description The NextResult() method advances the data reader to the next record. It is used when reading the result of SQL batch statements. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("exec SqlBatch", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

string value;
while(reader.NextResult()){
value = reader.GetValue(0);
}
reader.Close();

SqlDataReader.Read()

Syntax

Boolean Read()

Description The Read() method advances the data reader to the next record. It returns True if there are more records and False otherwise. Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

//Get command object
SqlCommand cmd = new SqlCommand("Select fieldname from aTable", conn);

//Execute reader
SqlDataReader reader = cmd.ExecuteReader();

string value;
while(reader.Read()){
value = reader.GetValue(0);
}
reader.Close();

DataReaders are fast, flexible, and powerful tools for data access to remote data sources. This article highlights three specific kinds of applications for DataReaders in .NET applications, but there are many others. Exploiting DataReaders in your custom solutions will make them go faster and even offer you the chance to reinforce your basic .NET development skills. You'll often be able to derive additional value from DataReaders by using them in concert with arrays.

Summary:

So it is clear that Datareader requires live connection to fetch data from datasource and is user to just display data,No modification can be done by using datareader.SqlDataReader can be used if you want to keep an open connection to the database and use the rows as they come to you (forward only stream of data).
When you use SqlHelper.ExecuteDataSet, what happens is it serializes the data to XML and returns it to you at the end of the GET operation. However, with SqlDataReader, you get the rows as they come, which can be handy if there is a lot of rows or if you have some sort of parallel processing you want to do while the data is still coming.