internal static string GetSelect(this Type type, string whereOrLimit = null)
{
var result = new StringBuilder();
result.AppendLine("SELECT");
var columns = new List <string>();
var properties = PropertyDiscovery.GetProperties(type);
foreach (var property in properties)
{
var columnName = AttributeDiscovery.GetColumnName(property);
columns.Add(columnName);
}
result.AppendLine(string.Join(",\r\n", columns));
result.AppendLine("FROM");
var tableName = AttributeDiscovery.GetTableName(type);
result.AppendLine(tableName);
if (!string.IsNullOrEmpty(whereOrLimit))
{
result.AppendLine(whereOrLimit);
}
return(result.ToString());
}
internal static string GetUpdate(this object instance)
{
var result = new StringBuilder();
var type = instance.GetType();
var tableName = AttributeDiscovery.GetTableName(type);
result.AppendLine($"UPDATE {tableName}");
result.AppendLine("SET");
var columns = new List <string>();
var properties = PropertyDiscovery.GetProperties(type);
var idProperty = PropertyDiscovery.GetProperty("Id", type);
foreach (var property in properties)
{
if (property.Name == idProperty.Name)
{
continue;
}
var columnName = AttributeDiscovery.GetColumnName(property);
var columnValue = ValueDiscovery.GetValue(instance, property);
columns.Add($"{columnName} = {columnValue}");
}
result.AppendLine(string.Join(",\r\n", columns));
result.AppendLine("WHERE");
result.AppendLine($"Id = {ValueDiscovery.GetValue(instance, idProperty)}");
return(result.ToString());
}
internal static string GetCreateTable(this Type type)
{
var result = new StringBuilder();
var tableName = AttributeDiscovery.GetTableName(type);
result.AppendLine($"CREATE TABLE IF NOT EXISTS {tableName}");
result.AppendLine("(");
var columns = new List <string>();
var properties = PropertyDiscovery.GetProperties(type);
var idProperty = PropertyDiscovery.GetProperty("Id", type);
if (idProperty != null)
{
if (idProperty.PropertyType != typeof(int) && idProperty.PropertyType != typeof(long))
{
throw new InvalidSqlLiteIdentityMappingException("Invalid type for 'Id' expected Int32 or Int64.");
}
columns.Add($"{idProperty.Name.ToUpper()} INTEGER PRIMARY KEY AUTOINCREMENT");
}
foreach (var property in properties)
{
if (property.Name == idProperty.Name)
{
continue;
}
var columnName = AttributeDiscovery.GetColumnName(property);
var columnType = AttributeDiscovery.GetColumnType(property);
var columnNullable = AttributeDiscovery.GetColumnNullable(property);
columns.Add($"{columnName} {columnType.ToString().ToUpper()} {columnNullable}");
}
result.AppendLine(string.Join(",\r\n", columns));
result.AppendLine(");");
result.AppendLine($"CREATE UNIQUE INDEX IF NOT EXISTS {tableName}_ID_INDEX ON {tableName} (ID);");
foreach (var property in properties)
{
if (property.Name == idProperty.Name)
{
continue;
}
var columnName = AttributeDiscovery.GetColumnName(property);
var indexName = AttributeDiscovery.GetIndexName(property);
var indexUnique = AttributeDiscovery.GetIndexUnique(property);
var additionalColumns = AttributeDiscovery.GetIndexAdditionalColumns(property);
if (!string.IsNullOrEmpty(additionalColumns))
{
columnName = string.Join(",", columnName, additionalColumns);
}
result.AppendLine($"CREATE {indexUnique} INDEX IF NOT EXISTS {tableName}_{indexName}_INDEX ON {tableName} ({columnName});");
}
return(result.ToString());
}
internal static List <T> TransformTo <T>(this QueryResponse response) where T : new()
{
var results = new List <T>();
var type = typeof(T);
var properties = PropertyDiscovery.GetProperties(type);
foreach (var row in response.Rows)
{
var result = new T();
foreach (var prop in response.Query.Properties)
{
var ordinal = response.Query.Properties.IndexOf(prop);
var property = properties.FirstOrDefault(x => x.Name.ToUpper() == prop.ToUpper());
var columnType = AttributeDiscovery.GetColumnType(property);
var columnName = AttributeDiscovery.GetColumnName(property);
if (columnName.ToUpper() == "ID")
{
ReadInteger(property, row[ordinal], result);
continue;
}
switch (columnType)
{
case SqlLiteType.Integer:
{
ReadInteger(property, row[ordinal], result);
break;
}
case SqlLiteType.Real:
{
ReadReal(property, row[ordinal], result);
break;
}
case SqlLiteType.Text:
{
ReadString(property, row[ordinal], result);
break;
}
case SqlLiteType.Blob:
{
throw new NotSupportedException("Cannot do blobs yet...");
}
}
}
results.Add(result);
}
return(results);
}
internal static string GetInsert(this object instance, bool withIdentity = false)
{
var result = new StringBuilder();
var type = instance.GetType();
var tableName = AttributeDiscovery.GetTableName(type);
result.AppendLine($"INSERT INTO {tableName}");
result.AppendLine("(");
var columns = new List <string>();
var properties = PropertyDiscovery.GetProperties(type);
var idProperty = PropertyDiscovery.GetProperty("Id", type);
foreach (var property in properties)
{
if (property.Name == idProperty.Name && !withIdentity)
{
continue;
}
var columnName = AttributeDiscovery.GetColumnName(property);
columns.Add(columnName);
}
result.AppendLine(string.Join(",\r\n", columns));
result.AppendLine(")");
result.AppendLine("VALUES");
result.AppendLine("(");
var values = new List <string>();
foreach (var property in properties)
{
if (property.Name == idProperty.Name && !withIdentity)
{
continue;
}
if (property.Name == idProperty.Name)
{
var value = (string)ValueDiscovery.GetValue(instance, property);
values.Add(value == "0" ? "NULL" : value.ToString());
}
else
{
var value = (string)ValueDiscovery.GetValue(instance, property);
values.Add(value.ToString());
}
}
result.AppendLine(string.Join(",\r\n", values));
result.AppendLine(");");
result.AppendLine("SELECT last_insert_rowid();");
return(result.ToString());
}
internal static SQLiteCommand GetInsertPrepared(this object instance, SQLiteConnection connection, bool withIdentity = false)
{
var command = new SQLiteCommand(connection);
var result = new StringBuilder();
var type = instance.GetType();
var tableName = AttributeDiscovery.GetTableName(type);
result.AppendLine($"INSERT INTO {tableName}");
result.AppendLine("(");
var columns = new List <string>();
var properties = PropertyDiscovery.GetProperties(type);
var idProperty = PropertyDiscovery.GetProperty("Id", type);
foreach (var property in properties)
{
if (property.Name == idProperty.Name && !withIdentity)
{
continue;
}
var columnName = AttributeDiscovery.GetColumnName(property);
columns.Add(columnName);
}
result.AppendLine(string.Join(",\r\n", columns));
result.AppendLine(")");
result.AppendLine("VALUES");
result.AppendLine("(");
var values = new List <string>();
foreach (var property in properties)
{
if (property.Name == idProperty.Name && !withIdentity)
{
continue;
}
values.Add("?");
var instanceValue = property.GetValue(instance);
var sqlParameter = new SQLiteParameter(instanceValue.GetType().ToDbType(), instanceValue);
command.Parameters.Add(sqlParameter);
}
result.AppendLine(string.Join(",\r\n", values));
result.AppendLine(");");
result.AppendLine("SELECT last_insert_rowid();");
command.CommandText = result.ToString();
return(command);
}
internal static List <string> GetColumns(this Type type)
{
var columns = new List <string>();
var properties = PropertyDiscovery.GetProperties(type);
foreach (var property in properties)
{
var columnName = AttributeDiscovery.GetColumnName(property);
columns.Add(columnName);
}
return(columns);
}
internal static string GetCount(this Type type)
{
var result = new StringBuilder();
result.AppendLine("SELECT COUNT(*)");
result.AppendLine("FROM");
var tableName = AttributeDiscovery.GetTableName(type);
result.AppendLine(tableName);
return(result.ToString());
}
internal static string GetDelete(this object instance)
{
var result = new StringBuilder();
var type = instance.GetType();
var idProperty = PropertyDiscovery.GetProperty("Id", type);
var tableName = AttributeDiscovery.GetTableName(type);
result.AppendLine($"DELETE FROM {tableName} WHERE Id = {ValueDiscovery.GetValue(instance, idProperty)}");
return(result.ToString());
}
internal static SQLiteCommand GetUpdatePrepared(this object instance, SQLiteConnection connection)
{
var command = new SQLiteCommand(connection);
var result = new StringBuilder();
var type = instance.GetType();
var tableName = AttributeDiscovery.GetTableName(type);
result.AppendLine($"UPDATE {tableName}");
result.AppendLine("SET");
var columns = new List <string>();
var properties = PropertyDiscovery.GetProperties(type);
var idProperty = PropertyDiscovery.GetProperty("Id", type);
foreach (var property in properties)
{
if (property.Name == idProperty.Name)
{
continue;
}
var columnName = AttributeDiscovery.GetColumnName(property);
columns.Add($"{columnName} = ?");
var columnValue = ValueDiscovery.GetValue(instance, property);
var sqlParameter = new SQLiteParameter(columnValue.GetType().ToDbType(), columnValue);
command.Parameters.Add(sqlParameter);
}
result.AppendLine(string.Join(",\r\n", columns));
result.AppendLine("WHERE");
var idValue = ValueDiscovery.GetValue(instance, idProperty);
result.AppendLine("Id = ?");
var idSqlParameter = new SQLiteParameter(idValue.GetType().ToDbType(), idValue);
command.Parameters.Add(idSqlParameter);
command.CommandText = result.ToString();
return(command);
}
internal static async Task <List <T> > QueryAsync <T>(this DbConnection dbConnection, string where = null) where T : new()
{
var type = typeof(T);
var results = new List <T>();
var properties = PropertyDiscovery.GetProperties(type);
var query = type.GetSelect(where);
using (var dbReader = await dbConnection.ExecuteQueryAsync(query))
{
var reader = dbReader.Reader;
while (reader.Read())
{
var result = new T();
foreach (var property in properties)
{
int ordinal;
var columnType = AttributeDiscovery.GetColumnType(property);
var columnName = AttributeDiscovery.GetColumnName(property);
if (columnName.ToUpper() == "ID")
{
ordinal = reader.GetOrdinal(columnName.ToUpper());
ReadInteger(property, reader, ordinal, result);
continue;
}
ordinal = reader.GetOrdinal(columnName);
switch (columnType)
{
case SqlLiteType.Integer:
{
ReadInteger(property, reader, ordinal, result);
break;
}
case SqlLiteType.Real:
{
ReadReal(property, reader, ordinal, result);
break;
}
case SqlLiteType.Text:
{
ReadString(reader, ordinal, property, result);
break;
}
case SqlLiteType.Blob:
{
throw new NotSupportedException("Cannot do blobs yet...");
}
}
}
results.Add(result);
}
}
return(results);
}
internal static string GetDropTable(this Type type)
{
var tableName = AttributeDiscovery.GetTableName(type);
return($"DROP TABLE IF EXISTS {tableName};");
}
