public override VMHumor[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString(new string[] {
"dm.*",
filter.WithComments.HasValue && filter.WithComments == true?"(SELECT COUNT(1) FROM D_COMMENT AS dc WHERE dc.MaterialId=dm.Id AND dc.ModelCoreType=dm.ModelCoreType) AS CommentsCount":null,
"dmc.*",
"anu.*",
"dp.Id", "dp.Width", "dp.Height",
"dst.*"
}));
sb.Append(" FROM DV_MATERIAL AS dm ");
sb.Append(" LEFT JOIN D_MATERIAL_CATEGORY AS dmc ON dmc.Id = dm.CategoryId ");
sb.Append(" LEFT JOIN D_USER AS anu ON anu.Id = dm.UserId ");
sb.Append(" LEFT JOIN D_PICTURE AS dp ON dp.Id = dm.FrontPictureId ");
sb.Append(" LEFT JOIN D_SEO_TAGS AS dst ON (dst.ModelCoreType=dm.ModelCoreType AND dst.MaterialId=dm.Id AND dst.MaterialId IS NOT NULL) ");
object param = null;
var gws = GetMaterialsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "DateCreate", Direction = SortDirection.Desc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order, new System.Collections.Generic.Dictionary <string, string> {
{ "DateCreate", "dm.[DateCreate]" },
{ "Title", "dm.[Title]" }
}));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM DV_MATERIAL AS dm ");
sbCount.Append(gws);
using (var connection = new SqlConnection(ConnectionString))
{
filter.PagerInfo.TotalItems = connection.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
var data = connection.Query <VMHumor, SxVMMaterialCategory, SxVMAppUser, SxVMPicture, SxVMSeoTags, VMHumor>(sb.ToString(), (m, c, u, p, st) =>
{
m.Category = c;
m.User = u;
m.FrontPicture = p;
m.SeoTags = st;
return(m);
}, param: param, splitOn: "Id").ToArray();
if (filter.WidthVideos == true && data.Any())
{
FillMaterialsVideo(connection, ref data);
}
return(data);
}
}
public override VMSituation[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString());
sb.Append(" FROM D_SITUATION AS ds");
object param;
var gws = GetSituationsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "ds.[Text]", Direction = SortDirection.Desc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM D_SITUATION AS ds");
sbCount.Append(gws);
using (var conn = new SqlConnection(ConnectionString))
{
var data = conn.Query <VMSituation>(sb.ToString(), param: param);
filter.PagerInfo.TotalItems = conn.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
return(data.ToArray());
}
}
public override VMGame[] Read(SxFilter filter)
{
//0 - Show, 1 - ShowSteamAppsCount
var showSteamAppsCount = filter.AddintionalInfo?[1] == null ? (bool?)filter.AddintionalInfo?[1]: Convert.ToBoolean(filter.AddintionalInfo[1]);
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString(new string[] { "dg.*", showSteamAppsCount == true? "(SELECT COUNT(1) FROM D_GAME_STEAM_APP AS dgsa WHERE dgsa.GameId=dg.Id) AS SteamAppsCount" : null }));
sb.Append(" FROM D_GAME AS dg ");
object param = null;
var gws = GetGamesWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "Title", Direction = SortDirection.Asc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM D_GAME AS dg ");
sbCount.Append(gws);
using (var connection = new SqlConnection(ConnectionString))
{
filter.PagerInfo.TotalItems = connection.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.TotalItems <= filter.PagerInfo.PageSize?0: filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
var data = connection.Query <VMGame>(sb.ToString(), param: param);
return(data.ToArray());
}
}
public override VMAuthorAphorism[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString());
sb.Append(@" FROM D_AUTHOR_APHORISM AS daa ");
object param = null;
var gws = GetAuthorAphorismsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "daa.Name", Direction = SortDirection.Asc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append(@"SELECT COUNT(1) FROM D_AUTHOR_APHORISM AS daa ");
sbCount.Append(gws);
using (var conn = new SqlConnection(ConnectionString))
{
var data = conn.Query <VMAuthorAphorism>(sb.ToString(), param: param);
filter.PagerInfo.TotalItems = conn.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
return(data.ToArray());
}
}
public override VMSteamApp[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString(new string[] { "dsa.*" }));
sb.Append(" FROM D_STEAM_APP AS dsa ");
object param = null;
var gws = GetSteamAppsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "Name", Direction = SortDirection.Asc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM D_STEAM_APP AS dsa ");
sbCount.Append(gws);
using (var connection = new SqlConnection(ConnectionString))
{
filter.PagerInfo.TotalItems = connection.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
if (filter.PagerInfo.PageSize < int.MaxValue)
{
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.TotalItems <= filter.PagerInfo.PageSize ? 0 : filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
}
var data = connection.Query <VMSteamApp>(sb.ToString(), param: param).ToArray();
return(data);
}
}
public override VMSiteTestSubject[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString(new string[] {
"dstq.Id",
"dstq.Title",
"dstq.Description",
"dstq.TestId",
"dstq.PictureId",
"dst.Id",
"dp.Id"
}));
sb.Append(" FROM D_SITE_TEST_SUBJECT AS dstq ");
var joinString = @"JOIN D_SITE_TEST AS dst ON dst.Id = dstq.TestId
LEFT JOIN D_PICTURE AS dp on dp.Id=dstq.PictureId";
sb.Append(joinString);
object param = null;
var gws = GetSiteTestSubjectsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "dstq.DateCreate", Direction = SortDirection.Desc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM D_SITE_TEST_SUBJECT AS dstq ");
sbCount.Append(joinString);
sbCount.Append(gws);
using (var connection = new SqlConnection(ConnectionString))
{
var data = connection.Query <VMSiteTestSubject, VMSiteTest, SxVMPicture, VMSiteTestSubject>(sb.ToString(), (q, t, p) =>
{
q.Picture = p;
q.Test = t;
return(q);
}, param: param, splitOn: "Id");
filter.PagerInfo.TotalItems = connection.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
return(data.ToArray());
}
}
public async Task <VMInfographic[]> ReadAsync(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString(new string[] {
// infographic
"dp.Id AS PictureId",
"di.MaterialId",
"di.ModelCoreType",
// picture
"dp.Id",
"dp.Caption"
}));
sb.Append(@" FROM D_PICTURE AS dp
LEFT JOIN D_INFOGRAPHIC AS di ON di.PictureId = dp.Id
LEFT JOIN DV_MATERIAL AS dm ON dm.Id = di.MaterialId AND dm.ModelCoreType = di.ModelCoreType ");
object param = null;
var gws = GetInfographicsWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "Caption", Direction = SortDirection.Asc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order, new Dictionary <string, string> {
["Caption"] = "dp.Caption",
["PictureId"] = "dp.Id"
}));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append(@"SELECT COUNT(1) FROM D_PICTURE AS dp
LEFT JOIN D_INFOGRAPHIC AS di ON di.PictureId = dp.Id
LEFT JOIN DV_MATERIAL AS dm ON dm.Id = di.MaterialId AND dm.ModelCoreType = di.ModelCoreType ");
sbCount.Append(gws);
using (var connection = new SqlConnection(_connectionString))
{
filter.PagerInfo.TotalItems = (await connection.QueryAsync <int>(sbCount.ToString(), param: param)).SingleOrDefault();
var data = connection.Query <VMInfographic, SxVMPicture, VMInfographic>(sb.ToString(), (i, p) => {
i.Picture = p;
return(i);
}, param: param, splitOn: "PictureId, Id");
return(data.ToArray());
}
}
public override VMEducation[] Read(SxFilter filter)
{
var sb = new StringBuilder();
sb.Append(SxQueryProvider.GetSelectString());
sb.Append(" FROM D_EDUCATION AS de LEFT JOIN D_PICTURE AS dp ON dp.Id=de.PictureId ");
object param;
var gws = GetEducationWhereString(filter, out param);
sb.Append(gws);
var defaultOrder = new SxOrderItem {
FieldName = "de.[Order]", Direction = SortDirection.Desc
};
sb.Append(SxQueryProvider.GetOrderString(defaultOrder, filter.Order));
sb.AppendFormat(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", filter.PagerInfo.SkipCount, filter.PagerInfo.PageSize);
//count
var sbCount = new StringBuilder();
sbCount.Append("SELECT COUNT(1) FROM D_EDUCATION AS de");
sbCount.Append(gws);
using (var conn = new SqlConnection(ConnectionString))
{
var data = conn.Query <VMEducation, SxVMPicture, VMEducation>(sb.ToString(), (e, p) => {
e.Picture = p;
return(e);
}, param: param, splitOn: "Id");
filter.PagerInfo.TotalItems = conn.Query <int>(sbCount.ToString(), param: param).SingleOrDefault();
return(data.ToArray());
}
}