コード例 #1
0
        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);
            }
        }
コード例 #2
0
        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());
            }
        }
コード例 #3
0
        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());
            }
        }
コード例 #4
0
        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());
            }
        }
コード例 #5
0
        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);
            }
        }
コード例 #6
0
        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());
            }
        }
コード例 #7
0
        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());
            }
        }
コード例 #8
0
        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());
            }
        }