public bool TableExist(string TableName)
{
bool result = false;
if (TableName != null)
{
try
{
var cursor = ReadableDatabase.RawQuery("select count(*) from sqlite_master where type = ? and name = ?", new string[] { "table", TableName });
if (cursor != null)
{
if (cursor.MoveToFirst())
{
int count = cursor.GetInt(0);
cursor.Close();
result = count > 0;
}
}
}
catch (SQLException e)
{
}
}
return(result);
}
/// <summary>
/// Retrieve all recipe items owned by a recipe.
/// </summary>
/// <param name="owner">The owner of the desired recipe items.</param>
/// <returns>The owned recipe items.</returns>
public IList <RecipeItem> GetRecipeItemsByOwner(Recipe owner)
{
List <RecipeItem> res = new List <RecipeItem>();
string cmd = $"SELECT {RI_TABLE_NAME}.{RI_ID_COL}, {RI_TABLE_NAME}.{RI_PERCENTAGE_COL}, " +
$"{FLAVOR_TABLE_NAME}.{FLAVOR_ID_COL}, {FLAVOR_TABLE_NAME}.{FLAVOR_NAME_COL}, " +
$"{FLAVOR_TABLE_NAME}.{FLAVOR_PG_COL}, {FLAVOR_TABLE_NAME}.{FLAVOR_RECPER_COL} " +
$"FROM {RI_TABLE_NAME} JOIN {FLAVOR_TABLE_NAME} " +
$"ON {RI_TABLE_NAME}.{RI_FLAVOR_COL}={FLAVOR_TABLE_NAME}.{FLAVOR_ID_COL} " +
$"JOIN {RECIPE_RI_TABLE_NAME} " +
$"ON {RI_TABLE_NAME}.{RI_ID_COL}={RECIPE_RI_TABLE_NAME}.{RECIPE_RI_RI_ID} " +
$"WHERE {RECIPE_RI_RECIPE_ID}={owner.ID};";
try {
ICursor iter = ReadableDatabase.RawQuery(cmd, null);
while (iter.MoveToNext())
{
RecipeItem ri = ParseRecipeItem(iter);
if (ri != null)
{
res.Add(ri);
}
}
iter.Close();
} catch (SQLiteAbortException e) {
Console.WriteLine(e.Message);
}
return(res);
}
public List <Prato> listar()
{
List <Prato> lista = new List <Prato>();
String sql = "Select * from " + TABELA + " ORDER BY nome";
ICursor cursor = ReadableDatabase.RawQuery(sql, null);
try
{
while (cursor.MoveToNext())
{
Prato prat = new Prato();
prat.ID = cursor.GetLong(0);
prat.Nome = cursor.GetString(1);
prat.Valor = cursor.GetDouble(2);
lista.Add(prat);
}
}
catch (SQLException e)
{
Log.Error(TAG, e.Message);
}
finally
{
cursor.Close();
}
return(lista);
}
/// <summary>
/// Returns the ID of the last inserted element.
/// </summary>
/// <returns>On success: the last inserted ID. On failure: -1</returns>
public int GetLastInsertedID()
{
int res = -1;
try {
ICursor cur = ReadableDatabase.RawQuery("SELECT last_insert_rowid();", new string[] { });
cur.MoveToFirst();
res = cur.GetInt(0);
cur.Close();
} catch (SQLiteAbortException e) {
Console.WriteLine(e.Message);
}
return(res);
}
/// <summary>
/// Retrieve all flavors from the database.
/// </summary>
/// <returns>The collection of all flavors currently in the database.</returns>
public IList <Flavor> GetFlavors()
{
List <Flavor> res = new List <Flavor>();
ICursor iter = ReadableDatabase.RawQuery($"SELECT * FROM {FLAVOR_TABLE_NAME};", null);
while (iter.MoveToNext())
{
Flavor newFlavor = ParseFlavor(iter);
if (newFlavor != null)
{
res.Add(newFlavor);
}
}
iter.Close();
return(res);
}
/// <summary>
/// Retrieve all nicotines from the database.
/// </summary>
/// <returns>The collection of all nicotines currently in the database.</returns>
public IList <Nicotine> GetNicotines()
{
IList <Nicotine> res = new List <Nicotine>();
string cmd = $"SELECT * FROM {NIC_TABLE_NAME};";
ICursor iter = ReadableDatabase.RawQuery(cmd, null);
while (iter.MoveToNext())
{
Nicotine newNic = ParseNicotine(iter);
if (newNic != null)
{
res.Add(newNic);
}
}
iter.Close();
return(res);
}
public List <Template> GetData()
{
var result = new List <Template>();
using (ICursor c = ReadableDatabase.Query(TABLE_NAME, new string[] { KEY_ID, KEY_NAME, KEY_VALUE }, null, null, null, null, KEY_ID))
{
if (c != null)
{
int name_idx = c.GetColumnIndexOrThrow(KEY_NAME);
int value_idx = c.GetColumnIndexOrThrow(KEY_VALUE);
while (c.MoveToNext())
{
result.Add(new Template(c.GetString(name_idx), c.GetString(value_idx)));
}
}
}
return(result);
}
public List <LatLng> RetrieveMarkerList()
{
string[] columns =
{
MapMarkerContract.MarkerTable._ID,
MapMarkerContract.MarkerTable.COLUMN_NAME_LAT,
MapMarkerContract.MarkerTable.COLUMN_NAME_LONG
};
ICursor cursor = ReadableDatabase.Query(MapMarkerContract.MarkerTable.TABLE_NAME, columns, null, null, null, null, null);
List <LatLng> markerList = new List <LatLng>();
while (cursor.MoveToNext())
{
markerList.Add(new LatLng(cursor.GetDouble(cursor.GetColumnIndex(MapMarkerContract.MarkerTable.COLUMN_NAME_LAT)),
cursor.GetDouble(cursor.GetColumnIndex(MapMarkerContract.MarkerTable.COLUMN_NAME_LONG))));
}
return(markerList);
}
/// <summary>
/// Get all the recipes from the database.
/// </summary>
/// <returns>The collection of all of the recipes.</returns>
public IList <Recipe> GetRecipes()
{
List <Recipe> res = new List <Recipe>();
StringBuilder cmd = new StringBuilder();
cmd.Append($"SELECT ");
foreach (var field in new string[] { RECIPE_ID_COL, RECIPE_NAME_COL, RECIPE_VG_COL, RECIPE_BATCHSIZE_COL, RECIPE_TARGET_NIC_COL })
{
cmd.Append(RECIPE_TABLE_NAME + '.' + field + ", ");
}
foreach (var field in new string[] { NIC_ID_COL, NIC_NAME_COL, NIC_VG_COL, NIC_CONC_COL })
{
cmd.Append(NIC_TABLE_NAME + '.' + field + ", ");
}
cmd.Length = cmd.Length - 2;
cmd.Append($"FROM {RECIPE_TABLE_NAME} JOIN {NIC_TABLE_NAME} ");
cmd.Append($"ON {RECIPE_TABLE_NAME}.{RECIPE_ID_COL}={NIC_TABLE_NAME}.{NIC_ID_COL};");
try {
ICursor iter = ReadableDatabase.RawQuery(cmd.ToString(), null);
while (iter.MoveToNext())
{
Recipe recipe = ParseRecipe(iter);
if (recipe != null)
{
res.Add(recipe);
}
}
iter.Close();
foreach (var recipe in res)
{
recipe.Flavors = GetRecipeItemsByOwner(recipe);
}
} catch (SQLiteAbortException e) {
Console.WriteLine(e.Message);
}
return(res);
}
internal void AttachDB(string SessionId, string appId)
{
var dbFile = new Java.IO.File(_context.ExternalCacheDir.AbsolutePath + "/" + SessionId);
var db = WritableDatabase;
try
{
db.ExecSQL("attach database '" + dbFile.Path + "' as externaldb;");
// query to obtain the names of all tables in your database
ICursor tc = db.RawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
List <String> tables = new List <string>();
// iterate over the result set, adding every table name to a list
while (tc.MoveToNext())
{
tables.Add(tc.GetString(0));
}
// call DROP TABLE on every table name
foreach (String table in tables)
{
ICursor cc = db.RawQuery("PRAGMA table_info(" + table + ")", null);
StringBuilder c1 = new StringBuilder();
StringBuilder c2 = new StringBuilder();
if (cc != null)
{
cc.MoveToFirst();
bool firstRow = true;
int size = cc.Count;
for (int i = 0; i < size; i++)
{
cc.MoveToPosition(i);
String columnName = cc.GetString(cc.GetColumnIndex("name"));
if (firstRow)
{
c1.Append(columnName);
if (columnName == "AppId")
{
c2.Append("'" + appId + "' as " + columnName);
}
else
{
c2.Append(columnName);
}
}
else
{
c1.Append(",").Append(columnName);
if (columnName == "AppId")
{
c2.Append(",'" + appId + "' as " + columnName);
}
else
{
c2.Append(",").Append(columnName);
}
}
firstRow = false;
}
}
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO '")
.Append(table).Append("' (")
.Append(c1).Append(")")
.Append(" SELECT ").Append(c2).Append(" FROM ")
.Append("externaldb.").Append(table);
String raw = sql.ToString();
Logger.Debug("Executing sql : " + raw);
db.ExecSQL(raw);
try
{
ICursor result = ReadableDatabase.RawQuery("select changes();", null);
if (result != null)
{
result.MoveToFirst();
int c = result.GetInt(0);
Logger.Debug("Number of rows, inserted to table " + table + " = " + c);
}
}
catch
{
}
}
}
catch (Exception ex)
{
Logger.Error(ex);
}
finally
{
if (db != null)
{
db.Close();
}
dbFile.Delete();
}
}
public ICursor GetCountries(string searchText, uint limit)
{
return(ReadableDatabase.RawQuery(string.Format(
"SELECT * FROM {0} WHERE {1} LIKE '{2}%' ORDER BY {3} LIMIT 0,{4}",
TableCountries, FieldName, searchText, FieldName, limit), null));
}
public ICursor GetCountries()
{
return(ReadableDatabase.RawQuery(string.Format(
"SELECT * FROM {0}",
TableCountries), null));
}
public ICursor GetCountry(string id)
{
return(ReadableDatabase.RawQuery(string.Format(
"SELECT {0}, {1} FROM {2} WHERE {3} = {4}",
FieldId, FieldName, TableCountries, FieldId, id), null));
}
