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