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();
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();