private static string GetColumn(DataBase db, Field field)
{
string columnField = "";
string colName = DataBase.GetDataColumnByField(field).ColumnName;
if (field == Field.Identity)
{
colName = "[Identity]";
}
string prefix = "CD";
if (FieldHelper.IsCDArtistField(field))
{
prefix = "Artist";
}
if (FieldHelper.IsCDComposerField(field))
{
prefix = "Composer";
}
if (field == Field.CDSet)
{
prefix = "[Set]";
}
if (field == Field.Category)
{
prefix = "Category";
}
if (field == Field.Medium)
{
prefix = "Medium";
}
if (!string.IsNullOrEmpty(prefix))
{
columnField += prefix + "." + colName;
}
else
{
columnField += colName;
}
return(columnField);
}
private bool SortFieldByValue(DataBase db, Field field)
{
if (field == Field.ArchiveNumber || field == Field.NumberOfTracks ||
field == Field.Date || field == Field.TrackNumber)
{
return(true);
}
if (FieldHelper.IsUserField(field) && db.GetUserFieldType(field) == UserFieldType.Date)
{
return(true);
}
return(false);
}
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);
}
public TrackDataView(DataBase db, CDQueryDataSet cdQuery, Condition condition, SortFieldCollection sortedFields)
: this(db, cdQuery, condition, sortedFields, FieldHelper.GetAllFields())
{
}
public void Save(DataBase db)
{
CDQueryDataSetTableAdapters.TrackTableAdapter trackta = new CDQueryDataSetTableAdapters.TrackTableAdapter(db);
CDQueryDataSet.TrackDataTable tdt = trackta.GetDataById(ID);
CDQueryDataSet.TrackRow trackRow;
bool newTrack = this.ID == 0;
if (newTrack)
{
trackRow = tdt.NewTrackRow();
}
else
{
trackRow = tdt[0];
}
foreach (Field field in FieldHelper.GetAllTrackFields(true))
{
switch (field)
{
case Field.TrackCategory:
trackRow.CategoryID = db.AllCategories.GetIdByName(this.Category, true);
break;
default:
{
if (field != Field.ArtistTrackName && field != Field.ComposerTrackName)
{
DataColumn dataColumn = tdt.GetDataColumnByField(field);
object val = GetValueByField(field);
if (val == null)
{
trackRow[dataColumn] = DBNull.Value;
}
else
{
trackRow[dataColumn] = val;
}
}
break;
}
}
}
if (CDID == 0)
{
throw new Exception("CDID must be set!");
}
trackRow.CDID = CDID;
if (!String.IsNullOrEmpty(Artist))
{
trackRow.ArtistID = db.GetPersonGroupRowByName(Artist, true).PersonGroupID;
}
else
{
trackRow.ArtistID = 0;
}
if (!String.IsNullOrEmpty(Composer))
{
trackRow.ComposerID = db.GetPersonGroupRowByName(Composer, true).PersonGroupID;
}
else
{
trackRow.ComposerID = 0;
}
if (newTrack)
{
tdt.AddTrackRow(trackRow);
}
trackta.Update(tdt);
}
public ColumnField(Field field)
{
Field = field;
Width = FieldHelper.GetDefaultWidth(field);
TextAlignment = System.Windows.TextAlignment.Left;
}