public static string GenerateUpdate <TEntity>(ReflectionEntityCache typeCache, TEntity obj, IList <object> parameters, IEnumerable <ReflectionPropertyCache> properties, string where = null)
{
StringBuilder setValues = new StringBuilder();
where = where ?? $" WHERE {typeCache.PrimaryKey.Name}=@{parameters.Count()}";
if (properties == null || !properties.Any())
{
properties = typeCache.Properties;
}
parameters.Add(typeCache.PrimaryKey.GetValue(obj));
foreach (ReflectionPropertyCache property in properties)
{
object propertyVal = property.GetValue(obj);
if (propertyVal != null && !property.IsPrimaryKey)
{
if (property.IsForeignObj)
{
ReflectionEntityCache fk = ReflectionEntityCache.GetOrCreateEntity(property.Type);
propertyVal = fk.PrimaryKey.GetValue(propertyVal);
if (propertyVal.Equals(fk.PrimaryKey.Type.GetDefaultValue()))
{
setValues.Append($"{fk.Name}{fk.PrimaryKey.Name}=@{parameters.Count}, ");
parameters.Add(propertyVal);
}
}
else
{
setValues.Append($"{property.Name}=@{parameters.Count}, ");
parameters.Add(propertyVal);
}
}
}
return($"UPDATE {typeCache.Name} SET {setValues.SubstringLast()} {where};");
}
public static TEntity MapToEntity <TEntity>(IDataRecord dataRecord, Type type)
where TEntity : class, new()
{
if (type == null)
{
type = typeof(TEntity);
}
TEntity obj = new TEntity();
ReflectionEntityCache typeCache = ReflectionEntityCache.GetOrCreateEntity(type);
foreach (ReflectionPropertyCache property in typeCache.Properties)
{
object value = (property.IsForeignObj) ?
typeof(Mapper)
.GetMethod(nameof(Mapper.MapToEntity))
.MakeGenericMethod(property.Type)
.Invoke(null, new object[] { dataRecord, property.Type }) :
ConvertFromType(dataRecord[$"{type.Name}_{property.Name}"], property.Type);
property.SetValue(obj, value);
}
return(obj);
}
public static string GenerateCreate(ReflectionEntityCache typeCache)
{
StringBuilder beforeCreate = new StringBuilder();
StringBuilder query = new StringBuilder();
foreach (ReflectionPropertyCache property in typeCache.Properties)
{
StringBuilder column = new StringBuilder(property.Name);
if (property.IsPrimaryKey)
{
column.Append($" {property.Type.ToStringDb()} PRIMARY KEY NOT NULL IDENTITY(1,1), ");
}
else if (property.IsForeignObj)
{
ReflectionEntityCache fk = ReflectionEntityCache.GetOrCreateEntity(property.Type);
beforeCreate.AppendLine(GenerateCreate(fk));
column.Append($"{fk.PrimaryKey.Name} {fk.PrimaryKey.Type.ToStringDb()} FOREIGN KEY REFERENCES {property.Name}({typeCache.PrimaryKey.Name}) ON DELETE CASCADE, ");
}
else
{
column.Append($" {property.Type.ToStringDb()}, ");
}
query.AppendLine();
query.Append($"\t\t{column.ToString()}");
}
return
($@"{beforeCreate}
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{typeCache.Name}')
BEGIN
CREATE TABLE {typeCache.Name} ({query.SubstringLast()})
END;");
}
public static void GenericAction <TEntity>(ReflectionEntityCache typeCache, IList <TEntity> objs, GenerateScriptsEnum method, IEnumerable <ReflectionPropertyCache> properties = null)
{
List <object> parameters = new List <object>();
StringBuilder query = new StringBuilder();
foreach (TEntity obj in objs)
{
switch (method)
{
case GenerateScriptsEnum.GenerateInsertOrUpdate:
query.AppendLine(GenerateInsertOrUpdate(typeCache, obj, parameters, properties));
break;
case GenerateScriptsEnum.GenerateInsert:
query.AppendLine(GenerateInsert(typeCache, obj, parameters));
break;
}
}
List <string> columns = ExecuterManager.GetInstance().ExecuteQuerySimple <string>(query.ToString(), parameters.ToArray()).ToList();
for (int i = 0; i < objs.Count; i++)
{
typeCache.PrimaryKey.SetValue(objs[i], columns[i].To(typeCache.PrimaryKey.Type));
}
}
public SqlEntityHandler()
{
_executer = ExecuterManager.GetInstance();
_reflectionCache = ReflectionEntityCache.GetOrCreateEntity <TEntity>();
_expressionFunctions = new SqlExpressionFunctions();
_query_delete = QueryBuilder.GenerateDelete(_reflectionCache.Name, _reflectionCache.PrimaryKey.Name);
Init();
}
public static string GenerateSelect(ReflectionEntityCache typeCache, string fieldName, string where = null, int cont = 0)
{
where = where ?? $"WHERE {typeCache.Name.ToLower()}.{fieldName}=@{cont}";
StringBuilder query = new StringBuilder("SELECT ");
string joins = GenerateSelectFields(typeCache, query);
return
($@"{query.SubstringLast()}
FROM {typeCache.Name}
{joins}
{where};");
}
public static string GenerateTypeMigrations(ReflectionEntityCache typeCache, IEnumerable <InformationSchemaColumns> columns)
{
StringBuilder query = new StringBuilder();
foreach (InformationSchemaColumns column in columns)
{
ReflectionPropertyCache property = typeCache.GetProperty(column.Name);
string valType = property.Type.ToStringDb();
if (!column.TypeString.Equals(Regex.Replace(valType, @"(\(.*\))", string.Empty)))
{
query.AppendLine($"ALTER TABLE {typeCache.Name} ALTER COLUMN {property.Name} {valType};");
}
}
return(query.ToString());
}
public static string GenerateInsertOrUpdate <TEntity>(ReflectionEntityCache typeCache, TEntity obj, List <object> parameters, IEnumerable <ReflectionPropertyCache> properties)
{
StringBuilder where = new StringBuilder(" WHERE ");
if (properties != null && properties.Any())
{
StringBuilder whereLine = new StringBuilder();
foreach (ReflectionPropertyCache property in properties)
{
object propValue = property.GetValue(obj);
whereLine.Append(property.Name);
if (propValue == null)
{
whereLine.Append(" IS NULL");
}
else
{
whereLine.Append($" = @{parameters.Count()}");
parameters.Add(propValue);
}
whereLine.Append(" AND ");
}
where.Append(whereLine.SubstringLast(5));
}
else
{
where.Append($"{typeCache.PrimaryKey.Name} = @{parameters.Count}");
parameters.Add(typeCache.PrimaryKey.GetValue(obj));
}
string whereStr = where.ToString();
string select = GenerateGenericSimple(typeCache.Name, $"SELECT {typeCache.PrimaryKey.Name}", whereStr);
return
($@"IF EXISTS ({select.SubstringLast(1)})
BEGIN
{GenerateUpdate(typeCache, obj, parameters, properties, whereStr)}
{select}
END
ELSE
BEGIN
{GenerateInsert(typeCache, obj, parameters)}
END;");
}
private static string GenerateSelectFields(ReflectionEntityCache typeCache, StringBuilder query)
{
StringBuilder joins = new StringBuilder();
foreach (ReflectionPropertyCache property in typeCache.Properties)
{
if (property.IsForeignObj)
{
ReflectionEntityCache fk = ReflectionEntityCache.GetOrCreateEntity(property.Type);
joins.AppendLine($"INNER JOIN {fk.Name} {fk.Name.ToLower()} ON {typeCache.Name.ToLower()}.{property.Name}{fk.PrimaryKey.Name} = {fk.Name.ToLower()}.{fk.PrimaryKey.Name}");
joins.Append(GenerateSelectFields(fk, query));
}
else
{
query.Append($"{typeCache.Name.ToLower()}.{property.Name} AS {typeCache.Name}_{property.Name}, ");
}
}
return(joins.ToString());
}
public static string GenerateColumnMigrations(ReflectionEntityCache typeCache, IEnumerable <InformationSchemaColumns> columns)
{
List <string> dbColumns = columns.Select(x => x.Name).ToList();
StringBuilder query = new StringBuilder();
//ADD COLUMNS
foreach (ReflectionPropertyCache property in typeCache.Properties)
{
string propertyName = property.Name;
ReflectionEntityCache fk = ReflectionEntityCache.GetOrCreateEntity(property.Type);
if (property.IsForeignObj)
{
propertyName = $"{propertyName}{fk.PrimaryKey.Name}";
}
if (!dbColumns.Contains(propertyName))
{
string valType = property.Type.ToStringDb();
query.AppendLine($"ALTER TABLE {typeCache.Name} ADD {propertyName} {valType};");
if (property.IsForeignObj)
{
query.AppendLine($"ALTER TABLE {typeCache.Name} ADD FOREIGN KEY({propertyName}) REFERENCES {fk.Name}({fk.PrimaryKey.Name});");
}
}
}
//DROP COLUMNS
foreach (string column in dbColumns)
{
if (typeCache.GetProperty(column) == null)
{
query.AppendLine($"ALTER TABLE {typeCache.Name} DROP COLUMN {column};");
}
}
return(query.ToString());
}
public static string GenerateInsert(ReflectionEntityCache typeCache, object obj, List <object> parameters)
{
StringBuilder values = new StringBuilder(),
columns = new StringBuilder();
foreach (ReflectionPropertyCache property in typeCache.Properties)
{
if (!property.IsPrimaryKey)
{
object propertyVal = property.GetValue(obj);
string propertyName = property.Name;
if (propertyVal != null && property.IsForeignObj)
{
ReflectionEntityCache fk = ReflectionEntityCache.GetOrCreateEntity(property.Type);
object fkPkValue = fk.PrimaryKey.GetValue(propertyVal);
if (fkPkValue.Equals(fk.PrimaryKey.Type.GetDefaultValue()))
{
List <object> parametersFK = new List <object>();
string insertFK = GenerateInsert(fk, propertyVal, parametersFK);
fkPkValue = ExecuterManager.GetInstance().ExecuteQuery(insertFK, parametersFK.ToArray(), x => Mapper.ConvertFromType(x[0], fk.PrimaryKey.Type)).FirstOrDefault();
fk.PrimaryKey.SetValue(propertyVal, fkPkValue);
}
propertyVal = fkPkValue;
propertyName = $"{property.Name}{fk.PrimaryKey.Name}";
}
columns.Append($"{propertyName}, ");
parameters.Add(propertyVal ?? string.Empty);
values.Append($"@{parameters.Count - 1}, ");
}
}
return
($@"INSERT INTO {typeCache.Name} ({columns.SubstringLast()}) VALUES ({values.SubstringLast()});
SELECT IDENT_CURRENT('{typeCache.Name}');");
}
