// 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);
}
