bookdal.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 BookDal:IBook
    {
        #region IBook 成员

       public List<Book> selctBook(string sql, SqlParameter[] p)
        {
            List<Book> list = new List<Book>();
            using (SqlDataReader read=(p!=null)?DBHelp.Selparam(sql, p):DBHelp.Sel(sql)) 
            {
                while(read.Read())
                {
                    Book book=new Book();
                    book.Id=int.Parse(read["Id"].ToString());
                    book.Title=read["title"].ToString();
                    book.Author=read["Author"].ToString();
                    book.PublishDate=DateTime.Parse(read["PublishDate"].ToString());
                    book.ISBN=read["ISBN"].ToString();
                    book.WordsCount=int.Parse(read["WordsCount"].ToString());
                    book.UnitPrice=Double.Parse(read["UnitPrice"].ToString());
                    book.ContentDescription=read["ContentDescription"].ToString();
                    book.AuthorDescription=read["AuthorDescription"].ToString();
                    book.EditorComment=read["EditorComment"].ToString();
                    book.TOC = read["TOC"].ToString();
                    book.Clicks = int.Parse(read["Clicks"].ToString());
                    book.TypeId=int.Parse(read["TypeId"].ToString());
                    book.BookType.Name = read["t_name"].ToString();
                    book.PublisherId=int.Parse(read["PublisherId"].ToString());
                    book.Pub.Name = read["p_name"].ToString();
                    book.Img=read["Img"].ToString();
                    book.SimplePY=read["SimplePY"].ToString();
                    book.Pj=int.Parse(read["pj"].ToString());
                    list.Add(book);
                }
                return list;
            }
        }

       public List<Book> SeachBook(string seach, Page pager, string orderbyType, string index,string typeId)
       {
           string sqlStr = "";
           string ordername = "";
           List<Book> list = new List<Book>();
           sqlStr = "select top " + pager.getPageSize();
           sqlStr += " b.id,b.Title,b.Author,b.PublishDate,b.ISBN,b.WordsCount,b.UnitPrice,";
           sqlStr += " b.ContentDescription,b.AuthorDescription,b.EditorComment,b.TOC,b.Clicks,";
           sqlStr += " b.TypeId,bt.[name] as t_name,b.PublisherId,p.[name] as p_name,b.Img,b.SimplePY,b.pj";
           sqlStr += " from books as b,booktype as bt,Publishers as p";
           sqlStr += " where b.typeid=bt.id and b.PublisherId=p.id ";

           if (seach.Equals("") || seach.Equals("全部"))
           {
               sqlStr += " and b.id not in";
               sqlStr += " (select top " + (pager.getCurrentPage() - 1) * (pager.getPageSize());
               sqlStr += " b.id from books where b.typeid=bt.id and b.PublisherId=p.id)  ";
           }
           else
           {

               sqlStr += " and b.title  like '%" + seach + "%' and b.id not in";
               sqlStr += " (select top " + (pager.getCurrentPage() - 1) * (pager.getPageSize());
               sqlStr += " b.id from books where b.typeid=bt.id and b.PublisherId=p.id and b.title like '%" + seach + "%')";
           }
           if (!typeId.Equals("")) 
               sqlStr += " and b.TypeId="+int.Parse(typeId);
           if (index != null)
           {
               switch (index)
               {
                   case "0"://推荐
                       sqlStr += " and b.tuijian=1";
                       break;
                   case "1"://最新
                       ordername = "PublishDate desc";
                       break;
                   case "2"://热搜
                       ordername = "b.Clicks desc";
                       break;
                   default:
                       break;
               }   
           }
           ordername = (orderbyType.Equals("price")) ? "b.unitprice" : "PublishDate desc";
           sqlStr += " order by " + ordername;
           list = selctBook(sqlStr, null);
           return list;
       }

        public List<Book> selctBookTJ(Model.Page pager)
        {
            string sqlStr = "select top " + pager.getPageSize();
            sqlStr += " b.id,b.Title,b.Author,b.PublishDate,b.ISBN,b.WordsCount,b.UnitPrice,";
            sqlStr += " b.ContentDescription,b.AuthorDescription,b.EditorComment,b.TOC,b.Clicks,";
            sqlStr += " b.TypeId,bt.[name] as t_name,b.PublisherId,p.[name] as p_name,b.Img,b.SimplePY,b.pj";
            sqlStr += " from books as b,booktype as bt,Publishers as p";
            sqlStr += " where b.typeid=bt.id and b.PublisherId=p.id and b.tuijian=0 and b.id not in";
            sqlStr += " (select top " + (pager.getCurrentPage() - 1) * (pager.getPageSize());
            sqlStr += " b.id from books where b.typeid=bt.id and b.PublisherId=p.id and b.tuijian=0)";
            List<Book> list = new List<Book>();
            list = selctBook(sqlStr,null);
            return list;
        }

        public List<Book> selctBookNew(Page pager)
        {
            string sqlStr = "select top " + pager.getPageSize();
            sqlStr += " b.id,b.Title,b.Author,b.PublishDate,b.ISBN,b.WordsCount,b.UnitPrice,";
            sqlStr += " b.ContentDescription,b.AuthorDescription,b.EditorComment,b.TOC,b.Clicks,";
            sqlStr += " b.TypeId,bt.[name] as t_name,b.PublisherId,p.[name] as p_name,b.Img,b.SimplePY,b.pj";
            sqlStr += " from books as b,booktype as bt,Publishers as p";
            sqlStr += " where b.typeid=bt.id and b.PublisherId=p.id and b.id not in";
            sqlStr += " (select top " + (pager.getCurrentPage() - 1) * (pager.getPageSize());
            sqlStr += " b.id from books where b.typeid=bt.id and b.PublisherId=p.id )";
            sqlStr += " order by b.PublishDate desc";
            List<Book> list = new List<Book>();
            list = selctBook(sqlStr,null);
            return list;
        }

        public List<Book> selctBookHot(Page pager)
        {
            string sqlStr = "select top " + pager.getPageSize();
            sqlStr += " b.id,b.Title,b.Author,b.PublishDate,b.ISBN,b.WordsCount,b.UnitPrice,";
            sqlStr += " b.ContentDescription,b.AuthorDescription,b.EditorComment,b.TOC,b.Clicks,";
            sqlStr += " b.TypeId,bt.[name] as t_name,b.PublisherId,p.[name] p_name,b.Img,b.SimplePY,b.pj";
            sqlStr += " from books as b,booktype as bt,Publishers as p";
            sqlStr += " where b.typeid=bt.id and b.PublisherId=p.id and b.id not in";
            sqlStr += " (select top " + (pager.getCurrentPage() - 1) * (pager.getPageSize());
            sqlStr += " b.id from books where b.typeid=bt.id and b.PublisherId=p.id)";
            sqlStr += " order by b.Clicks desc";
            List<Book> list = new List<Book>();
            list = selctBook(sqlStr,null);
            return list;
        }

        public List<Book> selctBookInfo(int id)
        {
            string sqlStr = "select ";
            sqlStr += " b.id,b.Title,b.Author,b.PublishDate,b.ISBN,b.WordsCount,b.UnitPrice,";
            sqlStr += " b.ContentDescription,b.AuthorDescription,b.EditorComment,b.TOC,b.Clicks,";
            sqlStr += " b.TypeId,bt.[name] as t_name,b.PublisherId,p.[name] as p_name,b.Img,b.SimplePY,b.pj";
            sqlStr += " from books as b,booktype as bt,Publishers as p";
            sqlStr += " where b.typeid=bt.id and b.PublisherId=p.id and b.id=@id";
            //sqlStr += " (select top " + (pager.getCurrentPage() - 1) * (pager.getPageSize());
            //sqlStr += " b.id from books where b.typeid=bt.id and b.PublisherId=p.id and b.title like %@title%)";
            SqlParameter[] p = new SqlParameter[]
            {
                new SqlParameter("@id",id)
            };
            List<Book> list = new List<Book>();
            list = selctBook(sqlStr,p);
            return list;
        }

        public bool updateBook(Book book)
        {
            string sqlStr = "update  books  set";
            sqlStr += " Title=@Title,Author=@Author,PublishDate=@PublishDate,ISBN=@ISBN,WordsCount=@WordsCount,UnitPrice=@UnitPrice, ";
            sqlStr += " ContentDescription=@ContentDescription,AuthorDescription=@AuthorDescription,EditorComment=@EditorComment, ";
            sqlStr+= " TOC=@TOC,TypeId=@TypeId,PublisherId=@PublisherId,Img=@Img,SimplePY=@SimplePY  where id=@id";
            SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@Title",book.Title),
                new SqlParameter("@Author",book.Author),
                new SqlParameter("@PublishDate",book.PublishDate),
                new SqlParameter("@ISBN",book.ISBN),
                new SqlParameter("@WordsCount",book.WordsCount),
                new SqlParameter("@UnitPrice",book.UnitPrice),
                new SqlParameter("@ContentDescription",book.ContentDescription),
                new SqlParameter("@AuthorDescription",book.AuthorDescription),
                new SqlParameter("@EditorComment",book.EditorComment),
                new SqlParameter("@TOC",book.TOC),
                new SqlParameter("@TypeId",book.TypeId),
                new SqlParameter("@PublisherId",book.PublisherId),
                new SqlParameter("@Img",book.Img),
                new SqlParameter("@SimplePY",book.SimplePY),
                new SqlParameter("@id",book.Id)
            };
            return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
        }

        public bool addBook(Book book)
        {
            string sqlStr = "insert into books ";
            sqlStr += "(Title,Author,PublishDate,ISBN,WordsCount,UnitPrice,ContentDescription,AuthorDescription,EditorComment,TOC,TypeId,PublisherId,Img,SimplePY,tuijian)  ";
            sqlStr += "values (@Title,@Author,@PublishDate,@ISBN,@WordsCount,@UnitPrice,@ContentDescription,@AuthorDescription,@EditorComment,@TOC,@TypeId,@PublisherId,@Img,@SimplePY,@tuijian)";
            SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@Title",book.Title),
                new SqlParameter("@Author",book.Author),
                new SqlParameter("@PublishDate",book.PublishDate),
                new SqlParameter("@ISBN",book.ISBN),
                new SqlParameter("@WordsCount",book.WordsCount),
                new SqlParameter("@UnitPrice",book.UnitPrice),
                new SqlParameter("@ContentDescription",book.ContentDescription),
                new SqlParameter("@AuthorDescription",book.AuthorDescription),
                new SqlParameter("@EditorComment",book.EditorComment),
                new SqlParameter("@TOC",book.TOC),
                new SqlParameter("@TypeId",book.TypeId),
                new SqlParameter("@PublisherId",book.PublisherId),
                new SqlParameter("@Img",book.Img),
                new SqlParameter("@SimplePY",book.SimplePY),
                new SqlParameter("@tuijian",book.Tuijian)
            };
            return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
        }

       public bool delBook(List<string> lst)
        {
            string id = "";
            for (int i = 0; i < lst.Count; i++)
            {
                id += lst[i] + ",";
            }
            string sqlStr = "delete from books where id in ("+id.Substring(0,id.Length-1)+") ";
            return DBHelp.Rework(sqlStr) > 0 ? true : false;
        }

        public bool clickBook(int id)
        {
            string sqlStr = "update books set Clicks=Clicks+1 where id=@id";
            SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@id",id)
            };
            return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
        }

        public bool plBook(int id)
        {
            string sqlStr = "update books set pj=pj+1 where id=@id";
            SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@id",id)
            };
            return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
        }

       public bool typeBook(string lst,int id)
       {
           string sqlStr = "update books set typeId=@typeId where id in ("+lst+")";
           SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@typeId",id)
            };
           return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
       }

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

        #endregion

       // #region IBook 成员


       //public List<Book> selctBookType(int id, Page pager,string orderbyType)
       // {
       //     string sqlStr = "";
       //     string ordername = "";
       //     List<Book> list = new List<Book>();
       //     sqlStr = "select top " + pager.getPageSize();
       //     sqlStr += " b.id,b.Title,b.Author,b.PublishDate,b.ISBN,b.WordsCount,b.UnitPrice,";
       //     sqlStr += " b.ContentDescription,b.AuthorDescription,b.EditorComment,b.TOC,b.Clicks,";
       //     sqlStr += " b.TypeId,bt.[name] as t_name,b.PublisherId,p.[name] as p_name,b.Img,b.SimplePY,b.pj";
       //     sqlStr += " from books as b,booktype as bt,Publishers as p";
       //     sqlStr += " where b.typeid=bt.id and b.PublisherId=p.id and b.TypeId=@id";
       //     sqlStr += " and b.id not in";
       //     sqlStr += " (select top " + (pager.getCurrentPage() - 1) * (pager.getPageSize());
       //     sqlStr += " b.id from books where b.typeid=bt.id and b.PublisherId=p.id)  ";
       //     ordername = (orderbyType.Equals("price")) ? "b.unitprice" : "PublishDate desc";
       //     sqlStr += " order by " + ordername;
       //     SqlParameter[] p = new SqlParameter[] 
       //     {
       //         new SqlParameter("@typeId",id)
       //     };
       //     list = selctBook(sqlStr, p);
       //     return list;
       // }

       // #endregion
    }
}