Connection not getting closed when SqlDataAdapter is used

Question

After executing the Command using SqlDataAdapter, connection with the db is not getting closed. Let me know what needs to be done. Here is the code snippet

 DataSet dsResult = new DataSet("Result");
            SqlCommand selectCommand = new SqlCommand();
            if (_datasource.DataType == DataType.SqlText)
            {
                selectCommand = GenerateCommand(_datasource.DataType,_sqlquery);
            }
            else
            {
                selectCommand = GenerateCommand(_datasource.DataType, _datasource.DataObjectName, _fieldNames, _filters);
            }

            SqlDataAdapter da = new SqlDataAdapter(selectCommand.CommandText, _datasource.ConnectionString);
            da.Fill(dsResult);



            dataset = dsResult;

Tried explicity closing the connection like da.SelectCommand.Connection.Close(). But issue did not get fixed. Also tried the following still issue not fixed

using(SqlDataAdapter da = new SqlDataAdapter(selectCommand.CommandText, _datasource.ConnectionString))
{
                da.Fill(dsResult);
}

Let me know what can be done to release the session.

What leads to your conclusion that the connection is not closed? — Tim Schmelter, Jul 27 '12 at 09:48
@Tim: AFter going through Activity montoing in SSMS, Connection is still accessing db. — sameer, Jul 27 '12 at 09:48
Edited my answer. The physical connection keeps open, it will not be closed by `Connection.Close`. That's just a hint for the pool that it can be used again. That's the reason why you should always _close_ the connection immediately. — Tim Schmelter, Jul 27 '12 at 10:11

score 2 · Accepted Answer · edited May 23 '17 at 10:34

2

The Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property. The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection 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/en-us/library/377a8x4t.aspx

I highlighted you case. You haven't opened the connection, so the DataAdapter will open it automatically for you and close it when it's finished.

Edit: If you want to manage the connection yourself, you should always close it immediately you're finished with it.

Therefor you could use the using-statement which disposes/closes it (even in case of an exception).

using(var con = new SqlConnection(_datasource.ConnectionString))
{
    using(var da = new SqlDataAdapter(selectCommand.CommandText, con))
    {
        con.Open(); // not needed but ...
        da.Fill(dsResult); // will not close the conection now
    }
} // will close the connection

Edit2: Closing a conection does not mean that it is closed physically. It is just a hint for the Connection-Pool that it can be used again.

ExecuteReader requires an open and available Connection. The connection's current state is Connecting

edited May 23 '17 at 10:34

Community

1
1

answered Jul 27 '12 at 09:38

Tim Schmelter

450,073
74
686
939

Thanks Tim, But problem is as db is in used by active connection in Connection pool as you said. We are not able to perform the operation like renaming the db, We basically don't want this connection to be pooled. We check the Database Health of different server's from .net app. remotely post which database should be released and should not affect the production during any operation. This is the problem. – sameer Jul 27 '12 at 10:12
@sameer: Well, why do you use pooling at all then? You can disable it via ConnectionString: `;pooling=false;` – Tim Schmelter Jul 27 '12 at 10:18
Thanks sure will do that ,But need to know how does this work while accessing the Remote database and where will be the ConnectionPool located. – sameer Jul 27 '12 at 10:23
Maybe these links are helpful: http://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.100%29.aspx and http://aspalliance.com/1099_Understanding_Connection_Pooling_in_NET.2 In short: the pool (or multiple) exist in the application domain (f.e. a winform app or an ASP.NET app). – Tim Schmelter Jul 27 '12 at 10:29

Connection not getting closed when SqlDataAdapter is used

1 Answers1