Post
Ask Question

How To Create And Drop A Database Using C#

In this article, I am going to explain how you can create and drop a database, using C#. 

Creating database

This class is the control class, through which we are going to call the helper methods.

  1. class Program  
  2. {  
  3.     private static ExecuteSql _executeMaster;  
  4.     private static string _connectionString = "Data Source=.;Integrated Security=SSPI;Initial Catalog=testme;Application Name=Manager.Test;Connection Timeout=300;  
  5.     User ID = sa;  
  6.     Password = password;  
  7.     ";  
  8.     private static string _masterDatabaseName = "master";  
  9.     static void Main(string[] args)  
  10.     {  
  11.         _executeMaster = new ExecuteSql(openConnectionToMaster);  
  12.         createDatabase("testme");  
  13.         Console.WriteLine("done");  
  14.         Console.ReadLine();  
  15.     }  
  16.     private static SqlConnection openConnectionToMaster()  
  17.     {  
  18.         var connectionStringBuilder = new SqlConnectionStringBuilder(_connectionString);  
  19.         connectionStringBuilder.InitialCatalog = _masterDatabaseName;  
  20.         var conn = new SqlConnection(connectionStringBuilder.ConnectionString);  
  21.         conn.Open();  
  22.         return conn;  
  23.     }  
  24.     private static void createDatabase(string databaseName)  
  25.     {  
  26.         //check if the DB exists or not  
  27.         if (!Exists(databaseName))  
  28.         {  
  29.             try  
  30.             {  
  31.                 var script = File.ReadAllText("CreateDB.sql");  
  32.                 script = ReplaceScriptTags(script, databaseName);  
  33.                 _executeMaster.ExecuteTransactionlessNonQuery(script, 10800);  
  34.             } catch (Exception exception)  
  35.             {  
  36.                 string msg = exception.StackTrace;  
  37.             }  
  38.         }  
  39.     }  
  40.     private static string ReplaceScriptTags(string script, string name)  
  41.     {  
  42.         script = script.Replace("$(DBNAME)", name);  
  43.         return script;  
  44.     }  
  45.     public static bool Exists(string databaseName)  
  46.     {  
  47.         return  
  48.         Convert.ToBoolean(_executeMaster.ExecuteScalar("SELECT database_id FROM sys.databases WHERE Name = @databaseName", parameters: new Dictionary & lt; string, object & gt; {  
  49.             {  
  50.                 "@databaseName",  
  51.                 databaseName  
  52.             }  
  53.         }));  
  54.     }  
  55.     public static void Drop(string databaseName)  
  56.     {  
  57.         _executeMaster.ExecuteTransactionlessNonQuery(string.Format("DROP DATABASE [{0}]", databaseName), 120);  
  58.     }  
  59.     public static void SetOnline(string databaseName)  
  60.     {  
  61.         _executeMaster.ExecuteTransactionlessNonQuery(string.Format("ALTER DATABASE [{0}] SET ONLINE", databaseName));  
  62.     }  
  63. }  
