//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);
}
