// private string GetDeleteBy()
// {
// var lsKey = LstInfoTable.Where(q => q.isKey).ToList();
// if (lsKey.Count < 2) return "";
// var lsName = GetName(eMethod.DeleteBy);
// string r = "";
// for (int i = 0; i < lsKey.Count; i++)
// {
// string len = string.IsNullOrWhiteSpace(lsKey[i].Length) ? "" : $"({lsKey[i].Length})";
// string param = $"@{lsKey[i].Name} {lsKey[i].Type}{len}";
// string rs = $@"
//;CREATE PROC {lsName[i]}
//{param}
//AS BEGIN
// DELETE {Table} WHERE {lsKey[i].Name} = @{lsKey[i].Name}
//END;
//";
// Map.Add(GetName(eMethod.DeleteBy)[0], rs);
// r += rs;
// }
// return r;
// }
private string GetBy()
{
var lsKey = LstInfoTable.Where(q => q.IsPK).ToList();
if (lsKey.Count < 1)
{
return("");
}
var lsName = GetName(eMethod.GetBy);
string r = "";
for (int i = 0; i < lsKey.Count; i++)
{
string len = string.IsNullOrWhiteSpace(lsKey[i].Length) ? "" : $"({lsKey[i].Length})";
string param = $"@{lsKey[i].Name.Replace(' ', '_')} {lsKey[i].Type}{len}";
string col = "";
string join = "";
string where = $" WHERE [{NameTable}].[{lsKey[i].Name}] = @{lsKey[i].Name.Replace(' ', '_')} ";
//columns select
foreach (var item in Table.lstColumns)
{
if (item.Type.Equals("bit") && item.Name.ToLower().Contains("delete"))
{
where += $" AND [{NameTable}].[{item.Name}] <> 1 ";
}
string s = string.IsNullOrWhiteSpace(col) ? "" : ",";
if (item.Name.Any(c => c == ' '))
{
col += $@"
{s}[{NameTable}].[{item.Name}] as [{item.Name.Replace(' ', '_')}]";
}
else
{
col += $@"
{s}[{NameTable}].[{item.Name}]";
}
}
//column 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.Replace(' ', '_')}_{sTbl}]";
}
join += $@"
join [{tblJoin.Name}] as [{sTbl}] on [{NameTable}].[{item.Name}] = [{sTbl}].[{tblJoin.lstColumns.First(q => q.IsPK).Name}]";
}
string rs = $@"
;CREATE PROC {lsName[i]}
{param}
AS BEGIN
SELECT
{col}
FROM [{sTable}] as [{NameTable}]
{join}
{where}
END;
";
Map.Add(GetName(eMethod.GetBy)[i], rs);
r += rs;
}
return(r);
}
private string Get_GetBy()
{
var lsKey = LstInfoTable.Where(q => q.IsPK).ToList();
if (lsKey.Count < 1)
{
return(string.Empty);
}
string result = "";
for (int i = 0; i < lsKey.Count; i++)
{
var itemKey = lsKey[i];
string setValue = "";
foreach (var v in LstInfoTable)
{
string checkBool = v.GetTypeCs() == typeof(bool).ToString() ? "== true" : "";
setValue += $@"
obj.{v.Name.Replace(' ', '_')} = item.{v.Name.Replace(' ', '_')} {checkBool} ;";
}
foreach (var fk in Table.lstFK)
{
var tblJoin = new TableObject(fk.NameTableJoin, Connection);
string sDto = Setting.GetClassDto(tblJoin.Name);
string stableJoin = $"_{Setting.GetNameTable(tblJoin.Name)}Join";
string passValueJoin = "";
for (int j = 0; j < tblJoin.lstColumns.Count; j++)
{
var co = tblJoin.lstColumns[j];
string cm = j == tblJoin.lstColumns.Count - 1 ? "" : ",";
string checkBool = co.GetTypeCs() == typeof(bool).ToString() ? "== true" : "";
string checkFK = stableJoin;
string checkNullable = "";
string nameEntty = co.Name.Replace(' ', '_');
if (co.IsPK)
{
checkFK = "";
checkNullable = $"({co.GetTypeCs()})";
nameEntty = fk.Name.Replace(' ', '_');
}
passValueJoin += $@"
{co.Name.Replace(' ', '_')} = {checkNullable} item.{nameEntty}{checkFK} {checkBool} {cm}";
}
setValue += $@"
obj.{sDto}Join = new {sDto}()
{'{'}
{passValueJoin}
{'}'};
";
}
result += $@"
public {cDto} {GetNameMethod(eMethod.GetBy)}{itemKey.Name}({itemKey.GetTypeCs()} {itemKey.Name})
{'{'}
var list = new {dbEntity}().{proc.GetName(eMethod.GetBy)[i]}({itemKey.Name});
foreach (var item in list)
{'{'}
var obj = new {cDto}();
{setValue}
return obj;
{'}'}
return null;
{'}'}
";
}
return(result);
}