locked
How to read from existing .db file? RRS feed

  • Question

  • User390320 posted

    I created questions.db file using DB Browser and want to read data from it in my Xamarin Forms application. That sounds pretty simple, right, but I'm 10+ hours stuck googling. Most answers just link to "Xamarin.Forms Local Databases" in microsoft docs.

    Which explains nothing about the manually created database file, where to put it etc.

    Some unofficial guides tell to put .db file to Resources/Assets folder for IOS/Droid, which seems right and I did.

    I also added interfaces and [assembly: Dependency(typeof(DatabaseService))].... thing for android/ios, but failed. Everyone seems to use slightly different syntax, i haven't seen something for that in official docs, I'm completely lost.

    Sorry for not posting any code, I'm not even sure which path to take, that's why I'm not diving into exact syntax right now.

    Monday, November 11, 2019 2:05 PM

Answers

  • User390320 posted

    It seems I had to name my DB table same as class in C sharp : database.Table<"Name this like in your DB">().ToListAsync();

    Also if someone stumbles upon here in the future, here's a good link how to quickly implement excisting DB working:

    https://stackoverflow.com/questions/42092607/no-such-table-sqlite-when-execute-query-on-xamarin-forms-pcl

    • Marked as answer by Anonymous Thursday, June 3, 2021 12:00 AM
    Sunday, November 17, 2019 8:32 PM

