Dropping a database

It seems that even though you close the SqlConnection from C# or try diffrent things such as calling Dispose(), GC.Collect() or setting the connection to null , the database is still "in use". To avoid creating a new connection object, SQL caches the connection in its connection pool for further use. This results in improved speed the next time you try to use it.
To see if there are any "sleeping" connections, use this code:

SELECT spid, uid=rtrim(loginame), Program_name=rtrim(Program_name),
dbname=db_name(dbid), status=rtrim(status) FROM master.dbo.sysprocesses;

To kill one of them run in SQL:

kill spid

You must kill any sleeping connections before trying to drop a database.
However, if you try to use this connection from C# after you have killed it, you'll get the following SqlException:
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
You have to call SqlConnection.ClosePool() or SqlConnection.CloseAllPools() to avoid it.

Edit: it seems that SqlConnection.CloseAllPools() works by itself, so this is the only line of code needed (in C#) to solve the "database in use" problem.

Checking if different database objects exist

To check if a database exists:

if db_id('MyDatabase') is null
select 'false'
else
select 'true'


To check if a temporary table exist, use the following code. You need to search for them in tempdb because that's where all temporary tables are stored.

if object_id('tempdb..#temp', 'U') is not null
drop table #temp

GO