private List<AssociationModel> BuildAssociations(DAL dal)
{
var associations = new List<AssociationModel>();
foreach (var rawAssociation in dal.GetRawAssociations())
{
var association = new AssociationModel();
association.PrimaryTable = db.Tables.Single(t => t.Name.Equals(rawAssociation.Item2));
association.ForeignTable = db.Tables.Single(t => t.Name.Equals(rawAssociation.Item1));
association.PrimaryColumn = association.PrimaryTable.Columns.Single(c => c.Name.EqualsIgnoreCase(dal.GetPrimaryColumnName(association)));
association.ForeignColumn = association.ForeignTable.Columns.Single(c => c.Name.EqualsIgnoreCase(dal.GetForeignColumnName(association)));
association.ForeignTable.ForeignKeys.Add(association.ForeignColumn);
association.Type = AssociationType.OneToMany;
associations.Add(association);
}
return associations;
}
private void SetManyToMany(List<AssociationModel> associations)
{
var possibleJoinTables = db.Tables.Where(t => t.Columns.Count < 3 ||
(t.Columns.Count < 4 && t.Columns.Any(c => c.Name.EqualsIgnoreCase("id"))));
foreach (var table in possibleJoinTables)
{
var associationsInQuestion = associations.Where(a => a.ForeignTable.Equals(table)).ToList();
if (associationsInQuestion.Count() > 1)
{
if (Program.CallResponse(string.Format("Is {0} a join table that should not appear in the model? (y/n)", table.Name)).EqualsIgnoreCase("y"))
{
var newAssociation = new AssociationModel() { Type = AssociationType.ManyToMany };
newAssociation.PrimaryTable = associationsInQuestion[0].PrimaryTable.Equals(table) ? associationsInQuestion[0].ForeignTable : associationsInQuestion[0].PrimaryTable;
newAssociation.ForeignTable = associationsInQuestion[1].PrimaryTable.Equals(table) ? associationsInQuestion[1].ForeignTable : associationsInQuestion[1].PrimaryTable;
table.JoinOnly = true;
using (var dal = new DAL(connectionString))
{
newAssociation.ForeignColumn = table.Columns.Single(c => c.Name.EqualsIgnoreCase(dal.GetForeignColumnName(newAssociation.ForeignTable, table)));
newAssociation.PrimaryColumn = table.Columns.Single(c => c.Name.EqualsIgnoreCase(dal.GetForeignColumnName(newAssociation.PrimaryTable, table)));
}
newAssociation.JoinTable = table;
associationsInQuestion.ForEach(aiq => associations.Remove(aiq));
associations.Add(newAssociation);
}
}
}
}
internal string GetPrimaryColumnName(AssociationModel model)
{
using(var cmd = GetCommand(string.Format(GPCN_COMMAND, model.ForeignTable.Name, model.PrimaryTable.Name)))
{
return cmd.ExecuteScalar().ToString();
}
}
private void AddPrimaryAssociationMethods(AssociationModel association)
{
Add(string.Format("public virtual {0} GetBy{1}({1} model)", association.PrimaryTable.ModelName, association.ForeignTable.ModelName));
Open();
Add(string.Format("return ExecuteParamQuery(\"SELECT {0} FROM {1} p JOIN {2} f ON p.{3} = f.{4} WHERE f.{5} = @{5}\", new SqlParameter(\"@{5}\", model.{5})).SingleOrDefault();",
GetSQLColumns(association.PrimaryTable, "p"), association.PrimaryTable.Name, association.ForeignTable.Name, association.PrimaryColumn.Name,
association.ForeignColumn.Name, association.ForeignTable.PrimaryKey.Name));
Close();
}
private void AddOneToManyMethods(AssociationModel association, TableModel table)
{
if (association.PrimaryTable.Equals(table))
{
AddPrimaryAssociationMethods(association);
}
else
{
AddForeignAssociationMethods(association);
}
}
private void AddManyToManyMethods(TableModel table, AssociationModel association)
{
//other table in the relationship
TableModel other = association.PrimaryTable.Equals(table) ? association.ForeignTable : association.PrimaryTable;
Add(string.Format("public virtual List<{1}> GetBy{0}({0} model)", other.ModelName, table.ModelName));
Open();
Add(string.Format("return ExecuteParamQuery(\"SELECT {0} FROM {1} t JOIN {2} j ON t.{3} = j.{4} WHERE j.{5} = @{6}\", new SqlParameter(\"@{6}\", model.{6})).ToList();",
GetSQLColumns(table, "t"), //0
table.Name, //1
association.JoinTable.Name, //2
table.PrimaryKey.Name, //3
other.Equals(association.PrimaryTable) ? association.ForeignColumn.Name : association.PrimaryColumn.Name, //4
new DAL(dbModel.ConnectionString).GetForeignColumnName(other, association.JoinTable), //5
other.Equals(association.PrimaryTable) ? association.PrimaryTable.PrimaryKey.Name : association.ForeignTable.PrimaryKey.Name)); //6
Close();
Add(string.Format("public virtual void AddRelationship({0} primary, {1} foreign)", table.ModelName, other.ModelName));
Open();
Add(string.Format("ExecuteParamNonQuery(\"INSERT INTO {0} ({1}) VALUES (@Foreign, @Primary)\", new SqlParameter(\"@Foreign\", foreign.{2}), new SqlParameter(\"@Primary\", primary.{3}));", association.JoinTable.Name,
string.Join(", ", "[" + (association.PrimaryTable.Equals(table) ? association.ForeignColumn.Name : association.PrimaryColumn.Name) + "]",
"[" + (association.PrimaryTable.Equals(table) ? association.PrimaryColumn.Name : association.ForeignColumn.Name) + "]"),
association.ForeignTable.PrimaryKey.Name, association.PrimaryTable.PrimaryKey.Name));
Close();
Add(string.Format("public virtual void RemoveRelationship({0} primary, {1} foreign)", table.ModelName, other.ModelName));
Open();
Add(string.Format("ExecuteParamNonQuery(\"DELETE FROM {0} j WHERE j.{1} = @Primary AND j.{3} = @Foreign\", new SqlParameter(\"@Primary\", primary.{2}), new SqlParameter(\"@Foreign\", foreign.{4}));",
association.JoinTable.Name, association.PrimaryColumn.Name, association.PrimaryTable.PrimaryKey.Name,
association.ForeignColumn.Name, association.ForeignTable.PrimaryKey.Name));
Close();
}
private void AddForeignAssociationMethods(AssociationModel association)
{
Add(string.Format("public virtual {0} GetBy{1}({1} model)",
association.Type.Equals(AssociationType.OneToOne) ? association.ForeignTable.ModelName : string.Format("List<{0}>", association.ForeignTable.ModelName),
association.PrimaryTable.ModelName));
Open();
Add(string.Format("return ExecuteParamQuery(\"SELECT {0} FROM {1} p WHERE {2} = @{2}\", new SqlParameter(\"@{2}\", model.{3})).{4}();",
GetSQLColumns(association.ForeignTable, "p"), association.ForeignTable.Name, association.ForeignColumn.Name, association.PrimaryColumn.Name,
association.Type.Equals(AssociationType.OneToOne) ? "SingleOrDefault" : "ToList"));
Close();
Add(string.Format("public virtual void SetRelationship({0} primary, {1} foreign)", association.PrimaryTable.ModelName, association.ForeignTable.ModelName));
Open();
Add(string.Format("ExecuteParamNonQuery(\"UPDATE {0} SET {1} = @{2} WHERE {3} = @{3}\", new SqlParameter(\"@{2}\", primary.{2}), new SqlParameter(\"@{3}\", foreign.{3}));",
association.ForeignTable.Name, association.ForeignColumn.Name, association.PrimaryColumn.Name, association.ForeignTable.PrimaryKey.Name));
Close();
}