public string GenerateSelect(TableHierarchy tableHierarchy, string parentIdMap)
{
string fieldList = GenerateFieldList(tableHierarchy, Program.OrdinaryTemplate, false, true, "idMap");
string res = $"SELECT {fieldList} FROM {Program.QuoteName(tableHierarchy.TableName)}";
if (tableHierarchy.ForeignKey != "" && tableHierarchy.ForeignKey != null)
{
res += $" JOIN {parentIdMap} idMap ON idMap.{oldId}={Program.QuoteName(tableHierarchy.ForeignKey)}";
}
return(res);
}
public string GenerateSql(TableHierarchy tableHierarchy, string previousSql)
{
string sql = $"{previousSql}INSERT {tableHierarchy.TableName};";
if (tableHierarchy.childHierarchy != null)
{
foreach (var childHier in tableHierarchy.childHierarchy)
{
sql = GenerateSql(childHier, sql);
}
}
return(sql);
}
static void Main(string[] args)
{
TableHierarchy tableHier = new TableHierarchy
{
TableName = "Article",
PrimaryKey = "ArticleId",
//ForeignKey = "ArticleGroupId"
};
Program p = new Program();
Console.WriteLine(p.GetColumnTypeByName("ArticleGroup", "ArticleGroupId"));
string sql = p.GenerateSelect(tableHier, "#ArticleGroupMap");
Console.WriteLine(sql);
Console.WriteLine("===========FINISH============");
Console.ReadKey();
}
public string GenerateFieldList(TableHierarchy tableHierarchy, ApplyTemplateToColumn template, bool removePK = false, bool substituteFK = false, string parentIdMap = "", string alias = "")
{
string res = "";
bool needCheck = true;
string currentColumn;
Table table = db.Tables[tableHierarchy.TableName];
first = true;
string columnAlias;
foreach (Column col in table.Columns)
{
columnAlias = alias;
needCheck = true;
currentColumn = col.ToString();
if (needCheck && removePK)
{
if (col.ToString().Equals(Program.QuoteName(tableHierarchy.PrimaryKey)))
{
needCheck = false;
currentColumn = "";
}
}
if (needCheck && substituteFK)
{
if (col.ToString().Equals(Program.QuoteName(tableHierarchy.ForeignKey)))
{
needCheck = false;
columnAlias = "";
currentColumn = $"{parentIdMap}.{newId}";
}
}
if (currentColumn != "")
{
currentColumn = addColumnName(currentColumn, columnAlias, GetColumnType(col), template);
}
res += currentColumn;
}
return(res);
}