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 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 <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 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); }
public static SqlCondition NotEquals(string fieldName, Object value) { SqlCondition SqlCondition = new SqlCondition("!=", fieldName, value); return(SqlCondition); }
public static SqlCondition LowerThan(string fieldName, Object value) { SqlCondition SqlCondition = new SqlCondition("<", fieldName, value); return(SqlCondition); }