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