//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 exist, updates the db schema and initializes the db connection
public static DbResult CreateDbIfNotExistsAndUpdateSchema(string DbConnectionString = null)
{
DbResult apiResult = new DbResult();
try
{
AutoFindTypesToKeepTrackOf();
//create the db
apiResult = ExecuteCreateDatabaseSQLIfNotExists(DbConnectionString);
//we failed to create the db
if (apiResult.StatusCode != DbGlobals.SUCCESS_STATUS_CODE)
{
return(apiResult);
}
//try to create stored procedures for fetching
//parameters for any other stored proc
//this makes calls to any AutoParams method much faster
//if its successfull otherwise we default to executing raw sql
CreateStoredProcedures();
IConfigurationSource source = GetConfigurationSource(DbConnectionString);
//initialize active record
ActiveRecordStarter.Initialize(source, TypesToKeepTrackOf.ToArray());
ActiveRecordStarter.UpdateSchema();
//we are all good
_is_init_successfull = true;
apiResult.SetSuccessAsStatusInResponseFields();
}
catch (Exception ex)
{
if (ex.Message.ToUpper().Contains("MORE THAN ONCE"))
{
_is_init_successfull = true;
apiResult.StatusCode = DbGlobals.SUCCESS_STATUS_CODE;
apiResult.StatusDesc = "SUSPECTED DOUBLE INITIALIZE: " + ex.Message;
}
else
{
apiResult.SetFailuresAsStatusInResponseFields(EXCEPTION_LEAD_STRING + ex.Message);
}
}
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);
}
//find all types that inherit from ActiveRecordBase
//using reflection
protected static DbResult AutoFindTypesToKeepTrackOf()
{
DbResult dbResult = new DbResult();
try
{
List <Type> types_that_inherit_from_activeRecord = FindDerivedTypes(Assembly.GetEntryAssembly(), typeof(ActiveRecordBase)).ToList();
TypesToKeepTrackOf.AddRange(types_that_inherit_from_activeRecord);
dbResult.StatusCode = DbGlobals.SUCCESS_STATUS_CODE;
dbResult.StatusDesc = DbGlobals.SUCCESS_STATUS_TEXT;
}
catch (Exception ex)
{
dbResult.StatusCode = DbGlobals.FAILURE_STATUS_CODE;
dbResult.StatusDesc = $"EXCEPTION: {ex.Message}";
}
return(dbResult);
}
//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);
}
//Drops the database if its there, creates the database, updates the db schema and initializes the db connection
public static DbResult DropAndRecreateDb(string DbConnectionString = null)
{
DbResult dbResult = new DbResult();
try
{
AutoFindTypesToKeepTrackOf();
dbResult = SetConnectionStringInDatabaseHandler(DbConnectionString);
if (dbResult.StatusCode != DbGlobals.SUCCESS_STATUS_CODE)
{
return(dbResult);
}
dbResult = ExecuteDropDatabaseSQLIfExists(DbConnectionString);
if (dbResult.StatusCode != DbGlobals.SUCCESS_STATUS_CODE)
{
return(dbResult);
}
return(CreateDbIfNotExistsAndUpdateSchema(DbConnectionString));
}
catch (Exception ex)
{
if (ex.Message.ToUpper().Contains("MORE THAN ONCE"))
{
_is_init_successfull = true;
dbResult.StatusCode = DbGlobals.SUCCESS_STATUS_CODE;
dbResult.StatusDesc = "SUSPECTED DOUBLE INITIALIZE: " + ex.Message;
}
else
{
dbResult.SetFailuresAsStatusInResponseFields(EXCEPTION_LEAD_STRING + ex.Message);
}
}
return(dbResult);
}
//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);
}
