private List <Object> SelectFromAssociationTable(string tableName, string primaryKeyName, Object primaryKey)
        {
            List <Object> selectedObjects          = new List <Object> {
            };
            List <SqlCondition> listOfSqlCondition = new List <SqlCondition> {
                SqlCondition.Equals(primaryKeyName, primaryKey)
            };
            string        selectQuery = _queryBuilder.CreateSelectQuery(tableName, listOfSqlCondition);
            SqlDataReader dataReader  = _msSqlConnection.ExecuteObjectSelect(selectQuery);

            while (dataReader.Read())
            {
                IDataRecord record = (IDataRecord)dataReader;

                if (record.GetName(0) == primaryKeyName)
                {
                    selectedObjects.Add(record[1]);
                }
                else
                {
                    selectedObjects.Add(record[0]);
                }
            }

            dataReader.Close();

            return(selectedObjects);
        }
        public void Delete(Object obj)
        {
            int    primaryKey;
            string tableName = _dataMapper.GetTableName(obj.GetType());

            if (_msSqlConnection.CheckIfTableExists(tableName))
            {
                // concrete table inheritance, class table inheritance or normal insert on single class
                primaryKey = FindPrimaryKey(obj);
                DeletePrimaryKey(obj);
            }
            // single table inheritance
            else
            {
                Type rootHierarchyType = _tableInheritance.GetMainType(obj.GetType());
                tableName  = _dataMapper.GetTableName(rootHierarchyType);
                primaryKey = FindPrimaryKey(obj, true);
                DeletePrimaryKey(obj, true);
            }

            List <SqlCondition> listOfCriteria = new List <SqlCondition> {
                SqlCondition.Equals("id", primaryKey)
            };
            string query = _queryBuilder.CreateDeleteQuery(tableName, listOfCriteria);

            PerformQuery(query);
        }
        private List <int> GetForeignKeysFromTable(string childTableName, string parentTableName, int primaryKey)
        {
            List <int> foreignKeys = new List <int>();

            if (!_msSqlConnection.CheckIfTableExists(childTableName))
            {
                return(foreignKeys);
            }

            List <SqlCondition> foreignKeyConditions = new List <SqlCondition> {
                SqlCondition.Equals(parentTableName + "_id", primaryKey)
            };
            string        foreignKeyQuery  = _queryBuilder.CreateSelectQuery(childTableName, foreignKeyConditions);
            SqlDataReader foreignKeyReader = _msSqlConnection.ExecuteObjectSelect(foreignKeyQuery);

            while (foreignKeyReader.Read())
            {
                IDataRecord record = (IDataRecord)foreignKeyReader;
                Dictionary <string, Object> columnsAndValues = _dataMapper.CreateDictionaryFromRecord(record);
                foreignKeys.Add((int)columnsAndValues["id"]);
            }

            foreignKeyReader.Close();

            return(foreignKeys);
        }
        public Object Select(Type type, int id)
        {
            List <SqlCondition> listOfSqlCondition = new List <SqlCondition> {
                SqlCondition.Equals("id", id)
            };

            _msSqlConnection.ConnectAndOpen();
            Object result = SelectWithOpenConnection(type, listOfSqlCondition, id);

            _msSqlConnection.Dispose();

            return(result);
        }
        private int GetForeignKeyFromTable(string tableName, string childTableName, int primaryKey)
        {
            List <SqlCondition> foreignKeyConditions = new List <SqlCondition> {
                SqlCondition.Equals("id", primaryKey)
            };
            string        foreignKeyQuery  = _queryBuilder.CreateSelectQuery(tableName, foreignKeyConditions);
            SqlDataReader foreignKeyReader = _msSqlConnection.ExecuteObjectSelect(foreignKeyQuery);

            foreignKeyReader.Read();

            IDataRecord record = (IDataRecord)foreignKeyReader;
            Dictionary <string, Object> columnsAndValues = _dataMapper.CreateDictionaryFromRecord(record);

            foreignKeyReader.Close();

            if ((columnsAndValues[childTableName + "_id"]).GetType() == typeof(System.DBNull))
            {
                return(-1);
            }

            return((int)columnsAndValues[childTableName + "_id"]);
        }
        public void Update(Object instance)
        {
            int primaryKey;
            List <Tuple <string, Object> > valuesToSet;
            string tableName = _dataMapper.GetTableName(instance.GetType());

            if (!_msSqlConnection.CheckIfTableExists(tableName))
            {
                primaryKey  = FindPrimaryKey(instance, true);
                valuesToSet = _dataMapper.GetColumnsAndValues(instance, true);
            }
            else
            {
                primaryKey  = FindPrimaryKey(instance);
                valuesToSet = _dataMapper.GetColumnsAndValues(instance);
            }

            List <SqlCondition> updateConditions = new List <SqlCondition> {
                SqlCondition.Equals("id", primaryKey)
            };

            Update(instance.GetType(), valuesToSet, updateConditions);
        }
        public void Insert(Object obj, Tuple <string, object> parentKey = null)
        {
            if (obj != null)
            {
                string tableName = _dataMapper.GetTableName(obj.GetType());
                List <Tuple <string, object> > columnsAndValuesList;
                int primaryKey;

                if (_msSqlConnection.CheckIfTableExists(tableName))
                {
                    // concrete table inheritance

                    if ((_msSqlConnection.GetColumnNamesFromTable(tableName)).Count - 1 == (DataMapper.GetTypeAllProperties(obj.GetType())).Length)
                    {
                        columnsAndValuesList = _dataMapper.GetColumnsAndValues(obj, true);
                        primaryKey           = FindMinimumAvailableID(obj.GetType());
                        columnsAndValuesList.Add(new Tuple <string, Object>("id", primaryKey));
                        AddPrimaryKey(obj);
                    }
                    // class table inheritance or normal insert on single class
                    else
                    {
                        columnsAndValuesList = _dataMapper.GetColumnsAndValues(obj);
                        primaryKey           = FindMinimumAvailableID(obj.GetType());
                        columnsAndValuesList.Add(new Tuple <string, Object>("id", primaryKey));
                        AddPrimaryKey(obj);
                    }
                }
                // single table inheritance
                else
                {
                    Type rootHierarchyType = _tableInheritance.GetMainType(obj.GetType());
                    tableName            = _dataMapper.GetTableName(rootHierarchyType);
                    columnsAndValuesList = _dataMapper.GetColumnsAndValues(obj, true);
                    primaryKey           = FindMinimumAvailableID(obj.GetType(), true);
                    columnsAndValuesList.Add(new Tuple <string, Object>("id", primaryKey));
                    AddPrimaryKey(obj, true);
                }

                // relationships lists
                List <Relationship> oneToOne   = _relationshipFinder.FindOneToOne(obj.GetType());
                List <Relationship> oneToMany  = _relationshipFinder.FindOneToMany(obj.GetType());
                List <Relationship> manyToMany = _relationshipFinder.FindManyToMany(obj.GetType());

                string insertQuery;

                if (parentKey != null)
                {
                    columnsAndValuesList.Add(parentKey);
                    insertQuery = _queryBuilder.CreateInsertQuery(tableName, columnsAndValuesList);
                }
                else
                {
                    insertQuery = _queryBuilder.CreateInsertQuery(tableName, columnsAndValuesList);
                }

                PerformQuery(insertQuery);

                if (oneToOne.Count != 0)
                {
                    foreach (var relation in oneToOne)
                    {
                        PropertyInfo propertyObj = relation._secondMember;
                        MethodInfo   getter      = propertyObj.GetGetMethod(nonPublic: true);
                        Object       child       = getter.Invoke(obj, null);
                        if (child != null)
                        {
                            int    childPrimaryKey = FindMinimumAvailableID(child.GetType());
                            string childTableName  = _dataMapper.GetTableName(child.GetType());

                            List <Tuple <string, object> > valuesToSet = new List <Tuple <string, object> >
                            {
                                new Tuple <string, object>(childTableName + "_id", childPrimaryKey)
                            };

                            List <SqlCondition> updateConditions = new List <SqlCondition> {
                                SqlCondition.Equals("id", primaryKey)
                            };

                            Insert(child);
                            Update(obj.GetType(), valuesToSet, updateConditions);
                        }
                    }
                }

                if (oneToMany.Count != 0)
                {
                    foreach (var relation in oneToMany)
                    {
                        PropertyInfo propertyObj = relation._secondMember;
                        MethodInfo   getter      = propertyObj.GetGetMethod(nonPublic: true);
                        Object       child       = getter.Invoke(obj, null);
                        IList        childList   = child as IList;

                        foreach (var item in childList)
                        {
                            Tuple <string, object> parentKeyTuple = new Tuple <string, object>(tableName + "_id", primaryKey);
                            Insert(item, parentKeyTuple);
                        }
                    }
                }

                if (manyToMany.Count != 0)
                {
                    foreach (var relation in manyToMany)
                    {
                        PropertyInfo propertyObj = relation._secondMember;
                        MethodInfo   getter      = propertyObj.GetGetMethod(nonPublic: true);
                        Object       child       = getter.Invoke(obj, null);
                        IList        childList   = child as IList;

                        foreach (var item in childList)
                        {
                            int    secondMemberKey = FindMinimumAvailableID(item.GetType());
                            string childTableName  = _dataMapper.GetTableName(item.GetType());

                            Insert(item);

                            Tuple <string, object> oneTableKey    = new Tuple <string, object>(tableName + "_id", primaryKey);
                            Tuple <string, object> secondTableKey = new Tuple <string, object>(childTableName + "_id", secondMemberKey);
                            string associationTableName           = GetMergedNames((string)tableName, (string)childTableName);

                            _msSqlConnection.ConnectAndOpen();
                            List <Object> valuesFromAssociationTable = SelectFromAssociationTable(associationTableName, childTableName + "_id", secondMemberKey);;
                            _msSqlConnection.Dispose();

                            if (!valuesFromAssociationTable.Contains(primaryKey))
                            {
                                List <Tuple <string, object> > keysAndValues = new List <Tuple <string, object> > {
                                    oneTableKey, secondTableKey
                                };

                                string intoAssocTableInsertQuery = _queryBuilder.CreateInsertQuery(associationTableName, keysAndValues);

                                PerformQuery(intoAssocTableInsertQuery);
                            }
                        }
                    }
                }
            }
        }
        private Object SelectWithOpenConnection(Type type, List <SqlCondition> listOfSqlCondition, int primaryKey)
        {
            Object selectedObject;
            string tableName = _dataMapper.GetTableName(type);

            if (!_msSqlConnection.CheckIfTableExists(tableName))
            {
                Type rootHierarchyType = _tableInheritance.GetMainType(type);
                tableName = _dataMapper.GetTableName(rootHierarchyType);

                if (!_msSqlConnection.CheckIfTableExists(tableName))
                {
                    Console.WriteLine("Handled exception");
                    Console.WriteLine("This table is not present in database!");
                    Console.WriteLine("If single table inheritance is used then the root hierarchy table is not present!\n");

                    return(null);
                }
            }

            if (primaryKey < 0)
            {
                return(null);
            }

            string        selectQuery = _queryBuilder.CreateSelectQuery(tableName, listOfSqlCondition);
            SqlDataReader dataReader  = _msSqlConnection.ExecuteObjectSelect(selectQuery);

            if (!dataReader.HasRows)
            {
                return(null);
            }

            dataReader.Read();

            IDataRecord record = (IDataRecord)dataReader;

            Object[] parameters = FetchDataFromRecord(type, record);
            selectedObject = Activator.CreateInstance(type, parameters);

            dataReader.Close();

            List <Relationship> oneToOne   = _relationshipFinder.FindOneToOne(type);
            List <Relationship> oneToMany  = _relationshipFinder.FindOneToMany(type);
            List <Relationship> manyToMany = _relationshipFinder.FindManyToMany(type);

            foreach (var relationship in oneToOne)
            {
                PropertyInfo property       = relationship._secondMember;
                Type         childType      = property.PropertyType;
                string       childTableName = _dataMapper.GetTableName(childType);
                int          foreignKey     = -1;

                if (_msSqlConnection.CheckIfTableExists(childTableName))
                {
                    foreignKey = GetForeignKeyFromTable(tableName, childTableName, primaryKey);
                }

                List <SqlCondition> condition = new List <SqlCondition> {
                    SqlCondition.Equals("id", foreignKey)
                };
                Object child = SelectWithOpenConnection(childType, condition, foreignKey);

                property.SetValue(selectedObject, child, null);
            }

            foreach (var relationship in oneToMany)
            {
                PropertyInfo  property       = relationship._secondMember;
                Type          childType      = property.PropertyType.GetGenericArguments()[0];
                string        childTableName = _dataMapper.GetTableName(childType);
                List <Object> children       = new List <Object>();
                List <int>    foreignKeys    = GetForeignKeysFromTable(childTableName, tableName, primaryKey);

                foreach (int fk in foreignKeys)
                {
                    List <SqlCondition> childCondition = new List <SqlCondition> {
                        SqlCondition.Equals("id", fk)
                    };
                    children.Add(SelectWithOpenConnection(childType, childCondition, fk));
                }

                IList childTmp = children as IList;
                IList list     = Activator.CreateInstance(property.PropertyType) as IList;

                foreach (var it in childTmp)
                {
                    list.Add(it);
                }

                property.SetValue(selectedObject, list, null);
            }

            foreach (var relationship in manyToMany)
            {
                PropertyInfo property       = relationship._secondMember;
                Type         childType      = property.PropertyType.GetGenericArguments()[0];
                string       childTableName = _dataMapper.GetTableName(childType);

                string associationTable = GetMergedNames(tableName, childTableName);

                if (!_msSqlConnection.CheckIfTableExists(associationTable))
                {
                    continue;
                }

                List <Object> childPrimaryKeys = SelectFromAssociationTable(associationTable, tableName + "_id", primaryKey);
                List <Object> children         = new List <object>();

                foreach (var childPK in childPrimaryKeys)
                {
                    List <SqlCondition> condition = new List <SqlCondition> {
                        SqlCondition.Equals("id", childPK)
                    };
                    Object child = SelectWithOpenConnection(childType, condition, (int)childPK);
                    children.Add(child);
                }

                IList childTmp = children as IList;
                IList list     = Activator.CreateInstance(property.PropertyType) as IList;

                foreach (var it in childTmp)
                {
                    list.Add(it);
                }

                property.SetValue(selectedObject, list, null);
            }

            return(selectedObject);
        }