Cleaning up after calls to SMO Server and Database

Question

How do you make SMO release it's connections?

I have this code:

public static class SqlServerConnectionFactory
{
    public static Server GetSmoServer()
    {
        using (var c = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
        {
            var s = new ServerConnection(c);
            c.Close();
            return new Server(s);
        }
    }

    public static Database GetSmoDatabase(Server server)
    {
        var db = server.Databases[ConfigurationManager.AppSettings["Database"]];
        db.AutoClose = true;
        return db;
    }
}

Called like this from an ASP.Net MVC app run in IIS...:

public ActionResult Index()
    {
        server = SqlServerConnectionFactory.GetSmoServer();
        database = SqlServerConnectionFactory.GetSmoDatabase(server);
        var vm = new SettingsIndexViewmodel(database);
        return View(vm);
    }

For every call I make to this index method a connection is spun up - and is not released again.

So after 20 calls to the page, I have 20 of the connections awaiting command. This eventually ends up with an exception, when I cannot make new connections, because the connection pool is full.

What do I need to avoid this happening? I cannot seem to find a method on the SMO Server object like Dispose, close or similar.

Crichton

Answer 1

The MSDN article Disconnecting from an Instance of SQL Server might offer some help. It states that:

When the Connect method is called, the connection is not automatically released. The Disconnect method must be called explicitly to release the connection to the connection pool. Also, you can request a non-pooled connection. You do this by setting the NonPooledConnection property of the ConnectionContext property that references the ServerConnection object

Mahratta

This worked. I just called this after I was done with the server object: server.ConnectionContext.Disconnect(); – Crichton

Answer 2

After reading everything i managed to properly disconnect by setting

server.ConnectionContext.NonPooledConnection = true;

and then after i get my database i can call

server.ConnectionContext.Disconnect();

Conchiferous

I've been pulling my hair out for hours. This saved my life! – Subchaser