internal string GetForeignColumnName(TableModel primary, TableModel foreign)
{
using (var cmd = GetCommand(string.Format(GFCN_COMMAND, foreign.Name, primary.Name)))
{
return cmd.ExecuteScalar().ToString();
}
}
internal string GetPrimaryColumnName(TableModel table)
{
using (var cmd = GetCommand(string.Format("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME WHERE KCU.TABLE_NAME = '{0}' AND CONSTRAINT_TYPE = 'PRIMARY KEY'", table.Name)))
{
var result = cmd.ExecuteScalar();
if (result != null)
return result.ToString();
else
throw new Exception("All tables must have a primary key for this tool to work. Exception in table " + table.Name);
}
}
private string GetSQLColumns(TableModel table, string alias)
{
var columns = new List<string>();
Action<string> f = s => columns.Add(string.Format("{0}.[{1}]", alias, s));
foreach (var column in table.Columns.Union(table.ForeignKeys))
{
f(column.Name);
}
return string.Join(", ", columns);
}
internal List<ColumnModel> GetColumns(TableModel table)
{
var columns = new List<ColumnModel>();
using (var cmd = GetCommand("SELECT c.name AS ColumnName, t.name AS Type, c.is_nullable FROM sys.columns c JOIN sys.types t ON c.system_type_id = t.system_type_id WHERE object_id = " + table.object_id))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var column = new ColumnModel()
{
Name = reader["ColumnName"].ToString(),
Type = ConvertType(reader["Type"].ToString()),
Nullable = bool.Parse(reader["is_nullable"].ToString())
};
if(column.Type != "ignore")
{
columns.Add(column);
}
}
}
}
return columns;
}
private void AddUpdateMethod(TableModel table)
{
Add(string.Format("public virtual void Update({0} model)", table.ModelName));
Open();
var columns = table.Columns.Where(c=>!c.Equals(table.PrimaryKey));
Add(string.Format("var sparams = new SqlParameter[{0}];", columns.Count() + 1));
for(int i = 0; i < columns.Count(); i++)
{
if (columns.ElementAt(i).Nullable || columns.ElementAt(i).Type == "string")
{
Add(string.Format("sparams[{0}] = new SqlParameter(\"{1}\", model.{1} ?? (object)DBNull.Value);", i, columns.ElementAt(i).Name));
}
else
{
Add(string.Format("sparams[{0}] = new SqlParameter(\"{1}\", model.{1});", i, columns.ElementAt(i).Name));
}
}
Add(string.Format("sparams[{0}] = new SqlParameter(\"@{1}\", model.{1});", columns.Count(), table.PrimaryKey.Name));
Add(string.Format("ExecuteParamNonQuery(\"UPDATE {0} SET {2} WHERE [{3}] = @{3}\", sparams);", table.Name,
"",
BuildSetClause(table), table.PrimaryKey.Name));
Close();
}
private string BuildSetClause(TableModel table)
{
var names = from c in table.Columns
where !c.Equals(table.PrimaryKey)
select string.Format("[{0}] = @{0}", c.Name);
return string.Join(", ", names);
}
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 AddOneToManyMethods(AssociationModel association, TableModel table)
{
if (association.PrimaryTable.Equals(table))
{
AddPrimaryAssociationMethods(association);
}
else
{
AddForeignAssociationMethods(association);
}
}
private void AddGetMethods(TableModel table)
{
foreach (var column in table.Columns)
{
Add(string.Format("public virtual List<{0}> GetBy{1}({2} val)", table.ModelName, column.Name, column.Nullable && column.Type != "string" ? column.Type + "?" : column.Type));
Open();
Add(string.Format("IEnumerable<{0}> results;", table.ModelName));
if (column.Nullable)
{
Add("if(val == null)");
Open();
Add(string.Format("results = ExecuteQuery(\"SELECT {0} FROM {1} t WHERE t.{2} IS NULL\");", GetSQLColumns(table, "t"), table.Name, column.Name));
Close();
Add("else");
Open();
}
Add(string.Format("results = ExecuteParamQuery(\"SELECT {0} FROM {1} t WHERE t.{2} = @{2}\", new SqlParameter(\"@{2}\", val));", GetSQLColumns(table, "t"), table.Name, column.Name));
if (column.Nullable)
Close();
Add("return results.ToList();");
Close();
}
Add(string.Format("public virtual List<{0}> CustomGet(string where)", table.ModelName));
Open();
Add(string.Format("return ExecuteQuery(\"SELECT {0} FROM {1} t WHERE \" + where).ToList();", GetSQLColumns(table, "t"), table.Name));
Close();
Add(string.Format("public virtual IEnumerable<{0}> GetAll()", table.ModelName));
Open();
Add(string.Format("return ExecuteQuery(\"SELECT {0} FROM {1} p\");", GetSQLColumns(table, "p"), table.Name));
Close();
}
private void AddDeleteMethods(TableModel table, ColumnModel primaryKeyColumn)
{
Add(string.Format("public virtual void Delete({0} id)", primaryKeyColumn.Type));
Open();
Add(string.Format("ExecuteParamNonQuery(\"DELETE FROM {0} WHERE {1} = @{1}\", new SqlParameter(\"@{1}\", id));", table.Name, primaryKeyColumn.Name));
Close();
Add(string.Format("public virtual void Delete({0} model)", table.ModelName));
Open();
Add(string.Format("Delete(model.{0});", primaryKeyColumn.Name));
Close();
}
private void AddCreateMethods(TableModel table)
{
Add(string.Format("public virtual void Create({0} model)", table.ModelName));
Open();
Add(string.Format("string text = \"INSERT INTO {0} ({1}) VALUES({2})\";", table.Name,
string.Join(", ", table.Columns.Where(c => !c.Equals(table.PrimaryKey)).Select(c => string.Format("[{0}]", c.Name))),
string.Join(", ", table.Columns.Where(c => !c.Equals(table.PrimaryKey)).Select(c => string.Format("@{0}", c.Name)))));
Add(string.Format("ExecuteParamNonQuery(text, {0});", string.Join(", ", from c in table.Columns.Union(table.ForeignKeys)
where !c.Equals(table.PrimaryKey)
select c.Nullable || c.Type == "string" ?
string.Format("new SqlParameter(\"@{0}\", model.{0} ?? (object)DBNull.Value)", c.Name)
: string.Format("new SqlParameter(\"@{0}\", model.{0})", c.Name))));
Close();
}
private void AddConstructor(TableModel table, ColumnModel primaryKeyColumn)
{
Add(string.Format("internal {0}Repository(SqlConnection connection) : base(connection, new {0}Mapper())", table.ModelName));
Add("{}");
}
private void AddAssociationMethods(TableModel table)
{
foreach (var association in dbModel.Associations)
{
if (association.PrimaryTable.Equals(table) || association.ForeignTable.Equals(table))
{
if (association.Type.Equals(AssociationType.ManyToMany))
{
AddManyToManyMethods(table, association);
}
else
{
AddOneToManyMethods(association, table);
}
}
}
}