DataReader In C#
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.
John Hudai Godel
Aug 31 2018
2
0
214.2k
facebook
twitter
lin kedIn
google Plus
Reddit
o
o
o
o
Expand
Download Free .NET & JAVA Files API
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.
1. 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.
1. while (myReader.Read())
2. Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
3. 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.
1. SqlCommand myCMD = new SqlCommand("SELECT CategoryID, CategoryName FROM Categories;" +
"SELECT EmployeeID, LastName FROM Employees", nwindConn);
2. nwindConn.Open();
3. SqlDataReader myReader = myCMD.ExecuteReader();
4. do {
5. Console.WriteLine("\t{0}\t{1}", myReader.GetName(0), myReader.GetName(1));
6. while (myReader.Read()) Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myRea
der.GetString(1));
7. }
8. while (myReader.NextResult());
9. myReader.Close();
10. 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.
1. using System;
2. using System.Data;
3. using System.Globalization;
4. namespace DotNetDataProviderTemplate {
5. publicclass TemplateDataReader: IDataReader {
6. // The DataReader must always be open when returned to the user.
7. privatebool dReaderOpen = true;
8. // Keep track of the results and position
9. // within the resultset (starts prior to first record).
10. private TestDataBase.TestDataBaseResultSet testResultset;
11. privatestaticint testSTARTPOS = -1;
12. privateint testNPos = testSTARTPOS;
13. private TemplateConnection testconnection = null;
14. internal TemplateDataReader(TestDataBase.TestDataBaseResultSet resultset) {
15. testResultset = resultset;
16. }
17. internal TemplateDataReader(TestDataBase.TestDataBaseResultSet resultset, empla
teConnection connection) {
18. testResultset = resultset;
19. testconnection = connection;
20. }
21. publicint Depth {
22. get {
23. return 0;
24. }
25. }
26. publicbool IsClosed {
27. get {
28. return !dReaderOpen;
29. }
30. }
31. publicint RecordsAffected {
32. get {
33. return -1;
34. }
35. }
36. publicvoid Close() {
37. dReaderOpen = false;
38. }
39. publicbool NextResult() {
40. returnfalse;
41. }
42. publicbool Read() {
43. if (++testNPos >= testResultset.data.Length / testResultset.metaData.Length
) returnfalse;
44. else returntrue;
45. }
46. public DataTable GetSchemaTable() {
47. thrownew NotSupportedException();
48. }
49. publicint FieldCount {
50. get {
51. return testResultset.metaData.Length;
52. }
53. }
54. public String GetName(int i) {
55. return testResultset.metaData[i].name;
56. }
57. public String GetDataTypeName(int i) {
58. return testResultset.metaData[i].type.Name;
59. }
60. public Type GetFieldType(int i) {
61. return testResultset.metaData[i].type;
62. }
63. public Object GetValue(int i) {
64. return testResultset.data[testNPos, i];
65. }
66. publicint GetValues(object[] values) {
67. for (int i = 0; i < values.Length && i < testResultset.metaData.Length; i++
) {
68. values[i] = testResultset.data[testNPos, i];
69. }
70. return i;
71. }
72. publicint GetOrdinal(string name) {
73. for (int i = 0; i < testResultset.metaData.Length; i++) {
74. if (0 == _cultureAwareCompare(name, testResultset.metaData[i].name)) {
75. return i;
76. }
77. }
78. thrownew IndexOutOfRangeException("Could not find specified column in resul
ts");
79. }
80. publicobjectthis[int i] {
81. get {
82. return testResultset.data[testNPos, i];
83. }
84. }
85. publicobjectthis[String name] {
86. get {
87. returnthis[GetOrdinal(name)];
88. }
89. }
90. publicbool GetBoolean(int i) {
91. return (bool) testResultset.data[testNPos, i];
92. }
93. publicbyte GetByte(int i) {
94. return (byte) testResultset.data[testNPos, i];
95. }
96. publiclong GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, i
nt length) {
97. thrownew NotSupportedException("GetBytes not supported.");
98. }
99. publicchar GetChar(int i) {
100. return (char) testResultset.data[testNPos, i];
101. }
102. publiclong GetChars(int i, long fieldoffset, char[] buffer, int bufferof
fset, int length) {
103. thrownew NotSupportedException("GetChars not supported.");
104. }
105. public Guid GetGuid(int i) {
106. return (Guid) testResultset.data[testNPos, i];
107. }
108. public Int16 GetInt16(int i) {
109. return (Int16) testResultset.data[testNPos, i];
110. }
111. public Int32 GetInt32(int i) {
112. return (Int32) testResultset.data[testNPos, i];
113. }
114. public Int64 GetInt64(int i) {
115. return (Int64) testResultset.data[testNPos, i];
116. }
117. publicfloat GetFloat(int i) {
118. return (float) testResultset.data[testNPos, i];
119. }
120. publicdouble GetDouble(int i) {
121. return (double) testResultset.data[testNPos, i];
122. }
123. public String GetString(int i) {
124. return (String) testResultset.data[testNPos, i];
125. }
126. public Decimal GetDecimal(int i) {
127. return (Decimal) testResultset.data[testNPos, i];
128. }
129. public DateTime GetDateTime(int i) {
130. return (DateTime) testResultset.data[testNPos, i];
131. }
132. public IDataReader GetData(int i) {
133. thrownew NotSupportedException("GetData not supported.");
134. }
135. publicbool IsDBNull(int i) {
136. return testResultset.data[testNPos, i] == DBNull.Value;
137. }
138. privateint _cultureAwareCompare(string strA, string strB) {
139. return
140. CultureInfo.CurrentCulture.CompareInfo.Compare(strA, strB, ompareOpt
ions.IgnoreKanaType | CompareOptions.IgnoreWidth | ompareOptions.IgnoreCase);
141. }
142. }
143. }
* 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.
