Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQLite DatabaseExtension
public static class DatabaseExtension
{
private static readonly Dictionary<Type, List<PropertyInfo>> _properties = new Dictionary<Type, List<PropertyInfo>>();
private static readonly Dictionary<Type, string> _tableNames = new Dictionary<Type, string>();
private static readonly object _toLockTableName = new object();
private static readonly object _toLockTypeProperties = new object();
public static async Task UpsertAsync<T>(this DbContext dbContext, T item, CancellationToken cancellationToken)
where T : class
{
var props = GetProperties<T>();
var sbQuery = new StringBuilder(3000);
var sqliteParameters = new List<SqliteParameter>();
var tableName = GetTableName<T>(dbContext);
sbQuery.AppendLine($"REPLACE INTO {tableName}( ");
for (int i = 0; i < props.Count; i++)
{
sbQuery.Append($"[{props[i].Name}] ");
if (i == props.Count - 1)
{
sbQuery.AppendLine($")");
}
else
{
sbQuery.Append($", ");
}
}
sbQuery.AppendLine($"VALUES ( ");
for (int i = 0; i < props.Count; i++)
{
var parameterName = $"@param{i}";
object parameterValue = props[i].GetValue(item);
if (parameterValue == null)
{
parameterValue = DBNull.Value;
}
sqliteParameters.Add(new SqliteParameter(parameterName, parameterValue));
sbQuery.Append(parameterName);
if (i == props.Count - 1)
{
sbQuery.AppendLine($")");
}
else
{
sbQuery.Append($", ");
}
}
using (var command = dbContext.Database.GetDbConnection().CreateCommand())
{
foreach (var parameter in sqliteParameters)
{
command.Parameters.Add(parameter);
}
await dbContext.Database.GetDbConnection().OpenAsync(cancellationToken);
command.CommandText = sbQuery.ToString();
var query = sbQuery.ToString();
Debug.WriteLine(query);
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
}
}
private static List<PropertyInfo> GetProperties<T>()
{
var type = typeof(T);
List<PropertyInfo> props = null;
lock (_toLockTypeProperties)
{
if (_properties.ContainsKey(type))
{
props = _properties[type];
}
else
{
props = type.GetProperties().Where(p => p.CanRead && p.CanWrite && !p.GetCustomAttributes<NotMappedAttribute>().Any()).ToList();
_properties[type] = props;
}
}
return props;
}
public static string GetTableName<T>(this DbContext dbContext)
where T : class
{
lock (_toLockTableName)
{
var type = typeof(T);
if (_tableNames.ContainsKey(type))
{
return _tableNames[type];
}
var model = dbContext.Model;
var entityTypes = model.GetEntityTypes();
var entityType = entityTypes.First(t => t.ClrType == type);
var tableNameAnnotation = entityType.GetAnnotation("Relational:TableName");
var tableName = tableNameAnnotation.Value.ToString();
_tableNames[type] = tableName;
return tableName;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.