//修改浏览次数
public void modifyNum(int postId)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
try
{
conn = DBManage.getConn();
//1.打开数据库
conn.Open();
//2.创建command对象
comm = conn.CreateCommand();
comm.CommandText = "update [post] set browseNum = browseNum + 1 where postId = @postId ";
List <OleDbParameter> paras = DBManage.getListParameter(new String[] { "@postId" }, new Object[] { postId });
comm.Parameters.AddRange(paras.ToArray <OleDbParameter>());
comm.ExecuteNonQuery();
}
catch (Exception e)
{
Console.Write(e.Message);
throw new Exception();
}
finally
{
if (conn != null)
{
DBManage.closeConn(conn);
}
}
}
//根据id号获取用户
public User findUserById(String userId)
{
User user = null;
OleDbConnection conn = null;
OleDbCommand comm = null;
OleDbDataReader reader = null;
conn = DBManage.getConn();
//1.打开数据库
conn.Open();
//2.创建command对象
comm = conn.CreateCommand();
comm.CommandText = "select * from [user] where userId = '" + userId + "'";
reader = comm.ExecuteReader();
while (reader.Read())
{
user = new User();
user.UserId = reader.GetString(0);
user.Username = reader.GetString(1);
user.Password = reader.GetString(2);
user.Email = reader.GetString(3);
user.Phone = reader.GetString(4);
user.QQ = reader.GetString(5);
user.Picture = reader.GetString(6);
}
return(user);
}
//修改用户图片
public void updatePic(User user)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
try
{
conn = DBManage.getConn();
//1.打开数据库
conn.Open();
//2.创建command对象
comm = conn.CreateCommand();
comm.CommandText = "update [user] set picture = @picture where userId = @userId ";
List <OleDbParameter> paras = DBManage.getListParameter(new String[] { "@picture", "@userId" }, new Object[] { user.Picture, user.UserId });
comm.Parameters.AddRange(paras.ToArray <OleDbParameter>());
comm.ExecuteNonQuery();
}
catch (Exception e)
{
Console.Write(e.Message);
}
finally {
if (conn != null)
{
DBManage.closeConn(conn);
}
}
}
//通过postId获取回复数据，限制数据条数
public List <Reply> getReplysByPostId(int postId, int currentPage, int everyPageSize)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
OleDbDataReader reader = null;
List <Reply> listReplys = new List <Reply>();
UserDao userDao = new UserDao();
try
{
conn = DBManage.getConn();
//1.打开数据库
conn.Open();
//2.创建command对象
comm = conn.CreateCommand();
//comm.CommandText ="select top @everyPageSize * from "
// +"(select top @currentPage*@everyPageSize * from reply where postId = @postId order by replyTime desc)"+" order by replyTime asc" ;
int num = currentPage * everyPageSize;
//先获取currentPage前所有数据并降序，在获取每页大小并升序。结果就是第currentPage数据
comm.CommandText = "select top " + everyPageSize + " * from ( select top " + num + " * from reply where postId = " + postId + " order by replyTime desc ) order by replyTime asc";
//List<OleDbParameter> paras = DBManage.getListParameter(
// new String[] { "@postId"},
// new Object[] { 15});
//comm.Parameters.AddRange(paras.ToArray<OleDbParameter>());
//3.执行命令
reader = comm.ExecuteReader();
while (reader.Read())
{
Reply reply = new Reply();
reply.ReplyId = reader.GetString(0);
reply.PostId = reader.GetInt32(1);
reply.UserId = reader.GetString(2);
reply.ReplyMessage = reader.GetString(3);
reply.ReplyTime = reader.GetDateTime(4);
//通过userId获取对于的回复人信息
reply.User = userDao.findUserById(reply.UserId);
listReplys.Add(reply);
}
}
catch (Exception e)
{
Console.Write(e.Message);
throw new Exception();
}
finally
{
//4.关闭连接
if (conn != null)
{
DBManage.closeConn(conn);
}
}
return(listReplys);
}
//查询所有帖子
public List <Post> findALLPost()
{
List <Post> listPost = new List <Post>();
OleDbConnection conn = null;
OleDbCommand comm = null;
OleDbDataReader reader = null;
UserDao userDao = new UserDao();
try
{
conn = DBManage.getConn();
conn.Open();
comm = conn.CreateCommand();
comm.CommandText = "select * from post order by postTime desc";
reader = comm.ExecuteReader();
while (reader.Read())
{
Post post = new Post();
post.PostId = reader.GetInt32(0);
post.UserId = reader.GetString(1);
post.Title = reader.GetString(2);
post.Content = reader.GetString(3);
post.PostTime = (DateTime)reader.GetValue(4);
post.UserIp = reader.GetString(5);
post.ReplyNum = reader.GetInt32(6);
post.BrowseNum = reader.GetInt32(7);
//关联查询出user
User user = userDao.findUserById(post.UserId);
post.User = user;
listPost.Add(post);
}
return(listPost);
}
catch (Exception e)
{
Console.Write(e.Message);
return(null);
}
finally {
if (conn != null)
{
DBManage.closeConn(conn);
}
}
}
//根据帖子id查询
public Post findPostByPostId(int postId)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
OleDbDataReader reader = null;
UserDao userDao = new UserDao();
Post post = null;
try
{
conn = DBManage.getConn();
conn.Open();
comm = conn.CreateCommand();
comm.CommandText = "select * from post where postId = " + postId;
reader = comm.ExecuteReader();
if (reader.Read())
{
post = new Post();
post.PostId = reader.GetInt32(0);
post.UserId = reader.GetString(1);
post.Title = reader.GetString(2);
post.Content = reader.GetString(3);
post.PostTime = (DateTime)reader.GetValue(4);
post.UserIp = reader.GetString(5);
post.ReplyNum = reader.GetInt32(6);
post.BrowseNum = reader.GetInt32(7);
//关联查询出user
User user = userDao.findUserById(post.UserId);
post.User = user;
}
return(post);
}
catch (Exception e)
{
Console.Write(e.Message);
return(null);
}
finally
{
if (conn != null)
{
DBManage.closeConn(conn);
}
}
}
//注册用户
public void add(User user)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
try
{
conn = DBManage.getConn();
//1.打开数据库
conn.Open();
//2.创建command对象
comm = conn.CreateCommand();
comm.CommandText = "insert into [user] values(@userId,@username,@password,@email,@phone,@QQ,@picture)";
//动态设置参数
//List<OleDbParameter> paras = new List<OleDbParameter>();
//paras.Add(new OleDbParameter(){ParameterName = "@userId",Value=user.UserId});
//paras.Add(new OleDbParameter() { ParameterName = "@username", Value = user.Username });
//paras.Add(new OleDbParameter() { ParameterName = "@password", Value = user.Password });
//paras.Add(new OleDbParameter() { ParameterName = "@email", Value = user.Email });
//paras.Add(new OleDbParameter() { ParameterName = "@phone", Value = user.Phone });
//paras.Add(new OleDbParameter() { ParameterName = "@QQ", Value = user.QQ });
//paras.Add(new OleDbParameter() { ParameterName = "@picture", Value = user.Picture });
List <OleDbParameter> paras = DBManage.getListParameter(
new String[] { "@userId", "@username", "@password", "@email", "@phone", "@QQ", "@picture" },
new Object[] { user.UserId, user.Username, user.Password, user.Email, user.Phone, user.QQ, user.Picture });
comm.Parameters.AddRange(paras.ToArray <OleDbParameter>());
//3.执行命令
comm.ExecuteNonQuery();
}
catch (Exception e)
{
Console.Write(e.Message);
}
finally {
//4.关闭连接
if (conn != null)
{
DBManage.closeConn(conn);
}
}
}
//邮箱是否存在
public bool emailExists(string email)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
OleDbDataReader reader = null;
conn = DBManage.getConn();
//1.打开数据库
conn.Open();
//2.创建command对象
comm = conn.CreateCommand();
comm.CommandText = "select * from [user] where email = " + email;
reader = comm.ExecuteReader();
if (reader.Read())
{
return(true);
}
return(false);
}
//通过ID删除帖子
public bool deletePost(String postId, String userId)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
conn = DBManage.getConn();
conn.Open();
comm = conn.CreateCommand();
comm.CommandText = "delete from [post] where postId = @postId and userId = @userId";
List <OleDbParameter> paras = DBManage.getListParameter(new String[] { "@postId", "@userId" },
new Object[] { Convert.ToInt32(postId), userId });
comm.Parameters.AddRange(paras.ToArray <OleDbParameter>());
int flag;
try
{
flag = comm.ExecuteNonQuery();
if (flag == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e) {
return(false);
}
finally
{
if (conn != null)
{
DBManage.closeConn(conn);
}
}
}
//用户登入验证
public User findUser(User user)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
OleDbDataReader reader = null;
User findUser = null;
conn = DBManage.getConn();
//1.打开数据库
conn.Open();
//2.创建command对象
comm = conn.CreateCommand();
comm.CommandText = "select * from [user] where ( username = @username or email = @email ) and password = @password";
//List<OleDbParameter> paras = new List<OleDbParameter>();
//paras.Add(new OleDbParameter() { ParameterName = "@username", Value = user.Username });
//paras.Add(new OleDbParameter() { ParameterName = "@email", Value = user.Username });
//paras.Add(new OleDbParameter() { ParameterName = "@password", Value = user.Password });
List <OleDbParameter> paras = DBManage.getListParameter(new String[] { "@username", "@email", "password" }, new Object[] { user.Username, user.Username, user.Password });
comm.Parameters.AddRange(paras.ToArray <OleDbParameter>());
reader = comm.ExecuteReader();
while (reader.Read())
{
findUser = new User();
findUser.UserId = reader.GetString(0);
findUser.Username = reader.GetString(1);
findUser.Password = reader.GetString(2);
findUser.Email = reader.GetString(3);
findUser.Phone = reader.GetString(4);
findUser.QQ = reader.GetString(5);
findUser.Picture = reader.GetString(6);
}
return(findUser);
}
//通过postId获取所有回复数量
public int getAllReplyCount(int postId)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
OleDbDataReader reader = null;
int count = 0;
try
{
conn = DBManage.getConn();
//1.打开数据库
conn.Open();
//2.创建command对象
comm = conn.CreateCommand();
comm.CommandText = "select count(*) from reply where postId = " + postId;
//3.执行命令
reader = comm.ExecuteReader();
if (reader.Read())
{
count = reader.GetInt32(0);
}
return(count);
}
catch (Exception e)
{
Console.Write(e.Message);
throw new Exception();
}
finally
{
//4.关闭连接
if (conn != null)
{
DBManage.closeConn(conn);
}
}
}
public void add(Reply reply)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
try
{
conn = DBManage.getConn();
//1.打开数据库
conn.Open();
//2.创建command对象
comm = conn.CreateCommand();
comm.CommandText = "insert into reply(replyId,postId,userId,replyMessage,replyTime) values(@replyId,@postId,@userId,@replyMessage,@replyTime)";
List <OleDbParameter> paras = DBManage.getListParameter(
new String[] { "@replyId", "@postId", "@userId", "@replyMessage", "@replyTime" },
new Object[] { reply.ReplyId, reply.PostId, reply.UserId, reply.ReplyMessage, reply.ReplyTime.ToString() });
comm.Parameters.AddRange(paras.ToArray <OleDbParameter>());
//3.执行命令
comm.ExecuteNonQuery();
}
catch (Exception e)
{
Console.Write(e.Message);
throw new Exception();
}
finally
{
//4.关闭连接
if (conn != null)
{
DBManage.closeConn(conn);
}
}
}
//发表帖子
public void add(Post post)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
try
{
conn = DBManage.getConn();
conn.Open();
comm = conn.CreateCommand();
comm.CommandText = "insert into post(userId,title,content,postTime,userIp,replyNum,browseNum) values (@userId,@title,@content,@postTime,@userIp,@replyNum,@browseNum)";
//动态设置参数
List <OleDbParameter> paras = new List <OleDbParameter>();
//paras.Add(new OleDbParameter() { ParameterName = "@postId", Value = post.PostId });
paras.Add(new OleDbParameter()
{
ParameterName = "@userId", Value = post.UserId
});
paras.Add(new OleDbParameter()
{
ParameterName = "@title", Value = post.Title
});
paras.Add(new OleDbParameter()
{
ParameterName = "@content", Value = post.Content
});
paras.Add(new OleDbParameter()
{
ParameterName = "@postTime", Value = post.PostTime.ToString()
});
paras.Add(new OleDbParameter()
{
ParameterName = "@userIp", Value = post.UserIp
});
paras.Add(new OleDbParameter()
{
ParameterName = "@replyNum", Value = post.ReplyNum
});
paras.Add(new OleDbParameter()
{
ParameterName = "@browseNum", Value = post.BrowseNum
});
comm.Parameters.AddRange(paras.ToArray <OleDbParameter>());
//3.执行命令
comm.ExecuteNonQuery();
}
catch (Exception e)
{
Console.Write(e.Message);
throw new Exception();
}
finally {
if (conn != null)
{
DBManage.closeConn(conn);
}
}
}
