public IEnumerable <PictureSummary> GetTagPictureByName(string name, int index, int size)
{
DataTable dataTable = DB.Client.ExecuteDataTable(SqlServerClient.PagingBuild(@"
SELECT
pic.picId,
pic.picSize,
pic.picNum,
pic.picName,
(SELECT TOP 1 picSrc.picPreview FROM PictureSource picSrc WHERE picSrc.picId = pic.picId) AS picPreview,
Row_Number() OVER(ORDER BY pic.picId) RowNumber
FROM PictureTag tag
INNER JOIN PictureTagMap map ON map.tagId = tag.tagId
LEFT JOIN Picture pic ON pic.picId = map.picId
WHERE tag.tagName LIKE @tag", index, size),
new SqlParameter("@tag", $"%{name}%"),
new SqlParameter("@pageSize", size),
new SqlParameter("@pageIndex", index));
List <PictureSummary> pictures = new List <PictureSummary>();
foreach (DataRow item in dataTable.Rows)
{
pictures.Add(new PictureSummary(item));
}
return(pictures);
}
public IEnumerable <PictureSummary> GetChannelPicture(int id, int index, int size)
{
DataTable dataTable = DB.Client.ExecuteDataTable(SqlServerClient.PagingBuild(@"
SELECT
pic.picId,
pic.picSize,
pic.picNum,
pic.picName,
(SELECT TOP 1 picSrc.picPreview FROM PictureSource picSrc WHERE picSrc.picId = pic.picId) AS picPreview,
Row_Number() OVER(ORDER BY pic.picId) RowNumber
FROM Picture pic WHERE pic.picChannel = @channelId", index, size),
new SqlParameter("@channelId", id),
new SqlParameter("@pageSize", size),
new SqlParameter("@pageIndex", index));
List <PictureSummary> channels = new List <PictureSummary>();
foreach (DataRow item in dataTable.Rows)
{
channels.Add(new PictureSummary(item));
}
return(channels);
}
public IEnumerable <PictureSummary> GetPictureByIntro(string keyword, int index, int size)
{
DataTable dataTable = DB.Client.ExecuteDataTable(SqlServerClient.PagingBuild(@"
SELECT
pic.picId,
pic.picSize,
pic.picNum,
pic.picName,
(SELECT TOP 1 picSrc.picPreview FROM PictureSource picSrc WHERE picSrc.picId = pic.picId) AS picPreview,
Row_Number() OVER(ORDER BY pic.picId) RowNumber
FROM Picture pic WHERE pic.picIntro LIKE @keyword", index, size),
new SqlParameter("@keyword", $"%{keyword}%"),
new SqlParameter("@pageSize", size),
new SqlParameter("@pageIndex", index));
List <PictureSummary> pictures = new List <PictureSummary>();
foreach (DataRow item in dataTable.Rows)
{
pictures.Add(new PictureSummary(item));
}
return(pictures);
}