This class is the helper class being used. We will use this class to execute the script.
  1. public class ExecuteSql  
  2. {  
  3.     private readonly Func & lt;  
  4.     SqlConnection & gt;  
  5.     _openConnection;  
  6.     public ExecuteSql(Func & lt; SqlConnection & gt; openConnection)  
  7.     {  
  8.         _openConnection = openConnection;  
  9.     }  
  10.     public void ExecuteCustom(Action & lt; SqlConnection & gt; action)  
  11.     {  
  12.         using(var connection = _openConnection())   
  13.         {  
  14.             action(connection);  
  15.         }  
  16.     }  
  17.     public int ExecuteScalar(string sql, int timeout = 30, IDictionary & lt; string, object & gt; parameters = null)   
  18.     {  
  19.         parameters = parameters ? ? new Dictionary & lt;  
  20.         string, object & gt;  
  21.         ();  
  22.         var result = 0;  
  23.         using(var connection = _openConnection())  
  24.         {  
  25.             using(var transaction = connection.BeginTransaction())  
  26.              {  
  27.                 using(var command = connection.CreateCommand())  
  28.                     {  
  29.                     command.Transaction = transaction;  
  30.                     foreach(var parameter in parameters)  
  31.                       {  
  32.                         command.Parameters.AddWithValue(parameter.Key, parameter.Value);  
  33.                     }  
  34.                     command.CommandTimeout = timeout;  
  35.                     {  
  36.                         command.CommandText = sql;  
  37.                         command.CommandType = CommandType.Text;  
  38.                         result = (int)(command.ExecuteScalar() ? ?  
  39.                             default (int));  
  40.                     }  
  41.                     transaction.Commit();  
  42.                 }  
  43.             }  
  44.         }  
  45.         return result;  
  46.     }  
  47.     public void ExecuteTransactionlessNonQuery(string sql, int timeout = 30)  
  48.         {  
  49.         using(var connection = _openConnection())  
  50.         {  
  51.             using(var command = connection.CreateCommand())  
  52.             {  
  53.                 command.CommandType = CommandType.Text;  
  54.                 command.CommandTimeout = timeout;  
  55.                 command.CommandText = sql;  
  56.                 command.ExecuteNonQuery();  
  57.             }  
  58.         }  
  59.     }  
  60.     public void ExecuteNonQuery(string sql, int timeout = 30, IDictionary & lt; string, object & gt; parameters = null)   
  61.         {  
  62.         parameters = parameters ? ? new Dictionary & lt;  
  63.         string, object & gt;  
  64.         ();  
  65.         using(var connection = _openConnection())   
  66.         {  
  67.             using(var transaction = connection.BeginTransaction())  
  68.             {  
  69.                 using(var command = connection.CreateCommand())  
  70.                  {  
  71.                     command.Transaction = transaction;  
  72.                     command.CommandTimeout = timeout;   
  73.                     {  
  74.                         command.Parameters.Clear();  
  75.                         foreach(var parameter in parameters)   
  76.                       {  
  77.                             if (sql.Contains(parameter.Key))  
  78.                            {  
  79.                                 command.Parameters.AddWithValue(parameter.Key, parameter.Value);  
  80.                             }  
  81.                         }  
  82.                         command.CommandText = sql;  
  83.                         command.CommandType = CommandType.Text;  
  84.                         command.ExecuteNonQuery();  
  85.                     }  
  86.                     transaction.Commit();  
  87.                 }  
  88.             }  
  89.         }  
  90.     }  
  91. }  
This is the script file that is used to create the script. The script is well commented, so that you can see the details of the Application. You need to place this script in a file, called CreateDB.sql and save it in the current directory.
  1. BEGIN TRY  
  2. /*==============================================  
  3. Prepare statements according to server settings  
  4. and given input  
  5. ==============================================*/  
  6. -- DECLARES  
  7. DECLARE @rc int  
  8. DECLARE @DataDir nvarchar(4000)  
  9. DECLARE @LogDir nvarchar(4000)  
  10. DECLARE @LogFileName nvarchar(4000)  
  11. DECLARE @DataFileName nvarchar(4000)  
  12. DECLARE @CMD nvarchar(4000)  
  13. -- Read reg values to get default datapath  
  14. EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @DataDir output, 'no_output'  
  15. -- Check if value is NULL. In that case no changes has been done to default setup. Read from SQLDataRoot instead  
  16. IF (@DataDir is null)  
  17. BEGIN  
  18. EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @DataDir output, 'no_output'  
  19. SELECT @DataDir = @DataDir + N'\Data'  
  20. END  
  21. -- Read reg values to get default logpath  
  22. EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @LogDir output, 'no_output'  
  23. -- Check if value is NULL. In that case no changes has been done to default setup. Read from SQLDataRoot instead  
  24. IF (@LogDir is null)  
  25. BEGIN  
  26. EXEC @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @LogDir output, 'no_output'  
  27. SELECT @LogDir = @LogDir + N'\Data'  
  28. END  
  29. -- Add filenames to the folders  
  30. SET @DataFileName = @DataDir + '\$(DBNAME)_Data.mdf'  
  31. SET @LogFileName = @LogDir + '\$(DBNAME)_Log.ldf'  
  32. -- Prepare statement  
  33. SELECT @CMD =  
  34. 'CREATE DATABASE $(DBNAME)  
  35. ON  
  36. (  
  37. NAME = $(DBNAME)_Data,  
  38. FILENAME = ''' + @DataFileName + ''',  
  39. SIZE = 50,  
  40. FILEGROWTH = 10  
  41. )  
  42. LOG ON (  
  43. NAME = $(DBNAME)_Log,  
  44. FILENAME = ''' + @LogFileName + ''',  
  45. SIZE = 100,  
  46. FILEGROWTH = 20  
  47. )  
  48. '  
  49. /*==============================================  
  50. This is were the action starts  
  51. ==============================================*/  
  52. PRINT 'Adding database $(DBNAME). Working...'  
  53. -- Create database  
  54. EXEC (@CMD)  
  55. PRINT 'Adding database $(DBNAME). Finished!'  
  56. PRINT 'Adding settings and properties for $(DBNAME). Working...'  
  57. -- Add DBTYPE as extended property  
  58. EXEC $(DBNAME).sys.sp_addextendedproperty @name=N'DatabaseType', @value=N''  
  59. --Disable fulltext  
  60. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))  
  61. BEGIN  
  62. EXEC $(DBNAME).[dbo].[sp_fulltext_database] @action = 'disable'  
  63. END  
  64. -- Set recovery model  
  65. ALTER DATABASE $(DBNAME) SET RECOVERY FULL  
  66. ALTER DATABASE $(DBNAME) SET MULTI_USER  
  67. --All other database settings are based on the "model" database in local instance  
  68. --see: sp_configure  
  69. -- Set owner = sa (cosmetics only)  
  70. EXEC $(DBNAME).dbo.sp_changedbowner @loginame = N'sa', @map = false  
  71. PRINT 'Adding settings and properties for $(DBNAME). Finished'  
  72. END TRY  
  73. BEGIN CATCH  
  74. DECLARE @ErrorMessage NVARCHAR(4000)  
  75. DECLARE @ErrorNumber INT  
  76. DECLARE @ErrorSeverity INT  
  77. DECLARE @ErrorState INT  
  78. DECLARE @ErrorLine INT  
  79. IF ERROR_NUMBER() IS NOT NULL  
  80. BEGIN  
  81. SET @ErrorNumber = ERROR_NUMBER()  
  82. SET @ErrorSeverity = ERROR_SEVERITY()  
  83. SET @ErrorState = ERROR_STATE()  
  84. SET @ErrorLine = ERROR_LINE()  
  85. -- Return an error with state 127 since it will abort SQLCMD  
  86. SET @ErrorMessage = 'Error %d, Severity %d, State %d, Line %d, Message: '+ ERROR_MESSAGE()  
  87. RAISERROR (@ErrorMessage, 16, 127, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorLine)  
  88. END  
  89. END CATCH  
  90. PRINT 'Finished'  
  91. /*******************************************************************************/  
