The reason your connection is closed pre-maturely is that you don't return from the function until after you exit the Using block. The act of leaving the Using block will force your connection to close immediately. Setting the datareader as your returned object is not enough. Even using an explicit Return statement would not be enough... leaving the function would still mean leaving the Using block, and so your connection is still closed before you ever get to use the datareader.
To get around all that, I use a pattern that looks like this:
Public Iterator Function ExecuteDataReader(Of T)(ByVal sql As String, ByVal addParams as Action(Of SqlParameterCollection), ByVal castRow As Funnction(Of IDataRecord, T)) As IEnumerable(Of T)
Using cn As SqlConnection = getConnection(), _
cmd As New SqlCommand(sql, cn)
addParams(cmd.Parameters)
cn.Open()
Using rdr As SqlDataReader = cmd.ExecuteReader()
While rdr.Read()
Yield castRow(rdr)
End While
End Using
End Using
End Function
I would then call that function like this:
Dim results As IEnumerable(Of Customer) = ExecuteDataReader( _
"SELECT * FROM Customer WHERE Sales> @MinSales", _
Sub(p) p.Add("@MinSales", SqlDbType.Double).Value = 10000.0, _
Function(r) New Customer() With {Name=r("Name"), Address=r("Address"), Sales=r("Sales") })
For Each c As Customer in results
'...
Next
Let's go over that pattern a bit, because there are some things that can confuse... namely I want to cover the delegate arguments.
First up is the addParameter argument. You need to understand is that your original pattern is horribly broken, because it forces you to create code riddled with Sql injection vulnerabilities, as there is no other way to send parameter information. That is a huge problem. Thankfully, it's easily solved. This is what the addParameter argument is for. It's not the only way to do this — ie, you could do something as simple as pass an array of key/values/type as well — but I like it because it avoids duplicate work going through the array or duplicate memory storing the parameter data twice.
Next is the castRow argument. This is necessary because without it you run into a similar problem you see with your example. Here, the code would still run, but because you keep yielding the same object code elsewhere would end up all working with the final record in the results. This way, you get the correct expected results, and you get them in a strongly-typed manner.
Moving on, hopefully you're already familiar with the Iterator and Yield keywords, but as they're relatively new to VB.Net it's okay if you're not. Just know that they cause the compiler to transform this code into something that will let iterate over your datareader object.
