private static string GetSqlCondition(DataBase db, Condition condition) { string stringCondition = ""; int index = 0; Logical lastLogical = Logical.None; foreach (SingleCondition singleCondition in condition) { if (singleCondition.Field == Field.None) { continue; } string columnField = GetColumn(db, singleCondition.Field); string opName = Condition.GetNameOfOperatorForFilter(singleCondition.Operator); string singleCondString = columnField + " " + opName + " "; bool conditionValid = false; if (singleCondition.Operator != Operator.NotEmpty && singleCondition.Operator != Operator.Empty) { if (DataBase.GetTypeByField(singleCondition.Field) == typeof(string)) { string val = ""; if (singleCondition.Value != null) { val = singleCondition.Value.ToString(); if (singleCondition.Field == Field.Date) { val = db.ParseDate(val); } } val = val.Replace("'", "''"); if (singleCondition.Operator == Operator.Contains || singleCondition.Operator == Operator.NotContains) { singleCondString += "'%" + val + "%'"; } else { if (singleCondition.Operator == Operator.StartsWith) { singleCondString += "'" + val + "%'"; } else { singleCondString += "'" + val + "'"; } } conditionValid = true; } else { if (singleCondition.Value != null) { if (DataBase.GetTypeByField(singleCondition.Field) == typeof(int)) { if (singleCondition.Value is string && (singleCondition.Field == Field.TotalLength || singleCondition.Field == Field.TrackLength)) { singleCondString += Misc.ParseTimeString(singleCondition.Value.ToString()).ToString(); } else { singleCondString += Misc.Atoi(singleCondition.Value.ToString()).ToString(); } conditionValid = true; } else if (DataBase.GetTypeByField(singleCondition.Field) == typeof(bool)) { int intValue = 0; if (Int32.TryParse(singleCondition.Value.ToString(), out intValue)) { // Bei bool-Feldern nur nach 0 und 1 suchen können if (intValue == 0 || intValue == 1) { singleCondString += intValue.ToString(); conditionValid = true; } } } else { singleCondString += singleCondition.Value.ToString(); conditionValid = true; } } } } else { if (singleCondition.Operator == Operator.Empty) { singleCondString = string.Format("({0} IS NULL OR {0}='')", columnField); conditionValid = true; } if (singleCondition.Operator == Operator.NotEmpty) { singleCondString = string.Format("({0} IS NOT NULL AND {0}<>'')", columnField); conditionValid = true; } } if (conditionValid) { if (index > 0) { if (lastLogical == Logical.Or) { stringCondition += " OR "; } else { stringCondition += " AND "; } } stringCondition += singleCondString; } lastLogical = singleCondition.Logical; index++; } return(stringCondition); }
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(); }