SqlConnection, SqlCommand -- using them efficiently

SqlConnection and SqlCommand implement IDisposable, which means they could have unmanaged resources to cleanup and it is our job, the developers, to make sure Dispose() gets called on these classes after we are finished with them.

To avoid all mess, use following C# syntax :

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
using (SqlCommand sqlCommand = new SqlCommand(commandString, sqlConnection))
{
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}

This is essentially equivalent to :

SqlConnection sqlConnection = null;
SqlCommand sqlCommand = null;
try
{
sqlConnection = new SqlConnection(connectionString);
sqlCommand = new SqlCommand(commandString, sqlConnection);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
finally
{
if (null != sqlCommand);
sqlCommand.Dispose();
if (null != sqlConnection)
sqlConnection.Dispose();
}

If you don't use above code and if you forget to close connection, you cannot drop a database. It will throw an error saying that database is still in use.

However, there is a way out (though not advisable) for dropping the database:

USE MASTER
GO
ALTER DATABASE DBNAME
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE DBNAME

Note: above code is used to force database to delete all connection (which might cause loss of data in multi-user environment).