Xamarin SQLite database creating for all tables

Question

I have working on this topic for 4 hours but I couldn't get any solution.

My problem is actually; I have 5 table and I wanna create one controller to create different tables.

My current codes are below but this codes create only one table.

public interface ISQLite
{
    SQLiteConnection GetConnection();
}

-

public class TodoItem
{
    public TodoItem ()
    {
    }

    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string Name { get; set; }
    public string Notes { get; set; }
    public bool Done { get; set; }
}

-

public class TodoItemDatabase 
{
    static object locker = new object ();

    SQLiteConnection database;

    /// <summary>
    /// Initializes a new instance of the <see cref="Tasky.DL.TaskDatabase"/> TaskDatabase. 
    /// if the database doesn't exist, it will create the database and all the tables.
    /// </summary>
    /// <param name='path'>
    /// Path.
    /// </param>
    public TodoItemDatabase()
    {
        database = DependencyService.Get<ISQLite> ().GetConnection ();
        // create the tables
        database.CreateTable<TodoItem>();
    }

    public IEnumerable<TodoItem> GetItems ()
    {
        lock (locker) {
            return (from i in database.Table<TodoItem>() select i).ToList();
        }
    }

    public IEnumerable<TodoItem> GetItemsNotDone ()
    {
        lock (locker) {
            return database.Query<TodoItem>("SELECT * FROM [TodoItem] WHERE [Done] = 0");
        }
    }

    public TodoItem GetItem (int id) 
    {
        lock (locker) {
            return database.Table<TodoItem>().FirstOrDefault(x => x.ID == id);
        }
    }

    public int SaveItem (TodoItem item) 
    {
        lock (locker) {
            if (item.ID != 0) {
                database.Update(item);
                return item.ID;
            } else {
                return database.Insert(item);
            }
        }
    }

    public int DeleteItem(int id)
    {
        lock (locker) {
            return database.Delete<TodoItem>(id);
        }
    }
}

-

public class SQLite_Android : ISQLite
{
    public SQLite_Android()
    {
    }

    #region ISQLite implementation
    public SQLite.SQLiteConnection GetConnection()
    {
        var sqliteFilename = "TodoSQLite.db3";
        string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); // Documents folder
        var path = Path.Combine(documentsPath, sqliteFilename);

        // This is where we copy in the prepopulated database
        Console.WriteLine(path);
        if (!File.Exists(path))
        {
            var s = Forms.Context.Resources.OpenRawResource(Resource.Raw.TodoSQLite);  // RESOURCE NAME ###

            // create a write stream
            FileStream writeStream = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
            // write to the stream
            ReadWriteStream(s, writeStream);
        }

        var conn = new SQLite.SQLiteConnection(path);

        // Return the database connection 
        return conn;
    }
    #endregion

    /// <summary>
    /// helper method to get the database out of /raw/ and into the user filesystem
    /// </summary>
    void ReadWriteStream(Stream readStream, Stream writeStream)
    {
        int Length = 256;
        Byte[] buffer = new Byte[Length];
        int bytesRead = readStream.Read(buffer, 0, Length);
        // write the required bytes
        while (bytesRead > 0)
        {
            writeStream.Write(buffer, 0, bytesRead);
            bytesRead = readStream.Read(buffer, 0, Length);
        }
        readStream.Close();
        writeStream.Close();
    }
}

--- How can I create multi tables in one controller ?

Kai Brummund · Accepted Answer · 2016-07-25 14:40:45Z

1

Looks like you are using Sqlite.net-pcl, right?

