// 分页查询 图书模糊检索
public ActionResult GetList(int page, int size, string BiName, int? BtId, string BiAuthor, string BiLocation, string BiPress, string BiIsbn)
{
// 定义初始化sql
string sqlInit =
"select bi.bi_id BiId,bi.bi_name BiName,bi.bt_id BtId,bt.bt_name BtName,bi.bi_press BiPress,bi.bi_isbn BiIsbn,bi.bi_author BiAuthor,bi.bi_location BiLocation,bi.bi_price BiPrice,bi.bi_pages BiPages,bi.bi_addtime BiAddtime,bi.bi_num BiNum,bi.bi_cover_picture BiCoverPicture,bi.deleted Deleted,bi.bi_borrow_num BiBorrowNum from LibrarySystem03.ls_bookinfo bi LEFT JOIN LibrarySystem03.ls_booktype bt on bi.bt_id = bt.bt_id where bi.deleted=0 ";
List<MySqlParameter> parameters = new List<MySqlParameter>();
string sql = sqlInit;
// 判断有无需要模糊拼接
if ((BiName != null && BiName != "") || (BtId != null && BtId != 0) || (BiAuthor != null && BiAuthor != "") || (BiLocation != null && BiLocation != "") || (BiPress != null && BiPress != "") || (BiIsbn != null && BiIsbn != ""))
{
// 书名字段
if (BiName != null && BiName != "")
{
sql = sql + " AND bi.bi_name LIKE @BiName ";
var likeBiName = $"%{BiName}%";
parameters.Add(new MySqlParameter("@BiName", likeBiName));
}
// 类别下拉框加载
if (BtId != null && BtId != 0)
{
sql = sql + " AND bi.bt_id =@BtId";
parameters.Add(new MySqlParameter("@BtId", BtId));
}
// 作者检索
if (BiAuthor != null && BiAuthor != "")
{
sql = sql + " AND bi.bi_author LIKE @BiAuthor ";
var likeBiAuthor = $"%{BiAuthor}%";
parameters.Add(new MySqlParameter("@BiAuthor", likeBiAuthor));
}
// 位置检索拼接
if (BiLocation != null && BiLocation != "")
{
sql = sql + " AND bi.bi_location LIKE @BiLocation ";
var likeBiLocation = $"%{BiLocation}%";
parameters.Add(new MySqlParameter("@BiLocation", likeBiLocation));
}
// 出版社拼接
if (BiPress != null && BiPress != "")
{
sql = sql + " AND bi.bi_press LIKE @BiPress ";
var likeBiPress = $"%{BiPress}%";
parameters.Add(new MySqlParameter("@BiPress", likeBiPress));
}
// ISBN数据sql拼接
if (BiIsbn != null && BiIsbn != "")
{
sql = sql + " AND bi.bi_isbn LIKE @BiIsbn ";
var likeBiIsbn = $"%{BiIsbn}%";
parameters.Add(new MySqlParameter("@BiIsbn", likeBiIsbn));
}
// 分页sql拼接
string sqlPage = sql + " ORDER BY bi.bi_id DESC LIMIT @page,@size;";
parameters.Add(new MySqlParameter("@page", (page - 1) * size));
parameters.Add(new MySqlParameter("@size", size));
MySqlParameter[] parames = parameters.ToArray();
try
{
// 返回JSON数据
return Json(CommonResult.Success(db.Database.SqlQuery<BookAndTypeVO>(@sqlPage, parames).ToList(),
db.Database.SqlQuery<BookAndTypeVO>(sql, parames).ToList().Count),
JsonRequestBehavior.AllowGet);
}
catch (Exception)
{
return Json(CommonResult.Failed(), JsonRequestBehavior.AllowGet);
}
}
// 如果没有检索条件,则默认返回分页数据
else
{
sql = sql + " ORDER BY bi.bi_id DESC LIMIT @page,@size;";
parameters.Add(new MySqlParameter("@page", (page - 1) * size));
parameters.Add(new MySqlParameter("@size", size));
MySqlParameter[] parames = parameters.ToArray();
try
{
return Json(
CommonResult.Success(db.Database.SqlQuery<BookAndTypeVO>(sql, parames).ToList(),
db.BookModels.ToList().Count),
JsonRequestBehavior.AllowGet);
}
catch (Exception)
{
return Json(CommonResult.Failed(), JsonRequestBehavior.AllowGet);
}
}
}