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