public static string BuildUniqueKeyWhereClause(object Entity, bool usePrimaryKey)
{
EntityDescriber describer = new EntityDescriber(Entity);
List <EntityProperty> keys = null;
if (usePrimaryKey)
{
keys = describer.PrimaryKeys();
}
else
{
keys = describer.UniqueKeys();
}
string whereClause = "";
int i = 0;
foreach (var ky in keys)
{
whereClause += (i > 0 ? " and " : $" where Tenant = @Tenant and ") + ky.Name + $" = @{ky.Name}";
i++;
}
return(whereClause);
}
public long UpdateEntity(object Entity)
{
EntityDescriber ed = new EntityDescriber(Entity);
if (ed.UniqueKeyProvidedOnEntity() == false && ed.PrimaryKeyProvidedOnEntity() == false)
{
throw new Exception("Neither a Primary Key nor UniqueKey was provided for the object.");
}
var Statement = DMLStatementFactory.GetDMLStatementForGenericEntity(_tenant, Entity, DMLStatemtType.Update, null);
var ret = (Int32)((IExecuteDML)_dbaccess).Execute(Statement);
SqlStatementExecuted?.Invoke(Statement.PreparedStatement, Statement.Variables);
return(ret);
}
/// <summary>
/// Inspects the properties of an an object looking for those that make up the unique identifier
/// and from this returns a list of variables usable in a prepared sql statement
/// with names and values for these
/// </summary>
/// <param name="Entity"></param>
/// <returns></returns>
public static List <ISQLDMLStatementVariable> GetUniqueKeyNameValuePairs(object Entity, bool includePrimaryKey, bool includeUniqueKey)
{
List <ISQLDMLStatementVariable> vars = new List <ISQLDMLStatementVariable>
{
new SQLDMLStatementVariable {
Name = "@Tenant", Value = "TR1"
}
};
EntityDescriber describer = new EntityDescriber(Entity);
List <EntityProperty> keys = new List <EntityProperty>();
if (includePrimaryKey)
{
foreach (var x in describer.PrimaryKeys())
{
keys.Add(x);
}
}
if (includeUniqueKey)
{
foreach (var x in describer.UniqueKeys())
{
keys.Add(x);
}
}
foreach (var key in keys)
{
vars.Add(new SQLDMLStatementVariable {
Name = "@" + key.Name, Value = key.Value
});
}
return(vars);
}
public bool DoesEntityWithSamePrimaryKeyExist(object Entity)
{
EntityDescriber ed = new EntityDescriber(Entity);
if (ed.PrimaryKeys().Count > 0)
{
var sql = DMLStatementFactory.BuildExistenceCheckSql(Entity, true);
List <ISQLDMLStatementVariable> vars = DMLStatementFactory.GetUniqueKeyNameValuePairs(Entity, true, false);
SQLDMLStatement dml = new SQLDMLStatement
{
PreparedStatement = sql,
StatemtType = DMLStatemtType.Select,
Variables = vars
};
var results = (DataTable)((IExecuteDML)_dbaccess).Execute(dml);
var count = Convert.ToInt32(results.Rows[0].ItemArray[0]);
return(count > 0);
}
else
{
return(false);
}
}
/// <summary>
/// Inspects the properties of an an object looking for those that make up the unique identifier
/// and from this returns a list of variables usable in a prepared sql statement
/// with names and values for these
/// </summary>
/// <param name="Entity"></param>
/// <returns></returns>
public static List <ISQLDMLStatementVariable> GetFilterNameValuePairs(object Entity)
{
List <ISQLDMLStatementVariable> vars = new List <ISQLDMLStatementVariable>
{
new SQLDMLStatementVariable {
Name = "@Tenant", Value = "TR1"
}
};
EntityDescriber describer = new EntityDescriber(Entity);
foreach (var property in describer.AllPropertyValues())
{
var value = property.Value;
if (value != null)
{
vars.Add(new SQLDMLStatementVariable {
Name = "@" + property.Name, Value = value
});
}
}
return(vars);
}
/// <summary>
/// Inspects the properties of an an object looking for those that make up the unique identifier
/// and from this and returns a list of variables usable in a prepared sql statement
/// with names and values for these
/// </summary>
/// <param name="Entity"></param>
/// <returns></returns>
public static string BuildTableCreateForEntitySql(object Entity)
{
EntityDescriber describer = new EntityDescriber(Entity);
string sql = $"create table {Entity.GetType().Name} (";
sql += "Tenant varchar(100), ";
var type = Entity.GetType();
string fieldListSql = "";
int i = 0;
var properties = describer.AllPropertyValues();
foreach (var property in properties)
{
fieldListSql += (i > 0 ? ", " : "") + property.Name + " " + MapPropertyTypeToDBType(property.Type.Name);
i++;
}
List <EntityProperty> primaryKeys = null;
primaryKeys = describer.PrimaryKeys();
int primKeyCount = 0;
string primaryKey = "PRIMARY KEY(";
primaryKey += "Tenant, ";
foreach (var pk in primaryKeys)
{
primaryKey += (primKeyCount > 0 ? ", " : "") + pk.Name;
primKeyCount++;
}
List <EntityProperty> uniqueKeys = null;
uniqueKeys = describer.UniqueKeys();
int uniqueKeyCount = 0;
string uniqueKey = $"CONSTRAINT UQ_{Entity.GetType().Name} UNIQUE(";
uniqueKey += "Tenant, ";
foreach (var uk in uniqueKeys)
{
uniqueKey += (uniqueKeyCount > 0 ? ", " : "") + uk.Name;
uniqueKeyCount++;
}
sql += fieldListSql;
if (primKeyCount > 0)
{
sql += ", " + primaryKey + ")";
}
if (uniqueKeyCount > 0)
{
sql += ", " + uniqueKey + ")";
}
sql += ")";
return(sql);
}
private static List <string> GetObjectPropertyNames(object Entity)
{
EntityDescriber describer = new EntityDescriber(Entity);
return(describer.AllPropertyValues().Select(x => x.Name).ToList <string>());
}
internal static SQLDMLStatement GetDMLStatementForGenericEntity(IDatabaseTenant Tenant, object Entity, DMLStatemtType dMLStatemtType, List <string> querProperytParametersToUse)
{
List <object> propValues = GetObjectPropertyValues(Entity);
List <string> propNames = GetObjectPropertyNames(Entity);
List <ISQLDMLStatementVariable> uniqueKeys = null;
if (dMLStatemtType == DMLStatemtType.SelectAll && querProperytParametersToUse == null)
{
uniqueKeys = new List <ISQLDMLStatementVariable>
{
new SQLDMLStatementVariable {
Name = "@Tenant", Value = "TR1"
}
};
}
else if (querProperytParametersToUse == null)
{
EntityDescriber ed = new EntityDescriber(Entity);
bool primaryKeyIdFieldValueSupplied = ed.PrimaryKeyProvidedOnEntity();
if (primaryKeyIdFieldValueSupplied && (dMLStatemtType == DMLStatemtType.Update || dMLStatemtType == DMLStatemtType.Delete))
{
uniqueKeys = GetUniqueKeyNameValuePairs(Entity, true, false);
}
else if (primaryKeyIdFieldValueSupplied == false && (dMLStatemtType == DMLStatemtType.Update || dMLStatemtType == DMLStatemtType.Delete))
{
uniqueKeys = GetUniqueKeyNameValuePairs(Entity, false, true);
}
else
{
uniqueKeys = GetUniqueKeyNameValuePairs(Entity, false, true);
}
}
else
{
uniqueKeys = GetFilterNameValuePairs(Entity);
}
List <ISQLDMLStatementVariable> uniqueKeysFiltered = new List <ISQLDMLStatementVariable>();
if (querProperytParametersToUse != null)
{
querProperytParametersToUse.Add("Tenant");
foreach (var key in uniqueKeys)
{
if (querProperytParametersToUse.Contains(key.Name.Replace("@", "")))
{
uniqueKeysFiltered.Add(key);
}
}
}
else
{
uniqueKeysFiltered = uniqueKeys;
}
SQLDMLScripts dmlScripts = new SQLDMLScripts();
return(dmlScripts.GetInsertScriptForTypedEntity(Tenant, Entity, propNames, propValues, dMLStatemtType, uniqueKeysFiltered));
}
