public bool DeleteRecordUsingPrimaryKey <T>(MappedTable <T> table, T recordToDelete) { var recordType = typeof(T); var columnSet = SQLiteMapperHelpers.CreateColumnSetForType(recordType); var descriptors = SQLiteMapperHelpers.GetPropertyDescriptors(recordType, columnSet); var primaryKey = columnSet.PrimaryKeyColumn; if (primaryKey == null) { throw new InvalidOperationException("Cannot delete the record because there is no primary key"); } string sqlQuery = string.Format( "DELETE FROM [{0}] WHERE [{1}] = ?", table.TableName, primaryKey.Name); int primaryKeyIndex = columnSet.Columns.IndexOf(columnSet.PrimaryKeyColumn); object primaryKeyValue = descriptors[primaryKeyIndex].GetValue(recordToDelete); return(ExecuteNonQuery(sqlQuery, primaryKeyValue) != 0); }
private IEnumerable <T> QueryCore <T>(string sqlQuery, Type recordType) { if (recordType == null) { recordType = typeof(T); } var columnSet = SQLiteMapperHelpers.CreateColumnSetForType(recordType); var descriptors = SQLiteMapperHelpers.GetPropertyDescriptors(recordType, columnSet); using (SQLiteCommand command = connection.CreateCommand()) { command.CommandText = sqlQuery; using (var reader = command.ExecuteReader()) { Dictionary <string, int> fieldNameToIndex = new Dictionary <string, int>(reader.FieldCount, StringComparer.OrdinalIgnoreCase); for (int i = 0; i < reader.FieldCount; ++i) { string fieldName = reader.GetName(i); if (fieldNameToIndex.ContainsKey(fieldName)) { fieldNameToIndex[fieldName] = -1; // ambiguous } else { fieldNameToIndex[fieldName] = i; } } List <int> fieldIndexes = new List <int>(columnSet.Columns.Count); foreach (var column in columnSet.Columns) { int fieldIndex = 0; if (fieldNameToIndex.TryGetValue(column.Name, out fieldIndex)) { if (fieldIndex < 0) { throw new ArgumentException("The SQL result contains more than one column named " + column.Name); } } else { throw new ArgumentException("The SQL result does not contain a matching column for " + column.Name); } fieldIndexes.Add(fieldIndex); } while (reader.Read()) { object record = Activator.CreateInstance(recordType); for (int i = 0; i < columnSet.Columns.Count; ++i) { var fieldIndex = fieldIndexes[i]; var descriptor = descriptors[i]; object sqlValue = reader.GetValue(fieldIndex); try { object obj = SQLiteMapperHelpers.ConvertSqlToObject(descriptor.PropertyType, sqlValue); descriptor.SetValue(record, obj); } catch (Exception ex) { throw new Exception("Unable to assign " + columnSet.Columns[i].Name + ": " + ex.Message, ex); } } yield return((T)record); } } } }
public void InsertRecords <T>(MappedTable table, IEnumerable <T> records, SQLiteConflictResolution conflictResolution = SQLiteConflictResolution.Abort, Type recordType = null) { if (recordType == null) { recordType = typeof(T); } var columnSet = SQLiteMapperHelpers.CreateColumnSetForType(recordType); var descriptors = SQLiteMapperHelpers.GetPropertyDescriptors(recordType, columnSet); WrapDatabaseExceptions(() => { using (SQLiteCommand command = connection.CreateCommand()) using (SQLiteCommand fixReplaceCommand = connection.CreateCommand()) { command.CommandText = SQLiteMapperHelpers.GetInsertStatement(columnSet, table.TableName, conflictResolution); foreach (var column in columnSet.Columns) { var parameter = command.CreateParameter(); parameter.Direction = ParameterDirection.Input; command.Parameters.Add(parameter); } command.Prepare(); // SQLite implements "INSERT OR REPLACE" as a DELETE followed by an INSERT, which can cause // trouble when our TrackChanges trigger tries to compute MAX(ChangeNumber). The workaround // is to make sure an accurate ChangeNumber is in the C# record before we start // the INSERT OR REPLACE. (Normally this is already true, but sometimes it's 0 or a // little outdated so we do a SELECT to be sure.) bool fixReplace = table.TrackChanges && conflictResolution == SQLiteConflictResolution.Replace; SQLiteParameter fixReplaceParameter = null; PropertyDescriptor fixReplacePrimaryKeyDescriptor = null; if (fixReplace) { if (columnSet.PrimaryKeyColumn == null) { throw new InvalidOperationException( "SQLiteConflictResolution.Replace cannot be used without including the primary key"); } int primaryKeyIndex = columnSet.Columns.IndexOf(columnSet.PrimaryKeyColumn); fixReplacePrimaryKeyDescriptor = descriptors[primaryKeyIndex]; fixReplaceCommand.CommandText = string.Format( "SELECT [{0}] FROM [{1}] WHERE [{2}] = ?", SQLiteMapper.ChangeNumberColumnName, table.TableName, columnSet.PrimaryKeyColumn.Name); fixReplaceParameter = fixReplaceCommand.CreateParameter(); fixReplaceParameter.Direction = ParameterDirection.Input; fixReplaceCommand.Parameters.Add(fixReplaceParameter); } using (var transaction = this.BeginTransaction()) { foreach (var record in records) { if (fixReplace) { var castedRecord = record as IMappedRecordWithChangeTracking; if (castedRecord == null) { throw new InvalidOperationException( "SQLiteConflictResolution.Replace cannot be used without including the ChangeNumber column"); } fixReplaceParameter.Value = fixReplacePrimaryKeyDescriptor.GetValue(record); // NOTE: If the SELECT returns no rows, Convert.ToInt64() will convert NULL to 0 castedRecord.ChangeNumber = Convert.ToInt64(fixReplaceCommand.ExecuteScalar()); } for (int i = 0; i < columnSet.Columns.Count; ++i) { var column = columnSet.Columns[i]; var parameter = command.Parameters[i]; var descriptor = descriptors[i]; object obj = descriptor.GetValue(record); parameter.Value = SQLiteMapperHelpers.ConvertObjectToSql(descriptor.PropertyType, obj); } int recordsModified = command.ExecuteNonQuery(); if (SQLiteMapper.DefaultLogLevel >= SQLiteMapperLogLevel.Verbose) { Debug.WriteLine("Inserted " + recordsModified + " rows"); } } transaction.Commit(); } } }); }