private string GetAll()
{
string col = "";
string r = "";
string where = "";
string join = "";
//columns select
foreach (var item in Table.lstColumns)
{
string s = string.IsNullOrWhiteSpace(col) ? "" : ",";
col += $@"
{s}[{NameTable}].[{item.Name}]";
}
//columns join
foreach (var item in Table.lstFK)
{
var tblJoin = new TableObject(item.NameTableJoin, Connection);
string sTbl = Setting.GetNameTable(tblJoin.Name) + "Join";
foreach (var co in tblJoin.lstColumns)
{
if (co.IsPK)
{
continue;
}
col += $@"
,[{sTbl}].[{co.Name}] as [{co.Name}_{sTbl}]";
}
join += $@"
join [{tblJoin.Name}] as [{sTbl}] on [{NameTable}].[{item.Name}] = [{sTbl}].[{tblJoin.lstColumns.First(q => q.IsPK).Name}]";
}
//where
var cDelete = LstInfoTable.FirstOrDefault(q => q.Type.ToLower().Equals("bit") && q.Name.ToLower().Contains("delete"));
if (cDelete != null)
{
where += $@"
WHERE [{NameTable}].[{cDelete.Name}] <> 1 ";
}
r = $@"
;CREATE PROC {GetName(eMethod.GetAll)[0]}
AS BEGIN
SELECT {col} FROM [{sTable}] as [{NameTable}]
{join}
{where}
END;
";
Map.Add(GetName(eMethod.GetAll)[0], r);
return(r);
}
private string GetDelete()
{
var lsKey = LstInfoTable.Where(q => q.IsPK).ToList();
if (lsKey.Count < 1)
{
return("");
}
string param = "";
string where = "";
string s = "";
string len = "";
string r = "";
foreach (var item in lsKey)
{
len = string.IsNullOrWhiteSpace(item.Length) ? "" : $"({item.Length})";
s = string.IsNullOrWhiteSpace(param) ? " " : " , ";
param += $"{s} @{item.Name.Replace(' ', '_')} {item.Type}{len}";
s = string.IsNullOrWhiteSpace(where) ? " " : "AND";
where += $" {s} [{item.Name}] = @{item.Name.Replace(' ', '_')} ";
}
//delete if table have column "delete"
var cDelete = LstInfoTable.FirstOrDefault(q => q.Type.ToLower().Equals("bit") && q.Name.ToLower().Contains("delete"));
if (cDelete != null)
{
r = $@"
;CREATE PROC {GetName(eMethod.Delete)[0]}
{param}
AS BEGIN
UPDATE [{sTable}] SET [{cDelete.Name}] = 1 WHERE {where}
END;";
}
else
{
r = $@"
;CREATE PROC {GetName(eMethod.Delete)[0]}
{param}
AS BEGIN
DELETE [{sTable}] WHERE {where}
END;
";
}
Map.Add(GetName(eMethod.Delete)[0], r);
return(r);
}