readercommentsdal.cs

一个简单的网上书店

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using BLL;
using BLL.interfaces;

namespace DAL
{
   public  class ReaderCommentsDal:IReaderComments
    {
        #region IReaderComments 成员

        public bool addReaderC(ReaderComments rdc)
        {
            string sqlStr = "insert into readercomments (bookid,userid,comment) values (@bookid,@userid,@comment) ";
            SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@bookid",rdc.BookId),
                new SqlParameter("@userid",rdc.UserId),
                new SqlParameter("@comment",rdc.Comment)
            };
            return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
        }

        public bool updateReaderC(ReaderComments rdc)
        {
            string sqlStr = "update readercomments set bookid=@bookid,userid=@userid,comment=@comment where id=@id ";
            SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@bookid",rdc.BookId),
                new SqlParameter("@userid",rdc.UserId),
                new SqlParameter("@comment",rdc.Comment),
                new SqlParameter("@id",rdc.Id)
            };
            return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
        }

        public bool delReaderC(int id)
        {
            string sqlStr = "delete from readercomments where id=@id";
            SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@id",id)
            };
            return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
        }

        public List<ReaderComments> selReaderC(int id)
        {
            string sqlStr = "select  r.id,bookid,r.userid,u.[name],comment,date from readercomments as r,users as u   ";
            sqlStr += "where bookid=@id and r.userid=u.id";
           //sqlStr += "(select top " + (pager.getCurrentPage() - 1) * (pager.getPageSize()) + " id from readercomments where bookid=@id)";
            SqlParameter[] p = new SqlParameter[]
            {
                new SqlParameter("@id",id)
            };
            List<ReaderComments> list = new List<ReaderComments>();
            using (SqlDataReader read = DBHelp.Selparam(sqlStr,p))
            {
                while (read.Read())
                {
                    ReaderComments rdc = new ReaderComments();
                    rdc.Id = int.Parse(read["id"].ToString());
                    rdc.UserId=int.Parse(read["userid"].ToString());
                    rdc.User.Name = read["name"].ToString();
                    rdc.Comment=read["comment"].ToString();
                    list.Add(rdc);
                }
                return list;
            }
        }

        public int count()
        {
            int count = 0;
            string sqlStr = "select count(*) from readercomments";
            count = Count.getCount(sqlStr);
            return count;
        }

        #endregion

        #region IReaderComments 成员


        public List<ReaderComments> selRdc()
        {
            string sqlStr = "select  r.id,r.bookid,r.userid,u.[name],comment,date,b.Title from readercomments as r,users as u,books as b  ";
            sqlStr += "where r.userid=u.id and r.bookid=b.id order by date desc";
            //sqlStr += "(select top " + (pager.getCurrentPage() - 1) * (pager.getPageSize()) + " id from readercomments where bookid=@id)";
            //SqlParameter[] p = new SqlParameter[]
            //{
            //    new SqlParameter("@id",id)
            //};
            List<ReaderComments> list = new List<ReaderComments>();
            using (SqlDataReader read = DBHelp.Sel(sqlStr))
            {
                ReaderComments rdc = new ReaderComments();
                while (read.Read())
                {
                    
                    rdc.Id = int.Parse(read["id"].ToString());
                    rdc.UserId = int.Parse(read["userid"].ToString());
                    rdc.User.Name = read["name"].ToString();
                    rdc.Comment = read["comment"].ToString();
                    rdc.Date = DateTime.Parse(read["date"].ToString());
                    rdc.Book.Title = read["title"].ToString();
                    list.Add(rdc);
                }
                return list;
            }
        }

        #endregion
    }
}