public IActionResult getParticularShow(long showId) { DBHelper dbHelper = new DBHelper(); try { string queryShow = "SELECT INTRODUCTION,NAME,PHOTO FROM SHOW WHERE ID=:showId"; OracleParameter[] parameterForQuery = { new OracleParameter(":showId", OracleDbType.Long, 10) }; parameterForQuery[0].Value = showId; DataTable dtForShow = dbHelper.ExecuteTable(queryShow, parameterForQuery); var res = new ParticularShow(); res.slots = GeneralSlotController.getGeneralSlotByShow(showId); res.comments = CommentController.getCommentByShow(showId); res.labels = LabelController.getLabelByShow(showId); res.goods = GeneralGoodsController.getGeneralGoodsByShow(showId); res.introduction = dtForShow.Rows[0]["INTRODUCTION"].ToString(); res.name = dtForShow.Rows[0]["NAME"].ToString(); res.image = dtForShow.Rows[0]["PHOTO"].ToString() == string.Empty ? null : Convert.ToBase64String((byte[])(dtForShow.Rows[0]["PHOTO"])); string queryRate = "SELECT AVG(RATE) AVG_RATE,SHOW_ID FROM COMM WHERE SHOW_ID=:showId GROUP BY SHOW_ID"; DataTable dtForRate = dbHelper.ExecuteTable(queryRate, parameterForQuery); res.avgRate = (dtForRate.Rows.Count != 0) ? double.Parse(dtForRate.Rows[0]["AVG_RATE"].ToString()):null; return(Ok(new JsonResult(res))); } catch (OracleException oe) { return(BadRequest("数据库请求错误 " + "错误代码 " + oe.Number.ToString())); } }
public IActionResult getGeneralShows() { DBHelper dbHelper = new DBHelper(); try { var res = new List <GeneralShow>(); string queryShow = "SELECT ID,NAME,PHOTO FROM SHOW WHERE IS_VALID = 1 ORDER BY ID"; DataTable dtShow = dbHelper.ExecuteTable(queryShow); string queryAvgRate = "SELECT AVG(RATE) AVG_RATE,SHOW_ID FROM COMM GROUP BY SHOW_ID"; DataTable dtAvgRate = dbHelper.ExecuteTable(queryAvgRate); if (dtShow.Rows.Count == 0) { return(NotFound("暂无演出")); } else { foreach (DataRow row in dtShow.Rows) { long id = long.Parse(row["ID"].ToString()); res.Add(new GeneralShow() { showId = id, name = row["NAME"].ToString(), image = row["PHOTO"].ToString() == string.Empty ? null : Convert.ToBase64String((byte[])(row["PHOTO"])), labels = LabelController.getLabelByShow(id) }); } foreach (DataRow row in dtAvgRate.Rows) { long id = long.Parse(row["SHOW_ID"].ToString()); long idx = 0; for (int i = 0; i < res.Count; i++) { if (res[i].showId == id) { idx = i; break; } } res[(int)idx].avgRate = double.Parse(row["AVG_RATE"].ToString()); } return(Ok(new JsonResult(res))); } } catch (OracleException oe) { return(BadRequest("数据库请求错误 " + "错误代码 " + oe.Number.ToString())); } }
public IActionResult getSearchResult(string search) { if (search.Length > 30) { return(BadRequest("搜索条件过长")); } else { DBHelper dbHelper = new DBHelper(); try { var res = new SearchResult(); res.shows = new List <GeneralShow>(); res.goods = new List <GeneralGoods>(); // 查询演出信息 string queryShow = "SELECT ID,NAME,PHOTO FROM SHOW WHERE IS_VALID = 1 AND NAME LIKE :searchString ORDER BY ID"; OracleParameter[] parameterForShow = { new OracleParameter(":searchString", OracleDbType.Varchar2, 100) }; parameterForShow[0].Value = "%" + search + "%"; DataTable dtShow = dbHelper.ExecuteTable(queryShow, parameterForShow); foreach (DataRow row in dtShow.Rows) { long id = long.Parse(row["ID"].ToString()); string queryAvgRate = "SELECT AVG(RATE) RATE FROM COMM WHERE SHOW_ID = :showId GROUP BY SHOW_ID"; OracleParameter[] parameterForAvgRate = { new OracleParameter(":showId", OracleDbType.Long, 10) }; parameterForAvgRate[0].Value = id; DataTable dtAvgRate = dbHelper.ExecuteTable(queryAvgRate, parameterForAvgRate); res.shows.Add(new GeneralShow() { labels = LabelController.getLabelByShow(id), showId = id, image = row["PHOTO"].ToString() == string.Empty ? null : Convert.ToBase64String((byte[])(row["PHOTO"])), name = row["NAME"].ToString(), avgRate = dtAvgRate.Rows.Count != 0 ? double.Parse(dtAvgRate.Rows[0]["RATE"].ToString()) : null }); } // 查询周边信息 string queryGoods = "SELECT ID,NAME,PHOTO FROM GOODS WHERE IS_VALID = 1 AND NAME LIKE :searchString ORDER BY ID"; OracleParameter[] parameterForGoods = { new OracleParameter(":searchString", OracleDbType.Varchar2, 30) }; parameterForGoods[0].Value = "%" + search + "%"; DataTable dtGoods = dbHelper.ExecuteTable(queryGoods, parameterForGoods); foreach (DataRow row in dtGoods.Rows) { res.goods.Add(new GeneralGoods() { id = long.Parse(row["ID"].ToString()), name = row["NAME"].ToString(), image = row["PHOTO"].ToString() == string.Empty ? null : Convert.ToBase64String((byte[])(row["PHOTO"])) }); } if (res.goods.Count == 0 && res.shows.Count == 0) { return(NotFound("无匹配结果")); } else { return(Ok(new JsonResult(res))); } } catch (OracleException) { return(BadRequest("数据库请求错误")); } } }