Reader Level:
Articles

DataReader in C#

By John Hudai Godel on February 17, 2004
The ADO.NET DataSet is a data construct that can contain several relational rowsets, the relations that link those rowsets, and the metadata for each rowset. The DataSet also tracks which fields have changed, their new values and their original values, and can store custom information in its Extended Properties collection. The DataSet can be exported to XML or created from an XML document, thus enabling increased interoperability between applications.
  • 0
  • 0
  • 71616


A Key Class in ADO.NET: DataReader

The DataReader object is used for accessing data from the data store and is one of the two mechanisms that ADO.NET provides. As we will remember DataReader object provides a read only, forward only, high performance mechanism to retrieve data from a data store as a data stream, while staying connected with the data source. The DataReader is restricted but highly optimized. The .NET framework provides data providers for SQL Server native OLE DB providers and native ODBC drivers:

  • SqlDataReader
  • OleDbDataReader
  • OdbcDataReader

You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Using the DataReader can increase application performance and reduce system overhead because only one row at a time is ever in memory. After creating an instance of the Command object, you create a DataReader by calling Command.ExecuteReader to retrieve rows from a data source, as shown in the following example.

SqlDataReader myReader = myCommand.ExecuteReader();

You use the Read method of the DataReader object to obtain a row from the results of the query. You can access each column of the returned row by passing the name or ordinal reference of the column to the DataReader. However, for best performance, the DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on). For a list of typed accessor methods, see the OleDbDataReader Class and the SqlDataReader Class. Using the typed accessor methods when the underlying data type is known will reduce the amount of type conversion required when retrieving the column value.

The following code example iterates through a DataReader object, and returns two columns from each row.


while
(myReader.Read())
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
myReader.Close();


The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when retrieving large amounts of data because the data is not cached in memory. You should always call the Close method when you have finished using the DataReader object. If your Command contains output parameters or return values, they will not be available until the DataReader is closed.

Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You will not be able to execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.

Multiple Result Sets

If multiple result sets are returned, the DataReader provides the NextResult method to iterate through the result sets in order, as shown in the following code example.

SqlCommand myCMD =
new SqlCommand("SELECT CategoryID, CategoryName FROM Categories;" + "SELECT EmployeeID, LastName FROM Employees", nwindConn);
nwindConn.Open();
SqlDataReader myReader = myCMD.ExecuteReader();
do
{
Console.WriteLine("\t{0}\t{1}", myReader.GetName(0), myReader.GetName(1));
while
(myReader.Read())
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
}
while
(myReader.NextResult());
myReader.Close();
nwindConn.Close(); 


The DataReader implementation must provide two basic capabilities: forward-only access over one or more of the resultsets obtained by executing a Command, and access to the column values within each row. Data types from your data source will be stored in your .NET-based application as .NET Framework types. Your DataReader implementation will also provide strongly typed accessor methods for your DataReader that return column values as .NET Framework types. Examples of a strongly typed accessor would be GetInt32, GetString, and so on.

If your .NET data provider has proprietary types that cannot adequately be exposed as .NET Framework types, you may extend the interfaces to support proprietary types, then add typed accessors for your DataReader that return proprietary types as well. For example, you can add GetMyStructure, GetMyTimeStamp, and so on. An example of this is the SQL Server .NET Data Provider, which exposes proprietary types using the System.Data.SqlTypes Namespace. The SqlDataReader then exposes those types as SqlTypes using strongly typed accessor methods. For example: GetSqlBinary, GetSqlDateTime, GetSqlDecimal, and so on.


