CODEWITHSUNDEEP
c#asp.netsqldatareaderdatareaderadvantage-database-server
Expert wanna be
Expert wanna be

Reputation: 10624

ASP.NET, Right way to use DataReader

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!

Upvotes: 4

Views: 4072

Answers (3)

cavin luo
cavin luo

Reputation: 13

i suggest u to learn or use SQLHelper

http://www.sharpdeveloper.net/source/SqlHelper-Source-Code-cs.html

Upvotes: 0

Henk Holterman
Henk Holterman

Reputation: 273244

(as the more appropriate answer was deleted)

You can keep the Connection open but you should, as a matter of good practice, close the Command and the Reader as soon as possible.

Best way is to put of each these resources in a using() { } statement.

If it is for instance possible or beneficial to put each query in a separate method, then use a separate Connection for each connection. As long as your db supports connection-pooling there will be no loss in performance.

One more points:

  • don't use SELECT *, spell out the columns

Upvotes: 2

Dave Swersky
Dave Swersky

Reputation: 34810

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.

Upvotes: 4

Related Questions