//drops the Db if it exists protected static DbResult ExecuteDropDatabaseSQLIfExists(string DbConnectionString = null) { DbResult apiResult = new DbResult(); try { string connectionString = DbConnectionString ?? ReadConnectionFromConfig(); try { string databaseName = connectionString?.Split(';')?.Where(i => i.ToUpper().Contains("CATALOG"))?.FirstOrDefault()?.Split('=')?[1]; string newConnectionString = connectionString.Replace(databaseName, "master"); bool isSet = DbEntityDbHandler.SetConnectionString(newConnectionString); if (!isSet) { apiResult.SetFailuresAsStatusInResponseFields($"ERROR: UNABLE TO SET NEW CONNECTION STRING IN CONFIG FILE INORDER TO CREATE DATABASE"); return(apiResult); } //switch to the master db first ie. u cant drop a db if u are using it //change the db to single use ie. close existing connections //then we can drop it string useMasterSQL = "use master"; int rowsAffected = DbEntityDbHandler.ExecuteNonQuery(useMasterSQL); string alterSQL = $"ALTER DATABASE {databaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE"; rowsAffected = DbEntityDbHandler.ExecuteNonQuery(alterSQL); string dropSQL = $"Drop Database {databaseName}"; rowsAffected = DbEntityDbHandler.ExecuteNonQuery(dropSQL); apiResult.SetSuccessAsStatusInResponseFields(); } catch (Exception ex) { string msg = ex.Message; apiResult.SetFailuresAsStatusInResponseFields($"ERROR: {msg}"); } //rollback stuff DbEntityDbHandler.SetConnectionString(connectionString); return(apiResult); } catch (Exception ex) { string msg = ex.Message; apiResult.SetFailuresAsStatusInResponseFields($"ERROR: {msg}"); } return(apiResult); }
//creates the Db if it doesnt exists protected static DbResult ExecuteCreateDatabaseSQLIfNotExists(string DbConnectionString = null) { DbResult apiResult = new DbResult(); try { string connectionString = DbConnectionString ?? ReadConnectionFromConfig(); try { string databaseName = connectionString?.Split(';')?.Where(i => i.ToUpper().Contains("CATALOG"))?.FirstOrDefault()?.Split('=')?[1]; string newConnectionString = connectionString.Replace(databaseName, "master"); bool isSet = DbEntityDbHandler.SetConnectionString(newConnectionString); if (!isSet) { apiResult.SetFailuresAsStatusInResponseFields($"ERROR: UNABLE TO SET NEW CONNECTION STRING IN CONFIG FILE INORDER TO CREATE DATABASE"); return(apiResult); } string createSQL = $"Create Database {databaseName}"; int rowsAffected = DbEntityDbHandler.ExecuteNonQuery(createSQL); apiResult.SetSuccessAsStatusInResponseFields(); } catch (Exception ex) { string msg = ex.Message.ToUpper(); if (msg.Contains("ALREADY") || msg.Contains("EXISTS")) { _is_init_of_storedProcs_successfull = true; apiResult.SetSuccessAsStatusInResponseFields(); } else { apiResult.SetFailuresAsStatusInResponseFields($"ERROR: {msg}"); } } DbEntityDbHandler.SetConnectionString(connectionString); return(apiResult); } catch (Exception ex) { string msg = ex.Message; apiResult.SetFailuresAsStatusInResponseFields($"ERROR: {msg}"); } return(apiResult); }
//sets the constring to whatever is read from the config file protected static DbResult SetConnectionStringInDatabaseHandler(string dbConnectionString = null) { DbResult dbResult = new DbResult(); dbConnectionString = dbConnectionString ?? ReadConnectionFromConfig(); bool con_string_was_set = DbEntityDbHandler.SetConnectionString(dbConnectionString); if (con_string_was_set) { dbResult.StatusCode = DbGlobals.SUCCESS_STATUS_CODE; dbResult.StatusDesc = DbGlobals.SUCCESS_STATUS_TEXT; return(dbResult); } dbResult.StatusCode = DbGlobals.FAILURE_STATUS_CODE; dbResult.StatusDesc = "FAILED TO SET CONNECTION STRING"; return(dbResult); }