VB.NET - Multiple SQLDataReader's

Question

I develop a lot in ASP.NET and I know that you can only open one SQLDataReader for each SQLConnection. However, this does not appear to be the case in VB.NET (form application) i.e. I have opened multiple SQLDataReaders for one connection object. Is this allowed in VB.NET?

If there is not an obvious answer to this then I will post some code.

Here is some code:

Public Function CheckActiveReviews()
            Dim objCon As SqlConnection
            Dim objCommand As SqlCommand, objCommand2 As SqlCommand
            Dim objDR As SqlDataReader, objDR2 As SqlDataReader
            Try
                objCon = New SqlConnection("Data Source=TestDatabase;Initial Catalog=TestTable;User ID=TestUser;Password=TestPassword;MultipleActiveResultSets=True")
                objCommand = New SqlCommand
                objCommand.Connection = objCon
                objCommand2 = New SqlCommand
                objCommand2.Connection = objCon
                objCon.Open()
                objCommand.CommandText = "SELECT ID FROM Person WHERE PersonID > 1000"
                objDR = objCommand.ExecuteReader()
                Do While objDR.Read
                    objCommand2.CommandText = "SELECT * FROM Sport WHERE PersonID = @PersonID "
                    objCommand2.Parameters.AddWithValue("@PersonID", objDR("ID"))
                    objDR2 = objCommand2.ExecuteReader
                Loop

            Catch ex As Exception

            End Try

        End Function

For performance, use Stored Procedures rather than Ad-Hoc queries. — Jeremy Thompson
– Jeremy Thompson, Commented Jul 6, 2012 at 3:07

Jon Skeet · Accepted Answer · 2012-07-04 16:27:32Z

2

You can use multiple data readers if you use MARS - Multiple Active Result Sets - but I wouldn't advise that unless you really need it.

Instead, I'd suggest creating a new SqlConnection object each time you need it - use it for as short a period as you can, then dispose of it (use a Using statement to do this for you). The connection pool will take care of the efficiency in terms of reusing "physical" network connections where possible. That way you don't need to worry about whether the SqlConnection is already open etc - you just always follow the same "create, open, use, dispose" pattern every time.

answered Jul 4, 2012 at 16:27

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

8 Comments

w0051977 Over a year ago

Thanks. If you have a SQL statement in a WHILE loop then I don't think your pattern above will work? Are you able to confirm if this is the case?

Jon Skeet Over a year ago

@w0051977: Why wouldn't it work? If you're using the connection in a while loop then it's fine to use one connection for that whole block - it would work if you created a new connection on each iteration, but it would be slightly pointless. (You should close each command, mind you.) Basically it's hard to know whether or not something would work without more specifics, but it's entirely possible to execute SQL in a loop.

w0051977 Over a year ago

I think I see what you mean. When you say "create a new connection on every loop" are you referring to shadowing?

Jon Skeet Over a year ago

@w0051977: I never said that, so it's hard to know what you mean. Present actual code (e.g. in the question) and we can discuss that. I'm certainly not talking about shadowing of variables though.

w0051977 Over a year ago

OK, thanks. I have put some code in the question. Could you take a look?

|

Collectives™ on Stack Overflow

VB.NET - Multiple SQLDataReader's

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related