/// <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; } } }
private SQLiteCommand _BuildSelectCommand(string tableName, List <DBPredicate> predicates, string combiner) { try { m_sqlConnection.Open(); SQLiteCommand command = m_sqlConnection.CreateCommand(); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); command.CommandText = $"SELECT * FROM {builder.QuoteIdentifier(tableName)} WHERE"; for (int i = 0; i < predicates.Count; i++) { command.CommandText += $" {builder.QuoteIdentifier(predicates[i].Column)} {_ParseOperator(predicates[i].Operator)} @value{i}"; command.Parameters.AddWithValue($"@value{i}", predicates[i].Value); if (i < predicates.Count - 1) { command.CommandText += $" {combiner}"; } } command.CommandText += ";"; m_sqlConnection.Close(); return(command); } catch (Exception crap) { m_sqlConnection.Close(); throw crap; } }
/// <summary> /// Selects all rows and columns from the database /// </summary> /// <typeparam name="T"></typeparam> /// <param name="tableName">name of the table to select from</param> /// <returns>a list of data containers of the specified type containing the results, or null if there were no results</returns> public List <T> SelectAll <T>(string tableName, DBDataContainerCreator <T> dataContainerCreator) where T : DBDataContainer <T> { lock ( m_databaseLock ) { _VerifyConnection(); try { m_sqlConnection.Open(); List <T> results = new List <T>(); using (SQLiteCommand command = m_sqlConnection.CreateCommand()) { SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); command.CommandText = $"SELECT * FROM {builder.QuoteIdentifier(tableName)};"; results = _ExecuteSelectCommand <T>(command, dataContainerCreator); } m_sqlConnection.Close(); return(results); } 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> /// Inserts multiple rows into a table /// </summary> /// <param name="tableName">table to insert into</param> /// <param name="values">a list of rows to insert. each row must have parameters matching the number and order of columns in the table</param> /// <returns>the number of rows that were inserted</returns> public int Insert <T>(string tableName, List <T> values) where T : DBDataContainer <T> { SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); string query = $"INSERT INTO {builder.QuoteIdentifier(tableName)} VALUES"; for (int a = 0; a < values.Count; a++) { query += "("; for (int i = 0; i < values[a].PropertyCount(); i++) { query += $"{values[a][i]}"; if (i < values[a].PropertyCount() - 1) { query += ", "; } } query += ")"; if (a < values.Count - 1) { query += ", "; } } query += ";"; return(ExecuteNonQuery(query)); }
public string QuoteIdentifier(string identifier) { if (_builder == null) { _builder = new SQLiteCommandBuilder(); } return(_builder.QuoteIdentifier(identifier)); }
/// <summary> /// Inserts a new row into a table /// </summary> /// <param name="collisionAction">what to do if a row already exists</param> /// <param name="tableName">table to insert into</param> /// <param name="values">the data for the row to insert. each row must have parameters matching the number and order of columns in the table</param> /// <returns>the number of rows that were inserted</returns> public int Insert <T>(DBCollisionAction collisionAction, string tableName, T value) where T : DBDataContainer <T> { lock ( m_databaseLock ) { try { m_sqlConnection.Open(); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); int result = 0; using (SQLiteCommand command = m_sqlConnection.CreateCommand()) { if (collisionAction == DBCollisionAction.IGNORE) { command.CommandText = $"INSERT OR IGNORE"; } else { command.CommandText = $"INSERT OR REPLACE"; } command.CommandText += $" INTO {builder.QuoteIdentifier(tableName)} VALUES("; //NULL will make sqlite generate a primary key automatically for (int i = 0; i < value.PropertyCount(); i++) { if (value[i] == "NULL") { command.CommandText += "NULL"; } else { command.CommandText += $"@value{i}"; command.Parameters.AddWithValue($"@value{i}", value[i]); } if (i < value.PropertyCount() - 1) { command.CommandText += ", "; } } command.CommandText += ");"; result = command.ExecuteNonQuery(); } m_sqlConnection.Close(); return(result); } catch (Exception crap) { m_sqlConnection.Close(); throw crap; } } }
/// <summary> /// inserts a row into a table /// </summary> /// <param name="collisionAction">what to do if a row already exists</param> /// <param name="tableName">table to insert into</param> /// <param name="values">a list of column-value pairs to insert</param> public void Insert(DBCollisionAction collisionAction, string tableName, List <DBParameter> values) { lock ( m_databaseLock ) { try { m_sqlConnection.Open(); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); using (SQLiteCommand command = m_sqlConnection.CreateCommand()) { if (collisionAction == DBCollisionAction.IGNORE) { command.CommandText = $"INSERT OR IGNORE"; } else { command.CommandText = $"INSERT OR REPLACE"; } command.CommandText += $" INTO {builder.QuoteIdentifier(tableName)}"; string queryColumns = "("; string queryValues = "("; for (int i = 0; i < values.Count(); i++) { queryColumns += values[i].Column; queryValues += values[i].Value; if (i < values.Count() - 1) { queryColumns += ", "; queryValues += ", "; } } queryValues += ")"; queryColumns += ")"; command.CommandText += queryColumns + " VALUES" + queryValues + ";"; command.ExecuteNonQuery(); } m_sqlConnection.Close(); } catch (Exception crap) { m_sqlConnection.Close(); throw crap; } } }
/// <summary> /// efficiently deletes multiple rows from a table /// </summary> /// <param name="tableName">the name of the table to remove from</param> /// <param name="predicates">each list contains the predicates for the removal of a single row</param> public void DeleteTransacted(string tableName, List <List <DBPredicate> > predicates) { lock ( m_databaseLock ) { try { m_sqlConnection.Open(); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); using (SQLiteTransaction t = m_sqlConnection.BeginTransaction()) { using (SQLiteCommand command = m_sqlConnection.CreateCommand()) { for (int a = 0; a < predicates.Count; a++) { command.CommandText = $"DELETE FROM { builder.QuoteIdentifier(tableName)} WHERE("; for (int i = 0; i < predicates[a].Count; i++) { command.CommandText += $"{predicates[a][i].Column} {_OperatorToString(predicates[a][i].Operator)} @value{i}"; command.Parameters.AddWithValue($"@value{i}", predicates[a][i].Value); if (i < predicates[a].Count - 1) { command.CommandText += " AND "; } } command.CommandText += ");"; command.ExecuteNonQuery(); } } t.Commit(); } m_sqlConnection.Close(); } catch (Exception crap) { m_sqlConnection.Close(); throw crap; } } }
/// <summary> /// Selects all rows from a table where all of a list of predicates are true /// </summary> /// <typeparam name="T"></typeparam> /// <param name="tableName">name of the table to select from</param> /// <param name="predicates">list of predicates, all of which must evaluate to true</param> /// <param name="predicateConjunction">how the list of predicates should be joined when performing the query</param> /// <returns>a list of data containers, each containing one row of the results, or null if there were no results</returns> public List <T> Select <T>(string tableName, List <DBPredicate> predicates, DBDataContainerCreator <T> dataContainerCreator, DBConjunction predicateConjunction) where T : DBDataContainer <T> { lock ( m_databaseLock ) { _VerifyConnection(); List <T> queryResults = new List <T>(); try { m_sqlConnection.Open(); using (SQLiteCommand command = m_sqlConnection.CreateCommand()) { SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); command.CommandText = $"SELECT * FROM {builder.QuoteIdentifier(tableName)} WHERE "; _AppendPredicates(command, predicates, predicateConjunction.ToString()); command.CommandText += ";"; queryResults = _ExecuteSelectCommand <T>(command, dataContainerCreator); } } catch (Exception crap) { m_sqlConnection.Close(); throw crap; } finally { m_sqlConnection.Close(); } return(queryResults); } }
private void _AppendPredicates(SQLiteCommand command, List <DBPredicate> predicates, string conjunction) { if (command == null) { return; } SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); int valueIndex = command.Parameters.Count; for (int i = 0; i < predicates.Count; i++) { command.CommandText += $"{builder.QuoteIdentifier(predicates[i].Column)} {_OperatorToString(predicates[i].Operator)} @value{valueIndex}"; command.Parameters.AddWithValue($"@value{valueIndex}", predicates[i].Value); if (i < predicates.Count - 1) { command.CommandText += $" {conjunction} "; } valueIndex++; } }
/// <summary> /// performs a single 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="conjunction">defines how the predicates should be joined in the query</param> /// <param name="predicates">the predicates for the update operation, all of which must be true for the operation to succeed</param> public void Update(string tableName, List <DBPredicate> values, List <DBPredicate> predicates, DBConjunction conjunction = DBConjunction.AND) { 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("; _AppendPredicates(command, predicates, conjunction.ToString()); command.CommandText += ");"; Debug.WriteLine("Executing update query: " + command.CommandText); command.ExecuteNonQuery(); } m_sqlConnection.Close(); } 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); } }
/// <summary> /// Turn a datatable into a table in the temporary database for the connection /// </summary> /// <param name="cnn">The connection to make the temporary table in</param> /// <param name="table">The table to write out</param> /// <param name="dest">The temporary table name to write to</param> private void DataTableToTable(SQLiteConnection cnn, DataTable table, string dest) { StringBuilder sql = new StringBuilder(); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(); using (SQLiteCommand cmd = cnn.CreateCommand()) using (DataTable source = new DataTable()) { sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest)); string separator = String.Empty; SQLiteConnectionFlags flags = cnn.Flags; foreach (DataColumn dc in table.Columns) { DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType); string typeName = SQLiteConvert.DbTypeToTypeName(cnn, dbtypeName, flags); sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator); separator = ", "; } sql.Append(")"); cmd.CommandText = sql.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest)); using (SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd)) { builder.DataAdapter = adp; adp.Fill(source); foreach (DataRow row in table.Rows) { object[] arr = row.ItemArray; source.Rows.Add(arr); } adp.Update(source); } } }