public static List <ListDisplay.ListItem> LoadAllByDate(string path) { string commandString = "SELECT * FROM CardInfo ORDER BY date"; SQLiteConnection database = new SQLiteConnection("Data Source=" + path + ";Version=3;"); SQLiteCommand command = new SQLiteCommand(commandString, database); List <ListDisplay.ListItem> output = new List <ListDisplay.ListItem>(); SQLiteDataReader rowData; database.Open(); rowData = command.ExecuteReader(); /*Populate collection*/ while (rowData.Read()) { output.Add(ListDisplay.FormListItem(rowData)); } database.Close(); return(output); }
public static List <ListDisplay.ListItem> PerformFilter(DateTime?fromDate, DateTime?toDate, string filterCard, string ignoreCard, string path) { string commandString; SQLiteConnection database = new SQLiteConnection("Data Source=" + path + ";Version=3;"); SQLiteCommand command; List <ListDisplay.ListItem> output = new List <ListDisplay.ListItem>(); SQLiteDataReader rowData; bool flagDate = false, flagOne = false; //Form command commandString = "SELECT * FROM CardInfo WHERE "; //Date filtering if (fromDate != null && toDate != null) { commandString += "Date BETWEEN \"" + Utilities.FormSQLDateFormat(fromDate.Value, true) + "\" AND \"" + Utilities.FormSQLDateFormat(toDate.Value, false) + "\" "; flagDate = true; } else if (fromDate == null && toDate != null) { commandString += "Date < \"" + Utilities.FormSQLDateFormat(toDate.Value, true) + "\" "; flagDate = true; } else if (toDate == null && fromDate != null) { commandString += "Date > \"" + Utilities.FormSQLDateFormat(fromDate.Value, false) + "\" "; flagDate = true; } //Special snowflake if (filterCard != "Kod karty" && filterCard != null && filterCard != "") { if (flagDate) { commandString += "AND "; } commandString += "AccountID = \"" + filterCard + "\" "; flagOne = true; } //Ignore card if (ignoreCard != "Kod karty" && ignoreCard != null && ignoreCard != "") { if (flagDate || flagOne) { commandString += "AND "; } commandString += "AccountID != \"" + ignoreCard + "\" "; } commandString += "ORDER BY date"; command = new SQLiteCommand(commandString, database); database.Open(); try { rowData = command.ExecuteReader(); } catch (System.Data.SQLite.SQLiteException) { return(null); } /*Populate collection*/ while (rowData.Read()) { output.Add(ListDisplay.FormListItem(rowData)); } database.Close(); return(output); }