/// <summary>
/// Deletes an Entity from the database
/// </summary>
/// <remarks>This method utilizes the <see cref="PreparedNonQuery"/> to speed things along.</remarks>
/// <param name="obj">The <see cref="TEntity"/> object to remove from the dataset</param>
/// <returns>true if an entity was removed from the dataset; false otherwise.</returns>
public bool Remove(TEntity obj)
{
// Generate the SQL
if (DeleteQuery == null)
{
// Start the query using a query builder
var builder = new DeleteQueryBuilder(Context).From(EntityTable.TableName);
// build the where statement, using primary keys only
foreach (string keyName in EntityTable.PrimaryKeys)
{
PropertyInfo info = EntityTable.Columns[keyName].Property;
builder.Where(keyName, Comparison.Equals, new SqlLiteral($"@{keyName}"));
}
DeleteQuery = new PreparedNonQuery(builder.BuildCommand());
}
// Execute the SQL Command
lock (DeleteQuery)
{
DeleteQuery.SetParameters(obj, EntityTable);
return(DeleteQuery.Execute() > 0);
}
}
/// <summary>
/// Updates an Entity in the database, provided that none of the Primary
/// keys were modified.
/// </summary>
/// <remarks>This method utilizes the <see cref="PreparedNonQuery"/> to speed things along.</remarks>
/// <param name="obj">The <see cref="TEntity"/> object to update in the dataset</param>
/// <returns>true if any records in the database were affected; false otherwise.</returns>
public bool Update(TEntity obj)
{
if (UpdateQuery == null)
{
using (var updateQuery = new UpdateQueryBuilder(EntityTable.TableName, Context))
{
// Generate the SQL
foreach (var attribute in EntityTable.Columns)
{
// Keys go in the WHERE statement, not the SET statement
if (EntityTable.PrimaryKeys.Contains(attribute.Key))
{
PropertyInfo info = attribute.Value.Property;
updateQuery.Where(attribute.Key, Comparison.Equals, new SqlLiteral($"@{attribute.Key}"));
}
else
{
updateQuery.Set(attribute.Key, new SqlLiteral($"@{attribute.Key}"));
}
}
UpdateQuery = new PreparedNonQuery(updateQuery.BuildCommand());
}
}
lock (UpdateQuery)
{
// Update parameters and execute the SQL Command
UpdateQuery.SetParameters(obj, EntityTable);
return(UpdateQuery.Execute() == 1);
}
}
/// <summary>
/// Inserts a new Entity into the database, and sets any Foreign key properties. If the
/// entity table has a single integer primary key, the primary key value will be updated
/// with the <see cref="SQLiteConnection.LastInsertRowId"/>.
/// </summary>
/// <remarks>This method utilizes the <see cref="PreparedNonQuery"/> to speed things along.</remarks>
/// <param name="obj">The <see cref="TEntity"/> object to add to the dataset</param>
public void Add(TEntity obj)
{
// For fetching the RowID
AttributeInfo rowid = EntityTable.RowIdColumn;
// Generate the SQL
if (InsertQuery == null)
{
using (var query = new InsertQueryBuilder(EntityTable.TableName, Context))
{
foreach (var attribute in EntityTable.Columns)
{
// Grab value
PropertyInfo property = attribute.Value.Property;
bool isKey = attribute.Value.PrimaryKey;
// Check for integer primary keys
if (isKey && EntityTable.HasRowIdAlias && EntityTable.RowIdColumn == attribute.Value)
{
continue;
}
// Add attribute to the field list
query.Set(attribute.Key, new SqlLiteral($"@{attribute.Key}"));
}
InsertQuery = new PreparedNonQuery(query.BuildCommand());
}
}
// Execute the SQL Command
lock (InsertQuery)
{
InsertQuery.SetParameters(obj, EntityTable);
int result = InsertQuery.Execute();
// If the insert was successful, lets build our Entity relationships
if (result > 0)
{
// If we have a Primary key that is determined database side,
// than we can update the current object's key value here
if (EntityTable.HasRowIdAlias)
{
long rowId = Context.Connection.LastInsertRowId;
rowid.Property.SetValue(obj, Convert.ChangeType(rowId, rowid.Property.PropertyType));
}
// Build relationships after a fresh insert
EntityTable.CreateRelationships(obj, Context);
}
}
}
