public void InsertRecord <T>(MappedTable table, T record, SQLiteConflictResolution conflictResolution = SQLiteConflictResolution.Abort, Type recordType = null) { InsertRecords(table, new T[1] { record }, conflictResolution, recordType); }
public static string GetInsertStatement(SQLiteMappedColumnSet columnSet, string tableName, SQLiteConflictResolution conflictResolution) { StringBuilder builder = new StringBuilder(); builder.Append("INSERT"); if (conflictResolution != SQLiteConflictResolution.Abort) { builder.Append(" OR "); builder.Append(conflictResolution.ToString().ToUpperInvariant()); } builder.Append(" INTO " + GetEscaped(tableName) + " ("); string paramList = ""; bool needsComma = false; foreach (MappedColumn column in columnSet.Columns) { if (needsComma) { builder.Append(","); paramList += ","; } needsComma = true; builder.AppendLine(); builder.Append(" " + GetEscaped(column.Name)); paramList += "?"; } builder.AppendLine(); builder.AppendLine(") VALUES ("); builder.AppendLine(" " + paramList); builder.AppendLine(");"); return(builder.ToString()); }
public static void InsertRecord <T>(this MappedTable <T> table, T record, SQLiteConflictResolution conflictResolution = SQLiteConflictResolution.Abort) { table.Context.Mapper.InsertRecord(table, record, conflictResolution); }
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(); } } }); }