Jitesh Byahut 11/24/2014 2648

ExecuteReader and DataReader in ADO.NET

ExecuteReader used to retrieve rows from database that return type is DataReader. DataReader will works with connection oriented architecture. It is read only forward only cursor. DataReader object is always remains connected to database when you are using it. It is read only means its used for only select not for update and delete sql statement. It is forward only means its move only forward, once you read the row you can not move backward.

Make sure that you have to close the DataReader object before closing to database connection.

Properties of DataReader

HasRow

HasRow return true, if datareader contains results otherwise it returns false.

FieldCount

Count total number of columns in datareader result set.

IsClosed

Returns true if datareader is closed otherwise return false.

RecordsAffected

Get total number of row affected in inseted,updated or deleted.

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;";

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Select Id, Name, Points from tblTest";

SqlDataReader dr = cmd.ExecuteReader();

       

// Check SqlDataReader contains row or not

if (dr.HasRows)

{

    // Get total number of column

    int columns = dr.FieldCount;

    // Check SqlDataReader is closed or not

    bool isClose = dr.IsClosed;       

}

dr.Close();   

con.Close();

Methods of DataReader

Close()

Close the data reader

GetName(int i)

Returns name of column on specified index.

GetOrdnal(string FieldName)

Returns index of column of specified column name.

GetSchemaTable()

Returns schema data table of database.

NextResult()

Move the cursor to the next result set of data reader when multiple query is defined.Returns true if record is present otherwise returns false.

Read()

Move the cursor to the next record. Returns true if record is present otherwise returns false.

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;";

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Select * from tblTest";

SqlDataReader dr = cmd.ExecuteReader();

if (dr.HasRows)

{   // Get first column name

    string firstColumn = dr.GetName(0);

    // Get index of column "Name"

    int nameIndex = dr.GetOrdinal("Name");

    // Get schema of database

    DataTable dt = dr.GetSchemaTable();

 

    while (dr.Read())

    {               

        Console.WriteLine(dr[0] + "," + dr[1] + "," + dr[2]);

    }

}

dr.Close();

con.Close();


Please give your feedback for improving this page