public static SortFieldCollection LoadFromRegistry(string regKey, SortFieldCollection defaultFields)
{
try
{
object fields = Settings.GetValue(regKey, null);
if (fields == null)
{
return(defaultFields);
}
XmlSerializer bf = new XmlSerializer(typeof(SortFieldCollection));
StringReader sr = new StringReader(fields.ToString());
return((SortFieldCollection)bf.Deserialize(sr));
}
catch
{
return(defaultFields);
}
}
private static string GetSortColumns(DataBase db, SortFieldCollection fc)
{
string allFields = "";
foreach (SortField field in fc)
{
if (!string.IsNullOrEmpty(allFields))
{
allFields += ", ";
}
string columnField = GetColumn(db, field.Field);
if (field.SortDirection == SortDirection.Descending)
{
columnField += " DESC";
}
allFields += columnField;
}
return(allFields);
}
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, FieldCollection fields)
{
// Zunächst die Spalten definieren
for (int col = 0; col < fields.Count; col++)
{
AddField(db, fields[col]);
}
if (sortedFields != null)
{
for (int col = 0; col < sortedFields.Count; col++)
{
Field field = GetRealSortField(sortedFields[col].Field);
AddField(db, field);
}
}
if (condition != null)
{
// Jetzt noch die Spalten für die Condition definieren
for (int col = 0; col < condition.Count; col++)
{
if (!fieldsValueDictionary.ContainsKey(condition[col].Field))
{
AddField(db, condition[col].Field);
}
}
}
// Für die ID des Tracks auch noch eine Spalte vorsehen
DataColumn dc = new DataColumn();
dc.Caption = "TrackID";
dc.DataType = typeof(int);
dataTable.Columns.Add(dc);
TrackIDColumnName = dc.ColumnName;
fieldsValueDictionary.Add(Field.TrackID, TrackIDColumnName);
fieldsStringDictionary.Add(Field.TrackID, TrackIDColumnName);
// Für die ID der CD auch noch eine Spalte vorsehen
DataColumn dcCD = new DataColumn();
dcCD.Caption = "CDID";
dcCD.DataType = typeof(int);
dataTable.Columns.Add(dcCD);
CDIDColumnName = dcCD.ColumnName;
fieldsValueDictionary.Add(Field.CDID, CDIDColumnName);
fieldsStringDictionary.Add(Field.CDID, CDIDColumnName);
// Für die ID des Interpreten auch noch eine Spalte vorsehen
DataColumn dcArtist = new DataColumn();
dcArtist.Caption = "ArtistID";
dcArtist.DataType = typeof(int);
dataTable.Columns.Add(dcArtist);
ArtistIDColumnName = dcArtist.ColumnName;
// Für die ID des CD-Interpreten auch noch eine Spalte vorsehen
DataColumn dcCDArtist = new DataColumn();
dcCDArtist.Caption = "ArtistCDID";
dcCDArtist.DataType = typeof(int);
dataTable.Columns.Add(dcCDArtist);
ArtistCDIDColumnName = dcCDArtist.ColumnName;
// Alle Inhalte füllen
for (int i = 0; i < cdQuery.Track.Count; i++)
{
object[] values = new object[fieldsToRead.Count * 2 + 4];
int colCount = 0;
for (int col = 0; col < fieldsToRead.Count; col++)
{
object rawValue = cdQuery.Track[i].GetValueByField(db, fieldsToRead[col]);
string stringValue = CDQueryDataSet.TrackRow.GetStringByValue(db, fieldsToRead[col], rawValue);
values[colCount++] = stringValue;
if (Settings.Current.SortArchiveNumberNumeric && fieldsToRead[col] == Field.ArchiveNumber)
{
rawValue = Misc.Atoi((string)rawValue);
}
values[colCount++] = rawValue;
}
values[colCount++] = cdQuery.Track[i].TrackID;
values[colCount++] = cdQuery.Track[i].CDID;
values[colCount++] = cdQuery.Track[i].ArtistID;
values[colCount++] = cdQuery.Track[i].CDRow.ArtistID;
dataTable.Rows.Add(values);
}
// Sortierung definieren
string sortString = "";
foreach (SortField sortField in sortedFields)
{
Field field = GetRealSortField(sortField.Field);
if (fieldsStringDictionary.ContainsKey(field))
{
if (!string.IsNullOrEmpty(sortString))
{
sortString += ", ";
}
// Bei einigen Feldern wird nach Wert sortiert, bei den meisten nach der String-Repräsentation
if (SortFieldByValue(db, field))
{
sortString += fieldsValueDictionary[field];
}
else
{
sortString += fieldsStringDictionary[field];
}
if (sortField.SortDirection == SortDirection.Descending)
{
sortString += " DESC";
}
}
}
dataTable.DefaultView.Sort = sortString;
if (condition != null)
{
string filterString = "";
// Filter definieren
foreach (SingleCondition cond in condition)
{
if (!string.IsNullOrEmpty(filterString))
{
if (cond.Logical == Logical.Or)
{
filterString += " OR ";
}
else
{
filterString += " AND ";
}
}
if (fieldsStringDictionary.ContainsKey(cond.Field))
{
if (DataBase.GetTypeByField(cond.Field) == typeof(string))
{
if (cond.Field != Field.Date && cond.Field != Field.ArchiveNumber)
{
filterString += fieldsStringDictionary[cond.Field];
}
else
{
filterString += fieldsValueDictionary[cond.Field];
}
filterString += " " + Condition.GetNameOfOperatorForFilter(cond.Operator) + " ";
string val = cond.Value.ToString();
val = val.Replace("'", "''");
if (cond.Operator == Operator.Contains || cond.Operator == Operator.NotContains)
{
filterString += "'%" + val + "%'";
}
else
{
if (cond.Operator == Operator.StartsWith)
{
filterString += "'" + val + "%'";
}
else
{
filterString += "'" + val + "'";
}
}
}
else
{
filterString += fieldsValueDictionary[cond.Field];
filterString += " " + Condition.GetNameOfOperatorForFilter(cond.Operator) + " ";
filterString += cond.Value.ToString();
}
}
}
dataTable.DefaultView.RowFilter = filterString;
}
resultDataTable = dataTable.DefaultView.ToTable();
}
public TrackDataView(DataBase db, CDQueryDataSet cdQuery, Condition condition, SortFieldCollection sortedFields)
: this(db, cdQuery, condition, sortedFields, FieldHelper.GetAllFields())
{
}