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