public string Bag(HasMany hasMany, ITextFormatter formatter)
{
var builder = new StringBuilder();
if (_language == Language.CSharp)
{
builder.AppendFormat(
"\t\t\tBag(x => x.{0}, colmap => {{ colmap.Key(x => x.Column(\"{1}\")); colmap.Inverse(true); }}, map => {{ map.OneToMany(); }});",
formatter.FormatPlural(hasMany.Reference),
hasMany.ReferenceColumn);
}
else if (_language == Language.VB)
{
builder.AppendFormat(
"\t\t\tBag(Function(x) x.{0}, Sub(colmap) colmap.Key(Function(x) x.Column(\"{1}\")), Sub(map) map.OneToMany())",
formatter.FormatPlural(hasMany.Reference),
hasMany.ReferenceColumn);
}
return builder.ToString();
}
public CodeSnippetStatement Create(HasMany hasMany)
{
var hasManySnippet = string.Format("HasMany(x => x.{0})", Formatter.FormatPlural(hasMany.Reference));
var keySnippet = hasMany.AllReferenceColumns.Count == 1 ?
string.Format(".KeyColumn(\"{0}\")", hasMany.ReferenceColumn) :
string.Format(".KeyColumns({0})", hasMany.AllReferenceColumns.Aggregate("new string[] { ", (a, b) => a + "\"" + b + "\", ", c => c.Substring(0, c.Length - 2) + " }"));
return new CodeSnippetStatement(string.Format(AbstractGenerator.TABS + "{0}{1};", hasManySnippet, keySnippet));
}
// http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
private IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new SqlConnection(connectionStr);
conn.Open();
try {
using (conn) {
using (var command = new SqlCommand()) {
command.Connection = conn;
command.CommandText =
String.Format(
@"
SELECT DISTINCT
PK_TABLE = b.TABLE_NAME,
FK_TABLE = c.TABLE_NAME,
FK_COLUMN_NAME = d.COLUMN_NAME,
CONSTRAINT_NAME = a.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA AND a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE d on a.CONSTRAINT_NAME = d.CONSTRAINT_NAME
WHERE b.TABLE_NAME = '{0}'
ORDER BY 1,2",
table.Name.Replace("'","''"));
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()) {
var constraintName = reader["CONSTRAINT_NAME"].ToString();
var fkColumnName = reader["FK_COLUMN_NAME"].ToString();
var pkTableName = reader["PK_TABLE"].ToString();
var existing = hasManyRelationships.FirstOrDefault(hm => hm.ConstraintName == constraintName);
if (existing == null) {
var newHasManyItem = new HasMany
{
ConstraintName = constraintName,
Reference = reader.GetString(1),
PKTableName = pkTableName
};
newHasManyItem.AllReferenceColumns.Add(fkColumnName);
hasManyRelationships.Add(newHasManyItem);
} else {
existing.AllReferenceColumns.Add(fkColumnName);
}
}
}
}
} finally {
conn.Close();
}
return hasManyRelationships;
}
private IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetForeignKeys(new[] { "%" });
for (var i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["PKTABLE_NAME"].ToString() == table.Name)
{
var newHasManyItem = new HasMany
{
Reference = dt.Rows[i]["FKTABLE_NAME"].ToString(),
ConstraintName = dt.Rows[i]["FK_NAME"].ToString(),
ReferenceColumn = dt.Rows[i]["FKCOLUMN_NAME"].ToString()
};
hasManyRelationships.Add(newHasManyItem);
}
}
}
}
finally
{
conn.Close();
}
return hasManyRelationships;
}
private IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new OleDbConnection(_connectionStr);
conn.Open();
try
{
using (conn)
{
using (var command = new OleDbCommand())
{
command.Connection = conn;
command.CommandText =
String.Format(
@"
SELECT pt.table_name PK_TABLE
, t.Table_name FK_TABLE
, fc.column_name FK_COLUMN_NAME
, fk.role CONSTRAINT_NAME
FROM
SYSFOREIGNKEY fk
INNER JOIN systable t ON t.table_id = fk.foreign_table_id
INNER JOIN sysfkcol fcol ON fcol.foreign_table_id = fk.foreign_table_id AND fcol.foreign_key_id = fk.foreign_key_id
INNER JOIN syscolumn fc ON fc.column_id = fcol.foreign_column_id AND fc.Table_id = fcol.foreign_table_id
INNER JOIN systable pt ON pt.table_id = fk.primary_table_id
WHERE pt.table_name = '{0}'
ORDER BY 1, 2",
table.Name);
var reader = command.ExecuteReader();
while (reader.Read())
{
var constraintName = reader["CONSTRAINT_NAME"].ToString();
var fkColumnName = reader["FK_COLUMN_NAME"].ToString();
var existing = hasManyRelationships.FirstOrDefault(hm => hm.ConstraintName == constraintName);
if (existing == null)
{
var newHasManyItem = new HasMany
{
ConstraintName = constraintName,
Reference = reader.GetString(1)
};
newHasManyItem.AllReferenceColumns.Add(fkColumnName);
hasManyRelationships.Add(newHasManyItem);
}
else
{
existing.AllReferenceColumns.Add(fkColumnName);
}
}
}
}
}
finally
{
conn.Close();
}
return hasManyRelationships;
}
public CodeSnippetStatement Create(HasMany hasMany)
{
var hasManySnippet = string.Format("HasMany(x => x.{0}).WithMany(x => x.{1})", Formatter.FormatPlural(hasMany.Reference), pascalCaseTextFormatter.FormatSingular(hasMany.PKTableName));
var keySnippet = string.Format(".Map(m => {{m.ToTable(\"{0}\"); m.MapLeftKey(\"{1}\"); m.MapRightKey(\"{2}\");}})", hasMany.Reference, hasMany.ReferenceColumn, hasMany.ReferenceColumn);
return new CodeSnippetStatement(string.Format(AbstractGenerator.TABS + "{0}{1};", hasManySnippet, keySnippet));
}