/// <summary>
///Inserts two foreign key refernces into a mapping table
/// </summary>
public virtual void InsertManyToMany <T>(IDwarf obj1, IDwarf obj2, string alternateTableName = null)
{
if (!obj1.IsSaved)
{
obj1.Save();
}
if (!obj2.IsSaved)
{
obj2.Save();
}
var type1 = obj1.GetType();
var type2 = obj2.GetType();
var tableName = ManyToManyAttribute.GetManyToManyTableName(type1, type2, alternateTableName);
DbContextHelper <T> .ClearCacheForType(type1);
DbContextHelper <T> .ClearCacheForType(type2);
var preReq = string.Format("IF NOT EXISTS (SELECT * FROM [{0}] WHERE {1}Id = {2} AND {3}Id = {4}) \r\n",
tableName, type1.Name, ValueToSqlString(obj1.Id), type2.Name, ValueToSqlString(obj2.Id));
var query = new QueryBuilder <T>()
.InsertInto(tableName)
.Values(type1.Name + "Id", obj1.Id)
.Values(type2.Name + "Id", obj2.Id)
.ToQuery();
ExecuteNonQuery <T>(preReq + query);
}
/// <summary>
///Inserts two foreign key refernces into a mapping tables
/// </summary>
public virtual List <T> SelectManyToMany <T>(IDwarf owner, string alternateTableName = null) where T : Dwarf <T>, new()
{
if (owner == null || !owner.IsSaved)
{
return(new List <T>());
}
var targetType = typeof(T);
var ownerType = owner.GetType();
var tableName = ManyToManyAttribute.GetManyToManyTableName(targetType, ownerType, alternateTableName);
var command = new QueryBuilderLight().Select <T>()
.From <T>()
.InnerJoin("[" + tableName + "] ON [" + tableName + "].[" + targetType.Name + "Id] = [" + targetType.Name + "].[Id]")
.Where("[" + tableName + "].[" + ownerType.Name + "Id]", owner.Id, ownerType).ToQuery();
if (!typeof(T).Implements <ICacheless>())
{
var cache = CacheManager.GetCacheList <T>(command);
if (cache != null)
{
return(cache);
}
}
var sdr = ExecuteReader <T>(command);
var result = new List <T>();
try
{
while (sdr.Read())
{
result.Add(TupleToObject <T>(sdr));
}
}
catch (Exception e)
{
DwarfContext <T> .GetConfiguration().ErrorLogService.Logg(e);
throw new DatabaseOperationException(e.Message, e);
}
finally
{
sdr.Close();
}
return(typeof(T).Implements <ICacheless>() ? result : CacheManager.SetCacheList(command, result));
}
/// <summary>
///Delete two foreign key refernces from the mapping table
/// </summary>
public virtual void DeleteManyToMany <T>(IDwarf obj1, IDwarf obj2, string alternateTableName = null)
{
var type1 = obj1.GetType();
var type2 = obj2.GetType();
var tableName = ManyToManyAttribute.GetManyToManyTableName(type1, type2, alternateTableName);
DbContextHelper <T> .ClearCacheForType(type1);
DbContextHelper <T> .ClearCacheForType(type2);
var query = new QueryBuilder()
.DeleteFrom("dbo.[" + tableName + "]")
.Where("[" + type1.Name + "Id] = " + ValueToSqlString(obj1.Id))
.Where("[" + type2.Name + "Id] = " + ValueToSqlString(obj2.Id));
ExecuteNonQuery <T>(query);
}