Dropping database

Now this time, we will drop a DB. I have divided the code into two classes. Let's see them in detail.

The first class is the control class through which we will call the helpers. In the code, given below, first, we will try to connect to the master DB. In the drop database method, we need to check if the DB exists or not and if it does, we will need to set the DB online. The reason to set the DB online is when you drop the DB, the file on the disk will also be deleted.
  1. class Program   
  2. {  
  3.     private static ExecuteSql _executeMaster;  
  4.     private static string _connectionString = "Data Source=.;Integrated Security=SSPI;Initial Catalog=testme;Application Name=Manager.Test;Connection Timeout=300;  
  5.     User ID = sa;  
  6.     Password = password;  
  7.     ";  
  8.     private static string _masterDatabaseName = "master";  
  9.     static void Main(string[] args)  
  10.     {  
  11.         _executeMaster = new ExecuteSql(openConnectionToMaster);  
  12.         dropDatabase("testme");  
  13.         Console.WriteLine("done");  
  14.         Console.ReadLine();  
  15.     }  
  16.     private static SqlConnection openConnectionToMaster()  
  17.     {  
  18.         var connectionStringBuilder = new SqlConnectionStringBuilder(_connectionString);  
  19.         connectionStringBuilder.InitialCatalog = _masterDatabaseName;  
  20.         var conn = new SqlConnection(connectionStringBuilder.ConnectionString);  
  21.         conn.Open();  
  22.         return conn;  
  23.     }  
  24.     private static void dropDatabase(string databaseName)  
  25.     {  
  26.         if (Exists(databaseName))  
  27.         {  
  28.             SetOnline(databaseName); // if dropping a database that is offline, the file on disk will remain!  
  29.             _executeMaster.ExecuteTransactionlessNonQuery(string.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;", databaseName));  
  30.             Drop(databaseName);  
  31.         }  
  32.     }  
  33.     public static bool Exists(string databaseName)  
  34.     {  
  35.         return  
  36.         Convert.ToBoolean(_executeMaster.ExecuteScalar("SELECT database_id FROM sys.databases WHERE Name = @databaseName", parameters: new Dictionary & lt; string, object & gt; {  
  37.             {  
  38.                 "@databaseName",  
  39.                 databaseName  
  40.             }  
  41.         }));  
  42.     }  
  43.     public static void Drop(string databaseName)  
  44.     {  
  45.         _executeMaster.ExecuteTransactionlessNonQuery(string.Format("DROP DATABASE [{0}]", databaseName), 120);  
  46.     }  
  47.     public static void SetOnline(string databaseName)   
  48.     {  
  49.         _executeMaster.ExecuteTransactionlessNonQuery(string.Format("ALTER DATABASE [{0}] SET ONLINE", databaseName));  
  50.     }  
  51. }  
Now, you need to replace the user Id and password.