locked
Unable to create table in SQLite DB RRS feed

  • Question

  • I followed the instructions here repeatedly but unsuccessfully to create a table in a SQLite database. Specifically I

    1) installed SQLite on my PC.

    2) I referenced both the SQLite for Windows Runtime, and the Microsoft Visual C++ runtime package.

    3) I went into Build|Configuration Manager, and selected x86 for the Active solution platform, and x86 for the Platform.

    4) I installed the SQLite-net package.

    5) I ran Build|Build Solution.

    6) I then modified OnLaunched from App.xaml.cs as follows:

           protected override void OnLaunched(LaunchActivatedEventArgs args)
            {
                Frame rootFrame = Window.Current.Content as Frame;

                // Do not repeat app initialization when the Window already has content,
                // just ensure that the window is active
                if (rootFrame == null)
                {
                    // Create a Frame to act as the navigation context and navigate to the first page
                    rootFrame = new Frame();

                    // Get a reference to the SQLite database
                    DBPath = Path.Combine(
                        Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");
                    // Initialize the database if necessary
                    using (var db = new SQLite.SQLiteConnection(DBPath))
                    {
                        // Create the tables if they don't exist
                       
                        db.CreateTable<SimpleTable>();
                    }
                   
                    if (args.PreviousExecutionState == ApplicationExecutionState.Terminated)
                    {
                        //TODO: Load state from previously suspended application
                    }

                    // Place the frame in the current Window
                    Window.Current.Content = rootFrame;
                }

                if (rootFrame.Content == null)
                {
                    // When the navigation stack isn't restored navigate to the first page,
                    // configuring the new page by passing required information as a navigation
                    // parameter
                    if (!rootFrame.Navigate(typeof(MainPage), args.Arguments))
                    {
                        throw new Exception("Failed to create initial page");
                    }
                }
                // Ensure the current window is active
                Window.Current.Activate();
            }

    Note: I declared the variable DBPath elsewhere in the class the OnLaunched method belonged to. Also, I declared the following class in the namespace for the app, to be used to create the table I specified:

        public class SimpleTable
        {
            public int Id { get; set; }
            public bool Organization { get; set; }
        }
    }

    I was able to add tables from the example project provided here. But for some reason, I cannot reproduce that in my own home grown projects.

    P.S. I get no errors when I run my app.


    • Edited by PDoug Tuesday, October 22, 2013 9:24 PM typos
    Tuesday, October 22, 2013 8:54 PM

Answers

  • Are you absolutely sure the table is not present in the database? Have you tried to insert a record to the SimpleTable table:

    db.Insert(new SimpleTable()
    {
      Id = 1,
      Organization = true
    });

    Leszek


    Wiki: wbswiki.com
    Website: www.wisenheimerbrainstorm.com

    • Marked as answer by PDoug Tuesday, October 22, 2013 11:51 PM
    Tuesday, October 22, 2013 9:33 PM
  • Your suggestion seems to be working. I modified the using statement as follows:

                   using (var db = new SQLite.SQLiteConnection(DBPath))
                    {
                        // Create the tables if they don't exist
                       
                        db.CreateTable<SimpleTable>();
                        db.Insert(new SimpleTable()
                        {
                            Id = 2,
                            Organization = true
                        });
                        var customer = (db.Table<SimpleTable>().Where(
                            c => c.Id == 2)).Single();
                        bool OrgStatus = customer.Organization;
                    }

    The debugging results seem to indicate that the table is there, but for some mysterious reason, I cannot view it using the SQLite Database Browser. I'm going to take a closer look at the matter. Thanks for your help.

    • Marked as answer by PDoug Tuesday, October 22, 2013 11:51 PM
    Tuesday, October 22, 2013 10:00 PM
  • Egg on my face. It just so happened that I re-installed SQLite several times on my PC, and I forgot to check for the new DB path that went along with the new installations. As a result, I was looking at the wrong files with SQLite Database Browser.
    • Edited by PDoug Tuesday, October 22, 2013 11:50 PM
    • Marked as answer by PDoug Tuesday, October 22, 2013 11:51 PM
    Tuesday, October 22, 2013 11:49 PM

All replies

  • I don't see anything wrong with your code. What error are you receiving?  Have you tried to search the entire C:\Users\user_name_here\AppData\Local\Packages directory for the customers.sqlite file?

    Wiki: wbswiki.com
    Website: www.wisenheimerbrainstorm.com

    Tuesday, October 22, 2013 9:02 PM
  • I am not getting any errors. Also I'm able to look at the database at the location you specified, using the SQLite Database Browser.
    Tuesday, October 22, 2013 9:23 PM
  • Are you absolutely sure the table is not present in the database? Have you tried to insert a record to the SimpleTable table:

    db.Insert(new SimpleTable()
    {
      Id = 1,
      Organization = true
    });

    Leszek


    Wiki: wbswiki.com
    Website: www.wisenheimerbrainstorm.com

    • Marked as answer by PDoug Tuesday, October 22, 2013 11:51 PM
    Tuesday, October 22, 2013 9:33 PM
  • Your suggestion seems to be working. I modified the using statement as follows:

                   using (var db = new SQLite.SQLiteConnection(DBPath))
                    {
                        // Create the tables if they don't exist
                       
                        db.CreateTable<SimpleTable>();
                        db.Insert(new SimpleTable()
                        {
                            Id = 2,
                            Organization = true
                        });
                        var customer = (db.Table<SimpleTable>().Where(
                            c => c.Id == 2)).Single();
                        bool OrgStatus = customer.Organization;
                    }

    The debugging results seem to indicate that the table is there, but for some mysterious reason, I cannot view it using the SQLite Database Browser. I'm going to take a closer look at the matter. Thanks for your help.

    • Marked as answer by PDoug Tuesday, October 22, 2013 11:51 PM
    Tuesday, October 22, 2013 10:00 PM
  • Egg on my face. It just so happened that I re-installed SQLite several times on my PC, and I forgot to check for the new DB path that went along with the new installations. As a result, I was looking at the wrong files with SQLite Database Browser.
    • Edited by PDoug Tuesday, October 22, 2013 11:50 PM
    • Marked as answer by PDoug Tuesday, October 22, 2013 11:51 PM
    Tuesday, October 22, 2013 11:49 PM
  • I'm glad to hear the SQLite works for you :)


    Wiki: wbswiki.com
    Website: www.wisenheimerbrainstorm.com

    Tuesday, October 22, 2013 11:52 PM