// Fetch all video comments public static List <Comment_Struct> Fetch_Comments(long id, int type, int PageNumber, int PageSize) { SqlConnection con = new SqlConnection(Config.ConnectionString); con.Open(); System.Collections.Generic.List <Comment_Struct> items = new System.Collections.Generic.List <Comment_Struct>(); Comment_Struct str_ct = default(Comment_Struct); //// generate query StringBuilder str = new StringBuilder(); StringBuilder query = new StringBuilder(); query.Append("commentid,username,_comment,videoid,added_date,points from comments WHERE VideoID=" + id + " AND type=" + type + " AND isenabled=1 AND isApproved=1"); // implement paging script for differnt data sources. switch (Site_Settings.Pagination_Type) { case 0: // SQL SERVER 2005 or Later Supported Query str.Append(Pagination_Process.Prepare_SQLSERVER2005_Pagination(query.ToString(), "commentid DESC", PageNumber, PageSize)); break; case 1: // MySQL Supported Query string mysql_query = "SELECT " + query.ToString() + " ORDER BY commentid DESC"; str.Append(Pagination_Process.Prepare_MySQL_Pagination(mysql_query, PageNumber, PageSize)); break; case 2: // SQL SERVER 2000 Supported Query string normal_query = "SELECT " + query.ToString() + " ORDER BY commentid DESC"; str.Append(normal_query); break; } SqlCommand cmd = new SqlCommand(str.ToString(), con); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { str_ct = new Comment_Struct(); str_ct.CommentID = (long)reader["CommentID"]; str_ct.UserName = reader["username"].ToString(); str_ct.Added_Date = (DateTime)reader["added_date"]; str_ct.Comment = reader["_comment"].ToString(); str_ct.VideoID = Convert.ToInt64(reader["videoid"]); str_ct.Points = (int)reader["points"]; items.Add(str_ct); } reader.Close(); con.Close(); return(items); }
// Load photos in admin section public static List <Comment_Struct> Load_Comments(string search, string username, string type, int isEnabled, int isApproved, int filteroption, string order, int PageNumber, int PageSize) { SqlConnection con = new SqlConnection(Config.ConnectionString); if (con.State != ConnectionState.Open) { con.Open(); } List <Comment_Struct> _items = new List <Comment_Struct>(); Comment_Struct _item; // Query Building StringBuilder str = new StringBuilder(); StringBuilder query = new StringBuilder(); string logic = CommentsBLL.Return_Comment_Admin_Logic(search, username, type, isEnabled, isApproved, filteroption); query.Append("commentid,videoid,username,_comment,added_date,isenabled,type,points,isapproved,replyid,profileid FROM comments" + logic); // implement paging script for differnt data sources. switch (Site_Settings.Pagination_Type) { case 0: // SQL SERVER 2005 or Later Supported Query str.Append(Pagination_Process.Prepare_SQLSERVER2005_Pagination(query.ToString(), order, PageNumber, PageSize)); break; case 1: // MySQL Supported Query string mysql_query = "SELECT " + query.ToString() + " ORDER BY " + order; str.Append(Pagination_Process.Prepare_MySQL_Pagination(mysql_query, PageNumber, PageSize)); break; case 2: // SQL SERVER 2000 Supported Query string normal_query = "SELECT " + query.ToString() + " ORDER BY " + order; str.Append(normal_query); break; } // execute sql query SqlCommand video_cmd = new SqlCommand(str.ToString(), con); video_cmd.Parameters.Add(new SqlParameter("@search", search)); video_cmd.Parameters.Add(new SqlParameter("@username", username)); video_cmd.Parameters.Add(new SqlParameter("@type", type)); video_cmd.Parameters.Add(new SqlParameter("@isenabled", isEnabled)); video_cmd.Parameters.Add(new SqlParameter("@isapproved", isApproved)); SqlDataReader reader = video_cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { _item = new Comment_Struct(); _item.CommentID = (long)reader["commentid"]; _item.Comment = reader["_comment"].ToString(); _item.UserName = reader["username"].ToString(); _item.VideoID = (long)reader["videoid"]; _item.isEnabled = Convert.ToInt32(reader["isenabled"]); _item.isApproved = Convert.ToInt32(reader["isapproved"]); _item.Type = Convert.ToInt32(reader["type"]); _item.ReplyID = (long)reader["replyid"]; _item.Points = Convert.ToInt32(reader["points"]); _item.Added_Date = (DateTime)reader["added_date"]; _item.ProfileID = reader["profileid"].ToString(); _items.Add(_item); } reader.Close(); con.Close(); con.Dispose(); return(_items); }
// Fetch Comments With User Profile Picture public static List <Comment_Struct> Fetch_Comments_V2(long id, string profileid, int type, int PageNumber, int PageSize, string Order, bool ShowAuthoImage) { SqlConnection con = new SqlConnection(Config.ConnectionString); con.Open(); System.Collections.Generic.List <Comment_Struct> items = new System.Collections.Generic.List <Comment_Struct>(); Comment_Struct str_ct = default(Comment_Struct); //// generate query StringBuilder str = new StringBuilder(); StringBuilder query = new StringBuilder(); //// generate query string _filter = ""; if (profileid != "") { _filter = "c.profileid=@profileid"; //_filter = "c.profileid=" + profileid.Replace("'",""); } else { _filter = "c.videoid=@id"; // _filter = "c.videoid=" + id; } query.Append("c.commentid,c.username,c._comment,c.videoid,c.added_date,c.points,c.replyid"); // remove , if (ShowAuthoImage) { query.Append(",u.picturename"); // remove, } query.Append(" FROM comments c "); // WHERE p.videoid=555 ORDER BY level; if (ShowAuthoImage) { query.Append("inner join users as u on u.username=c.username "); } query.Append(" WHERE " + _filter + " AND c.type=@type AND c.isenabled=1 AND c.isApproved=1"); // implement paging script for differnt data sources. switch (Site_Settings.Pagination_Type) { case 0: // SQL SERVER 2005 or Later Supported Query str.Append(Pagination_Process.Prepare_SQLSERVER2005_Pagination(query.ToString(), Order, PageNumber, PageSize)); break; case 1: // MySQL Supported Query string mysql_query = "SELECT " + query.ToString() + " ORDER BY " + Order; str.Append(Pagination_Process.Prepare_MySQL_Pagination(mysql_query, PageNumber, PageSize)); break; case 2: // SQL SERVER 2000 Supported Query string normal_query = "SELECT " + query.ToString() + " ORDER BY " + Order; str.Append(normal_query); break; } SqlParameter profileparam = new SqlParameter("@profileid", profileid); SqlParameter contentidparam = new SqlParameter("@id", id); SqlParameter typeparam = new SqlParameter("@type", type); SqlCommand cmd = new SqlCommand(str.ToString(), con); cmd.Parameters.Add(profileparam); cmd.Parameters.Add(contentidparam); cmd.Parameters.Add(typeparam); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { str_ct = new Comment_Struct(); str_ct.CommentID = (long)reader["CommentID"]; str_ct.UserName = reader["username"].ToString(); str_ct.Added_Date = (DateTime)reader["added_date"]; str_ct.Comment = reader["_comment"].ToString(); str_ct.VideoID = Convert.ToInt64(reader["videoid"]); str_ct.Points = (int)reader["points"]; str_ct.ReplyID = Convert.ToInt64(reader["replyid"]); if (ShowAuthoImage) { str_ct.PictureName = reader["picturename"].ToString(); } items.Add(str_ct); } reader.Close(); con.Close(); return(items); }
// The following function load specific number of records without any pagination. e.g Fetching top 8 recently added photos // Non Cache Version public static List <UserActivity_Struct> Fetch_Activities(string term, string UserName, int Month, int Year, string order, int records, int datefilter, int PageNumber) { SqlConnection con = new SqlConnection(Config.ConnectionString); if (con.State != ConnectionState.Open) { con.Open(); } List <UserActivity_Struct> _items = new List <UserActivity_Struct>(); UserActivity_Struct _item; // generate sql query string logic = ActivityBLL.Process_Activity_V3_Logic(term, UserName, Month, Year, datefilter); StringBuilder str = new StringBuilder(); StringBuilder query = new StringBuilder(); query.Append("u.picturename,p.activityid,p.username,p.poster_username,p.activity,p.title,p.added_date,p.liked,p.disliked,u.picturename,p.comments FROM useractivities as p Inner join users as u on u.username=p.poster_username " + logic); switch (Site_Settings.Pagination_Type) { case 0: // SQL SERVER 2005 or Later Supported Query str.Append(Pagination_Process.Prepare_SQLSERVER2005_Pagination(query.ToString(), order, PageNumber, records)); break; case 1: // MySQL Supported Query string mysql_query = "SELECT " + query.ToString() + " ORDER BY " + order; str.Append(Pagination_Process.Prepare_MySQL_Pagination(mysql_query, PageNumber, records)); break; case 2: // SQL SERVER 2000 Supported Query string normal_query = "SELECT " + query.ToString() + " ORDER BY " + order; str.Append(normal_query); break; } // execute sql query SqlCommand cmd = new SqlCommand(str.ToString(), con); cmd.Parameters.Add(new SqlParameter("@username", UserName)); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { _item = new UserActivity_Struct(); _item.ActivityID = (long)reader["activityid"]; _item.UserName = reader["username"].ToString(); _item.Title = reader["title"].ToString(); _item.Activity = reader["activity"].ToString(); _item.Added_Date = (DateTime)reader["added_date"]; _item.Liked = Convert.ToInt32(reader["liked"]); _item.Disliked = Convert.ToInt32(reader["disliked"]); _item.Comments = Convert.ToInt32(reader["comments"]); _item.PictureName = reader["picturename"].ToString(); _item.PosterUserName = reader["poster_username"].ToString(); _items.Add(_item); } reader.Close(); con.Close(); con.Dispose(); return(_items); }