N-Tier, Data Access and returning a DataReader - A little help?

[RobS23]

I'm trying to avoid pasting all of my code in here - because I'm hoping a lot of people in attempting this would have come across the same problem.

Data Access biz objects Class:
-Stored Procedures and creation of Parameters etc

Generic Data Access Component:
- ExecuteNonQuery, Return DataSet or DataReader

Ok all familiar with this?

Ok. 1)My form calls the Stored Procedure from my Biz object.
2)My Biz objects sends a stored Proc name, a parameters collection and a (byref) Datareader to my Data Access component which ->
3) builds a connection string, opens it, builds a command and executes it.
4)This all trundles back to my form or whatever and I read and close my DataReader.

That's the outline. Now what I can't work out is how do I get the SQL connection to close? I can't close it before the Datareader is closed and I can't close it from the outside of the Data Access class? All this is sweet enough with a Dataset it closes on the 'Fill' but I don't want to limit myself.

Please. I'm going great with .NET but these hiccups are driving me mad. I need some advice!

ps.pooling just keeps opening new connections too)

 

[stnkyminky]

sqldatareader = sqlcommand.executeReader(CommandBehavior.ConnectionClose)

when sqldatareader.close happens...the connnection is closed too.

Scott
Programmer Analyst
<{{><

 

[SqueakinSweep]

Make the SQL Connection as a property of your DataReader. This way when the Datareader is closed the SQL connection class is also Disposed of too.

I have a connection object named clsSQLDataConnection

' A New Instance of our Connection Object for each New Reader
    Private Property oSQLDataConnection() As clsSQLDataConnection
        Get
            Return varSQLDataConnection
        End Get
        Set(ByVal Value As clsSQLDataConnection)
            varSQLDataConnection = Value
        End Set
    End Property : Private varSQLDataConnection As New clsSQLDataConnection

Sweep
...if it works dont mess with it

 

[RobS23]

Commandbehaviour isn't working for me (sorry stnky) I'll have a play with the Property.

Thanks for the 10 minute reply fellas!

 

[RobS23]

By the way. Is that all the code?