locked
Architecture adivse using SQLite

    Question

  • Dear all,

    I am setting up proper architecture for an Universal App project.

    For that I started from the Universal App Template in visual studio 2013.
    Then I will have an SQLite databse file for local storage.

    I was Thinking to work as follow :

    - My share project will contain a IDataService interface representing all methods declaration against SQlite
    - Then my share project constains a DataService class which implement the IDataService.

    As collection data from tables is not Platform dépendent I was thinking it that way, am I correct ?

    If yes, then I come to the point of where should I initialize the connection to database

    Question 1 :

    - Each method in my DataAcess service in my share folder need to have an open connection to database, so where should I instanciate that connection object to database ? in share or in each WinRT and WP project ?

    Question 2 :

    - Where should I open the database , in share project or each WinRT and WP8 ?

    Please note that when application start, I do not know if it is needed or not but I am copying the database file from installation to local folder as follow :

            private async Task CopyDatabase()
            {
                bool isDatabaseExisting = false;
    
                try
                {
                    StorageFile storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync("App.sqlite");
                    isDatabaseExisting = true;
                }
                catch
                {
                    isDatabaseExisting = false;
                }
    
                if (!isDatabaseExisting)
                {
                    databaseFile = await Package.Current.InstalledLocation.GetFileAsync("App.sqlite");
                    await databaseFile.CopyAsync(ApplicationData.Current.LocalFolder);
                }
            }
    Is this needed ?

    Thnaks for help

    getting confused

    regards

    Tuesday, April 7, 2015 4:50 PM

Answers

  • The SQLConnection class needs to implement IDisposable before you can use the "using" statement around it. This makes closing connections very easy.

    In your case, I don't think the SQLite-NET library implements IDisposable so you are responsible for ensuring the Sqlconnection is closed after use. The reason I stress this point about closing your sqlconnection is to prevent from possible memory leaks.


    Abdulwahab Suleiman

    • Marked as answer by wakefun Thursday, April 9, 2015 6:00 PM
    Thursday, April 9, 2015 3:52 PM
    Moderator

All replies

  • 1) For database operations, you should always ensure this is done within the using scope. This helps to ensure the connection is closed appropriately.

    2) In your shared class, your database operations should be done within the context of an open connection within the using scope.

    - For example:

    using(var DBconn = new SQLitePCL.SQLiteConnection("sqlitedatabase.db"))

    {

    //database operation here.

    }

    3) Lastly, you only need to copy the database file if you have existing data in the database you would like to migrate to the various devices after the app is installed. By default, using the SQLitePCL library, the database file is created if it doesn't already exist.

    Hope this helps.


    Abdulwahab Suleiman


    Tuesday, April 7, 2015 8:15 PM
    Moderator
  • Thanks for you reply.

    On answer to question 2, where the using statment should be handle  ? in my share Library or in each WinRT and WP project ?

    For point 3, I have a sqlite databse with already created table and some default data ion certain tables like country codes for instance.

    What is teh difference between suing the SQLite.PCL and sql-net ? confused in that

    regards



    • Edited by wakefun Wednesday, April 8, 2015 6:14 AM
    Wednesday, April 8, 2015 6:08 AM
  • You should use the "using" statement anytime you are performing any database related operation. So if your database operations are done in your shared library, then that's where it needs to be used.

    Regarding differences between SQLitePCL and sql-net, I don't know the differences. They are both wrappers that help make the database operations easier to handle on the Windows platform. So it's up to you to choose which one best fits your development needs.


    Abdulwahab Suleiman

    • Marked as answer by wakefun Thursday, April 9, 2015 6:07 AM
    • Unmarked as answer by wakefun Thursday, April 9, 2015 7:04 AM
    Wednesday, April 8, 2015 10:02 PM
    Moderator
  • Hello,

    Based on the fact of placing the connection in a using statement, seems not possible in my case because the new SQLite-NET libvrary required extra parameter in connection and if I place it Inside using statment, I get error saying that the type use must implement IDisposable.

    Any other way ?

    Thursday, April 9, 2015 7:06 AM
  • The SQLConnection class needs to implement IDisposable before you can use the "using" statement around it. This makes closing connections very easy.

    In your case, I don't think the SQLite-NET library implements IDisposable so you are responsible for ensuring the Sqlconnection is closed after use. The reason I stress this point about closing your sqlconnection is to prevent from possible memory leaks.


    Abdulwahab Suleiman

    • Marked as answer by wakefun Thursday, April 9, 2015 6:00 PM
    Thursday, April 9, 2015 3:52 PM
    Moderator