using
System;
using
System.Data;
using
System.Globalization;
namespace
DotNetDataProviderTemplate
{
public class
TemplateDataReader : IDataReader
{
// The DataReader must always be open when returned to the user.
private bool dReaderOpen = true
;
// Keep track of the results and position
// within the resultset (starts prior to first record).
private
TestDataBase.TestDataBaseResultSet testResultset;
private static int
testSTARTPOS = -1;
private int
testNPos = testSTARTPOS;
private TemplateConnection testconnection = null
;
internal
TemplateDataReader(TestDataBase.TestDataBaseResultSet resultset)
{
testResultset = resultset;
}
internal
TemplateDataReader(TestDataBase.TestDataBaseResultSet resultset,
emplateConnection connection)
{
testResultset = resultset;
testconnection = connection;
}
public int
Depth
{
get { return
0; }
}
public bool
IsClosed
{
get { return
!dReaderOpen; }
}
public int
RecordsAffected
{
get { return
-1; }
}
public void
Close()
{
dReaderOpen =
false
;
}
public bool
NextResult()
{
return false
;
}
public bool
Read()
{
if
(++testNPos >= testResultset.data.Length / testResultset.metaData.Length)
return false
;
else
return true
;
}
public
DataTable GetSchemaTable()
{
throw new
NotSupportedException();
}
public int
FieldCount
{
get { return
testResultset.metaData.Length; }
}
public String GetName(int
i)
{
return
testResultset.metaData[i].name;
}
public String GetDataTypeName(int
i)
{
return
testResultset.metaData[i].type.Name;
}
public Type GetFieldType(int
i)
{
return
testResultset.metaData[i].type;
}
public Object GetValue(int
i)
{
return
testResultset.data[testNPos, i];
}
public int GetValues(object
[] values)
{
for (int
i = 0; i < values.Length && i < testResultset.metaData.Length; i++)
{
values[i] = testResultset.data[testNPos, i];
}
return
i;
}
public int GetOrdinal(string
name)
{
for (int
i = 0; i < testResultset.metaData.Length; i++)
{
if
(0 == _cultureAwareCompare(name, testResultset.metaData[i].name))
{
return
i;
}
}
throw new
IndexOutOfRangeException("Could not find specified column in results");
}
public object this [ int
i ]
{
get { return
testResultset.data[testNPos, i]; }
}
public object this
[ String name ]
{

get
{ return this[GetOrdinal(name)]; }
}
public bool GetBoolean(int
i)
{
return (bool
)testResultset.data[testNPos, i];
}
public byte GetByte(int
i)
{
return (byte
)testResultset.data[testNPos, i];
}
public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int
length)
{
throw new
NotSupportedException("GetBytes not supported.");
}
public char GetChar(int
i)
{
return (char
)testResultset.data[testNPos, i];
}
public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int
length)
{
throw new
NotSupportedException("GetChars not supported.");
}
public Guid GetGuid(int
i)
{
return
(Guid)testResultset.data[testNPos, i];
}
public Int16 GetInt16(int
i)
{
return
(Int16)testResultset.data[testNPos, i];
}
public Int32 GetInt32(int
i)
{
return
(Int32)testResultset.data[testNPos, i];
}
public Int64 GetInt64(int
i)
{
return
(Int64)testResultset.data[testNPos, i];
}
public float GetFloat(int
i)
{
return (float
)testResultset.data[testNPos, i];
}
public double GetDouble(int
i)
{
return (double
)testResultset.data[testNPos, i];
}
public String GetString(int
i)
{
return
(String)testResultset.data[testNPos, i];
}
public Decimal GetDecimal(int
i)
{
return
(Decimal)testResultset.data[testNPos, i];
}
public DateTime GetDateTime(int
i)
{
return
(DateTime)testResultset.data[testNPos, i];
}
public IDataReader GetData(int
i)
{
throw new
NotSupportedException("GetData not supported.");
}
public bool IsDBNull(int
i)
{
return
testResultset.data[testNPos, i] == DBNull.Value;
}
private int _cultureAwareCompare(string strA, string
strB)
{
return

CultureInfo.CurrentCulture.CompareInfo.Compare(strA, strB,
ompareOptions.IgnoreKanaType | CompareOptions.IgnoreWidth |
ompareOptions.IgnoreCase);
}
}


* IgnoreKanaType Specifies that the string comparison must ignore the Kana type. Kana type refers to Japanese hiragana and katakana characters, which represent phonetic sounds in the Japanese language.

Summary

In this article we had a discussion about the DataSet and its role in data-oriented applications. The DataSet is main one of the main components and it is important to understand to DataAdapter, DataTable, DataView, DataGrid and other objects in ADO.NET. Finally we create an example, which it has several functionality about DataSet and its relations with other ADO.NET classes. Next article we will discuss about multiple data tables and it will give us more idea on complex, advanced DataSets.

COMMENT USING

Trending up