[Newbie] How to use Sqlite-net extensions and foreign keys

Hi everyone,

Google bring me here when I looked for a solution to use foreign key in Windows RT/Phone application. It's very important because I work as a intern and nobody is able to give me an answer here...

My goal is to create and manage a database to store data from a form. The database will be "simple", indeed I just want to store the data and to get them to print them in the different fields of the form when the user will come back to his work. SQLite seems to be perfect for that but it doesn't have foreign key support...

I work on windows 8.1 and visual studio and I use the following library :

  • SQLite for Windows Runtime
  • SQLiteNetExtensions

I saw on this forum that it's possible to use foreign key in an easy way using SQLiteNetExtensions but I don't manage to use it even with the help of https://bitbucket.org/twincoders/sqlite-net-extensions#branches

For example I use 2 simple class and I want to create a database :

`
using SQLite;

using SQLiteNetExtensions.Attributes;

namespace TestSQLite.Models
{
class Person
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }

    [ForeignKey(typeof(Vehicle))]
    public int FK { get; set; }

    [MaxLength(30)]
    public string NameDB { get; set; }

    [MaxLength(30)]
    public string Name { get; set; }

    [MaxLength(30)]
    public string Surname { get; set; }

    public DateTime CreationDate { get; set; }

    [ManyToOne] // Many to one relationship with Vehicle
    public Vehicle Vehicle { get; set; }
}

}
`

`using SQLite;
using SQLiteNetExtensions.Attributes;

namespace TestSQLite.Models
{
class Vehicle
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }

    [MaxLength(30)]
    public string Brand { get; set; }

    [MaxLength(30)]
    public string Color { get; set; }

    [OneToMany] // One to many relationship with Person
    public List<Person> Person { get; set; }
}

}`

`using SQLite;

using SQLiteNetExtensions;
using SQLiteNetExtensions.Attributes;
using SQLiteNetExtensions.Extensions;

using TestSQLite.Models;
using System.Diagnostics;

namespace TestSQLite
{
///


/// An empty page that can be used on its own or navigated to within a Frame.
///

public sealed partial class MainPage : Page
{

    public MainPage()
    {
        this.InitializeComponent();       
    }




    private async void Button_Tapped(object sender, TappedRoutedEventArgs e)
    {

        var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "TestDBvs20132.db");
        var conn = new SQLiteAsyncConnection(dbPath);

        var person = new Person() //== valuation, I use the example of https://bitbucket.org/twincoders/sqlite-net-extensions#branches to help me
        {
            NameDB = namedb.Text,
            Name = name.Text,
            Surname = surname.Text,
            CreationDate = DateTime.Now
        };

        await conn.InsertAsync(person);


        var vehicle = new Vehicle() //== stock, I use the example of https://bitbucket.org/twincoders/sqlite-net-extensions#branches to help me
        {
            Brand = "Mustang",
            Color = "Red"
        };

        await conn.InsertAsync(vehicle);


        vehicle.Person = new List<Person> { person };

        UpdateWithChildren(vehicle);

    }`

But at the line of "UpdateWithChildren" I get the following error :
"The name "UpdateWithChildren" doesn't exist in this context"...

Please, Is there someone would be able to help ?

Posts

  • JonDouglasJonDouglas USXamarin Team, University, Developer Group Leader Xamurai
    edited June 2014

    You should probably call:

    await conn.UpdateWithChildren(vehicle); (Not sure if it's needed as await, but should work regardless)

    (You may want to make sure that your SQLiteAsyncConnection supports that method, or use the normal version)

  • chris-nilsonchris-nilson USMember

    Thanks for your reply JonDouglas.

    It works but only with the normal version. When I use the async version I get the following message "cannot await void"

    But now I don't know why I have other errors. In the Person and Vehicle class I have :

    • [ForeignKey(typeof(Vehicle))] public int FK { get; set; }
      'SQLiteNetExtensions.Attributes.ForeignKeyAttribute' is not an attribute class"
      "The type 'SQLite.Net.Attributes.IndexedAttribute' is defined in an assembly that is not referenced. You must add a reference to assembly 'SQLite.Net, Version=2.2.0.0, Culture=neutral, PublicKeyToken=null'."

    Same for [ManyToOne] and [OneToMany]... I don't know why...

    Visual Studio drives me crazy! ahah

  • JonDouglasJonDouglas USXamarin Team, University, Developer Group Leader Xamurai

    Make sure you are referencing SQLite.Net as it serves as an ORM wrapper for SQLite. The extensions I believe extend that instead.

  • chris-nilsonchris-nilson USMember

    Attached you will find all my references. I think I don't forget anything, isn't it ?

  • chris-nilsonchris-nilson USMember

    I had two other nuget package "SQLite.Net PCL" and "SQLite.Net.Async PCL" and now the conn.UpdateWithChildren(vehicle); doesn't work and I get the following errors :

    • Instance argument: cannot convert from 'SQLite.SQLiteAsyncConnection' to 'SQLite.Net.SQLiteConnection

    • "SQLite.SQLiteAsyncConnection' does not contain a definition for 'UpdateWithChildren' and the best extension method overload 'SQLiteNetExtensions.Extensions.WriteOperations.UpdateWithChildren(SQLite.Net.SQLiteConnection, T)' has some invalid arguments "

    I don't understand....

  • JeremyKolbJeremyKolb USMember ✭✭✭

    The extensions from twincoders don't support async UpdateWithChildren.

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    Hey Chris,

    The current version of SQLite-Net extensions doesn't support 'SQLite-Net Async', so you should stick to 'SQLite-Net PCL' to make it work with the pre-compiled DLL or you can simply copy the sources to your project to make it work with the standard SQLite-Net library (the one that you used in the first post).

    Hope it helps.
    Guillermo

Sign In or Register to comment.