Business layer passing results from a SQLDatareader

Hi,

I'm creating a business layer to access an SQL database cycle through the data for validation and pass the clean data onto an ASP form. I am currently retrieving the results into a SQLDatareader into my class file. I  loop through the reader and validate the data. Should I place the results into an array   Is that the best way to pass that data via code to my VB code for the ASP form. Can I transfer that into a datatable or something similar. I don't mind cycling through the records in the VB part of the ASP form but I don't want to persist any SQL connections. 

Thank you for your help

 Vear

Hello Vear

In your code where you call the ExecuteReader of your sql or oledb command pass the the following commandBehaviour value which is CloseConnection.

in this case you can pass your sql / oledb Datareader to your asp page then when u finish looping through your datareader call the Close() method of the datareader

Hope this is helpful for you

Hi,

Thanks for your response. I'm looking to close the SQL connection prior to passing the data back to the ASP page. I can use an array but this could become quite a large array.

Vear

Hi,

I'm not sure what I am doing wrong. I can't seem to pass the sqldatareader back to the form. I'm coding in VB and have a class and form with the following.

{CLASS}

Public Class Booking

Public Shared Function Load_booking_campgrounds() As SqlDataReader

Dim iBCsettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("iBCctnstr")

Dim iBCconnectionString As String = iBCsettings.ConnectionString

Using iBCConnection As New Data.SqlClient.SqlConnection(iBCconnectionString)

Dim iBCCommand As SqlCommand = New SqlCommand("Load_bookings", iBCConnection)

Try

'Return iBCCommand.ExecuteReader(CommandBehavior.CloseConnection)

end try

end function

End Class

{END CLASS}

{ASP FORM}

Using iReader As SqlDataReader = Booking.Load_bookings

Try

Do While iReader.Read()  ---- this is where I get an error "Invalid attempt to read"