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();
}
