public static void DropUserConnections(string serverName, string databaseName)
{
var connectionInfo = new DbConnectionInfo {
DatabaseName = "master", ServerName = serverName
};
string sql = String.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", databaseName);
SimpleDataAccess.ExecuteSql(sql, DbConnectionInfo.GetMsSqlServerConnectionString(connectionInfo));
}
public static void DropDatabaseIfExists(string serverName, string databaseName)
{
string connectionString = DbConnectionInfo.GetMsSqlServerConnectionString(serverName, "master");
if (!DatabaseExists(connectionString, databaseName))
{
return;
}
DropDatabase(serverName, databaseName);
}
public static void CreateDatabase(string serverName, string databaseName, bool drop)
{
if (drop)
{
DropDatabaseIfExists(serverName, databaseName);
}
string createDatabaseScript = "IF (SELECT DB_ID('" + databaseName + "')) IS NULL "
+ " CREATE DATABASE " + databaseName;
string connectionString = DbConnectionInfo.GetMsSqlServerConnectionString(serverName, "master");
SimpleDataAccess.ExecuteSql(createDatabaseScript, connectionString);
}
public static void DropDatabase(string serverName, string databaseName)
{
DropUserConnections(serverName, databaseName);
var connectionInfo = new DbConnectionInfo {
DatabaseName = "master", ServerName = serverName
};
string sql = String.Format("DROP DATABASE {0}", databaseName);
SimpleDataAccess.ExecuteSql(sql, DbConnectionInfo.GetMsSqlServerConnectionString(connectionInfo));
//Necessary so as to avoid a connection to the database that just been dropped being being reused from the pool
//Not clearing the pool, leads to your app throwing an exception when it tried to access the dropped database
//from a pooled connection that was no longer open
SqlConnection.ClearAllPools();
}