A nested SQL query within a while loop in ASP.NET

Question

I intended to do another SQL query inside here and retrieve data from another table by using the "category_id"

I know the problems that asp.net required me to close the data reader before proceed to another query. But is there any solution for me to do another query and open another data reader within the opening data reader?

My current code is as follows...

Dim dr, dr2 As SqlDataReader
Dim conn As SqlConnection
Dim cmd, cmd2 As SqlCommand

conn = New SqlConnection("server=XXX-PC;user=sa;password=abc123321;database=xxx")
cmd = New SqlCommand("SELECT * FROM category ORDER BY category_Name", conn)
conn.Open()

dr = cmd.ExecuteReader()

Do While dr.Read()
Dim category_id As Integer = dr.GetInt32(0)
Dim category_name As String = dr.GetString(1)

/* Another data reader and query here */

Loop
dr.Close()
conn.Close()

duplicate question probably Can I avoid the open DataReader exception when using nested commands? — Rashmi Kant Shrivastwa
– Rashmi Kant Shrivastwa, Commented Dec 2, 2011 at 6:19

Adam Rackis · Accepted Answer · 2011-12-02 06:15:36Z

2

You have a few options:

Create a new connection to your database, and create the second data reader from that.
Use a SqlDataAdapter, and dump your queries into in-memory DataTables, and loop through them.
Use an Object-Relational mapper, like NHibernate, or Entity Framework, and obviate all these problems completely.

2 would probably be the simplest, and quickest to implement. 3 will require a bit of a learning curve, but would likely be worth it in the long run. 1 is actually a terrible idea; don't do it. I probably shouldn't even have listed it.

answered Dec 2, 2011 at 6:15

Adam Rackis

83.5k57 gold badges281 silver badges402 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam Lear · Accepted Answer · 2011-12-05 06:30:24Z

2

You can use MARS the ultimate feature of vs2005 [Multiple Active Result Sets ] instead of datareader use Idatareader

Dim dr, dr2 As IDataReader
Dim conn As SqlConnection
Dim cmd, cmd2 As SqlCommand

conn = New SqlConnection("server=XXX-PC;user=sa;password=abc123321;database=xxx")
cmd = New SqlCommand("SELECT * FROM category ORDER BY category_Name", conn)
conn.Open()

dr = cmd.ExecuteReader()

Do While dr.Read()
Dim category_id As Integer = dr.GetInt32(0)
Dim category_name As String = dr.GetString(1)

/* Another data reader and query here */

cmd2.CommandText=” your query”
dr2 = cmd2.ExecuteReader();


Loop
dr2.Close();

dr.Close()
conn.Close()

MARS is disabled by default on the Connection object. You have to enable it with the addition of MultipleActiveResultSets=true in your connection string.

edited Dec 5, 2011 at 6:30

Adam Lear♦

38.9k13 gold badges83 silver badges105 bronze badges

answered Dec 2, 2011 at 6:28

Rashmi Kant Shrivastwa

1,1778 silver badges17 bronze badges

Comments

Prathmesh Shah · Accepted Answer · 2014-05-30 21:07:56Z

0

Create a Separate function, and create private data adapter & data set into it and perform your logic then return value to main procedure.

answered May 30, 2014 at 21:07

Prathmesh Shah

1

Collectives™ on Stack Overflow

A nested SQL query within a while loop in ASP.NET

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related