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