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