Closing connections

[tshad]

I have been misunderstanding the closing of the connection.

If I have set CommandBehavior.CloseConnection, I thought that connection
would be closed when you had read through the dataread. As a matter of fact
I found that if I had it set, I couldn't seem to get to the next results, if
there were multiple results (NextResult() would give me an error).

Now I find that I need to close the Reader to close the connection.

This is a problem with my Database object. I have an object (RunProcedure)
that executes a stored procedure and then returns a DataReader. I have the
reader set to CommandBehavior.CloseConnection. This would be ok as I can
close the DataReader, which should close the connection if I do a:

dbReader = RunProcedure(...)
dbReader.Close()

But how do I deal with (or can I) the situation where I do a direct Databind
to Grid, listbox, etc?

DataGrid1.DataSource = RunProcedure()

I have no DataReader to close.

Thanks,

Tom

 

[Marina]

You have to assign the result of calling the function to a variable. Then
bind to that data reader variable. And then close the datareader through the
variable.

 

[William \(Bill\) Vaughn]

Complex bound controls (those that deal with the entire rowset)
automatically close the DataSource when they complete population. This means
you don't have to close a DataReader when binding it to a Grid or List.
However, this assumes that you've set the CommandBehavior.CloseConnection on
the ExecuteReader.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[tshad]

Complex bound controls (those that deal with the entire rowset)
automatically close the DataSource when they complete population. This means
you don't have to close a DataReader when binding it to a Grid or List.
However, this assumes that you've set the CommandBehavior.CloseConnection on
the ExecuteReader.

This includes datagrid, datalist, repeater, dropdownlist and listbox?

So if I do:

 

[tshad]

CommandBehavior.CloseConnection

This includes datagrid, datalist, repeater, dropdownlist and listbox?

So if I do (where RunProcedure returns a DataReader and sets
CommandBehavior.CloseConnection):

DataGrid1.DataSource = RunProcedure(...)
DataGrid1.DataBind()

or

theListBox.DataSource =
objCmd.ExecuteReader(CommandBehavior,CloseConnection)
theListBox.DataBind()

or

Dim dbReader as SqlDataReader
....
dbReader = objCmd.ExecuteReader(CommandBehavior,CloseConnection)
while dbReader.Read()
....
end while

I don't have to explicitly close either the Reader or the Connection?

Just want to make sure.

Also, does CommandBehavior,CloseConnection only apply to DataReaders and not
DataAdapters ?

Thanks,

Tom

 

[William \(Bill\) Vaughn]

The responsibility of any complex bound control (those that display an
entire rowset) is to close the DataReader post population.
The DataAdapter is not bindable. However, the DataTable within the generated
DataSet is. In this case the DataAdpater has already executed the Fill
method (which opens a DataReader) to populate the DataTable(s) and if (and
only if) the Connection was closed when the Fill is executed, the Connection
will be closed when rowset population is complete.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[tshad]

The responsibility of any complex bound control (those that display an
entire rowset) is to close the DataReader post population.
The DataAdapter is not bindable. However, the DataTable within the generated
DataSet is. In this case the DataAdpater has already executed the Fill
method (which opens a DataReader) to populate the DataTable(s) and if (and
only if) the Connection was closed when the Fill is executed, the Connection
will be closed when rowset population is complete.

What about the ListBox, which is also Bindable? Would I need to close that
connection or would the Listbox close it for me?

theListBox.DataSource =
objCmd.ExecuteReader(CommandBehavior,CloseConnection)
theListBox.DataBind()

Also, what about this case, (RunProcedure returns a DataReader that sets
CommandBehavior.CloseConnection):

Dim dbReaderAs SqlDataReader
for each oItem as DataGridItem in DataGrid1.items
dbReader = myDbObject.RunProcedure("DeleteEmailMessageSent",
parameters,rowsAffected)
Next

Would I need to put a close after each execution of RunProcedure ( I assume
this would be the case), or could I put it after the Next command (only
close it once). If this were to run 5 loops, would it use 5 different
connections (if so, I would assume that I would need to close each
connection (before the Next statement).

Also, if I were to do the following, (assuming RunProcedure passes back a
DataReader that has set CommandBehavior.CloseConnection)

Dim dbReader As SqlDataReader
....
dbReader = RunProcedure ()
DataGrid1.DataSource = dbReader
DataGrid1.DataBind()
dbReader.Close()

If the DataGrid1 would close the dbReader (and therefore the connection in
this case because of the CommandBehavior), would the dbReader.Close() cause
an error trying to close a connection that has already been closed or would
it know that it had been closed and just return with no error?

Thanks,

Tom

 

[William \(Bill\) Vaughn]

Any control that fills itself with more than one row closes the DataReader.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________