public Bussiness(string nameTable, SqlConnection connection, Setting setting) { Connection = connection; sTable = nameTable; Setting = setting; LstInfoTable = new SqlDatabaseContext(connection).GetInfoTable(nameTable); NameTable = setting.GetNameTable(nameTable); Table = new TableObject(sTable, connection); }
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_GetAll() { string passValue = ""; foreach (var item in LstInfoTable) { string checkBool = item.GetTypeCs() == typeof(bool).ToString() ? "== true" : ""; passValue += $@" obj.{item.Name.Replace(' ', '_')} = item.{item.Name.Replace(' ', '_')} {checkBool} ;"; } foreach (var itemFK in Table.lstFK) { var tblJoin = new TableObject(itemFK.NameTableJoin, Connection); string sDto = Setting.GetClassDto(tblJoin.Name); string stableJoin = itemFK.IsPK ? "" : $"_{Setting.GetNameTable(tblJoin.Name)}Join"; string passValueJoin = ""; for (int i = 0; i < tblJoin.lstColumns.Count; i++) { var co = tblJoin.lstColumns[i]; string cm = i == 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 = itemFK.Name.Replace(' ', '_'); } passValueJoin += $@" {co.Name.Replace(' ', '_')} = {checkNullable}item.{nameEntty}{checkFK} {checkBool} {cm}"; } passValue += $@" obj.{sDto}Join = new {sDto}() {'{'} {passValueJoin} {'}'}; "; } return($@" public List<{cDto}> {GetNameMethod(eMethod.GetAll)}() {'{'} var list = new {dbEntity}().{proc.GetName(eMethod.GetAll)[0]}(); List<{cDto}> lst = new List<{cDto}>(); foreach (var item in list) {'{'} var obj = new {cDto}(); {passValue} lst.Add(obj); {'}'} return lst; {'}'} "); }
// 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); }