DataReader class in ADO.NET connections

Question

Hi, Ques What's the role of the DataReader class in ADO.NET connections ?

Satyapriya Nayak · Answer

Hi Arun, 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. Normal 0 DataReader represents only one database record at a time. You must call the Read() method to fetch each new record from the underlying database table into memory. Each time you call Read() again, the previously fetched record is lost. DataReader must remain connected to a database table. A DataReader is tied down to its underlying data source. It returns a forward-only, read-only view of datafrom the data source when the command is executed. 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 GetBoolean() 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(); Refer http://www.triconsole.com/dotnet/sqldatareader_class.php Thanks

Jignesh Trivedi · Answer

Hi, ADO.NET DataReader is used to retrieve a read-only, forward-only stream of data from a database. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. Changes made to a result set by another process or thread while data is being read may be visible to the user of the SqlDataReader. the precise behavior is timing dependent. please refer http://msdn.microsoft.com/en-us/library/haa3afyz.aspx working with datareader.

Senthilkumar · Answer

Hi, The SqlDataReader is class in System.Data.SqlClient namespace, which is used to read the data from the sql server in connected database. It is connection oriented architecture and it reads the underleying connected database in demand basis. It is read only, forward only and it is fast to retrieve the data. Microsoft suggest to use the DataReader because it read only and reads the record at a time, minimum usage of the memory. It can go front and back because it does not have the cursor. It cannot write the data because it is read only. This class cannot be inherited because it is sealed by default. In order to fill the data in the DataReader class, we need to execute the ExecuteReader() method in SqlCommand object. For more info: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

Vulpes · Answer

A DataReader is used to read the records returned by a query one after the other. Records can only be read in a forward direction and cannot be changed. This improves performance because a record can be read as soon as the query returns it rather than waiting for all the records to be returned. Also memory usage is minimized because only a single record needs to be in memory at any one time.

DataReader class in ADO.NET connections

Answers (4)