// private string Get_DeleteBy()
// {
// var ls = LstInfoTable.Where(q => q.isKey).ToList();
// if (ls.Count < 2) return string.Empty;
// string result = "";
// foreach (var item in ls)
// {
// result += $@"
//public bool {GetNameMethod(eMethod.DeleteBy)}{item.Name}({item.GetTypeCs()} {item.Name})
//{'{'}
// return new {dbEntity}().{proc.GetName(eMethod.Delete)}({item.Name})>0;
//{'}'}
//";
// }
// return result;
// }
private string Get_Delete()
{
var ls = LstInfoTable.Where(q => q.IsPK).ToList();
string param = "";
string value = "";
bool isFirst = true;
string s = "";
if (ls.Count > 1)
{
foreach (var item in ls)
{
s = isFirst ? "" : ",";
param += $"{s} {item.GetTypeCs()} {item.Name}";
value += $"{s} {item.Name}";
isFirst = false;
}
}
else
{
foreach (var item in ls)
{
s = isFirst ? "" : ",";
param += $"{s} object {item.Name}";
value += $"{s}({item.GetTypeCs()}) {item.Name}";
isFirst = false;
}
}
return($@"
public int {GetNameMethod(eMethod.Delete)}({param})
{'{'}
return new {dbEntity}().{proc.GetName(eMethod.Delete)[0]}({value});
{'}'}
");
}
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 Get_DeleteBy()
// {
// if (LstInfoTable.Count(q => q.isKey) < 2) return string.Empty;
// var lstKey = LstInfoTable.Where(q => q.isKey).ToList();
// string param = null;
// string value = null;
// string result = "";
// foreach (var item in lstKey)
// {
// value = item.Name;
// param = $"{item.GetTypeCs()} {item.Name}";
// result += $@"
//public bool {GetNameMethod(eMethod.DeleteBy)}{item.Name}({param})
//{'{'}
// return new {cDal}().{GetNameMethod(eMethod.DeleteBy)}{item.Name}({value});
//{'}'}
//";
// }
// return result;
// }
private string Get_Update()
{
if (!LstInfoTable.Any(q => q.IsPK) || LstInfoTable.Count == LstInfoTable.Count(q => q.IsPK))
{
return(string.Empty);
}
return($@"
public int Update({cDto} ob)
{'{'}
return new {cDal}().{GetNameMethod(eMethod.Update)}(ob);
{'}'}
");
}
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);
}
public List <string> GetName(Bussiness.eMethod method)
{
List <string> lst = new List <string>();
switch (method)
{
case Bussiness.eMethod.GetAll:
lst.Add($"{Setting.GetNameProc(NameTable)}_{method.ToString()}");
break;
case Bussiness.eMethod.Insert:
lst.Add($"{Setting.GetNameProc(NameTable)}_{method.ToString()}");
break;
case Bussiness.eMethod.Delete:
lst.Add($"{Setting.GetNameProc(NameTable)}_{method.ToString()}");
break;
case Bussiness.eMethod.Update:
lst.Add($"{Setting.GetNameProc(NameTable)}_{method.ToString()}");
break;
case Bussiness.eMethod.GetBy:
var ls = LstInfoTable.Where(q => q.IsPK).ToList();
if (ls.Count > 0)
{
foreach (var item in ls)
{
lst.Add($"{Setting.GetNameProc(NameTable)}_{method.ToString()}{item.Name}");
}
}
break;
//case Bussiness.eMethod.DeleteBy:
// var ls2 = LstInfoTable.Where(q => q.isKey).ToList();
// if (ls2.Count > 1)
// {
// foreach (var item in ls2)
// {
// lst.Add($"{Setting.GetNameProc(NameTable)}_{method.ToString()}{item.Name}");
// }
// }
// break;
default:
break;
}
return(lst);
}
private string GetUpdate()
{
var lsKey = LstInfoTable.Where(q => q.IsPK).ToList();
if (lsKey.Count < 1 || LstInfoTable.Count == lsKey.Count)
{
return("");
}
string param = "";
string value = "";
string where = "";
string s = "";
string len = "";
foreach (var item in LstInfoTable)
{
len = string.IsNullOrWhiteSpace(item.Length) ? "" : $"({item.Length})";
s = string.IsNullOrWhiteSpace(param) ? "" : ",";
param += $"{s} @{item.Name.Replace(' ', '_')} {item.Type}{len}";
if (item.IsPK)
{
s = string.IsNullOrWhiteSpace(where) ? "" : " AND ";
where += $" {s} [{item.Name}] = @{item.Name}";
}
else
{
s = string.IsNullOrWhiteSpace(value) ? "" : ",";
value += $"{s} [{item.Name}] = @{item.Name.Replace(' ', '_')}";
}
}
string r = $@"
;CREATE PROC {GetName(eMethod.Update)[0]}
{param}
AS BEGIN
UPDATE [{sTable}] SET {value} WHERE {where}
END;
";
Map.Add(GetName(eMethod.Update)[0], r);
return(r);
}
private string Get_Insert()
{
string passValue = "";
bool isFirst = true;
var ls = LstInfoTable.Where(q => !q.IsIdentity).ToList();
foreach (var item in ls)
{
string s = isFirst ? "" : ",";
passValue += $@"{s} ob.{item.Name.Replace(' ', '_')} ";
isFirst = false;
}
return($@"
public int {GetNameMethod(eMethod.Insert)}({cDto} ob)
{'{'}
return new {dbEntity}().{proc.GetName(eMethod.Insert)[0]}({passValue});
{'}'}
");
}
private string GetInsert()
{
var lsKey = LstInfoTable.Where(q => q.IsPK).ToList();
if (lsKey.Count < 1)
{
return("");
}
string param = "";
string value = "";
string column = "";
string s = "";
string len = "";
foreach (var item in LstInfoTable)
{
if (!item.IsIdentity)
{
len = string.IsNullOrWhiteSpace(item.Length) ? "" : $"({item.Length})";
s = string.IsNullOrWhiteSpace(param) ? "" : ",";
param += $"{s} @{item.Name.Replace(' ', '_')} {item.Type}{len}";
s = string.IsNullOrWhiteSpace(value) ? "" : ",";
value += $"{s} @{item.Name.Replace(' ', '_')}";
s = string.IsNullOrWhiteSpace(column) ? "" : ",";
column += $" {s} [{item.Name}]";
}
}
string r = $@"
;CREATE PROC {GetName(eMethod.Insert)[0]}
{param}
AS BEGIN
INSERT INTO [{sTable}]({column}) VALUES({value})
END;
";
Map.Add(GetName(eMethod.Insert)[0], r);
return(r);
}
private string Get_Update()
{
if (!LstInfoTable.Any(q => q.IsPK) || LstInfoTable.Count == LstInfoTable.Count(q => q.IsPK))
{
return("");
}
string passValue = "";
bool isFirst = true;
foreach (var item in LstInfoTable)
{
string s = isFirst ? "" : ",";
passValue += $@"{s} ob.{item.Name.Replace(' ', '_')} ";
isFirst = false;
}
return($@"
public int {GetNameMethod(eMethod.Update)}({cDto} ob)
{'{'}
return new {dbEntity}().{proc.GetName(eMethod.Update)[0]}({passValue});
{'}'}
");
}
private string Get_Delete()
{
if (!LstInfoTable.Any(q => q.IsPK))
{
return(string.Empty);
}
var lstKey = LstInfoTable.Where(q => q.IsPK).ToList();
string param = null;
string value = null;
bool isFirst = true;
if (lstKey.Count > 1)
{
foreach (var item in lstKey)
{
string s = isFirst ? "" : ",";
value += s + item.Name;
param += $"{s}{item.GetTypeCs()} {item.Name}";
isFirst = false;
}
}
else
{
foreach (var item in lstKey)
{
string s = isFirst ? "" : ",";
value += s + item.Name;
param += $"object {item.Name}";
isFirst = false;
}
}
return($@"
public int {GetNameMethod(eMethod.Delete)}({param})
{'{'}
return new {cDal}().{GetNameMethod(eMethod.Delete)}({value});
{'}'}
");
}
private string Get_GetBy()
{
if (LstInfoTable.Count(q => q.IsPK) < 1)
{
return(string.Empty);
}
var lstKey = LstInfoTable.Where(q => q.IsPK).ToList();
string param = null;
string value = null;
string result = "";
foreach (var item in lstKey)
{
value = item.Name;
param = $"{item.GetTypeCs()} {item.Name}";
result += $@"
public {cDto} {GetNameMethod(eMethod.GetBy)}{item.Name}({param})
{'{'}
return new {cDal}().{GetNameMethod(eMethod.GetBy)}{item.Name}({value});
{'}'}
";
}
return(result);
}
// 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);
}
