//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);
}
//method implemented for readability
//similar to user.Query()
//query db for rows matching object using a stored proc
public static T[] QueryWithStoredProc(string storedProc, params object[] storedProcParameters)
{
List <T> all = new List <T>();
DataTable dt = DbEntityDbHandler.ExecuteStoredProc(storedProc, storedProcParameters);
foreach (DataRow dr in dt.Rows)
{
T obj = new T();
CopyDataRowValuesToObjectProperties(dr, obj);
all.Add(obj);
}
return(all.ToArray());
}
//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);
}
//query for results from DB
//auto populate the stored proc parameters based on the obj properties
public virtual T[] QueryWithStoredProcAutoParams(string storedProc)
{
List <T> all = new List <T>();
object[] storedProcParameters = GetStoredProcParameters(storedProc);
DataTable dt = DbEntityDbHandler.ExecuteStoredProc(storedProc, storedProcParameters);
foreach (DataRow dr in dt.Rows)
{
T obj = new T();
CopyDataRowValuesToObjectProperties(dr, obj);
all.Add(obj);
}
this.StatusCode = DbGlobals.SUCCESS_STATUS_CODE;
this.StatusDesc = DbGlobals.SUCCESS_STATUS_TEXT;
return(all.ToArray());
}
//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);
}
protected DataSet FetchStoredProcParametersFromDb(string storedProc)
{
DataSet ds = new DataSet();
//if we managed to create the stored proc we can use to
//get parameters of other stored procs
if (DbInitializerBase.WasInitOfStoredProcSuccessfull())
{
ds = DbEntityDbHandler.ExecuteDataSet(DbGlobals.NameOfStoredProcToGetParameterNames, storedProc);
return(ds);
}
//we should just run a normal sql query to get the parameters for the
//stored proc being called
string sql = "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";
ds = DbEntityDbHandler.ExecuteSqlQuery(sql);
return(ds);
}
//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);
}
