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