DataReader Interview Questions

Can dataReader hold data from multiple tables?

Yes, DataReader can hold data from multiple tables.

 SqlConnection con = new SqlConnection("server=sugandha;initial catalog = dotnet;uid=sa;pwd=sugandha");
        string query = "select * from Employee; select * from Employee1";
        con.Open();
        SqlCommand cmd = new SqlCommand(query, con);

       SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {

            dr.Read();

            GridView1.DataSource = dr;

            GridView1.DataBind();

            if (dr.NextResult())
            {

                GridView2.DataSource = dr;

                GridView2.DataBind();

            }

        }

        dr.Close();

        con.Close();
  
How can we retrieve two tables of data at a time by using DataReader?

If we pass two queries in the commandText then we get data from two tables.
It return 2 tables in DataReader.

like :
string str="Select * from a;select * from b";
cmd.CommandText=str;
dr=cmd.ExecuteReader();

What is difference between dataset and datareader ?

Following are some major differences between dataset and datareader :

1. DataReader provides forward-only and read-only access to data , while the DataSet object can hold more than one table   from the same data source as well as the relationships between them.
2. Dataset is a disconnected architecture while datareader is connected architecture.
3. Dataset can persists contents while datareader can not persist contents , they are forward only.

I want to force the DataReader to return only schema of the datastore rather than data ?

objDataReader = objCommand.ExecuteReader(CommandBehavior.SchemaOnly)

How can we force the connection object to close after my DataReader is closed ?

ExecuteReader takes a parameter called as CommandBehavior where in we can specify saying close connection automatically after the DataReader is close.
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)