Have function return SqlDatReader and then read results. How?

[D. Shane Fowlkes]

Up until now, I've always had my functions return integers, strings, or
booleans. Now, I've (hopefully) written a function to return a 2 column,
single row datareader. Assuming I did this correctly (the function), how
could I look at the results of the function in page_load and get the values?

A little guidance would be great. Thanks once again!!

(using ASP/VB .NET 2 and VWD)




Protected Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)

Dim drAppData As SqlDataReader
Dim intDaysLeft As Integer
Dim strCloseDate As String

drAppData = DaysLeftInAppSeason(1)
intDaysLeft = drAppData("DaysLeft")
strCloseDate = drAppData("CloseDate")

....etc...

End Sub



*************************************************



Protected Function DaysLeftInAppSeason(ByVal intAppID As Integer) As
SqlDataReader


Dim objConnection As SqlConnection
Dim cmdSelect As SqlCommand
Dim drData As SqlDataReader
Dim strConnectString As String
Dim strSQL As String

strConnectString = yadda.....

strSQL = "SELECT CloseDate, DateDiff(Day, GetDate(), CloseDate) AS DaysLeft
FROM MyTable WHERE ID = " & intAppID

objConnection = New SqlConnection(strConnectString)
cmdSelect = New SqlCommand(strSQL, objConnection)

objConnection.Open()
drData = cmdSelect.ExecuteReader()
drData.Read()
objConnection.Close()

Return drData

End Function

 

[Marina Levit [MVP]]

You can't close your connection - the data reader requires an open and
available connection.
You have 3 options:

1. Declare the connection as a class level variable, and have Page_Load
close it when it's done with the reader
2. Pass in CommandBehavior.CloseConnection to ExecuteReader, and when
Page_Load closes the reader, this will close the connection as well. Of
course you have to make sure you close the reader
3. Forget using datareaders and use datatables instead.

I recommend #3 for you, because it seems you don't quite have the handle of
ADO.NET and connection management. Opening and closing a connection all in
one method will ensure that you avoid connection leaks.

Make sure your code has the proper exception handling to close connections
if an unforseen error occurrs.

 

[Karl Seguin [MVP]]

you can't close ur connection/datareader until AFTER you've finished with
it. That's because datareaders are CONNECTED to the database. Of course,
it's a pain to open a connection/datareader in one function and then have to
remember to close it in another ...it's hard to maintain and likely to cause
bugs.

One solution is to use a datatable which is disconnected.

Another solution is to map your datareader into objects, and return that
instead.


so you might create something like (total pseudocode)

public class DaysList
field int daysLeft
field date closedDate

property DaysLeft
property ClosedDate
end class

....

objConnection.open()
dr.Read()
DaysList dl = new DaysList(dr("DaysLeft"), dr("CloseDate");
dr.close()
connection.close()
return dl



hopefully that gives you some ideas..

Karl

 

[D. Shane Fowlkes]

Thanks. I normally catch all my errors but stripped out a lot lines to post
here. I didn't even think about the connection being closed issue. I'll
try a datatable.

Thanks again.

 

[sloan]

This is the VB.net version of what you're looking for.

this code assumes you have already made an Emp object, with EmpID, LastName,
and FirstName properties.

and you created your own collection base ... EmployeeCollection .. which
implements CollectionBase


This is a good design, because the code below...you can use any one of many
datareaders to get the collection.
What I mean is that....
your idr can have 1 employee, all employees , some employees in it...because
of different sql .....

the code below doesn't care how you created the datareader....it only care
that you have one.. and that it has 3 columns of data
EmpId, FirstName and LastName

You'll also notice I use the IDataReader interface.....
You can pass in a SqlDataReader or any kind of DataReader that implements
IDataReader..
This keeps your code not tied to any single database type.

This code belongs in your BusinessLayer object.



Private Function SerializeEmployees(ByVal idr As IDataReader) As
EmployeeCollection


Dim coll As New EmployeeCollection


dim empIDOrdinal as int32 = 0
dim empFirstNameOrdinal as int32 = 1
dim empLastNameOrdinal as int32 = 2


Try

While idr.Read()

If Not idr.IsDBNull(empIDOrdinal ) Then



Dim item As New Emp(idr.GetInt32(empIDOrdinal ))
''assumes you have a constructor for the Emp object..which takes the EmpID


If Not idr.IsDBNull(empFirstNameOrdinal ) Then
item.FirstName =
idr.GetString(empFirstNameOrdinal )
End If

If Not idr.IsDBNull(empLastNameOrdinal ) Then
item.LastNameName =
idr.GetString(empLastNameOrdinal )
End If




coll.Add(item)
End If
End While
Return coll

Finally

If Not (idr Is Nothing) Then
Try
idr.Close() ''--Ding Ding Ding....because you have
put all your data into the EmployeeCollection, you can get rid of the
datareader now
Catch
End Try
End If
End Try

End Function