Forum Xamarin.Forms

How to load static data in local sqlite DB?

Hi,

I have requirement to load static data when application starts for the first time. This the data of countries, district and cities. How do I populate this data in sqlite db. The data is huge, it takes time to load it through API and as data is being static, I want to have it loaded when application loads.

Any help?

Answers

  • juanzeta33juanzeta33 USMember ✭✭

    @oriangedev said:
    Hi,

    I have requirement to load static data when application starts for the first time. This the data of countries, district and cities. How do I populate this data in sqlite db. The data is huge, it takes time to load it through API and as data is being static, I want to have it loaded when application loads.

    Any help?

    Hey, there two ways that I do that.

    One: Create the list of the model and save it in the databases like a object.
    Two: Make a service that consume a xml or json file saving locally.

    If you need any help with the code, feel free to send me a message.

  • oriangedevoriangedev Member ✭✭

    Can you please help with code snippet? Our solution is built in MVVM. Can you please suggest which approach will be better performance wise?

  • oriangedevoriangedev Member ✭✭

    @juanzeta33 said:

    @oriangedev said:
    Hi,

    I have requirement to load static data when application starts for the first time. This the data of countries, district and cities. How do I populate this data in sqlite db. The data is huge, it takes time to load it through API and as data is being static, I want to have it loaded when application loads.

    Any help?

    Hey, there two ways that I do that.

    One: Create the list of the model and save it in the databases like a object.
    Two: Make a service that consume a xml or json file saving locally.

    If you need any help with the code, feel free to send me a message.

    Can you please help me with the code snippet? Also suggest which approach is better for performance.

  • Liêm_NguyễnLiêm_Nguyễn USMember ✭✭✭✭

    @oriangedev said:
    Can you please help with code snippet? Our solution is built in MVVM. Can you please suggest which approach will be better performance wise?

    Or you can use location to get current location. Then at startup, load data of that City only

  • AndreiMisiukevich_AndreiMisiukevich_ USMember ✭✭✭✭✭

    Why do you prefer SqLite?
    You can use several files with build action EmbeddedResource (in your PCL project)

    I think, you can try to store sqlite file like that, then access via code)

  • oriangedevoriangedev Member ✭✭

    @AndreiMisiukevich_ said:
    Why do you prefer SqLite?
    You can use several files with build action EmbeddedResource (in your PCL project)

    I think, you can try to store sqlite file like that, then access via code)

    My all data for application is stored locally in sqlite db to make work the application offline. Once online we will invoke sync to sync data. All web and app data has to be in sync. To get all states, cities from web it take lot of time. This data will hardly change. so instead of calling API, I want to have it inserted into tables when app loads. This state, city table has references to other tables and need to have it in sqlite.

  • AndreiMisiukevich_AndreiMisiukevich_ USMember ✭✭✭✭✭

    @oriangedev Ok

    In this case, try to create sqlite.db file and add it into your project as content (as image or xml etc.)
    Then attach creating of SqlConnection to this path.

  • oriangedevoriangedev Member ✭✭

    @AndreiMisiukevich_ said:
    @oriangedev Ok

    In this case, try to create sqlite.db file and add it into your project as content (as image or xml etc.)
    Then attach creating of SqlConnection to this path.

    Sorry I am new to this. But how do I create sqlite.db file with loaded data. Do I need to write all those 1000 record insert statements to load data in sqllite. Any easy way?

  • AndreiMisiukevich_AndreiMisiukevich_ USMember ✭✭✭✭✭

    Yes

    https://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/

    Just create console app and fill your db as you want

  • JohnHardmanJohnHardman GBUniversity admin

    @oriangedev - There are also tools available for editing/creating/browsing SQLite databases. I use SQLiteBrowser on Windows 10 - that allows importing a database from an existing SQL DB, or importing a table from a CSV file. The imported data/schema can then be edited as required.

  • oriangedevoriangedev Member ✭✭

    @JohnHardman said:
    @oriangedev - There are also tools available for editing/creating/browsing SQLite databases. I use SQLiteBrowser on Windows 10 - that allows importing a database from an existing SQL DB, or importing a table from a CSV file. The imported data/schema can then be edited as required.

    Yes that would help. But once created DB, we might have to use it as embedded resource in the application. What if there are changes in the model in future for other tables ? Do we need to replace the DB file? In that case users data will be lost from local DB?

  • JohnHardmanJohnHardman GBUniversity admin

    @oriangedev - I recommend having a strategy that handles versioning of databases, as is done in enterprise environments for non-mobile databases. A simplistic method of doing this is to have a version number included in the table names. On startup, the app checks whether tables exist with names for the current app version. If they do, then great. If not, the app attempts a migration from whatever older versions it finds.

  • oriangedevoriangedev Member ✭✭

    Can you please provide me any pointers about to how to embed the db file in the application as resource?

  • AndreiMisiukevich_AndreiMisiukevich_ USMember ✭✭✭✭✭

    Just add file to your solution
    Right click on this file -> Build Action -> EmbeddedResource (If you use VS for mac like me)

  • AndreiMisiukevich_AndreiMisiukevich_ USMember ✭✭✭✭✭

    Or add this files both iOS and Android

  • JamesLaveryJamesLavery GBBeta, University ✭✭✭✭✭
    Anticipating your next potential problem. I don't think you will be able to open the SQLite database from an embedded resource (other contributors please correct me if I'm wrong).

    So you will need to copy the resource to a file at app startup (this will need to be platform specific).
  • oriangedevoriangedev Member ✭✭

    @JohnHardman said:
    @oriangedev - There are also tools available for editing/creating/browsing SQLite databases. I use SQLiteBrowser on Windows 10 - that allows importing a database from an existing SQL DB, or importing a table from a CSV file. The imported data/schema can then be edited as required.

    Using SQLite browser I have created the database with the tables imported from .csv file. But how can i get connected to that particular file? I tried using System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal) to get my path but its giving me an error 'Environment' does not contain a definition for 'GetFolderPath'. So can anyone tell me where to save my database.db3 file and get connected to that file every time I install or run my application?

Sign In or Register to comment.