public static string ObjectToQuery <T>(this T obj, string type)
{
DatabaseAttribute dynamicType = typeof(T).GetCustomAttributes(typeof(DatabaseAttribute), true).FirstOrDefault() as DatabaseAttribute;
string tableName = dynamicType.GetValue();
const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance;
var objectProperties = typeof(T).GetProperties(flags).Where(y => y.PropertyType.Namespace == "System").Select(y => { return(y.Name != "Id" ? (y.GetCustomAttributes(typeof(DatabaseAttribute), true).FirstOrDefault() as DatabaseAttribute).GetValue() : null); }).Where(x => x != null).ToList();
var objectPropertyId = obj.GetType().GetProperties(flags).Where(y => y.PropertyType.Namespace == "System").Select(y => { return(y.Name == "Id" ? y.GetValue(obj) : null); }).Where(x => x != null).FirstOrDefault();
List <string> objectPropertyValues = new List <string>();
objectProperties.ToList().ForEach(p => {
var value = obj
.GetType()
.GetProperties(flags)
.Where(y => y.PropertyType.Namespace == "System")
.Select(y => {
return((y.GetCustomAttributes(typeof(DatabaseAttribute), true).FirstOrDefault() as DatabaseAttribute).GetValue() == p.ToString() ?
(y.PropertyType == typeof(DateTime) || y.PropertyType == typeof(DateTime?) ?
"convert(datetime,'" + Convert.ToDateTime(y.GetValue(obj)).ToString("MM/dd/yyyy hh:mm:ss tt") + "')" :
y.PropertyType == typeof(Byte[]) ? "" + y.GetValue(obj) + "" : "'" + y.GetValue(obj) + "'") :
null);
})
.Where(x => x != null)
.FirstOrDefault();
if (value.ToString() == "convert(datetime,'01/01/0001 12:00:00 AM')")
{
value = "''";
}
objectPropertyValues.Add(value.ToString());
});
List <string> columnNames = objectProperties.ToList();
StringBuilder sCommand = new StringBuilder();
List <string> Rows = new List <string>();
switch (type)
{
case "insert":
sCommand = new StringBuilder("INSERT INTO " + tableName + "(" + String.Join(", ", columnNames.ToArray()) + ") VALUES ");
Rows.Add(string.Format("({0})", String.Join(",", objectPropertyValues.ToArray())));
sCommand.Append(string.Join(", ", Rows));
sCommand.Append("; ");
break;
case "update":
string columnsAndParams = String.Join(", ", columnNames.Select(x => x + " = ###" + x + "###").ToArray());
int index = 0;
foreach (string val in objectPropertyValues)
{
string columnName = columnNames[index];
columnsAndParams = columnsAndParams.Replace("###" + columnName + "###", val);
index++;
}
sCommand = new StringBuilder("UPDATE " + tableName + " SET " + columnsAndParams);
sCommand.Append(" where ID = " + objectPropertyId);
sCommand.Append(";");
break;
}
return(sCommand.ToString());
}
private void InnerColumns(PropertyInfo[] properties, string shortName, StringBuilder retorno)
{
int c = properties.Length;
for (int i = 0; i < c; i++)
{
DatabaseAttribute field = properties[i].GetCustomAttribute <DatabaseAttribute>();
string fieldName = properties[i].Name;
if (field is not null)
{
if (!field.Ignore)
{
if (field.Inner != InnerDirection.NONE)
{
//add columns from the property model
InnerColumns(properties[i], retorno, field, shortName);
fieldName = string.Empty;
}
else
{
if (drualcman.Helpers.ObjectHelpers.IsGenericList(properties[i].PropertyType.FullName))
{
fieldName = string.Empty;
}
else
{
if (string.IsNullOrEmpty(field.Name))
{
fieldName = properties[i].Name;
}
else
{
fieldName = field.Name;
}
}
}
}
else
{
fieldName = string.Empty;
}
}
if (!string.IsNullOrEmpty(fieldName))
{
retorno.Append($" {shortName}.[{fieldName}] [{shortName}.{fieldName}],");
}
}
}
public static IServiceCollection AddDbContextWithReflection(this IServiceCollection services, IConfiguration configuration)
{
List <Type> databaseTypes = ReflectionExtensions.GetTypesWithHelpAttribute(typeof(DatabaseAttribute));
foreach (var databaseType in databaseTypes)
{
DatabaseAttribute databaseAttribute = databaseType.GetTypeInfo().GetCustomAttribute <DatabaseAttribute>();
var method = typeof(ServiceCollectionExtensions).GetRuntimeMethods().Where(m => m.Name == "AddDbCustom").SingleOrDefault();
method = method.MakeGenericMethod(databaseType);
object[] parameters = new object[] { services, configuration, databaseAttribute.ConnectionString };
method.Invoke(null, parameters);
}
return(services);
}
/// <summary>
/// Get all the columns properties need from the query used
/// </summary>
/// <param name="columns"></param>
/// <param name="model"></param>
/// <returns></returns>
public IEnumerable <Columns> HaveColumns(Type model, string shortName)
{
PropertyInfo[] properties = model.GetProperties(BindingFlags.Public | BindingFlags.Instance);
List <Columns> result = new List <Columns>();
bool isDirectQuery = Columns[0].IndexOf(".") < 0;
int c = properties.Length;
for (int propertyIndex = 0; propertyIndex < c; propertyIndex++)
{
string columnName;
DatabaseAttribute field = properties[propertyIndex].GetCustomAttribute <DatabaseAttribute>();
bool notIgnored;
if (field is not null)
{
notIgnored = !field.Ignore;
}
else
{
notIgnored = true;
}
if (notIgnored)
{
if (properties[propertyIndex].PropertyType.IsClass &&
!properties[propertyIndex].PropertyType.IsArray &&
properties[propertyIndex].PropertyType != typeof(string))
{
result.AddRange(HaveColumns(properties[propertyIndex].PropertyType, shortName));
}
else
{
if (isDirectQuery)
{
columnName = properties[propertyIndex].Name.ToLower();
}
else
{
columnName = $"{shortName}.{properties[propertyIndex].Name}".ToLower();
}
if (Columns.Contains(columnName))
{
result.Add(SetColumn(properties[propertyIndex], shortName, columnName));
}
}
}
}
return(result);
}
public void AddTableNames <TModel>()
{
tableNamesBK = new List <TableName>();
PropertyInfo[] properties = typeof(TModel).GetProperties(BindingFlags.Public | BindingFlags.Instance);
string tableName;
DatabaseAttribute table = typeof(TModel).GetCustomAttribute <DatabaseAttribute>();
if (table is not null)
{
if (string.IsNullOrEmpty(table.Name))
{
tableName = typeof(TModel).Name;
}
else
{
tableName = table.Name;
}
}
else
{
tableName = typeof(TModel).Name;
}
int tableCount = 0;
string shortName = $"t{tableCount}";
TableName newTable = new TableName(tableName, shortName, string.Empty, InnerDirection.NONE, string.Empty, string.Empty, typeof(TModel).Name);
tableNamesBK.Add(newTable);
int c = properties.Length;
for (int i = 0; i < c; i++)
{
DatabaseAttribute field = properties[i].GetCustomAttribute <DatabaseAttribute>();
if (field is not null)
{
if (!field.Ignore)
{
if (field.Inner != InnerDirection.NONE)
{
//add columns from the property model
AddTable(properties[i], field, ref tableCount, shortName);
}
}
}
}
}
private void InnerColumns(PropertyInfo column, StringBuilder retorno,
DatabaseAttribute origin, string shortReference)
{
Type t = column.PropertyType;
PropertyInfo[] properties;
DatabaseAttribute table;
if (drualcman.Helpers.ObjectHelpers.IsGenericList(column.PropertyType.FullName))
{
properties = column.PropertyType.GetGenericArguments()[0].GetProperties();
table = column.PropertyType.GetGenericArguments()[0].GetCustomAttribute <DatabaseAttribute>();
}
else
{
properties = t.GetProperties(BindingFlags.Public | BindingFlags.Instance);
table = t.GetCustomAttribute <DatabaseAttribute>();
}
TableName father = tableNamesBK.Find(r => r.Name == table.Name);
InnerColumns(properties, father?.ShortName, retorno);
}
private void AddTable(PropertyInfo column, DatabaseAttribute origin,
ref int tableCount, string shortReference)
{
Type t = column.PropertyType;
string tableName;
string shortName;
PropertyInfo[] properties;
DatabaseAttribute table;
if (drualcman.Helpers.ObjectHelpers.IsGenericList(column.PropertyType.FullName))
{
properties = column.PropertyType.GetGenericArguments()[0].GetProperties();
table = column.PropertyType.GetGenericArguments()[0].GetCustomAttribute <DatabaseAttribute>();
if (table is not null && !string.IsNullOrEmpty(table.Name))
{
tableName = table.Name;
}
else
{
tableName = column.PropertyType.GetGenericArguments()[0].Name;
}
}
private Columns SetColumn(PropertyInfo property, string shortName, string columnName)
{
string propertyType;
if (property.PropertyType.Name == typeof(bool).Name)
{
propertyType = "bool";
}
else if (property.PropertyType.Name == typeof(int).Name)
{
propertyType = "int";
}
else if (property.PropertyType.Name == typeof(long).Name)
{
propertyType = "long";
}
else if (property.PropertyType.Name == typeof(double).Name)
{
propertyType = "double";
}
else if (property.PropertyType.Name == typeof(decimal).Name)
{
propertyType = "decimal";
}
else if (property.PropertyType.Name == typeof(float).Name)
{
propertyType = "float";
}
else if (property.PropertyType.Name == typeof(short).Name)
{
propertyType = "short";
}
else if (property.PropertyType.Name == typeof(byte).Name)
{
propertyType = "byte";
}
else if (property.PropertyType.Name == typeof(DateTime).Name)
{
propertyType = "date";
}
else if (property.PropertyType.Name == typeof(Nullable).Name)
{
propertyType = "nullable";
}
else if (property.PropertyType.Name == typeof(Nullable <>).Name)
{
propertyType = "nullable";
}
else
{
propertyType = "text";
}
TableName table = Tables.Where(t => t.ShortName == shortName).FirstOrDefault();
DatabaseAttribute options = property.GetCustomAttribute <DatabaseAttribute>();
return(new Columns
{
Column = property,
Options = options,
TableShortName = shortName,
ColumnName = columnName,
PropertyType = propertyType,
TableIndex = Array.IndexOf(Tables.ToArray(), table)
});
}
/// <summary>
/// Get the select from the properties name about the model send
/// </summary>
/// <typeparam name="TModel"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public string SetQuery <TModel>()
{
PropertyInfo[] properties = typeof(TModel).GetProperties(BindingFlags.Public | BindingFlags.Instance);
TableNamesHelper tableNames = new TableNamesHelper();
tableNames.AddTableNames <TModel>();
this.tableNamesBK = new List <TableName>(tableNames.TableNames);
string shortName = tableNamesBK[0].ShortName;
StringBuilder retorno = new StringBuilder("SELECT ");
int c = properties.Length;
for (int i = 0; i < c; i++)
{
DatabaseAttribute field = properties[i].GetCustomAttribute <DatabaseAttribute>();
string fieldName;
if (field is not null)
{
if (!field.Ignore)
{
if (field.Inner != InnerDirection.NONE)
{
//add columns from the property model
InnerColumns(properties[i], retorno, field, shortName);
fieldName = string.Empty;
}
else
{
if (string.IsNullOrEmpty(field.Name))
{
fieldName = properties[i].Name;
}
else
{
fieldName = field.Name;
}
}
}
else
{
fieldName = string.Empty;
}
}
else
{
fieldName = properties[i].Name;
}
if (!string.IsNullOrEmpty(fieldName))
{
retorno.Append($" {tableNamesBK[0].ShortName}.[{fieldName}] [{tableNamesBK[0].ShortName}.{fieldName}],");
}
}
retorno.Remove(retorno.Length - 1, 1);
retorno.Append(Environment.NewLine);
retorno.Append($"FROM [{tableNamesBK[0].Name}] {tableNamesBK[0].ShortName} ");
if (tableNamesBK.Count() > 1)
{
//add inner joins depending of the model database attributes
int tc = tableNamesBK.Count();
for (int i = 1; i < tc; i++)
{
retorno.Append(Environment.NewLine);
retorno.Append($"\t{tableNamesBK[i].Inner} JOIN [{tableNamesBK[i].Name}] {tableNamesBK[i].ShortName} on {tableNamesBK[i].ShortReference}.{(string.IsNullOrEmpty(tableNamesBK[i].InnerIndex) ? string.IsNullOrEmpty(tableNamesBK[i].Column) ? $"{tableNamesBK[i].Name}Id" : tableNamesBK[i].Column : tableNamesBK[i].InnerIndex)} = {tableNamesBK[i].ShortName}.{(string.IsNullOrEmpty(tableNamesBK[i].Column) ? $"{tableNamesBK[i].Name}Id" : tableNamesBK[i].Column)}");
}
}
retorno.Append(Environment.NewLine);
if (this.WhereRequired is not null)
{
bool foundSome = false;
retorno.Append($"WHERE ");
for (int i = 0; i < c; i++)
{
DatabaseAttribute field = properties[i].GetCustomAttribute <DatabaseAttribute>();
string fieldName = properties[i].Name;
if (field is not null)
{
if (!string.IsNullOrEmpty(field.Name))
{
fieldName = field.Name;
}
if (field.IndexKey)
{
if (string.IsNullOrEmpty(field.IndexedName) && this.WhereRequired.ContainsKey(fieldName))
{
foundSome = true;
retorno.Append($" {tableNamesBK[0].ShortName}.[{fieldName}] = {this.GetWhereValue(fieldName)} ");
retorno.Append("AND");
}
else
{
if (this.WhereRequired.ContainsKey(field.IndexedName))
{
foundSome = true;
retorno.Append($" {tableNamesBK[0].ShortName}.[{fieldName}] = {this.GetWhereValue(field.IndexedName)} ");
retorno.Append("AND");
}
}
}
}
else
{
if (this.WhereRequired.ContainsKey(fieldName))
{
foundSome = true;
retorno.Append($" {tableNamesBK[0].ShortName}.[{fieldName}] = {this.GetWhereValue(fieldName)} ");
retorno.Append("AND");
}
}
}
if (foundSome)
{
retorno.Remove(retorno.Length - 3, 3);
}
else
{
retorno.Remove(retorno.Length - 7, 7);
}
}
return(retorno.ToString());
}
