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); }
/// <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 DoesEntityWithSameUniqueKeyExist(object Entity) { EntityDescriber ed = new EntityDescriber(Entity); if (ed.UniqueKeys().Count > 0) { var sql = DMLStatementFactory.BuildExistenceCheckSql(Entity, false); List <ISQLDMLStatementVariable> vars = DMLStatementFactory.GetUniqueKeyNameValuePairs(Entity, false, true); 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 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); }