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