ASP.NET, Right way to use DataReader

Question

I want to know right way to use DataReader. (C# and Advantage Database)

Assuming that I have Order,Item and Customer tables in my database.

and I need to read the data from each tables in a cs file.

So I opened the database connection and read data using DataReader.

like,

AdsConnection conn = new AdsConnection("~~~~");
AdsCommand cmd;
AdsDataReader reader;

conn.open();
cmd = conn.CreateCommand();
cmd.CommandText = "Select * from order";
reader = cmd.ExecuteReader();

and Now I need to read the other table. but I think I need to close the connection and reader and reconnect and redefine them.

So, I define the other reader.

conn.Close();
conn.Open();
AdsDataReader itemReader;
cmd.CommandText = " Select * from item";
itemReader = cmd.ExecuteReader();

.
.
reader.close();
itemReader.close();
conn.Close();

Is it ok? using like this way? Anybody know better way, please advice me ~

Thank you!

Answer 1

ADO.NET 2.0 introduced a feature called MARS- Multiple Active Result Sets. This allows you to submit multiple queries to the database and retrieve them with a single call.

Here is the MSDN article with a code sample that uses MARS:

http://msdn.microsoft.com/en-us/library/yf1a7f4f(v=vs.80).aspx

Note that the connection string sets the MultipleActiveResultSets property to true.