public static List <WeikeData> GetAllWeikeOrderByComment()
{
List <WeikeData> wdList = new List <WeikeData>();
string sql = "select weike.weike_id,weike.title,weike.subject,weike.user_id,weike.src,weike.size,weike.description,weike.star,weike.postdate,weike.commentNum,user.name,user.avatar from weike natural join user order by commentNum desc";
MySqlConnection conn = Connection.getMySqlCon();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Weike weike = new Weike((int)reader["weike_id"], (string)reader["title"], (string)reader["subject"], (int)reader["user_id"], (string)reader["src"], (string)reader["size"], (string)reader["description"], (int)reader["star"], (DateTime)reader["postdate"], (int)reader["commentNum"]);
WeikeData wd = new WeikeData(weike, reader.GetString("name"), reader.GetString("avatar"));
wdList.Add(wd);
}
reader.Close();
conn.Close();
foreach (WeikeData wd in wdList)
{
wd.attachment = MyFileDB.FindByWeikeId(wd.weike.weike_id);
}
return(wdList);
}
static public List <CommentData> FindCommentDataByTime(int user_id, DateTime time)
{
List <CommentData> cdList = new List <CommentData>();
DateTime high = new DateTime(time.Year, time.Month, time.Day + 1);
string sql = "select weike.weike_id ,weike.title,weike.subject,weike.user_id as author_id,weike.src,weike.size,weike.description,weike.star,weike.postdate,weike.commentNum, comment.comment_id,comment.user_id as commenter_id,comment.date,comment.content,comment.parent,user1.name as author, user2.name as commenter,user2.avatar from weike inner join comment inner join user as user1 inner join user as user2 where comment.weike_id = weike.weike_id and user1.user_id = weike.user_id and user2.user_id = comment.user_id and comment.user_id = @userid and comment.date >= @low and comment.date<@high order by comment.date desc;";
MySqlConnection conn = Connection.getMySqlCon();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@userid", user_id);
cmd.Parameters.AddWithValue("@low", time);
cmd.Parameters.AddWithValue("@high", high);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Weike weike = new Weike((int)reader["weike_id"], (string)reader["title"], reader.GetString("subject"), (int)reader["author_id"], (string)reader["src"], (string)reader["size"], (string)reader["description"], (int)reader["star"], (DateTime)reader["postdate"], (int)reader["commentNum"]);
Comment comment = new Comment((int)reader["comment_id"], (int)reader["commenter_id"], (int)reader["weike_id"], (DateTime)reader["date"], (string)reader["content"], (int)reader["parent"]);
CommentData cd = new CommentData(weike, comment, (string)reader["author"], (string)reader["commenter"], (string)reader["avatar"]);
cdList.Add(cd);
}
reader.Close();
conn.Close();
return(cdList);
}
public static WeikeData FindByWeikeId(int weike_id)
{
WeikeData wd = null;
string sql = "select weike.weike_id,weike.title,weike.subject,weike.user_id,weike.src,weike.size,weike.description,weike.star,weike.postdate,weike.commentNum,user.name,user.avatar from weike natural join user where weike_id = @weike_id";
MySqlConnection conn = Connection.getMySqlCon();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@weike_id", weike_id);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
Weike weike = new Weike((int)reader["weike_id"], (string)reader["title"], (string)reader["subject"], (int)reader["user_id"], (string)reader["src"], (string)reader["size"], (string)reader["description"], (int)reader["star"], (DateTime)reader["postdate"], (int)reader["commentNum"]);
wd = new WeikeData(weike, reader.GetString("name"), reader.GetString("avatar"));
}
reader.Close();
conn.Close();
wd.attachment = MyFileDB.FindByWeikeId(wd.weike.weike_id);
return(wd);
}
public static List <WeikeData> FindByUserId(int user_id, int top)
{
List <WeikeData> wdList = new List <WeikeData>();
string sql = "select weike.weike_id,weike.title,weike.subject,weike.user_id,weike.src,weike.size,weike.description,weike.star,weike.postdate,weike.commentNum,user.name,user.avatar from weike inner join user on user.user_id = weike.user_id where user.user_id = 2 ORDER BY weike.star DESC limit 0,@top";
MySqlConnection conn = Connection.getMySqlCon();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@id", user_id);
cmd.Parameters.AddWithValue("@top", top);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Weike weike = new Weike((int)reader["weike_id"], (string)reader["title"], (string)reader["subject"], (int)reader["user_id"], (string)reader["src"], (string)reader["size"], (string)reader["description"], (int)reader["star"], (DateTime)reader["postdate"], (int)reader["commentNum"]);
WeikeData wd = new WeikeData(weike, reader.GetString("name"), reader.GetString("avatar"));
wdList.Add(wd);
}
reader.Close();
conn.Close();
foreach (WeikeData wd in wdList)
{
wd.attachment = MyFileDB.FindByWeikeId(wd.weike.weike_id);
}
return(wdList);
}
public static int Insert(Weike weike)
{
int result = 0;
string sql = "insert into weike VALUES (" + weike.weike_id + ",'" + weike.title + "','" + weike.subject + "','" + weike.user_id + "','" + weike.src + "','" + weike.size + "','" + weike.description + "'," + weike.star + ",'" + weike.postdate + "'," + weike.commentNum + "," + 1 + ")";
MySqlConnection conn = Connection.getMySqlCon();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "update user set postNum = postNum+1 where user_id = @user_id";
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@user_id", weike.user_id);
cmd.ExecuteNonQuery();
sql = "select weike_id from weike order by weike_id desc limit 0,1";
cmd.CommandText = sql;
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
result = (int)reader[0];
}
reader.Close();
conn.Close();
return(result);
}
public static List <WeikeData> FindByAuthor(string author)
{
List <WeikeData> wdList = new List <WeikeData>();
string sql = "select weike.weike_id,weike.title,weike.subject,weike.user_id,weike.src,weike.size,weike.description,weike.star,weike.postdate,weike.commentNum,user.name,user.avatar from weike natural join user where name like @author";
MySqlConnection conn = Connection.getMySqlCon();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
string condition = "";
for (int i = 0; i < author.Length; i++)
{
condition = condition + "%" + author[i];
}
condition = condition + "%";
Console.WriteLine(condition);
cmd.Parameters.AddWithValue("@author", condition);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Weike weike = new Weike((int)reader["weike_id"], (string)reader["title"], (string)reader["subject"], (int)reader["user_id"], (string)reader["src"], (string)reader["size"], (string)reader["description"], (int)reader["star"], (DateTime)reader["postdate"], (int)reader["commentNum"]);
WeikeData wd = new WeikeData(weike, reader.GetString("name"), reader.GetString("avatar"));
wdList.Add(wd);
}
reader.Close();
conn.Close();
foreach (WeikeData wd in wdList)
{
wd.attachment = MyFileDB.FindByWeikeId(wd.weike.weike_id);
}
return(wdList);
}
public WeikeData(Weike weike, string author, MyFile attachment, string avatar)
{
this.weike = weike;
this.author = author;
this.attachment = attachment;
this.avatar = avatar;
}
public FavoriteData(Weike weike, string author, DateTime favoritedate, string avatar)
{
this.weike = weike;
this.author = author;
this.favoritedate = favoritedate;
this.avatar = avatar;
}
public CommentData(Weike weike, Comment comment, string author, string commenter, string avatar)
{
this.weike = weike;
this.comment = comment;
this.author = author;
this.commenter = commenter;
this.avatar = avatar;
}
static public List <FavoriteData> FindFavoriteWeikeByUserId(int user_id)
{
List <FavoriteData> fdList = new List <FavoriteData>();
string sql = "SELECT weike.weike_id,weike.title,weike.subject,weike.user_id,weike.src,weike.size,weike.description,weike.star,weike.postdate,weike.commentNum,user.name,user.avatar,favorite.date FROM weike.weike inner join user inner join favorite where weike.weike_id = favorite.weike_id and user.user_id = weike.user_id and favorite.user_id = @userid;";
MySqlConnection conn = Connection.getMySqlCon();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@userid", user_id);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Weike weike = new Weike((int)reader["weike_id"], (string)reader["title"], (string)reader["subject"], (int)reader["user_id"], (string)reader["src"], (string)reader["size"], (string)reader["description"], (int)reader["star"], (DateTime)reader["postdate"], (int)reader["commentNum"]);
FavoriteData fd = new FavoriteData(weike, (string)reader["name"], (DateTime)reader["date"], (string)reader["avatar"]);
fdList.Add(fd);
}
reader.Close();
conn.Close();
return(fdList);
}
public WeikeData(Weike weike, string author, string avatar)
{
this.weike = weike;
this.author = author;
this.avatar = avatar;
}