DataReader class in ADO.NET connections

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.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

0

Jignesh Trivedi14y ago

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.

0

Senthilkumar14y ago

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

0

Vulpes14y ago

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.

0