CODEWITHSUNDEEP
c#sql-server
Marc Castejón
Marc Castejón

Reputation: 5

C# - SQL Connection won't close

I'm making a C#program that interacts with some SQL Server databases. The problem is if I connect to a database (dbA) and then close this connection and open another one to another database (dbB) and then do a Restore of dbA, the SqlException triggers saying that database (dbA) is in use. However if I execute the program and connect only to dbB, I can restore the other databases without problem, is like the first connection is kept stored. Anyway here is the code where the connection should open and close:

private bool CheckConnection()
    {
        bool res = false;
        string conString = string.Empty;
        if (!String.IsNullOrEmpty(serverBox.Text) && !String.IsNullOrEmpty(dbBox.Text))
        {
            conString = ConcatConString(dbBox.Text);
            using (SqlConnection conn = new SqlConnection(conString))
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    res = true;
                }
            }
        }        
        return res;
    }

Upvotes: 0

Views: 1806

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131774

ADO.NET uses connection pooling to reuse expensive connection objects. When you close a connection, any existing transactions are rolled back, its server-side state is reset and it's placed in the connection pool awating for the next Open command.

To the server though, that still counts as a server connection when you try to take such drastic actions as shutting down the server, restoring the database etc. That means that you have to take explicit action and tell the server that it's OK to proceed.

In this case, you need to set the database to SINGLE USER mode, perform the restore operation then bring it back to MULTI USER mode, eg:

ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [MyDB] ...
ALTER DATABASE [MyDB] SET MULTI_USER

That's what SSMS does by the way, when you check the "Close Existing Connections to Database" option when restoring

You can also use WITH ROLLBACK AFTER xx SECONDS if you want give some time to existing connections to finish. In this case though, you are going to overwrite the database.

Upvotes: 3

Crowcoder
Crowcoder

Reputation: 11514

Closing a connection does not close it at the database, it just returns the connection to the pool maintained by ADO.Net. Normally I would never suggest this but it seems you may have a legitimate case for disabling connection pooling.

In the connection string set the Pooling attribute to no or false and the connection should actually close on the server.

You can observe how it works by running sp_who2 in SSMS with connection pooling enabled or disabled in the following code:

class Program
{
    static void Main(string[] args)
    {
        SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder();
        bldr.IntegratedSecurity = true;
        bldr.InitialCatalog = "YourDB";
        bldr.DataSource = "(localdb)\\YourServer";
        bldr.Pooling = false;  //Comment and uncomment this and run sp_who2

        using (SqlConnection con = new SqlConnection(bldr.ConnectionString))
        {
            con.Open();
        }
    }
}

Update

Don't do this unless your program is the only thing connecting. I got the impression that the program was specifically for restoring databases. If you have other clients on the databases then this will tank performance.

Upvotes: 0

Related Questions