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