//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);
}
//creates any initial stored procedures necessary
protected static DbResult CreateStoredProcedures()
{
DbResult apiResult = new DbResult();
try
{
string createSql = $"create proc {DbGlobals.NameOfStoredProcToGetParameterNames}" +
" @StoredProcName varchar(200)" +
" as" +
" Begin" +
" select" +
" 'Parameter_name' = name," +
" 'Type' = type_name(user_type_id)," +
" 'Param_order' = parameter_id" +
" from sys.parameters where object_id = object_id(@StoredProcName)" +
" order by Param_order asc" +
" End";
int rowsAffected = DbEntityDbHandler.ExecuteNonQuery(createSql);
_is_init_of_storedProcs_successfull = true;
apiResult.SetSuccessAsStatusInResponseFields();
}
catch (Exception ex)
{
string msg = ex.Message.ToUpper();
//stored proc is already there
if (msg.Contains("ALREADY") || msg.Contains("EXISTS"))
{
_is_init_of_storedProcs_successfull = true;
apiResult.SetSuccessAsStatusInResponseFields();
}
else
{
apiResult.SetSuccessAsStatusInResponseFields();//($"ERROR: UNABLE TO CREATE NECESSARY STORED PROC's: {msg}");
}
}
return(apiResult);
}
//update/insert/delete from db rows using a stored proc
protected static int ExecuteNonQueryUsingStoredProc(string storedProc, params object[] storedProcParameters)
{
int rowsAffected = DbEntityDbHandler.ExecuteNonQuery(storedProc, storedProcParameters);
return(rowsAffected);
}