SQLDataAdapter Connection Question
In VB.NET when using a SQLDataAdapter when is the connection to the database closed (if ever)? If it's never closed how do I close it?
I'm writing a program that uses SQLDataAdapters extensively and the last thing I want after it's deployed is to find out that the connections aren't being closed properly (never a good thing!).
The help file isn't exactly clear on this point. Here is the code from the help file example. Maybe I'm thinking about this the wrong way but it appears the SQLDataAdapter is closing the connection automatically since it's never explicitly closed. Is this the case?
Public Function SelectSqlSrvRows(dataSet As DataSet, connection As String, query As String) As DataSet
Dim conn As New SqlConnection(connection)
Dim adapter As New SqlDataAdapter()
adapter.SelectCommand = new SqlCommand(query, conn)
adapter.Fill(dataset)
Return dataset
End Function
[983 byte] By [
howiegoo] at [2007-11-11 7:20:55]
# 1 Re: SQLDataAdapter Connection Question
Well u can close the connection explicitly by closing the connection object...
in your case it is conn if iam not wrong.........
# 2 Re: SQLDataAdapter Connection Question
ya use conn.close in a finally block it is useful
or use the
using(SqlConnection conn = new SqlCOnnection(stringconn))
{
and do your code here.. this will automatically dispose the conn object once closed
}
# 3 Re: SQLDataAdapter Connection Question
How about in this situation?
Dim strConn as String = "user id=user;password=password;server=server;database=database"
Dim strCommand As String = "SELECT * FROM TABLE1 WHERE DATA1 = '" & Whatever & "'"
Dim DA As New SqlDataAdapter(strCommand, strConn)
DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim DS As New DataSet
DA.Fill(DS)
# 4 Re: SQLDataAdapter Connection Question
In this situation you are giving the sql adapter to manage the sqlconnection for you and you are only specifying a connection string so i guess you dont have dispose, the adapter works disconnected and you dont have to manage the connection object for it
In the first case you created a connection object explcitly and hence it was required to dispose
