Database operations using c# – Dropping a database

In the first post we have created a DB using c#. Now this time we will drop a DB I have divided the code in to two classes so lets see them in detail.

The first class is the control class through which we will call the helpers. In the code below first we will try to connect to the master DB. In dropDatabse method we need to check if the DB exists or not and if it does then we will need to set the DB online. The reason to set the DB online is when you drop the DB then the file on the disk will also be deleted.

 

class Program
	{
		private static ExecuteSql _executeMaster;
 		private static string _connectionString =
			"Data Source=.;Integrated Security=SSPI;Initial Catalog=testme;Application Name=Manager.Test;Connection Timeout=300;
                         User ID=sa;Password=password;";
 		private static string _masterDatabaseName = "master";
		static void Main(string[] args)
		{
			_executeMaster = new ExecuteSql(openConnectionToMaster);
			dropDatabase("testme");
			Console.WriteLine("done");
			Console.ReadLine();
		}
 
		private static SqlConnection openConnectionToMaster()
		{
			var connectionStringBuilder = new SqlConnectionStringBuilder(_connectionString);
			connectionStringBuilder.InitialCatalog = _masterDatabaseName;
			var conn = new SqlConnection(connectionStringBuilder.ConnectionString);
			conn.Open();
			return conn;
		}
 
		private static void dropDatabase( string databaseName)
		{
			if (Exists(databaseName))
			{
				SetOnline(databaseName); // if dropping a database that is offline, the file on disk will remain!
				_executeMaster.ExecuteTransactionlessNonQuery(
					string.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;", databaseName));
				Drop(databaseName);
			}
 
		}
 
		public static bool Exists(string databaseName)
		{
			return
				Convert.ToBoolean(_executeMaster.ExecuteScalar("SELECT database_id FROM sys.databases WHERE Name = @databaseName",
															  parameters:
																  new Dictionary<string, object> { { "@databaseName", databaseName } }));
		}
 
		public static void Drop(string databaseName)
		{
			_executeMaster.ExecuteTransactionlessNonQuery(string.Format("DROP DATABASE [{0}]", databaseName), 120);
		}
 
		public static void SetOnline(string databaseName)
		{
			_executeMaster.ExecuteTransactionlessNonQuery(string.Format("ALTER DATABASE [{0}] SET ONLINE", databaseName));
		}
 
	}

The helper class ExecuteSql have all the helper functions. The class is described below.

public class ExecuteSql
	{
		private readonly Func<SqlConnection> _openConnection;
 		public ExecuteSql(Func<SqlConnection> openConnection)
		{
			_openConnection = openConnection;
		}
 		public void ExecuteCustom(Action<SqlConnection> action)
		{
			using (var connection = _openConnection())
			{
				action(connection);
			}
		}
 
		public int ExecuteScalar(string sql, int timeout = 30, IDictionary<string, object> parameters = null)
		{
			parameters = parameters ?? new Dictionary<string, object>();
 
			var result = 0;
 
			using (var connection = _openConnection())
			{
				using (var transaction = connection.BeginTransaction())
				{
					using (var command = connection.CreateCommand())
					{
						command.Transaction = transaction;
 
						foreach (var parameter in parameters)
						{
							command.Parameters.AddWithValue(parameter.Key, parameter.Value);
						}
						command.CommandTimeout = timeout;
 
						{
							command.CommandText = sql;
							command.CommandType = CommandType.Text;
							result = (int) (command.ExecuteScalar() ?? default(int));
						}
 
						transaction.Commit();
					}
				}
			}
 
			return result;
		}
 
		public void ExecuteTransactionlessNonQuery(string sql, int timeout = 30)
		{
			using (var connection = _openConnection())
			{
				using (var command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandTimeout = timeout;
					command.CommandText = sql;
					command.ExecuteNonQuery();
				}
			}
		}
 
		public void ExecuteNonQuery(string sql, int timeout = 30, IDictionary<string, object> parameters = null)
		{
			parameters = parameters ?? new Dictionary<string, object>();
 
			using (var connection = _openConnection())
			{
				using (var transaction = connection.BeginTransaction())
				{
					using (var command = connection.CreateCommand())
					{
						command.Transaction = transaction;
						command.CommandTimeout = timeout;
 
						{
							command.Parameters.Clear();
							foreach (var parameter in parameters)
							{
								if (sql.Contains(parameter.Key))
								{
									command.Parameters.AddWithValue(parameter.Key, parameter.Value);
								}
							}
							command.CommandText = sql;
							command.CommandType = CommandType.Text;
							command.ExecuteNonQuery();
						}
 
						transaction.Commit();
					}
				}
			}
		}
 
 
	}

All you need to do is change the name of the DB, user id and password.