commandbehavior.closeconnection with applicationblocks

[dave]

I am calling a shared fucntion within a class from codebehind with asp.net. The class returns a sqldatareader to be bound to a datalist. I am also utilizing ms applicationblocks. How can I return the datareader from the shared function so that the datareader is closed properly after it has been bound

th
dave

[[MSFT]]

Hi Dave,

Normally we can call the CLose method of the SQLDataReader, for example:

Dim myreader As SQLDataReader

Dim o As New MyDataClass

myreader = o.GetReader

DataList1.DataSource = myreader

DataList1.DataMember = "Name"

DataList1.DataBind()

myreader.Close()

By the way, we don't need to close the connection since it will be managed
by connection pool.

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[[MSFT]]

Hi Dave,

Does my suggestion answer your question? Or you have other concern?

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[dave]

Your post did not answer my question

1.) I am using MS Data Application blocks to return the datareader through dr = sqlhelper.getdatreader(...)
The problem occurs because i am running this within a function and returning to the calling program the datareader. There is a property commandbehavior.closeconnection that will close the data reader after all elements have been read. How can i use commandbehavior.closeconnection if I use MS Data Application blocks

2) Your example does not align with the description of the problem. Remember that the call to the datareader is within a function and the reader is returned from the function. If i used your example and returned the reader it would not work as the reader would have been closed
th
dave

[[MSFT]]

Hi Dave,

If My understanding is correct, is it "executeReader" in Data Application
blocks.

To make "ExecuteReader" work with commandbehavior.closeconnection, we need
to pass in a external Connection. Following are source code of it:

Private Overloads Shared Function ExecuteReader(ByVal connection As
SqlConnection, _
ByVal transaction
As SqlTransaction, _
ByVal commandType
As CommandType, _
ByVal commandText
As String, _
ByVal
commandParameters() As SqlParameter, _
ByVal
connectionOwnership As SqlConnectionOwnership) As SqlDataReader
'create a command and prepare it for execution
Dim cmd As New SqlCommand()
'create a reader
Dim dr As SqlDataReader

PrepareCommand(cmd, connection, transaction, commandType,
commandText, commandParameters)

' call ExecuteReader with the appropriate CommandBehavior
If connectionOwnership = SqlConnectionOwnership.External Then
dr = cmd.ExecuteReader()
Else
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
End If

'detach the SqlParameters from the command object, so they can
be used again
cmd.Parameters.Clear()

Return dr
End Function 'ExecuteReader

As you see when last parameter is SqlConnectionOwnership.External,
CommandBehavior.CloseConnection will be used. Is this helpful in your
project?

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[[MSFT]]

Hi Dave,

How about my suggestion? Is it helpful or you have other concerns?

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)