N. Smeding N. Smeding - 18 days ago 6
Android Question

How to get data out of sqlite database by using a function?

I am making a application in Xamarin forms but whatever I try I can't get the sqlite database working. I want to select all Categories where menu_ID = 1 how can I do this? I need this code inside a other page (CategoriePage.xaml.cs) can somewann help me with this?

Here is some code that I use:

(Tables.cs):

namespace AmsterdamTheMapV3
{
public class Categories
{
[PrimaryKey, AutoIncrement]
public int ID { get; set; }
public int Menu_ID { get; set; }
public string Name { get; set; }

public Categories()
{
}
}

public class Places
{
[PrimaryKey, AutoIncrement]
public int ID { get; set; }
public int Categorie_ID { get; set; }
public string Name { get; set; }
public Boolean Featured { get; set; }
public string OpenHours { get; set; }
public string Info { get; set; }
public string Images { get; set; }
public string Phone { get; set; }
public string Website { get; set; }
public string Adress { get; set; }

public Places()
{
}
}

public class Events
{
[PrimaryKey, AutoIncrement]
public int ID { get; set; }

public Events()
{
}
}
}


DB helper (TheMapDB.cs):

public class TheMapDB
{
private SQLiteConnection db;

public TheMapDB()
{
//Getting conection and Creating table
db = DependencyService.Get<ISQLite>().GetConnection();
db.CreateTable<Categories>();
db.CreateTable<Places>();
db.CreateTable<Events>();

var categories = new Categories()
{
ID = 1,
Menu_ID = 1,
Name = "test"
};
db.Insert(categories); // Insert the object in the database
}


public IEnumerable<Categories> GetCategories()
{
return (from t in db.Table<Categories>() select t).ToList();
}

//Get specific Categorie
public Categories GetCategorie(int id)
{
return db.Table<Categories>().FirstOrDefault(t => t.ID == id);
}

//Delete specific Categorie
public void DeleteCategorie(int id)
{
db.Delete<Categories>(id);
}

//Add new student to Categorie
public void AddCategorie(Categories categorie)
{
db.Insert(categorie);
}
}
}


CategoriePage.xaml.cs:

public partial class CategoriePage : ContentPage
{
static TheMapDB database;
TheMapDB categorie = new TheMapDB();

public CategoriePage(String txt)
{
InitializeComponent();
var layout = new StackLayout { Padding = new Thickness(5, 10) };
this.Content = layout;
if(txt.Equals("1"))
{
txt = "this text is number 1";
//needed code can't find soluction

}
var label = new Label { Text = txt, TextColor = Color.FromHex("#77d065"), FontSize = 20 };
layout.Children.Add(label);
}
}


thank you in advance,

Answer

I suggest you make a DAO class with a function like this: (or put this function in TheMapDB.cs)

public List<Category> GetCategoryByID(int menuID)
{
    return db.Table<Category>().Where(x => x.menu_ID == menuID).ToList();
}

Then you can call this function in your DAO from everywhere you want. That seems the best solution to me. When you put this function in the class TheMapDB.cs you can say in your CategoryPage.xaml.cs:

database.GetCategoryByID(menuID);

Comments