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 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 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);
}