 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / ADO.NET / January 2006Closing connections
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 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. >I have been misunderstanding the closing of the connection. > [quoted text clipped - 28 lines] > > Tom 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  Signature____________________________________ 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. __________________________________ >I have been misunderstanding the closing of the connection. > [quoted text clipped - 28 lines] > > Tom > 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: > hth > [quoted text clipped - 30 lines] > > > > Tom > > Complex bound controls (those that deal with the entire rowset) > > automatically close the DataSource when they complete population. This [quoted text clipped - 3 lines] > on > > the ExecuteReader. 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 > > hth > > [quoted text clipped - 35 lines] > > > > > > Tom 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 Signature____________________________________ 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. __________________________________ >> > Complex bound controls (those that deal with the entire rowset) >> > automatically close the DataSource when they complete population. This [quoted text clipped - 82 lines] >> > > >> > > Tom > The responsibility of any complex bound control (those that display an > entire rowset) is to close the DataReader post population. [quoted text clipped - 3 lines] > 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 > hth > [quoted text clipped - 84 lines] > >> > > > >> > > Tom Any control that fills itself with more than one row closes the DataReader. Signature____________________________________ 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. __________________________________ >> The responsibility of any complex bound control (those that display an >> entire rowset) is to close the DataReader post population. [quoted text clipped - 151 lines] >> >> > > >> >> > > Tom Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.