//查询单个实体对象 public BookInfo GetEntity(string bookGuid) { string sql = selectSql + " where bookguid=@bookguid"; SqlParameter pms = new SqlParameter("@bookguid", bookGuid); SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text, pms); BookInfo bookInfo = new BookInfo(); if (reader.HasRows) { while (reader.Read()) { bookInfo.BookGuid = reader.GetString(0); bookInfo.BookName = reader.GetString(1); bookInfo.BookType = reader.GetString(2); bookInfo.SuitAble = reader.GetString(3); bookInfo.BuyDate = reader.GetDateTime(4); bookInfo.Count = reader.GetInt32(5); bookInfo.Remark = reader.GetString(6); } } reader.Close(); return(bookInfo); }
//根据key分页查询图书 public List <BookInfo> Search(string key, int pageIndex, int pageSize) { //select [column1] ,[column2]... ,[columnN] from[tableName] order by[columnM] //offset(pageIndex-1)*pageSize rows fetch next pageSize rows only if (key == null) { key = ""; } string sql = selectSql + "\nwhere booktype=@booktype order by bookguid offset(@pageIndex)*@pageSize rows fetch @pageSize rows only"; List <BookInfo> bookList = new List <BookInfo>(); SqlParameter[] pms = new SqlParameter[] { new SqlParameter("@booktype", key), new SqlParameter("@pageIndex", pageIndex), new SqlParameter("@pageSize", pageSize) }; SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text, pms); if (reader.HasRows) { while (reader.Read()) { BookInfo bookInfo = new BookInfo(); bookInfo.BookGuid = reader.GetString(0); bookInfo.BookName = reader.GetString(1); bookInfo.BookType = reader.GetString(2); bookInfo.SuitAble = reader.GetString(3); bookInfo.BuyDate = reader.GetDateTime(4); bookInfo.Count = reader.GetInt32(5); bookInfo.Remark = reader.GetString(6); bookList.Add(bookInfo); } } reader.Close(); return(bookList); }
//获取全部图书 不分页 public List <BookInfo> SearchAllBookWithoutPaging() { string sql = selectSql; List <BookInfo> bookList = new List <BookInfo>(); SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text); if (reader.HasRows) { while (reader.Read()) { BookInfo bookInfo = new BookInfo(); bookInfo.BookGuid = reader.GetString(0); bookInfo.BookName = reader.GetString(1); bookInfo.BookType = reader.GetString(2); bookInfo.SuitAble = reader.GetString(3); bookInfo.BuyDate = reader.GetDateTime(4); bookInfo.Count = reader.GetInt32(5); bookInfo.Remark = reader.GetString(6); bookList.Add(bookInfo); } } reader.Close(); return(bookList); }
//添加图书信息 public int AddBookInfo(BookInfo book) { string sql = @"insert into BookInfo select @BookId,@BookName,@TimeIn,@BookTypeId,@Author,@PinYinCode,@Translator,@Language,@BookNumber,@Price,@Layout,@Address,@ISBN,@Versions,@BookRemark"; SqlParameter[] sp = { new SqlParameter("@BookId", book.BookId), new SqlParameter("@BookName", book.BookName), new SqlParameter("@TimeIn", book.TimeIn), new SqlParameter("@BookTypeId", book.BookTypeId), new SqlParameter("@Author", book.Author), new SqlParameter("@PinYinCode", book.PinYinCode), new SqlParameter("@Translator", book.Translator), new SqlParameter("@Language", book.Language), new SqlParameter("@BookNumber", book.BookNumber), new SqlParameter("@Price", book.Price), new SqlParameter("@Layout", book.Layout), new SqlParameter("@Address", book.Address), new SqlParameter("@ISBN", book.ISBN), new SqlParameter("@Versions", book.Versions), new SqlParameter("@BookRemark", book.BookRemark), }; return(DBhelp.Create().ExecuteNonQuery(sql, sp: sp)); }
//查询分页的图书列表 public List <BookInfo> GetBookList(int pageIndex, int pageSize, string key, DateTime?beginDate, DateTime?endDate, string sortField, string sortOrder) { // " order by bookguid offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; string sql = null; if (!string.IsNullOrEmpty(key)) { if (key == "全部") { if (string.IsNullOrEmpty(sortField)) { sql = selectSql + " order by bookguid offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (!string.IsNullOrEmpty(sortField) && string.IsNullOrEmpty(sortOrder)) { sql = selectSql + " order by " + sortField + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (!string.IsNullOrEmpty(sortField) && !string.IsNullOrEmpty(sortOrder)) { sql = selectSql + " order by " + sortField + " " + sortOrder + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } } else { if (string.IsNullOrEmpty(sortField)) { sql = selectSql + " where booktype='" + key + "' order by bookguid offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; if (beginDate != null && endDate == null) { sql = selectSql + "where booktype='" + key + "'and buydate > '" + beginDate + "' order by bookguid offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate == null && endDate != null) { sql = selectSql + "where booktype='" + key + "'and buydate < '" + endDate + "' order by bookguid offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate != null && endDate != null) { sql = selectSql + "where booktype='" + key + "'and buydate between '" + beginDate + "'and '" + endDate + "' order by bookguid offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } } else if (!string.IsNullOrEmpty(sortField) && string.IsNullOrEmpty(sortOrder)) { sql = selectSql + " where booktype='" + key + "' order by " + sortField + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; if (beginDate != null && endDate == null) { sql = selectSql + "where booktype='" + key + "'and buydate > '" + beginDate + "' order by " + sortField + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate == null && endDate != null) { sql = selectSql + "where booktype='" + key + "'and buydate < '" + endDate + "' order by " + sortField + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate != null && endDate != null) { sql = selectSql + "where booktype='" + key + "'and buydate between '" + beginDate + "'and '" + endDate + "' order by " + sortField + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } } else if (!string.IsNullOrEmpty(sortField) && !string.IsNullOrEmpty(sortOrder)) { sql = selectSql + " where booktype='" + key + "' order by " + sortField + " " + sortOrder + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; if (beginDate != null && endDate == null) { sql = selectSql + "where booktype='" + key + "'and buydate > '" + beginDate + "' order by " + sortField + " " + sortOrder + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate == null && endDate != null) { sql = selectSql + "where booktype='" + key + "'and buydate < '" + endDate + "' order by " + sortField + " " + sortOrder + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate != null && endDate != null) { sql = selectSql + "where booktype='" + key + "'and buydate between '" + beginDate + "'and '" + endDate + "' order by " + sortField + " " + sortOrder + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } } } } else { if (string.IsNullOrEmpty(sortField)) { sql = selectSql + " order by bookguid offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; if (beginDate != null && endDate == null) { sql = selectSql + "where buydate > '" + beginDate + "' order by bookguid offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate == null && endDate != null) { sql = selectSql + "where buydate < '" + endDate + "' order by bookguid offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate != null && endDate != null) { sql = selectSql + "where buydate between '" + beginDate + "'and '" + endDate + "' order by bookguid offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } } else if (!string.IsNullOrEmpty(sortField) && string.IsNullOrEmpty(sortOrder)) { sql = selectSql + " order by " + sortField + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; if (beginDate != null && endDate == null) { sql = selectSql + "where buydate > '" + beginDate + "' order by " + sortField + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate == null && endDate != null) { sql = selectSql + "where buydate < '" + endDate + "' order by " + sortField + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate != null && endDate != null) { sql = selectSql + "where buydate between '" + beginDate + "'and '" + endDate + "' order by " + sortField + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } } else if (!string.IsNullOrEmpty(sortField) && !string.IsNullOrEmpty(sortOrder)) { sql = selectSql + " order by " + sortField + " " + sortOrder + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; if (beginDate != null && endDate == null) { sql = selectSql + "where buydate > '" + beginDate + "' order by " + sortField + " " + sortOrder + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate == null && endDate != null) { sql = selectSql + "where buydate < '" + endDate + "' order by " + sortField + " " + sortOrder + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } else if (beginDate != null && endDate != null) { sql = selectSql + "where buydate between '" + beginDate + "'and '" + endDate + "' order by " + sortField + " " + sortOrder + " offset(@pageIndex)*@pageSize rows fetch next @pageSize rows only"; } } } List <BookInfo> bookList = new List <BookInfo>(); SqlParameter[] pms = new SqlParameter[] { new SqlParameter("@pageIndex", pageIndex), new SqlParameter("@pageSize", pageSize) }; SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text, pms); if (reader.HasRows) { while (reader.Read()) { BookInfo bookInfo = new BookInfo(); bookInfo.BookGuid = reader.GetString(0); bookInfo.BookName = reader.GetString(1); bookInfo.BookType = reader.GetString(2); bookInfo.SuitAble = reader.GetString(3); bookInfo.BuyDate = reader.GetDateTime(4); bookInfo.Count = reader.GetInt32(5); bookInfo.Remark = reader.GetString(6); bookList.Add(bookInfo); } } reader.Close(); return(bookList); }
public DALHelp() { this.book = new BookInfo(); }