SQl cmd.ExecuteReader() what does it return

Question

I'm studying ASP.NET. I wondered if cmd.ExecuteReader()'s output could be temporally stored into something, like a temp variable, to later re-use it or alter it. I often use temp variables to store stuff.

How can I let a dropbox and a gridview both work with the result of cmd.exectuteReader. I don't want to create a new SQL connection for it.

A variable t might keep the content, but obviously I'm wrong here since it doesn't work. It executes the reader twice, and on the second run there is no data to fill the dropdown box.

How should i do that ?

 protected void Page_Load(object sender, EventArgs e)
 {
    string cs = System.Configuration.ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; // reading by name DBCS out of the web.config file
    using (SqlConnection con = new SqlConnection(cs))
    {

      SqlCommand cmd = new SqlCommand("Select * from tblEmployees", con);
      con.Open();

       var t = cmd.ExecuteReader();

       GridView1.DataSource = t;// cmd.ExecuteReader();
       GridView1.DataBind();

        // DropDownList2.DataSource = cmd.ExecuteReader();
        DropDownList2.DataSource = t;//cmd.ExecuteReader();

       DropDownList2.DataTextField = "Name";
       DropDownList2.DataValueField = "EmployeeId";
       DropDownList2.DataBind();
    }
}

Answer 1

SqlDataReader is a forward-only stream of rows from a SQL Server database.

You can bind a SqlDataReader to a GridView in the following ways:

Simple Examples:

    connection.Open();
    command.Connection = connection;
    SqlDataReader reader = command.ExecuteReader();
    GridView1.DataSource = reader;
    GridView1.DataBind();

Or:

 DataTable dt = new DataTable();
 dt.Load(cmd.ExecuteReader());
 GridView1.DataSource = dt;

Don't forget to configure the columns inside the Gridview control.