public static DataBaseView Create(DataBase db, string sql)
{
DataBaseView view = new DataBaseView();
SqlCeCommand command = new SqlCeCommand(sql, db.Connection);
view.reader = command.ExecuteReader();
return(view);
}
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);
}
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);
}
