//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); }
public static DbResult DropAndRecreate() { DbResult apiResult = new DbResult(); try { SetConnectionStringInDatabaseHandler(); IConfigurationSource source = System.Configuration.ConfigurationManager.GetSection(SectionName) as IConfigurationSource; ActiveRecordStarter.Initialize(source, TypesToKeepTrackOf.ToArray()); ActiveRecordStarter.DropSchema(); ActiveRecordStarter.UpdateSchema(); apiResult.SetSuccessAsStatusInResponseFields(); } catch (Exception ex) { if (ex.Message.ToUpper().Contains("MORE THAN ONCE")) { apiResult.StatusCode = DbGlobals.SUCCESS_STATUS_CODE; apiResult.StatusDesc = "SUSPECTED DOUBLE INITIALIZE: " + ex.Message; } else { apiResult.SetFailuresAsStatusInResponseFields(ExceptionLeadString + ex.Message); } } return(apiResult); }
//Initialize Db connection only...no updates done public static DbResult Initialize(string ConnectionString = null) { DbResult dbResult = new DbResult(); try { //attempt to determine the types to keep track of automatically //by reflection (op result doesnt really matter) AutoFindTypesToKeepTrackOf(); dbResult = SetConnectionStringInDatabaseHandler(ConnectionString); //failed to set con string //we stop here since setting the con string is necessary to contine if (dbResult.StatusCode != DbGlobals.SUCCESS_STATUS_CODE) { return(dbResult); } //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(); var source = GetConfigurationSource(ConnectionString); //initialize active record ActiveRecordStarter.Initialize(source, TypesToKeepTrackOf.ToArray()); _is_init_successfull = true; //all is good dbResult.SetSuccessAsStatusInResponseFields(); } catch (Exception ex) { //they have called the initialize method again 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; } //some other failure else { dbResult.SetFailuresAsStatusInResponseFields(EXCEPTION_LEAD_STRING + ex.Message); } } return(dbResult); }
//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); }
//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); }