Multiple tables from the same model are not supported (it's for simple cases only).

You can create multiple models (possibly by just inheriting) and then call CreatTable<T> for each of them.

answered Jul 25, 2016 at 14:40

Kai Brummund

3,5683 gold badges26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aybuke Over a year ago

Yes I'm using Sqlite.net-pcl. Okay thank you very much. I will use intercafe. Right?

Aybuke · Accepted Answer · 2016-09-07 12:53:43Z

I solved problem. Maybe this solution helps somenone.

I have two DbHepler Class and two model class for creating two tables on DB.

Base connection codes are same;

public interface ISQLite
{
    SQLiteConnection GetConnection();
}

This is the App.cs file;

public class App : Application {
    public App()
    {
        authenticationDB = new AuthenticationDbHelper(Database);
        settingsDbHelper = new SettingsDbHelper(Database);
        MainPage = new Views.MainMenuPage();
    }
public static CreateDB Database
    {
        get
        {
            if (database == null)
            {
                database = new CreateDB();
            }
            return database;
        }
    }
}

The CreateDB class is necessary for create one db for all tables

public class CreateDB
{
    public SQLiteConnection database;
    public object locker = new object();
    public CreateDB()
    {
        database = DependencyService.Get<ISQLite>().GetConnection();
    }

}

This interface is necessary for created tables actions. Since implement this class we can use theese methods all tables.(T is table class)(To understand look AuthenticationDBHelper class)

public interface SQLiteBase<T>
{
    IEnumerable<T> GetItems();
    T GetItem(long id);
    long SaveItem(T item);
    void UpdateItem(T item);
    int DeleteItem(int id);
    int Clear();
    int getCount();
}

This DbHelper class will be used for delete,insert,clear.... items.

public class AuthenticationDbHelper : SQLiteBase<AuthenticationDbTable>
{
    SQLiteConnection database;
    object locker;
    public AuthenticationDbHelper(CreateDB db)
    {
        database = db.database;
        locker = db.locker;
        database.CreateTable<AuthenticationDbTable>();
    }

    public int Clear()
    {
        lock(locker)
        {
            return database.DeleteAll<AuthenticationDbTable>();
        }
    }

    public int DeleteItem(int id)
    {
        lock (locker)
        {
            return database.Delete<AuthenticationDbTable>(id);
        }
    }

    public AuthenticationDbTable GetItem(long id)
    {
        lock (locker)
        {
            return database.Table<AuthenticationDbTable>().FirstOrDefault(x => x.UserId == id);
        }
    }

    public IEnumerable<AuthenticationDbTable> GetItems()
    {
        lock (locker)
        {
            return (from i in database.Table<AuthenticationDbTable>() select i).ToList();
        }
    }

    public long SaveItem(AuthenticationDbTable item)
    {
        lock (locker)
        {
             return database.Insert(item);
        }
    }

    public void UpdateItem(AuthenticationDbTable item)
    {
        lock(locker)
        {
            database.Update(item);
        }
    }

    public int getCount()
    {
        return GetItems().Count();
    }

}

I know it is very confused but this is the last. We will create model for authentication.

public class AuthenticationDbTable
{

    public AuthenticationDbTable(long userId, string sessionId, string username, string clientuuid)
    {
        this.userId = userId;
        this.sessionId = sessionId;
        this.username = username;
        this.clientuuid = clientuuid;
    }


    private long userId;
    private string sessionId;
    private string username;
    private string clientuuid;

    [PrimaryKey]
    public long UserId
    {
        get { return userId; }
        set { userId = value; }
    }

    public string SessionId
    {
        get { return sessionId; }
        set { sessionId = value; }
    }

    public string Username
    {
        get { return username; }
        set { username = value; }
    }

    public string Clientuuid
    {
        get { return clientuuid; }
        set { clientuuid = value; }
    }
}

Using

AuthenticationDbTable authentication = new AuthenticationDbTable(authenticateduser.User.UserId, r.Retval.SessionStatus.SessionId, authenticateduser.User.Name, authenticateduser.Clientuuid);
App.authenticationDB.SaveItem(authentiaction);

Note

For creating second table you can use same way. You should create second DbHelper and model class. Assume that you will create a table for settings. You should create SettingsDbHelper and SettingsDbTable class. through same way.

Thank you :)

Collectives™ on Stack Overflow

Xamarin SQLite database creating for all tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related