/// <summary> /// performs a single, simple update operation on a table /// </summary> /// <param name="tableName">name of the table to perform the updates within</param> /// <param name="values">the columns to update and the values to update them to</param> /// <param name="predicate">the predicate for the update operation</param> public void Update(string tableName, List <DBPredicate> values, DBPredicate predicate) { lock ( m_databaseLock ) { _VerifyConnection(); try { m_sqlConnection.Open(); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); using (SQLiteCommand command = m_sqlConnection.CreateCommand()) { command.CommandText = $"UPDATE {builder.QuoteIdentifier(tableName)} SET "; _AppendPredicates(command, values, ","); command.CommandText += $" WHERE({builder.QuoteIdentifier(predicate.Column)} {_OperatorToString(predicate.Operator)} @predicateValue);"; command.Parameters.AddWithValue("@predicateValue", predicate.Value); command.ExecuteNonQuery(); } m_sqlConnection.Close(); } catch (Exception crap) { m_sqlConnection.Close(); throw crap; } } }
/// <summary> /// deletes a row from a table /// </summary> /// <param name="tableName">the name of the table to remove from</param> /// <param name="predicates">predicates for the removal of a single row</param> /// <returns>the number of rows that were deleted</returns> public int Delete(string tableName, DBPredicate predicate) { lock ( m_databaseLock ) { _VerifyConnection(); try { m_sqlConnection.Open(); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); int result = 0; using (SQLiteCommand command = m_sqlConnection.CreateCommand()) { command.CommandText = $"DELETE FROM { builder.QuoteIdentifier(tableName)} WHERE({builder.QuoteIdentifier(predicate.Column)} {_OperatorToString(predicate.Operator)} @value0);"; command.Parameters.AddWithValue("@value0", predicate.Value); result = command.ExecuteNonQuery(); } m_sqlConnection.Close(); return(result); } catch (Exception crap) { m_sqlConnection.Close(); throw crap; } } }
/// <summary> /// Selects all rows from a table where a single predicate is true /// </summary> /// <typeparam name="T"></typeparam> /// <param name="tableName">name of the table to select from</param> /// <param name="predicates">predicate which must evaluate to true</param> /// <returns>the desired row, or null if there were no results</returns> public T Select <T>(string tableName, DBPredicate predicate, DBDataContainerCreator <T> dataContainerCreator) where T : DBDataContainer <T> { lock ( m_databaseLock ) { _VerifyConnection(); List <T> queryResults = null; try { m_sqlConnection.Open(); using (SQLiteCommand command = m_sqlConnection.CreateCommand()) { SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); command.CommandText = $"SELECT * FROM {builder.QuoteIdentifier(tableName)} WHERE({builder.QuoteIdentifier(predicate.Column)} {_OperatorToString(predicate.Operator)} @value0);"; command.Parameters.AddWithValue("@value0", predicate.Value); queryResults = _ExecuteSelectCommand <T>(command, dataContainerCreator); } } catch (Exception crap) { m_sqlConnection.Close(); throw crap; } finally { m_sqlConnection.Close(); } return((queryResults != null && queryResults.Count > 0) ? queryResults[0] : null); } }