Have function return SqlDatReader and then read results. How?

Discussion in 'ASP .Net' started by D. Shane Fowlkes, Mar 24, 2006.

  1. 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
     
    D. Shane Fowlkes, Mar 24, 2006
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:%...
    > 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], Mar 24, 2006
    #2
    1. Advertising

  3. 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" <> wrote in message
    news:%...
    > 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
    >
    >
    >
    >
     
    Karl Seguin [MVP], Mar 24, 2006
    #3
  4. 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]" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:%...
    >> 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
    >>
    >>
    >>
    >>

    >
    >
     
    D. Shane Fowlkes, Mar 24, 2006
    #4
  5. D. Shane Fowlkes

    sloan Guest

    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" <> wrote in message
    news:%...
    > 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
    >
    >
    >
    >
     
    sloan, Mar 24, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mufasa
    Replies:
    7
    Views:
    422
    Alexey Smirnov
    Sep 4, 2007
  2. Replies:
    0
    Views:
    287
  3. Casey Hawthorne
    Replies:
    3
    Views:
    1,103
    Flash Gordon
    Nov 1, 2009
  4. Replies:
    0
    Views:
    303
  5. Replies:
    4
    Views:
    546
    cwdjrxyz
    Jan 17, 2006
Loading...

Share This Page