/// <summary> /// Creates the query for inserting data into the datasource. /// </summary> /// <param name="dbObject"><seealso cref="DBObject"/> to get information from.</param> /// /// <returns> /// Generated Insert query. /// </returns> private static string CreateInsertString(DBObject dbObject) { StringBuilder partOne = new StringBuilder("("); StringBuilder partTwo = new StringBuilder("("); string lastPropertyName = dbObject.SchemaFieldProperties.Last().Name; foreach (PropertyInfo property in dbObject.SchemaFieldProperties) { partOne.Append($"[{property.Name}]"); partTwo.Append($"@{property.Name}"); if (property.Name.Equals(lastPropertyName)) { partOne.Append(")"); partTwo.Append(")"); } else { partOne.Append(", "); partTwo.Append(", "); } } string tableName = dbObject.GetType().Name; return($"INSERT INTO [{tableName}]{partOne.ToString()} VALUES {partTwo.ToString()}"); }
/// <summary> /// Creates the query for updating data in the datasource. /// </summary> /// <param name="dbObject"><seealso cref="DBObject"/> to get information from.</param> /// /// <returns> /// Generated Update query. /// </returns> private static string CreateUpdateString(DBObject dbObject) { StringBuilder updatedValues = new StringBuilder(); string lastPropertyName = dbObject.SchemaFieldProperties.Last().Name; foreach (PropertyInfo property in dbObject.SchemaFieldProperties.Where(pi => !pi.GetCustomAttribute <SchemaField>().IsPrimaryKey)) { updatedValues.Append($"[{property.Name}] = @{property.Name}"); if (!property.Name.Equals(lastPropertyName)) { updatedValues.Append(", "); } } string tableName = dbObject.GetType().Name; return($"UPDATE [{tableName}] SET {updatedValues} WHERE {dbObject.PrimaryKeyPropertyInfo.Name} = {dbObject.PrimaryKey}"); }
/// <summary> /// Deletes the <seealso cref="DBObject"/> from the datasource. /// </summary> /// <param name="dbObject"><seealso cref="DBObject"/> to delete from the datasource.</param> /// /// <example> /// The following example shows how to <see cref="Delete"/> a <seealso cref="Customer"/> with a username of "billy123" from the datasource. /// <code> /// List<Customer> customers = DBController.GetAllRecords<Customer>().Where(cust => cust.Username == "billy123").ToList(); /// foreach (Customer customer in customers) /// DBController.Delete(customer); /// </code> /// </example> /// /// <returns> /// Whether the delete was successful or not. /// </returns> /// <exception cref="OleDbException"> /// Throws an exception when a foreign key linkage is being violated. /// </exception> public static bool Delete(DBObject dbObject) { using (OleDbConnection dbConnection = new OleDbConnection(CONNECTION_STRING)) { string table = dbObject.GetType().Name; string query = $"DELETE FROM {table} WHERE {dbObject.PrimaryKeyPropertyInfo.Name} = {dbObject.PrimaryKey}"; int rowsAffected; using (OleDbCommand command = new OleDbCommand(query, dbConnection)) { dbConnection.Open(); try { rowsAffected = command.ExecuteNonQuery(); } catch (OleDbException ex) { Console.Error.WriteLine(ex.Message); throw; } } return(rowsAffected != 0); } }