예제 #1
0
        public static DataBaseView CreateView(DataBase db, FieldCollection fc, SortFieldCollection sfc, int trackid = 0, Condition condition = null, int skip = 0, int take = 0)
        {
            string allFields  = GetColumns(db, fc);
            string sortFields = GetSortColumns(db, sfc);

            bool containsArtistField        = fc.Any(field => FieldHelper.IsCDArtistField(field)) || sfc.Any(field => FieldHelper.IsCDArtistField(field.Field));
            bool containsComposerField      = fc.Any(field => FieldHelper.IsCDComposerField(field)) || sfc.Any(field => FieldHelper.IsCDComposerField(field.Field));
            bool containsCategoryField      = fc.Any(field => field == Field.Category) || sfc.Any(field => field.Field == Field.Category);
            bool containsMediumField        = fc.Any(field => field == Field.Medium) || sfc.Any(field => field.Field == Field.Medium);
            bool containsCDSetField         = fc.Any(field => field == Field.CDSet) || sfc.Any(field => field.Field == Field.CDSet);
            bool containsTrackArtistField   = fc.Any(field => FieldHelper.IsTrackArtistField(field)) || sfc.Any(field => FieldHelper.IsTrackArtistField(field.Field));
            bool containsTrackComposerField = fc.Any(field => FieldHelper.IsTrackComposerField(field)) || sfc.Any(field => FieldHelper.IsTrackComposerField(field.Field));
            bool containsTrackCategoryField = fc.Any(field => field == Field.TrackCategory) || sfc.Any(field => field.Field == Field.TrackCategory);
            bool containsCDField            = fc.Any(field => FieldHelper.IsCDField(field, true)) || sfc.Any(field => FieldHelper.IsCDField(field.Field, true));

            if (condition != null)
            {
                containsArtistField        |= condition.Any(singleCond => FieldHelper.IsCDArtistField(singleCond.Field));
                containsComposerField      |= condition.Any(singleCond => FieldHelper.IsCDComposerField(singleCond.Field));
                containsCategoryField      |= condition.Any(singleCond => singleCond.Field == Field.Category);
                containsMediumField        |= condition.Any(singleCond => singleCond.Field == Field.Medium);
                containsCDSetField         |= condition.Any(singleCond => singleCond.Field == Field.CDSet);
                containsTrackArtistField   |= condition.Any(singleCond => FieldHelper.IsTrackArtistField(singleCond.Field));
                containsTrackComposerField |= condition.Any(singleCond => FieldHelper.IsTrackComposerField(singleCond.Field));
                containsTrackCategoryField |= condition.Any(singleCond => singleCond.Field == Field.TrackCategory);
                containsCDField            |= condition.Any(singleCond => FieldHelper.IsCDField(singleCond.Field, true));
            }

            string sql;

            if (!string.IsNullOrEmpty(allFields))
            {
                sql = "SELECT TrackID, " + allFields + " FROM Track ";
            }
            else
            {
                sql = "SELECT TrackID FROM Track";
            }

            if (containsCDField)
            {
                sql += "INNER JOIN CD ON Track.CDID = CD.CDID ";
            }
            if (containsArtistField)
            {
                sql += "INNER JOIN PersonGroup as Artist ON CD.ArtistID = Artist.PersonGroupID ";
            }
            if (containsComposerField)
            {
                sql += "LEFT JOIN PersonGroup as Composer ON CD.ComposerID = Composer.PersonGroupID ";
            }
            if (containsCDSetField)
            {
                sql += "LEFT JOIN [Set] ON CD.SetID = [Set].SetID ";
            }
            if (containsCategoryField)
            {
                sql += "LEFT JOIN Category ON CD.CategoryID = Category.CategoryID ";
            }
            if (containsMediumField)
            {
                sql += "LEFT JOIN Medium ON CD.MediumID = Medium.MediumID ";
            }
            if (containsTrackArtistField)
            {
                sql += "INNER JOIN PersonGroup as TrackArtist ON Track.ArtistID = TrackArtist.PersonGroupID ";
            }
            if (containsTrackComposerField)
            {
                sql += "LEFT JOIN PersonGroup as TrackComposer ON Track.ComposerID = TrackComposer.PersonGroupID ";
            }
            if (containsTrackCategoryField)
            {
                sql += "LEFT JOIN Category as TrackCategory ON Track.CategoryID = TrackCategory.CategoryID ";
            }

            if (trackid != 0)
            {
                sql += " WHERE Track.TrackID=" + trackid.ToString();
            }

            if (condition != null)
            {
                string where = GetSqlCondition(db, condition);
                if (!string.IsNullOrEmpty(where))
                {
                    sql += " WHERE ";
                    sql += where;
                }
            }

            if (!string.IsNullOrEmpty(sortFields))
            {
                sql += " ORDER BY " + sortFields;
            }

            //sql += " OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY";

            DataBaseView view = DataBaseView.Create(db, sql);

            return(view);
        }
예제 #2
0
        public static DataBaseView CreateView(DataBase db, FieldCollection fc, SortFieldCollection sfc, int cdid = 0, Condition condition = null)
        {
            string allFields  = GetColumns(db, fc);
            string sortFields = GetSortColumns(db, sfc);

            bool containsArtistField   = fc.Any(field => FieldHelper.IsCDArtistField(field));
            bool containsComposerField = fc.Any(field => FieldHelper.IsCDComposerField(field));
            bool containsCategoryField = fc.Any(field => field == Field.Category);
            bool containsMediumField   = fc.Any(field => field == Field.Medium);
            bool containsCDSetField    = fc.Any(field => field == Field.CDSet);

            if (condition != null)
            {
                containsArtistField   |= condition.Any(singleCond => FieldHelper.IsCDArtistField(singleCond.Field));
                containsComposerField |= condition.Any(singleCond => FieldHelper.IsCDComposerField(singleCond.Field));
                containsCategoryField |= condition.Any(singleCond => singleCond.Field == Field.Category);
                containsMediumField   |= condition.Any(singleCond => singleCond.Field == Field.Medium);
                containsCDSetField    |= condition.Any(singleCond => singleCond.Field == Field.CDSet);
            }

            string sql;

            if (!string.IsNullOrEmpty(allFields))
            {
                sql = "SELECT CDID, " + allFields + " FROM CD ";
            }
            else
            {
                sql = "SELECT CDID FROM CD";
            }

            if (containsArtistField)
            {
                sql += "INNER JOIN PersonGroup as Artist ON CD.ArtistID = Artist.PersonGroupID ";
            }
            if (containsComposerField)
            {
                sql += "LEFT JOIN PersonGroup as Composer ON CD.ComposerID = Composer.PersonGroupID ";
            }
            if (containsCDSetField)
            {
                sql += "LEFT JOIN [Set] ON CD.SetID = [Set].SetID ";
            }
            if (containsCategoryField)
            {
                sql += "LEFT JOIN Category ON CD.CategoryID = Category.CategoryID ";
            }
            if (containsMediumField)
            {
                sql += "LEFT JOIN Medium ON CD.MediumID = Medium.MediumID ";
            }

            if (cdid != 0)
            {
                sql += " WHERE CD.CDID=" + cdid.ToString();
            }

            if (condition != null && condition.Count > 0)
            {
                string where = GetSqlCondition(db, condition);
                if (!string.IsNullOrEmpty(where))
                {
                    sql += " WHERE ";
                    sql += where;
                }
            }

            if (!string.IsNullOrEmpty(sortFields))
            {
                sql += " ORDER BY " + sortFields;
            }

            DataBaseView view = DataBaseView.Create(db, sql);

            return(view);
        }