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