Disconnecting users from sql server database via c#

I’ve been setting up an automated build for our project. Our project has a very large SQL server database that we write “upgrade” scripts for – basically anything that modifies the schema – like adding new columns, modifying data etc as part of the release process.

We’ve found that over the past few releases that these upgrade scripts didn’t often work. They were buggy and not tested properly – primarily because testing them was a pain. You’d need to grab the latest backup of the production database, restore it, run the upgrade scripts against it, run the stored procs into it and then test your apps.

So instead I’ve automated it. Now it copies the latest production backup, restores it, runs the upgrade scripts, runs in the stored procs, compiles the code and then runs the unit tests. It is very cool, and has already saved us days of work.

Part of the fun is restoring the latest database backup. It’s all been working fine for a while, but sometimes we run into a problem – when restoring the latest backup we find that someone is already connected to the database. Normally because they wanted to check their upgrade scripts.

So I need to automatically disconnect anyone from the upgraded database. And yes, everyone is warned that this DB is blown away every night, and not to do anything in it! Here’s how I did it. The only thing that I’m not crazy about it using sp_who2 – it would be nice to interrogate the DB directly.. but hey, it works.

string conn_str1 = "Server=" + serverName + ";Trusted_Connection=True;Database=master";

using (SqlConnection conn = new SqlConnection(conn_str1))
{
    conn.Open();
    SqlCommand cmd = new SqlCommand();

    // give it 5 minutes:
    cmd.CommandTimeout = 5 * 60;
    cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "sp_who2";

    using (SqlDataReader sqr = cmd.ExecuteReader())
    {
        while (sqr.Read())
        {
            int dbNameColumnId = sqr.GetOrdinal("DBName");
            int connectionIdColumnId = sqr.GetOrdinal("SPID");
            int loginColumnId = sqr.GetOrdinal("Login");

            if (sqr.GetValue(dbNameColumnId) != DBNull.Value &&
                sqr.GetValue(connectionIdColumnId) != DBNull.Value &&
                sqr.GetValue(loginColumnId) != DBNull.Value)
            {
                string dbName = sqr.GetString(dbNameColumnId);
                int connectionId = Int32.Parse(sqr.GetString(connectionIdColumnId));
                string username = sqr.GetString(loginColumnId);

                if (dbName.ToUpper() == newDatabaseName.ToUpper())
                {
                    Console.WriteLine("Disconnecting user: " + username);
                    DisconnectUser(connectionId);
                }
            }
        }
    }
}

and then the vital function:

private void DisconnectUser(int ConnectionId)
{
    string sqlKillConnection = "kill " + ConnectionId.ToString();
    string masterConnection = "Server=" + serverName + ";Trusted_Connection=True;Database=master";

    using (SqlConnection conn = new SqlConnection(masterConnection))
    {
        conn.Open();
        SqlCommand cmdKill = new SqlCommand();
        cmdKill.Connection = conn;
        cmdKill.CommandType = CommandType.Text;
        cmdKill.CommandText = sqlKillConnection;
        cmdKill.ExecuteNonQuery();
    }
    
}