private List <tbl_BLOG> list_comm(string id) { PDBC db = new PDBC("PandaMarketCMS", true); List <tbl_BLOG> List_BLG = new List <tbl_BLOG>(); string query = "SELECT [Id],[message],[Name],[PostId],[ImagePath],[Date]"; query += "FROM [tbl_BLOG_Comment] where[PostId] = " + id + "order by Id desc"; db.Connect(); DataTable dt = db.Select(query); db.DC(); for (int i = 0; i < dt.Rows.Count; i++) { tbl_BLOG BLG = new tbl_BLOG(); BLG.Id = Convert.ToInt32(dt.Rows[0]["Id"]); BLG.message = dt.Rows[i]["message"].ToString(); BLG.name = dt.Rows[i]["Name"].ToString(); BLG.Date = dt.Rows[i]["Date"].ToString(); BLG.ImagePath = dt.Rows[i]["ImagePath"].ToString(); List_BLG.Add(BLG); } return(List_BLG); }
public List <tbl_BLOG> Pic_BLOG(string id) { List <tbl_BLOG> List_BLG = new List <tbl_BLOG>(); PDBC db = new PDBC("PandaMarketCMS", true); string query = "SELECT distinct [PostId],[PicSizeType],[PicAddress],[PicThumbnailAddress]"; query += "FROM [tbl_ADMIN_UploadStructure_ImageAddress]inner join [tbl_BLOG_Pic_Connector]"; query += " on [tbl_BLOG_Pic_Connector].PicId =[tbl_ADMIN_UploadStructure_ImageAddress].PicID "; query += $" where PostId ={id}"; db.Connect(); using (DataTable dt = db.Select(query)) { db.DC(); for (int i = 0; i < dt.Rows.Count; i++) { tbl_BLOG BLG = new tbl_BLOG(); BLG.PicId = id; BLG.PicSizeType = dt.Rows[i]["PicSizeType"].ToString(); BLG.PicAddress = dt.Rows[i]["PicAddress"].ToString(); BLG.PicThumbnailAddress = dt.Rows[i]["PicThumbnailAddress"].ToString(); List_BLG.Add(BLG); } } return(List_BLG); }
public List <tbl_BLOG> BLOG(string NamePage, string Valuepage) { PDBC db = new PDBC("PandaMarketCMS", true); List <tbl_BLOG> List_BLG = new List <tbl_BLOG>(); string query = ""; query = " SELECT [tbl_BLOG_Post].[Id], [Title],[Text_min],[Date] ,[name] as'Cat_Id'"; query += " ,(SELECT [ad_firstname]FROM [tbl_ADMIN_main] where[id_Admin]= [tbl_BLOG_Post].[WrittenBy_AdminId]) as'firstname' "; query += " ,(SELECT [ad_lastname]FROM [tbl_ADMIN_main] where[id_Admin]= [tbl_BLOG_Post].[WrittenBy_AdminId]) as'lastname' "; query += " ,(SELECT count([Id])FROM [tbl_BLOG_Comment]where [PostId] = [tbl_BLOG_Post].[Id] )as'Comments' "; query += " FROM [tbl_BLOG_Post] "; query += " inner join [tbl_BLOG_Categories] on [tbl_BLOG_Post].[Cat_Id] = [tbl_BLOG_Categories].[Id] "; query += " where [tbl_BLOG_Post].[Is_Deleted] like 0 AND [tbl_BLOG_Post].[Is_Disabled] like 0 "; query += " And [tbl_BLOG_Categories].[Is_Deleted] like 0 AND [tbl_BLOG_Categories].[Is_Disabled] like 0 "; if (NamePage == "post") { query += " order by [Date] desc "; } else if (NamePage == "Categories") { query += $" And [name] = N'{Valuepage}' order by [Date] desc "; } else if (NamePage == "Search") { query += $"And [tbl_BLOG_Post].Title like N'%{Valuepage}%' OR [tbl_BLOG_Post].Text like N'%{Valuepage}%' order by([tbl_BLOG_Post].weight) DESC,[Date] DESC"; } else if (NamePage == "tag") { query = BLOG_Tags(Valuepage, true)[0].name; } db.Connect(); using (DataTable dt = db.Select(query)) { db.DC(); for (int i = 0; i < dt.Rows.Count; i++) { tbl_BLOG BLG = new tbl_BLOG(); BLG.Id = Convert.ToInt32(dt.Rows[i]["Id"]); BLG.Title = dt.Rows[i]["Title"].ToString(); BLG.Text_min = dt.Rows[i]["Text_min"].ToString(); BLG.Date = dt.Rows[i]["Date"].ToString(); BLG.Cat_Id = dt.Rows[i]["Cat_Id"].ToString(); BLG.Comments = dt.Rows[i]["Comments"].ToString(); BLG.WrittenBy_AdminId = dt.Rows[i]["firstname"].ToString() + " " + dt.Rows[i]["lastname"].ToString(); BLG.list_pic = Pic_BLOG(dt.Rows[i]["Id"].ToString()); List_BLG.Add(BLG); } } return(List_BLG); }
public List <tbl_BLOG> TabS(string Value) { PDBC db = new PDBC("PandaMarketCMS", true); List <tbl_BLOG> List_BLG = new List <tbl_BLOG>(); string query = ""; if (Value == "new") { query = " SELECT top 5 [tbl_BLOG_Post].[Id], [Title],[Date],[name]"; query += " ,(SELECT count([Id])FROM [tbl_BLOG_Comment]where [PostId] = [tbl_BLOG_Post].[Id] )as'Comments' "; query += " FROM [tbl_BLOG_Post]"; query += " inner join [tbl_BLOG_Categories] on [tbl_BLOG_Post].[Cat_Id] = [tbl_BLOG_Categories].[Id]"; query += " where [tbl_BLOG_Post].[Is_Deleted] like 0 AND [tbl_BLOG_Post].[Is_Disabled] like 0"; query += " And [tbl_BLOG_Categories].[Is_Deleted] like 0 AND [tbl_BLOG_Categories].[Is_Disabled] like 0 order by Date desc "; } else if (Value == "like") { query = " SELECT top 5 [tbl_BLOG_Post].[Id], [tbl_BLOG_Post].[Title], [tbl_BLOG_Post].[Date], [tbl_BLOG_Categories].[name] "; query += " ,(SELECT count([Id])FROM [tbl_BLOG_Comment]where [PostId] = [tbl_BLOG_Post].[Id] )as'Comments' "; query += " FROM [tbl_BLOG_Post] "; query += " inner join [tbl_BLOG_Comment] on [tbl_BLOG_Post].[Id] = [tbl_BLOG_Comment].[PostId]"; query += " inner join [tbl_BLOG_Categories] on [tbl_BLOG_Post].[Cat_Id] = [tbl_BLOG_Categories].[Id]"; query += " where [tbl_BLOG_Post].[Is_Deleted] like 0 AND [tbl_BLOG_Post].[Is_Disabled] like 0 "; query += " And [tbl_BLOG_Categories].[Is_Deleted] like 0 AND [tbl_BLOG_Categories].[Is_Disabled] like 0 "; query += " And [tbl_BLOG_Comment].[PostId] = [tbl_BLOG_Post].[Id]"; query += " GROUP BY [tbl_BLOG_Post].[Id] , [tbl_BLOG_Post].[Title], [tbl_BLOG_Post].[Date],[tbl_BLOG_Categories].[name] "; query += " HAVING COUNT([PostId]) > 5 order by count([PostId]) desc"; } db.Connect(); using (DataTable dt = db.Select(query)) { db.DC(); for (int i = 0; i < dt.Rows.Count; i++) { tbl_BLOG BLG = new tbl_BLOG(); BLG.Id = Convert.ToInt32(dt.Rows[0]["Id"]); BLG.Title = dt.Rows[i]["Title"].ToString(); BLG.Date = dt.Rows[i]["Date"].ToString(); BLG.Comments = dt.Rows[i]["Comments"].ToString(); foreach (var item in Pic_BLOG(dt.Rows[i]["Id"].ToString())) { BLG.PicAddress = item.PicAddress; } List_BLG.Add(BLG); } } return(List_BLG); }
public ActionResult comment_post(tbl_BLOG tbl) { string query_new; string res = " "; PDBC db = new PDBC("PandaMarketCMS", true); List <ExcParameters> paramss = new List <ExcParameters>(); ExcParameters parameters = new ExcParameters(); query_new = "INSERT INTO [dbo].[tbl_BLOG_Comment]([Email],[message],[Name],[PostId])VALUES(@Email ,@message ,@Name ,@PostId)"; parameters = new ExcParameters() { _KEY = "@Email", _VALUE = tbl.Email }; paramss.Add(parameters); parameters = new ExcParameters() { _KEY = "@message", _VALUE = tbl.message }; paramss.Add(parameters); parameters = new ExcParameters() { _KEY = "@Name", _VALUE = tbl.name }; paramss.Add(parameters); parameters = new ExcParameters() { _KEY = "@PostId", _VALUE = tbl.Id }; paramss.Add(parameters); db.Connect(); res = db.Script(query_new, paramss); db.DC(); return(Redirect("blog_post?IdPage=" + tbl.Id)); }
//////////////////////////////////////////////BlogPost tbl_BLOG public tbl_BLOG BLOGPOST(string id) { PDBC db = new PDBC("PandaMarketCMS", true); string query = ""; query += " SELECT [tbl_BLOG_Post].[Id], [Title],[Text_min],[Text],[Date] ,[name] as'Cat_Id' "; query += " ,(SELECT [ad_firstname]FROM [tbl_ADMIN_main] where[id_Admin]= [tbl_BLOG_Post].[WrittenBy_AdminId]) as'firstname'"; query += " ,(SELECT [ad_lastname]FROM [tbl_ADMIN_main] where[id_Admin]= [tbl_BLOG_Post].[WrittenBy_AdminId]) as'lastname'"; query += " ,(SELECT count([Id])FROM [tbl_BLOG_Comment]where [PostId] = [tbl_BLOG_Post].[Id] )as'Comments'"; query += " FROM [tbl_BLOG_Post]"; query += " inner join [tbl_BLOG_Categories] on [tbl_BLOG_Post].[Cat_Id] = [tbl_BLOG_Categories].[Id] "; query += " where [tbl_BLOG_Post].[Is_Deleted] like 0 AND [tbl_BLOG_Post].[Is_Disabled] like 0"; query += " And [tbl_BLOG_Categories].[Is_Deleted] like 0 AND [tbl_BLOG_Categories].[Is_Disabled] like 0"; query += " And [tbl_BLOG_Post].[Id]=" + id; db.Connect(); DataTable dt = db.Select(query); db.DC(); var tbg = new tbl_BLOG { Id = Convert.ToInt32(dt.Rows[0]["Id"]), Title = dt.Rows[0]["Title"].ToString(), Text_min = dt.Rows[0]["Text_min"].ToString(), Text = dt.Rows[0]["Text"].ToString(), Date = dt.Rows[0]["Date"].ToString(), Cat_Id = dt.Rows[0]["Cat_Id"].ToString(), Comments = dt.Rows[0]["Comments"].ToString(), WrittenBy_AdminId = dt.Rows[0]["firstname"].ToString() + " " + dt.Rows[0]["lastname"].ToString(), list_pic = Pic_BLOG(dt.Rows[0]["Id"].ToString()), list_cat = BLOG_Categories(dt.Rows[0]["Cat_Id"].ToString(), true), list_comment = list_comm(dt.Rows[0]["Id"].ToString()) }; return(tbg); }
public List <tbl_BLOG> BLOG_Tags(string Valuepage, bool IsVal) { List <tbl_BLOG> List_BLG = new List <tbl_BLOG>(); PDBC db = new PDBC("PandaMarketCMS", true); string query; if (IsVal) { query = " SELECT [tbl_BLOG_Post].[Id], [Title],[Text_min],[Date] , [tbl_BLOG_Categories].[name] as'Cat_Id'"; query += " ,(SELECT [ad_firstname]FROM [tbl_ADMIN_main] where[id_Admin]=[tbl_BLOG_Post].[WrittenBy_AdminId]) as'firstname'"; query += " ,(SELECT [ad_lastname]FROM [tbl_ADMIN_main] where[id_Admin]= [tbl_BLOG_Post].[WrittenBy_AdminId]) as'lastname' "; query += " ,(SELECT count([Id])FROM [tbl_BLOG_Comment]where [PostId] = [tbl_BLOG_Post].[Id] )as'Comments' "; query += " FROM [tbl_BLOG_Post] "; query += " inner join [tbl_BLOG_TagConnector] on [tbl_BLOG_TagConnector].[Tag_Id] = [tbl_BLOG_Post].[Id]"; query += " inner join [tbl_BLOG_Tags] on [tbl_BLOG_TagConnector].[Tag_Id] = [tbl_BLOG_Tags].[Id]"; query += " inner join [tbl_BLOG_Categories] on [tbl_BLOG_Post].[Cat_Id] = [tbl_BLOG_Categories].[Id] "; query += " where [tbl_BLOG_Post].[Is_Deleted] like 0 AND [tbl_BLOG_Post].[Is_Disabled] like 0 "; query += " And [tbl_BLOG_Categories].[Is_Deleted] like 0 AND [tbl_BLOG_Categories].[Is_Disabled] like 0 "; query += " And [tbl_BLOG_Tags].[Is_Deleted] like 0 AND [tbl_BLOG_Tags].[Is_Disabled] like 0"; query += $" And [tbl_BLOG_Tags].[name] = N'{Valuepage}' order by [Date] desc "; tbl_BLOG BLG = new tbl_BLOG { name = query }; List_BLG.Add(BLG); } else { if (Valuepage == "مشاهده همه") { query = " SELECT [Name] "; query += " FROM [tbl_BLOG_Tags] "; query += " where [Is_Deleted] like 0 AND [Is_Disabled] like 0 order by id desc "; db.Connect(); using (DataTable dt = db.Select(query)) { db.DC(); for (int i = 0; i < dt.Rows.Count; i++) { tbl_BLOG BLG = new tbl_BLOG(); BLG.name = dt.Rows[i]["name"].ToString(); List_BLG.Add(BLG); } } } else { query = " SELECT top 10 [Name] "; query += " FROM [tbl_BLOG_Tags] "; query += " where [Is_Deleted] like 0 AND [Is_Disabled] like 0 order by id desc "; db.Connect(); using (DataTable dt = db.Select(query)) { db.DC(); for (int i = 0; i < dt.Rows.Count; i++) { tbl_BLOG BLG = new tbl_BLOG(); BLG.name = dt.Rows[i]["name"].ToString(); List_BLG.Add(BLG); } } } } return(List_BLG); }
public List <tbl_BLOG> BLOG_Categories(string Valuepage, bool IsVal) { List <tbl_BLOG> List_BLG = new List <tbl_BLOG>(); PDBC db = new PDBC("PandaMarketCMS", true); string query; if (IsVal) { /**/ query = " SELECT top 3 [tbl_BLOG_Post].[Id], [Title],[Text_min],[Date],[name] as'Cat_Id' "; query += ",(SELECT count([Id])FROM[tbl_BLOG_Comment]where[PostId] =[tbl_BLOG_Post].[Id] )as'Comments' "; query += " FROM [tbl_BLOG_Post] "; query += " inner join [tbl_BLOG_Categories] on [tbl_BLOG_Post].[Cat_Id] = [tbl_BLOG_Categories].[Id] "; query += " where [tbl_BLOG_Post].[Is_Deleted] like 0 AND [tbl_BLOG_Post].[Is_Disabled] like 0 "; query += " And [tbl_BLOG_Categories].[Is_Deleted] like 0 AND [tbl_BLOG_Categories].[Is_Disabled] like 0 "; query += " And[name] = N'" + Valuepage + "'order by[Date] desc"; db.Connect(); using (DataTable dt = db.Select(query)) { db.DC(); for (int i = 0; i < dt.Rows.Count; i++) { tbl_BLOG BLG = new tbl_BLOG(); BLG.name = dt.Rows[i]["Cat_Id"].ToString(); BLG.Id = Convert.ToInt32(dt.Rows[0]["Id"]); BLG.Comments = dt.Rows[i]["Comments"].ToString(); BLG.Title = dt.Rows[i]["Title"].ToString(); BLG.Text_min = dt.Rows[i]["Text_min"].ToString(); BLG.Date = dt.Rows[i]["Date"].ToString(); foreach (var item in Pic_BLOG(dt.Rows[i]["Id"].ToString())) { BLG.PicAddress = item.PicAddress; } List_BLG.Add(BLG); } } } else { query = " SELECT [name] ,( SELECT count(Id)"; query += " FROM [tbl_BLOG_Post]where [Cat_Id] = [tbl_BLOG_Categories].[Id])as 'count'"; query += " FROM [tbl_BLOG_Categories]"; query += " where [Is_Deleted] like 0 AND [Is_Disabled] like 0 order by id desc "; db.Connect(); using (DataTable dt = db.Select(query)) { db.DC(); for (int i = 0; i < dt.Rows.Count; i++) { tbl_BLOG BLG = new tbl_BLOG(); BLG.name = dt.Rows[i]["name"].ToString(); BLG.count = dt.Rows[i]["count"].ToString(); List_BLG.Add(BLG); } } } return(List_BLG); }