Wednesday 23 April 2008

Drop a SQL Server database from your .net code

Have you ever been in a situation where you need to provide uninstall funcitonality for database installation? Imagine you have just created your nice new database in Sql server and all the scripts have been created but at the last line something happens and you need to rollback. This also includes dropping the database and it isn't straight forward. Closing your connection(s) doesn't help as they don't close straight away. While looking for a solution I came accross one and decided to share it here.
This is the method I created:

private string GetKillProcessStatement(string database)
{
string result = string.Empty;
StringBuilder sql = new StringBuilder();

sql.AppendLine("DECLARE @SQL varchar(max)");
sql.AppendLine("SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'");
sql.AppendLine("FROM MASTER..SysProcesses");
sql.AppendLine("WHERE DBId = DB_ID('" + database + "') AND SPId <> @@SPId");
sql.AppendLine("SELECT @SQL");

SqlConnection dropDbSqlConn = new SqlConnection(_masterConnStr);
dropDbSqlConn.Open();

using (SqlCommand sqcomm = new SqlCommand())
{
sqcomm.Connection = dropDbSqlConn;

sqcomm.CommandType = CommandType.Text;
sqcomm.CommandText = sql.ToString();

sqcomm.Prepare();

object obj = sqcomm.ExecuteScalar();

if (obj!=null)
result = obj.ToString();
}
dropDbSqlConn.Close();
return result;
}
A few clarifications: _masterConnStr is the connection string to the master db on this server. If you need to create a db you will probably already have this one available. In this case it is a private member in the installer class. I explicitly need it to be inline sql as i don't want to install stored procs in other databases on the server. Database access rights to do that will be available because the user we require also needs to create dbs.
The inline sql when executed returns a list of kill statements like this one: 'Kill 11;Kill 23;Kill 45;' . The numbers will be the process ids in Sql Server. Once you get this you are just a simple ExecuteNonQuery of this string before you will be able to drop your database regardless of the number of connections on it. Ok I know there might be more arguments for connection initiated between your kill execute and your drop statement execute but there is cure for this as well. If this is a worry then execute this sql script before you issue the drop statement:
"ALTER DATABASE [" + database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
I wouldn't be bothered to do so as in my case this is a newly installed db and there is no chance someone will know about it.
Back to the point I believe this is a good solution to ensure that you can drop a sql server db and the statement won’t fail because of open connections.

No comments: