/// <summary> /// Returns all the images saved to DB /// </summary> /// <param name="sqlc">SQLite3 Connection</param> /// <returns>All images from DB</returns> public static List <c_Object> getImagesAll(SQLiteConnection sqlc, string tableName) { List <c_Object> lco = new List <c_Object>(); if (sqlc.State != System.Data.ConnectionState.Open) { return(null); } SQLiteCommand sqlComm = new SQLiteCommand { Connection = sqlc, CommandText = "SELECT * FROM " + tableName + "" }; SQLiteDataReader r = sqlComm.ExecuteReader(); while (r.Read()) { string _title = r.GetString(1); string _desc = r.GetString(2); string _image = r.GetString(3); string _tags = r.GetString(4); string _savedate = r.GetString(5); c_Object obj = new c_Object(_title, _desc, _image, _tags, _savedate); lco.Add(obj); } //c_Object obj = new c_Object(); return(lco); }
/// <summary> /// Returns images that have a specific description text set to them /// </summary> /// <param name="sqlc">SQLite3 Connection</param> /// <param name="desc">Description text to search for</param> /// <param name="like">Bool value determining if the description has to be matched properly</param> /// <returns>Images that have specified description</returns> public static List <c_Object> getImagesByDescription(SQLiteConnection sqlc, string desc, bool like, string tableName) { List <c_Object> lco = new List <c_Object>(); if (sqlc.State != System.Data.ConnectionState.Open) { return(null); } SQLiteCommand sqlComm = new SQLiteCommand { Connection = sqlc }; if (like) { sqlComm.CommandText = string.Format("SELECT * FROM " + tableName + " WHERE desc like '%{0}%'", desc); } else { sqlComm.CommandText = string.Format("SELECT * FROM " + tableName + " WHERE desc='{0}'", desc); } SQLiteDataReader r = sqlComm.ExecuteReader(); while (r.Read()) { string _title = r.GetString(1); string _desc = r.GetString(2); string _image = r.GetString(3); string _tags = r.GetString(4); string _savedate = r.GetString(5); c_Object obj = new c_Object(_title, _desc, _image, _tags, _savedate); lco.Add(obj); } //c_Object obj = new c_Object(); return(lco); }
/// <summary> /// Returns images saved in a specific date range /// </summary> /// <param name="sqlc">SQLite3 Connection</param> /// <param name="date">Date range split by pipe</param> /// <returns>Pictures that were taken in the selected range</returns> public static List <c_Object> getImagesByDate(SQLiteConnection sqlc, string date, string tableName) { List <c_Object> lco = new List <c_Object>(); date = date.Replace("/", ""); string[] dates = date.Split('|'); if (sqlc.State != System.Data.ConnectionState.Open) { return(null); } SQLiteCommand sqlComm = new SQLiteCommand { Connection = sqlc, CommandText = string.Format("SELECT * FROM " + tableName + " WHERE save_date between '{0}' and '{1}'", dates[0], dates[1]) }; SQLiteDataReader r = sqlComm.ExecuteReader(); while (r.Read()) { string _title = r.GetString(1); string _desc = r.GetString(2); string _image = r.GetString(3); string _tags = r.GetString(4); string _savedate = r.GetString(5); c_Object obj = new c_Object(_title, _desc, _image, _tags, _savedate); lco.Add(obj); } //c_Object obj = new c_Object(); return(lco); }
/// <summary> /// Returns images that have specific tags /// </summary> /// <param name="sqlc">SQLite3 Connection</param> /// <param name="tags">The String[] containing the tags needed to return</param> /// <returns>Imaghes that have specified tags</returns> public static List <c_Object> getImagesByTags(SQLiteConnection sqlc, string[] tags, string tableName) { List <c_Object> lco = new List <c_Object>(); List <int> ids = new List <int>(); string idString = ""; if (sqlc.State != System.Data.ConnectionState.Open) { return(null); } SQLiteCommand sqlComm = new SQLiteCommand { Connection = sqlc, CommandText = "SELECT id, tags FROM " + tableName + "" }; SQLiteDataReader r1 = sqlComm.ExecuteReader(); while (r1.Read()) { int id = r1.GetInt32(0); string tagStr = r1.GetString(1); foreach (string s in tags) { if (tagStr.ToLower().Split(';').Contains(s.ToLower())) { ids.Add(id); } } } r1.Close(); if (ids.Count > 0) { foreach (int i in ids) { idString += i + ","; } idString = idString.Trim(','); sqlComm.CommandText = string.Format("SELECT * FROM " + tableName + " WHERE id IN ( {0} )", idString); SQLiteDataReader r = sqlComm.ExecuteReader(); while (r.Read()) { string _title = r.GetString(1); string _desc = r.GetString(2); string _image = r.GetString(3); string _tags = r.GetString(4); string _savedate = r.GetString(5); c_Object obj = new c_Object(_title, _desc, _image, _tags, _savedate); lco.Add(obj); } } //c_Object obj = new c_Object(); return(lco); }