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()

Answer 1

You have a few options:

1. Create a new connection to your database, and create the second data reader from that.

2. Use a SqlDataAdapter, and dump your queries into in-memory DataTables, and loop through them.

3. 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.

Answer 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.