Reusing datasource

Question

I'm tying to use one database call and reuse that data for other controls - without having to do another call. Scenario: I call the books table which returns all the authors and titles. I create an author's list control called list1 to displays all the titles by Shakespeare and a list2 to display titles by Charles Dickens.

Void Bindme()
{

string commandText = "Select * from books";

        SqlCommand mycommand = new SqlCommand(commandText, datasource1);
        datasource1.Open();
        SqlDataReader myReader1 = mycommand.ExecuteReader();


        list1.DataSource = myReader1;
        list1.DataBind();

        list2.DataSource = myReader1;
        list2.DataBind();

        datasource1.Close();
}

In my example only the first bind to the source, list1, gets data. Any ideas?

Answer 1

You can do that Like following.

string commandText = "Select * from books where firstcondition;Select * from books where secondcondition";

        SqlCommand mycommand = new SqlCommand(commandText, datasource1);
        datasource1.Open();
        SqlDataReader myReader1 = mycommand.ExecuteReader();

 list1.DataSource = myReader1;
 list1.DataBind();

 myReader1.NextResultSet();

 list2.DataSource = myReader1;
 list2.DataBind();