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