Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Have function return SqlDatReader and then read results. How?

Reply
Thread Tools

Have function return SqlDatReader and then read results. How?

 
 
D. Shane Fowlkes
Guest
Posts: n/a
 
      03-24-2006
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




 
Reply With Quote
 
 
 
 
Marina Levit [MVP]
Guest
Posts: n/a
 
      03-24-2006
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.

"D. Shane Fowlkes" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
>
>



 
Reply With Quote
 
 
 
 
Karl Seguin [MVP]
Guest
Posts: n/a
 
      03-24-2006
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


--
http://www.openmymind.net/
http://www.fuelindustries.com/


"D. Shane Fowlkes" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
>
>



 
Reply With Quote
 
D. Shane Fowlkes
Guest
Posts: n/a
 
      03-24-2006
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.


"Marina Levit [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> "D. Shane Fowlkes" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>>
>>

>
>



 
Reply With Quote
 
sloan
Guest
Posts: n/a
 
      03-24-2006
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












"D. Shane Fowlkes" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
C/C++ compilers have one stack for local variables and return addresses and then another stack for array allocations on the stack. Casey Hawthorne C Programming 3 11-01-2009 08:23 PM
Required user to read - then click a checkbox and then a button becomes enabled. Mufasa ASP .Net 7 09-04-2007 06:04 PM
When I open laptop lid i have display then it goes away then comes back Ed Computer Support 1 03-02-2006 07:26 PM
Help. SessionID is x then y then x then y BodiKlamph@gmail.com ASP General 0 09-03-2005 03:02 PM
Read all of this to understand how it works. then check around on otherRead all of this to understand how it works. then check around on other thelisa martin Computer Support 2 08-18-2005 06:40 AM



Advertisments