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