Example #1
0
        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);
        }
Example #2
0
        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);
        }