All replies

  • User2148 posted

    https://docs.microsoft.com/it-it/xamarin/android/data-cloud/data-access/using-sqlite-orm

    Monday, November 11, 2019 2:18 PM
  • User390320 posted

    so where do I add this manually created DB FILE?

    MyApp ? or maybe somewhere in MyApp.Android and MyApp.IOS?

    Monday, November 11, 2019 3:30 PM
  • User390320 posted

    I copied it to App.Android Assets:

    is that supposed to connect like that? :) probably not, because everyone in guides just seems to be creating new databases like in Xamarin docs. I can get that to work... but not with my manually added DB. It's quite depressing not to get something simple like that running.

    Monday, November 11, 2019 3:38 PM
  • User390320 posted

    This is what I'm trying to achieve : https://arteksoftware.com/deploying-a-database-file-with-a-xamarin-forms-app/

    Sadly the guide is outdated and some things don't work.

    Monday, November 11, 2019 11:16 PM
  • User369979 posted

    If you want to read files from the .Net Standard projects you can embed it in the Forms project as the Embedded Resource and follow this documentation to get the file path: https://docs.microsoft.com/en-us/xamarin/xamarin-forms/data-cloud/data/files?tabs=windows#loading-files-embedded-as-resources You can also embed it on each platform and using separate methods to read it. Here is an approach for Android: https://stackoverflow.com/a/18720041/8354952 And you can access the file path on iOS like:

    NSBundle.MainBundle.PathForResource("yourfile.db", null);
    
    Tuesday, November 12, 2019 9:57 AM
  • User390320 posted

    Meh, still struggling. I have created Interfaces for my PLC and Android Project, I'm using dependency services to get filePath. I mean everything, can someone look where i Fail? My project: (as you can see questions.db is in assets)

    Plc interface:

    public interface IGetDBFilePath
              {
            string FilePath(string fileName);
             }    
    

    Android interface + dependency services syntax:

    [assembly: Dependency(typeof(Appsas.Droid.GetDBFilePath_Android))]
    namespace Appsas.Droid
    {  
        public class GetDBFilePath_Android : IGetDBFilePath
        {
            public string FilePath(string fileName)
            {
    
                string dbPath = Path.Combine(
            System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal),
            "questions.db");
    
                return dbPath;
            }
    
        }
    }
    

    Part of App.xaml.cs:

            static string dbPath = DependencyService.Get<IGetDBFilePath>().FilePath("questions.db");
    
            static Database database;
    
            public static Database Database
            {
                get
                {
                    database = new Database(dbPath);
                    return database;
                }
            }
    
            public App()
            {
                InitializeComponent();
    
                MainPage = new StartPage();
            }
    

    and StartPage.xaml.cs sadly doesn't work on ListView (x:name sarasas)

    namespace Appsas
    {
        [XamlCompilation(XamlCompilationOptions.Compile)]
        public partial class StartPage : ContentPage
        {
            public StartPage()
            {
                InitializeComponent();
            }
            protected override async void OnAppearing()
            {
    
                var items = await App.Database.GetPeopleAsync();
    
                base.OnAppearing();
                sarasas.ItemsSource = items;
            }
    
        }
    }
    

    These two should be OK, but I still add them... hopefully someone can help. I tried to keep this as basic as possible.

    Database.cs and Klausimas.cs

     public class Klausimas
        {
            [PrimaryKey, AutoIncrement]
            public int Id { get; set; }
            public string Question { get; set; }
            public string Option1 { get; set; }
            public string Option2 { get; set; }
            public string Option3 { get; set; }
            public string Answer { get; set; }
    
    
        }
    
      public class Database
        {
            readonly SQLiteAsyncConnection _database;
    
            public Database(string dbPath)
            {
                _database = new SQLiteAsyncConnection(dbPath);
                _database.CreateTableAsync<Klausimas>().Wait();
            }
    
            public Task<List<Klausimas>> GetPeopleAsync()
            {
                return _database.Table<Klausimas>().ToListAsync();
            }
    
        }
    
    Tuesday, November 12, 2019 5:57 PM
  • User369979 posted

    You need to copy the file from the asset folder to your application's local storage before you want to access it there. See step 4 in the thread I posted above:

    string dbName = "questions.db";
    string dbPath = Path.Combine (System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), dbName);
    // Check if your DB has already been extracted.
    if (!File.Exists(dbPath))
    {
        using (BinaryReader br = new BinaryReader(Android.App.Application.Context.Assets.Open(dbName)))
        {
            using (BinaryWriter bw = new BinaryWriter(new FileStream(dbPath, FileMode.Create)))
            {
                byte[] buffer = new byte[2048];
                int len = 0;
                while ((len = br.Read(buffer, 0, buffer.Length)) > 0)
                {
                    bw.Write (buffer, 0, len);
                }
            }
        }
    }
    

    And then you could use this pdbPath to manipulate the database.

    Wednesday, November 13, 2019 2:39 AM
  • User390320 posted

    It seems I had to name my DB table same as class in C sharp : database.Table<"Name this like in your DB">().ToListAsync();

    Also if someone stumbles upon here in the future, here's a good link how to quickly implement excisting DB working:

    https://stackoverflow.com/questions/42092607/no-such-table-sqlite-when-execute-query-on-xamarin-forms-pcl

    • Marked as answer by Anonymous Thursday, June 3, 2021 12:00 AM
    Sunday, November 17, 2019 8:32 PM
  • User394988 posted

    Any reason that after following ALL the steps listed in this thread that the tables are not accessible?

    Placed 'dbfile.db3' in Assets folder as AnroidAsset. Db file has tables name same as the Entity classes. On MainActivity copied to Personal and/or LocalApplicationData forlder as _LandLu _stated.

    Confirmed after copying that file exists:

    var basePath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.LocalApplicationData);
    var path = System.IO.Path.Combine(basePath, "dbfile.db3");
    if (System.IO.File.Exists(path)) // exists
    {
           System.Diagnostics.Debug.Write($"FILE FOUND APPDATA: {path}");
    }
    

    Confirmed that the data in the database is present in the APK file.

    Yet when accessing the table getting SqliteException:

    SQLite.SQLiteException: no such table: XXXXXX

    Emulator: Pixel2pie990-api28

    Friday, June 12, 2020 9:19 PM
  • User396652 posted

    @LandLu said: You need to copy the file from the asset folder to your application's local storage before you want to access it there. See step 4 in the thread I posted above:

    string dbName = "questions.db";
    string dbPath = Path.Combine (System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), dbName);
    // Check if your DB has already been extracted.
    if (!File.Exists(dbPath))
    {
        using (BinaryReader br = new BinaryReader(Android.App.Application.Context.Assets.Open(dbName)))
        {
            using (BinaryWriter bw = new BinaryWriter(new FileStream(dbPath, FileMode.Create)))
            {
                byte[] buffer = new byte[2048];
                int len = 0;
                while ((len = br.Read(buffer, 0, buffer.Length)) > 0)
                {
                    bw.Write (buffer, 0, len);
                }
            }
        }
    }
    

    And then you could use this pdbPath to manipulate the database.

    @LandLu said: You need to copy the file from the asset folder to your application's local storage before you want to access it there. See step 4 in the thread I posted above:

    string dbName = "questions.db";
    string dbPath = Path.Combine (System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), dbName);
    // Check if your DB has already been extracted.
    if (!File.Exists(dbPath))
    {
        using (BinaryReader br = new BinaryReader(Android.App.Application.Context.Assets.Open(dbName)))
        {
            using (BinaryWriter bw = new BinaryWriter(new FileStream(dbPath, FileMode.Create)))
            {
                byte[] buffer = new byte[2048];
                int len = 0;
                while ((len = br.Read(buffer, 0, buffer.Length)) > 0)
                {
                    bw.Write (buffer, 0, len);
                }
            }
        }
    }
    

    And then you could use this pdbPath to manipulate the database.

    Thank you for your contributions, they helped me to solve my problem. But I have a question that I appreciate if you can help me: The above code makes a copy of the file in another directory on the system. Is there some way that it always works with the original directory, Axis: Assets.

    Thank you very much for your help

    Friday, September 4, 2020 9:45 AM