//查询单个实体对象
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();
}
