//修改用户图片 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); } } }
//修改浏览次数 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); } } }
//通过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); } } }
//通过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); } } }
//通过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); } } }