Closing connection and datasets

[robdotnet00]

Would there be any reason why you would still need to close a connectection when creating a dataset. For some reasons my connections arent closing on the database. Though i was under the impression that you didnt need to close them.

[axion_sa]

Which client are you using & are you opening the connection yourself or are you leaving that to the adapter?

[Cander]

Though i was under the impression that you didnt need to close them.

And why exactly would you think that?

[Edneeis]

Post your code if you want and we'll help you get it straight, but if you opened it then you need to close it.

*Kinda sounds like something a mother would say.

[robdotnet00]

Here is the method that i created im using the Provider OraOLEDB.Oracle.1; Though i plan to go to the oracle client eventually. I though you didnt have to close the connection because it open and closed automatically with the fill method is this not the case i added the close and the dispose code just now



Public Function MakeDs(ByVal strSql As String, ByVal strTable As String) As DataSet

Dim strConn As String = "DbConnection"
Dim ds As New DataSet()
Dim Conn As New OleDb.OleDbConnection(strConn)
Dim adptDs As New OleDb.OleDbDataAdapter(strSql, Conn)
Try

adptDs.Fill(ds, strTable)

Catch err As Exception
Trace.Write(err.Message)
End Try
adptDs.Dispose()
Conn.Close()
Return ds

End Function

[Cander]

.Fill doesnt disconnect the database. I think you mistook the fact that it fills a dataset which is disconnected.

[robdotnet00]

Wow that is a big mess up on my part. I think in a lot of code samples you dont see the close method on the connection being called.


i grabed this from ms web site

The Fill method retrieves the data from the data source using a SELECT statement. The IDbConnection object associated with the select command must be valid, but it does not need to be open. If the IDbConnection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondbdataadapterclassfilltopic4.asp

am i misunderstanding this it seems to say to me the connection if not opened opens and then closes after filling the dataset yes/no

[robdotnet00]

So is my code the problem or is this my misunderstanding the dataadapter? Or could it be some otehr reason why users sessions arent being closed on teh database.

[DevGrp]

Ok, if you are using a DataAdapter, you dont need to explicitly call the open method of the connection object. Once you call the Fill method of the DataAdapter, the DataAdapter will open the connection, fill the DataSet then close the connection.

Now what you got from MS is basically saying that if you call

conn.Open() then call da.Fill(ds, "mytable") the DataAdapter wont close the connection, you have to explicitly close it by calling conn.Close().

Hope that helps.

[robdotnet00]

That is what I had believed however the connections are still staying open on the oracle server. And defintly not a good thing. Hopefully closing the connection again and disposing of the object does something Im thinking does this sound potentially like a soulution. Im also think that it might be a prob with mdac however v 2.7 is installed.

[robdotnet00]

So what is the proper way to handle a dataadpter that makes a dataset do we make sure to close the connection again. Dispose of the dataadapter and set the connection to null and the dataadpter to null as well. And if we do this why do we do this?

[robdotnet00]

I think potentially that the connection wasnt the problem with how the fill method works i think possible I wasnt calling the dispose method for the datadapter and this was occuring


A Worst-Case Scenario
Imagine a server component that uses a database connection and does not have a Dispose method. On a server with a large amount of memory, you might create and release many instances of the component without having much impact on free memory. In this case, garbage collection might not destroy the components for some time after the references to them are released.

Eventually, all of the available database connections could be tied up by components that had been released but not destroyed. Even though the server had no shortage of memory, it might be unable to respond to user requests.

ms-help://MS.VSCC/MS.MSDNQTR.2003JAN.1033/vbcon/html/vbconInitializationTerminationOfComponents.htm