/// <summary>
/// Gets rows from the table fulfilling the criteria.
/// </summary>
/// <param name="wFilter">A filter to use as where condition</param>
/// <returns>All rows fulfilling the criteria</returns>
public IEnumerable <DauerauftragRow> GetRowsByFilter(DauerauftragFilter wFilter)
{
using (OleDbCommand command = new OleDbCommand())
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "SELECT [ID],[IDBudget],[Bezeichnung],[GueltigAb],[GueltigBis],[Lauftag],[Betrag] FROM [Dauerauftrag]";
StringBuilder sbWhere = new StringBuilder();
StringBuilder sbColumnWhere;
string wComboWord = (wFilter.ComboMode == OryxFilterCombinationEnum.And ? " AND " : " OR ");
sbColumnWhere = new StringBuilder();
if (wFilter.IDBudget != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_IDBudget + "=@wIDBudget");
command.Parameters.AddWithValue("wIDBudget", wFilter.IDBudget);
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
sbColumnWhere = new StringBuilder();
if (wFilter.Bezeichnung != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Bezeichnung + "=@wBezeichnung");
command.Parameters.AddWithValue("wBezeichnung", wFilter.Bezeichnung);
}
if (wFilter.BezeichnungPattern != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Bezeichnung + " LIKE @wBezeichnungPattern");
command.Parameters.AddWithValue("wBezeichnungPattern", wFilter.BezeichnungPattern);
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
sbColumnWhere = new StringBuilder();
if (wFilter.GueltigAb != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_GueltigAb + "=@wGueltigAb");
command.Parameters.AddWithValue("wGueltigAb", this.GetAccessDateTime(wFilter.GueltigAb.Value));
}
if (wFilter.GueltigAbMaxWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_GueltigAb + "<@wGueltigAbMaxWo");
command.Parameters.AddWithValue("wGueltigAbMaxWo", this.GetAccessDateTime(wFilter.GueltigAbMaxWo.Value));
}
if (wFilter.GueltigAbMinWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_GueltigAb + ">@wGueltigAbMinWo");
command.Parameters.AddWithValue("wGueltigAbMinWo", this.GetAccessDateTime(wFilter.GueltigAbMinWo.Value));
}
if (wFilter.GueltigAbMax != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_GueltigAb + "<=@wGueltigAbMax");
command.Parameters.AddWithValue("wGueltigAbMax", this.GetAccessDateTime(wFilter.GueltigAbMax.Value));
}
if (wFilter.GueltigAbMin != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_GueltigAb + ">=@wGueltigAbMin");
command.Parameters.AddWithValue("wGueltigAbMin", this.GetAccessDateTime(wFilter.GueltigAbMin.Value));
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
sbColumnWhere = new StringBuilder();
if (wFilter.GueltigBis != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_GueltigBis + "=@wGueltigBis");
command.Parameters.AddWithValue("wGueltigBis", this.GetAccessDateTime(wFilter.GueltigBis.Value));
}
if (wFilter.GueltigBisMaxWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_GueltigBis + "<@wGueltigBisMaxWo");
command.Parameters.AddWithValue("wGueltigBisMaxWo", this.GetAccessDateTime(wFilter.GueltigBisMaxWo.Value));
}
if (wFilter.GueltigBisMinWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_GueltigBis + ">@wGueltigBisMinWo");
command.Parameters.AddWithValue("wGueltigBisMinWo", this.GetAccessDateTime(wFilter.GueltigBisMinWo.Value));
}
if (wFilter.GueltigBisMax != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_GueltigBis + "<=@wGueltigBisMax");
command.Parameters.AddWithValue("wGueltigBisMax", this.GetAccessDateTime(wFilter.GueltigBisMax.Value));
}
if (wFilter.GueltigBisMin != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_GueltigBis + ">=@wGueltigBisMin");
command.Parameters.AddWithValue("wGueltigBisMin", this.GetAccessDateTime(wFilter.GueltigBisMin.Value));
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
sbColumnWhere = new StringBuilder();
if (wFilter.Lauftag != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Lauftag + "=@wLauftag");
command.Parameters.AddWithValue("wLauftag", wFilter.Lauftag);
}
if (wFilter.LauftagMaxWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Lauftag + "<@wLauftagMaxWo");
command.Parameters.AddWithValue("wLauftagMaxWo", wFilter.LauftagMaxWo);
}
if (wFilter.LauftagMinWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Lauftag + ">@wLauftagMinWo");
command.Parameters.AddWithValue("wLauftagMinWo", wFilter.LauftagMinWo);
}
if (wFilter.LauftagMax != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Lauftag + "<=@wLauftagMax");
command.Parameters.AddWithValue("wLauftagMax", wFilter.LauftagMax);
}
if (wFilter.LauftagMin != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Lauftag + ">=@wLauftagMin");
command.Parameters.AddWithValue("wLauftagMin", wFilter.LauftagMin);
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
sbColumnWhere = new StringBuilder();
if (wFilter.Betrag != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Betrag + "=@wBetrag");
command.Parameters.AddWithValue("wBetrag", wFilter.Betrag);
}
if (wFilter.BetragMaxWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Betrag + "<@wBetragMaxWo");
command.Parameters.AddWithValue("wBetragMaxWo", wFilter.BetragMaxWo);
}
if (wFilter.BetragMinWo != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Betrag + ">@wBetragMinWo");
command.Parameters.AddWithValue("wBetragMinWo", wFilter.BetragMinWo);
}
if (wFilter.BetragMax != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Betrag + "<=@wBetragMax");
command.Parameters.AddWithValue("wBetragMax", wFilter.BetragMax);
}
if (wFilter.BetragMin != null)
{
if (sbColumnWhere.Length > 0)
{
sbColumnWhere.Append(wComboWord);
}
sbColumnWhere.Append(SQL_COL_Betrag + ">=@wBetragMin");
command.Parameters.AddWithValue("wBetragMin", wFilter.BetragMin);
}
if (sbColumnWhere.Length > 0)
{
if (sbWhere.Length > 0)
{
sbWhere.Append(wComboWord);
}
sbWhere.Append(sbColumnWhere.ToString());
}
// Add the WHERE term
if (sbWhere.Length > 0)
{
command.CommandText += (" WHERE " + sbWhere.ToString());
}
command.CommandText += ";";
DataTable resultTable = base.ExecuteCommand(command);
List <DauerauftragRow> result = new List <DauerauftragRow>(resultTable.Rows.Count);
foreach (DataRow oneRow in resultTable.Rows)
{
result.Add(RowToDto(oneRow));
}
return(result);
}
}
/// <summary>
/// Gets rows from the table fulfilling the criteria.
/// </summary>
/// <param name="wFilter">A filter to use as where condition</param>
/// <returns>All rows fulfilling the criteria</returns>
public IEnumerable <DauerauftragRow> GetRowsByFilter(DauerauftragFilter wFilter)
{
return(mTable.GetRowsByFilter(wFilter));
}
