/* * Removes the specified object from the database * List<T> Outlist: The list of objects passed back by the method, if an exception is encountered, an empty list is passed * SQLDB dbcon: The database connection. Must implement the SQLDB interface * return: boolean value to indicate if an error has occurred */ public static bool RemoveFromDB <T>(T deleteObj, I_SQLDB dbCon) where T : class, new() { PropertyInfo[] fields = typeof(T).GetProperties(); T tableType = new T(); using (SqlConnection dbConnect = dbCon.GetConnection()) { dbConnect.Open(); // Crafting the SQL Query string query = "DELETE FROM " + tableType.GetType().Name + " WHERE "; int i = 0; int last = fields.Length - 1; foreach (PropertyInfo prop in fields) { query += prop.Name + " = \'" + prop.GetValue(deleteObj) + "\'"; if (i++ < last) { query += " and "; } } // Running the Query try { using (SqlCommand cmd = new SqlCommand(query, dbConnect)) { cmd.ExecuteScalar(); dbConnect.Close(); return(true); } } catch (Exception ex) { LogWriter.Log(ex); return(false); } } }
/* * Runs an update query on the db, with the caller specifying the query * SQLDB dbcon: The database connection. Must implement the SQLDB interface * string query: The SQL query to be executed to update the DB * return: boolean value to indicate if an error has occurred */ public static bool UpdateInDB <T>(I_SQLDB dbCon, string query) where T : class, new() { using (SqlConnection dbConnect = dbCon.GetConnection()) { dbConnect.Open(); try { using (SqlCommand cmd = new SqlCommand(query, dbConnect)) { cmd.ExecuteScalar(); dbConnect.Close(); return(true); } } catch (Exception ex) { LogWriter.Log(ex); return(false); } } }
/* * Updates the passed object in the database, generates query automatically based on object * T updateObj: The object to be updated, with new values inserted (may not change the PK) * SQLDB dbcon: The database connection. Must implement the SQLDB interface * return: boolean value to indicate if an error has occurred */ public static bool UpdateInDB <T>(T updatetObj, I_SQLDB dbCon) where T : class, new() { PropertyInfo[] fields = typeof(T).GetProperties(); T tableType = new T(); using (SqlConnection dbConnect = dbCon.GetConnection()) { dbConnect.Open(); // Crafting the SQL Query string query = "Update " + tableType.GetType().Name + " Set "; int i = 0; int last = fields.Length - 1; foreach (PropertyInfo prop in fields) { if (i != 0) //No updating the PK { if (!prop.PropertyType.Equals(typeof(int))) { query += prop.Name + " = \'" + prop.GetValue(updatetObj) + "\'"; } else { query += prop.Name + " = " + prop.GetValue(updatetObj); } if (i < last) { query += ", ";//Don't put a comma on the last option } } i++; } query += " where " + fields[0].Name + " = " + fields[0].GetValue(updatetObj); //Use Primary key to define which object to update // Running the Query try { using (SqlCommand cmd = new SqlCommand(query, dbConnect)) { cmd.ExecuteScalar(); dbConnect.Close(); return(true); } } catch (Exception ex) { LogWriter.Log(ex); return(false); } } }
/* * Gets a list of the supplied type from the database, allows specifying the query to narrow down the pull results * List<T> Outlist: The list of objects passed back by the method, if an exception is encountered, an empty list is passed * SQLDB dbcon: The database connection. Must implement the I_SQLDB interface * string sqlQuery: Sql query to be run. * return: boolean value to indicate if an error has occurred */ public static bool GetFromDB <T>(out List <T> outList, I_SQLDB dbCon, string sqlQuery) where T : class, new() { outList = new List <T>(); PropertyInfo[] fields = typeof(T).GetProperties(); using (SqlConnection dbConnect = dbCon.GetConnection()) { dbConnect.Open(); string query = sqlQuery; try { using (SqlCommand cmd = new SqlCommand(query, dbConnect)) { //run command and process results using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { T o = new T(); foreach (PropertyInfo prop in fields) { if (reader[prop.Name] != DBNull.Value) { string column = prop.Name; string readO = reader[column].ToString(); Type propType = prop.PropertyType; prop.SetValue(o, Convert.ChangeType(readO, propType), null); } else { prop.SetValue(o, null, null); } //prop.SetValue(o, default(), null); } outList.Add(o); } } } } catch (Exception ex) { LogWriter.Log(ex); outList = new List <T>(); return(false); } dbConnect.Close(); return(true); } }
/* * Inserts the passed object into the database, generates query automatically based on object * T insertObj: The object to be inserted * SQLDB dbcon: The database connection. Must implement the SQLDB interface * return: boolean value to indicate if an error has occurred */ public static bool InsertToDB <T>(T insertObj, I_SQLDB dbCon) where T : class, new() { PropertyInfo[] fields = typeof(T).GetProperties(); T tableType = new T(); using (SqlConnection dbConnect = dbCon.GetConnection()) { dbConnect.Open(); // Crafting the SQL Query string query = "INSERT INTO " + tableType.GetType().Name + "("; int i = 0; int last = fields.Length - 1; foreach (PropertyInfo prop in fields) { if (i != 0) { query += prop.Name; if (i < last) { query += ", "; } } i++; } query += ") Values ("; i = 0; foreach (PropertyInfo prop in fields) { if (i != 0) { query += "@" + prop.Name; if (i < last) { query += ", "; } } i++; } query += ")"; // Running the Query try { using (SqlCommand cmd = new SqlCommand(query, dbConnect)) { foreach (PropertyInfo prop in fields) { if (prop.GetValue(insertObj) == null) { cmd.Parameters.AddWithValue("@" + prop.Name, ""); } else { cmd.Parameters.AddWithValue("@" + prop.Name, prop.GetValue(insertObj)); } } cmd.ExecuteScalar(); dbConnect.Close(); return(true); } } catch (Exception ex) { LogWriter.Log(ex); return(false); } } }