Database Misconception

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

31 Replies - 1305 Views - Last Post: 17 October 2013 - 06:55 AM Rate Topic: -----

#1 synlight  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 84
  • View blog
  • Posts: 557
  • Joined: 14-September 11

Database Misconception

Posted 08 October 2013 - 05:09 AM

I have an application written with WPF and SQLite. I did some research and was under the impression that if I created a database in my bin folder, it would be there and accessible when I published my application. So, yesterday I tried to publish (my first time), and the software throws an exception when it tries to query the database.

Invalid Operation Error
Operation is not valid due to the current state of the object.

Partial Stack Trace:
at System.Data.SQLite.SQLiteConnection.Open()
at CervellaAM.DataManager.ExecuteScalar(String sql)
at CervellaAM.Login.btnSubmitLogin_Click(Object sender, RoutedEventArgs e)
at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)


So.. it seems to me like the error is coming into play when the user clicks the login button, and SQLite cannot open the database. AT least, that's my take on it.

Setup generated an Application Files folder on my desktop, and the database is not in there. Does this mean the database is not available to my program? I did not write and create database/tables code because I thought embedding the database in the binaries would auto generate it when I deployed it. The System.Data.SQLite dll is included in the Application Files, so I know it's not missing from the published version.


Edit: The purpose is for the application to ship with a database that contains 2 empty tables (columns are there to be filled by customer actions)

This post has been edited by synlight: 08 October 2013 - 05:27 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Database Misconception

#2 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 486
  • Joined: 13-June 12

Re: Database Misconception

Posted 08 October 2013 - 05:37 AM

Do you have full version of Visual Studio or one of the Express products? If so, which one?
Was This Post Helpful? 0
  • +
  • -

#3 synlight  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 84
  • View blog
  • Posts: 557
  • Joined: 14-September 11

Re: Database Misconception

Posted 08 October 2013 - 05:38 AM

I'm using Visual Studio 2012 Professional

Edit: Although it is the Free version I got when I was a student, with the student MSDN subscription

Edit Again: Part of me wants to scrap this whole thing and use SqlServer instead, there is so much more support available for it. But I'm so stubborn, I want to make this work! I spent so much time learning about SQLite and I feel like if I can't get this working as is, I have failed.

This post has been edited by synlight: 08 October 2013 - 05:42 AM

Was This Post Helpful? 0
  • +
  • -

#4 Robin19  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 256
  • View blog
  • Posts: 529
  • Joined: 07-July 10

Re: Database Misconception

Posted 08 October 2013 - 05:47 AM

First - It means you aren't doing proper error handling. Your code should not assume that everything is perfect and everything is where it needs to be. You should check conditions and have try/catches around anything suspicious. Then you know exactly what line of code (or at least what sub-part of the logic) is having a problem.

Second - You shouldn't have to manually move the database file to the bin folder. These steps are from memory. Right click on your project and select "Add Existing Item". Select your database file. Right click the database and select "Properties". The "Copy" property can be changed from Never. You can decide how and when to copy, but Copy Always should work for you. Now when you publish/debug/whatever, VS will worry about copying that file to the correct destination.

Third - Try to find what line is throwing the error. Can you get this error when you debug? This is a generic error that has multiple causes.

The most common is that the connection is closed. You need to make sure the connection is open. Don't forget that using(var sql = new SQLite()) will close the connection at the end bracket so you will need to reopen it when you want to use it again.
Was This Post Helpful? 3
  • +
  • -

#5 synlight  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 84
  • View blog
  • Posts: 557
  • Joined: 14-September 11

Re: Database Misconception

Posted 08 October 2013 - 06:18 AM

Bah I just wrote out this long post and it got eaten.

The program does not throw the error when I debug, only when I try to run a published version.

I do have it in a catch block, it is throwing the error inside the TRY block, on

 this.dbConnection.Open();


Which tells me I wrote the catch block incorrectly.

So, I added the database as an existing file. I changed the Copy property to Always Copy. What should the Build Action be? Maybe Embedded Resource?


EDIT AGAIN:

Thank you @Robin19, adding it to my Project worked. I know that was a stupid mistake on my part, but I appreciate you pointing that out. This is my first time actually trying to publish something, I thought since it ran great in Development that it would run great when I published it.

This post has been edited by synlight: 08 October 2013 - 06:26 AM

Was This Post Helpful? 0
  • +
  • -

#6 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 486
  • Joined: 13-June 12

Re: Database Misconception

Posted 08 October 2013 - 06:27 AM

Definitely not as an embedded resource, that will mean you can't write to it (as it'll be part of your .exe, an embedded resource is read-only). I think just "None" should work, maybe someone can tell me otherwise.

EDIT: Glad to hear you've fixed it.

This post has been edited by MrShoes: 08 October 2013 - 06:28 AM

Was This Post Helpful? 1
  • +
  • -

#7 synlight  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 84
  • View blog
  • Posts: 557
  • Joined: 14-September 11

Re: Database Misconception

Posted 08 October 2013 - 06:29 AM

I did look up the various options, but I don't know what half the stuff means.

None - The file is not included in the project output group and is not compiled in the build process. An example is a text file that contains documentation, such as a Readme file.

Compile - The file is compiled into the build output. This setting is used for code files.

Content - The file is not compiled, but is included in the Content output group. For example, this setting is the default value for an .htm or other kind of Web file.

Embedded Resource - This file is embedded in the main project build output as a DLL or executable. It is typically used for resource files.



I was afraid that "None" would mean it was not available to the program at runtime. Because n00b.
Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5643
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: Database Misconception

Posted 08 October 2013 - 06:46 AM

If you already have a sqlite file, then:

Quote

Content - The file is not compiled, but is included in the Content output group. For example, this setting is the default value for an .htm or other kind of Web file.


However, you still have to answer the question: what do you do if you can't find the file?

For most applications for which sqlite would be appropriate, the answer would be: create a new one.

That is, if you can't find your sqlite db, you simply make a new, empty, one. Usually, with a bunch of create table commands. Maybe some seed inserts.
Was This Post Helpful? 1
  • +
  • -

#9 synlight  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 84
  • View blog
  • Posts: 557
  • Joined: 14-September 11

Re: Database Misconception

Posted 08 October 2013 - 06:53 AM

So, I need to figure out how to check if the file exists. I looked that up the other day, and found one thread on SO where they said to check the filepath. But I don't know what the filepath will be when an end user installs it. I was also wondering if I should do some sort of backup of the database daily or whatnot.
Was This Post Helpful? 0
  • +
  • -

#10 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 486
  • Joined: 13-June 12

Re: Database Misconception

Posted 08 October 2013 - 07:14 AM

You can get the installation directory of your application using System.Reflection.Assembly.GetExecutingAssembly().Location - that's a good starting point.
Was This Post Helpful? 1
  • +
  • -

#11 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 3168
  • View blog
  • Posts: 9,574
  • Joined: 05-May 12

Re: Database Misconception

Posted 08 October 2013 - 07:10 PM

Except if you follow MS recommendations to install programs into the Program Files tree... Then you won't have write privileges unless you are running as admin. The better idea is to ensure that the database file lives in the ApplicationData or the LocalApplicationData.
http://msdn.microsof...cialfolder.aspx
Was This Post Helpful? 3
  • +
  • -

#12 synlight  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 84
  • View blog
  • Posts: 557
  • Joined: 14-September 11

Re: Database Misconception

Posted 09 October 2013 - 08:16 AM

So, the sample code from MSDN is this:

// Sample for the Environment.GetFolderPath method 
using System;

class Sample 
{
    public static void Main() 
    {
    Console.WriteLine();
    Console.WriteLine("GetFolderPath: {0}", 
                 Environment.GetFolderPath(Environment.SpecialFolder.System));
    }
}
/*
This example produces the following results:

GetFolderPath: C:\WINNT\System32
*/



so, should my logic be:

String path = String.Format("{0}\\MyApp\\DatabaseName", Environment.GetFolderPath(Environment.SpecialFolder.System);

if(path)
 database exists
else
 create new database




edit: I wanted to make sure I had the file path stuff correct

This post has been edited by synlight: 09 October 2013 - 08:17 AM

Was This Post Helpful? 0
  • +
  • -

#13 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 995
  • View blog
  • Posts: 2,386
  • Joined: 04-October 09

Re: Database Misconception

Posted 09 October 2013 - 08:25 AM

No, you don't want to use the System folder. Change the enum to Environment.SpecialFolder.ApplicationData
Was This Post Helpful? 1
  • +
  • -

#14 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4311
  • View blog
  • Posts: 7,467
  • Joined: 08-June 10

Re: Database Misconception

Posted 09 October 2013 - 08:26 AM

Some clarification here:

As Skydiver suggests (edit: and Momerath as well), it's better to use the AppData folder, rather than the System folder. You probably want to stay out of the System folder.

Next, I suggest using Path.Combine to build path strings. It handles separators for you:

string appdata = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
string dbpath = Path.Combine(appdata, "myapp", "data", "db.sqlite");
//dbpath is now: @"C:\Users\crutland\AppData\Roaming\myapp\data\db.sqlite"



I just guessed at the DB's file extension.

Lastly, I suggest that when you do this for real, you should probably do this in a constants or settings class, using a static get-only property (this is a value that will only need to be stood up once and should never change while the app is running).
Was This Post Helpful? 1
  • +
  • -

#15 synlight  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 84
  • View blog
  • Posts: 557
  • Joined: 14-September 11

Re: Database Misconception

Posted 09 October 2013 - 08:35 AM

Here is what I have right now:

  //TODO: Check if database exists
            
        string appdata = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
    	string dbpath = System.IO.Path.Combine(appdata, "CervellaAM", "data", "CervellaDB.sqlite");
            
            if (File.Exists(dbpath))
            {
                Alert alert = new Alert("Databse Exists");
                alert.Show();
            }
            else
            {
                Alert alert = new Alert("Database does not exist");
                alert.Show();
            }



I have to put out some fires before I can republish and test, but if anyone has any criticisms, I would appreciate them, as always.

Edit: I missed the suggestions Curtis made about constants. I will definitely do that

This post has been edited by synlight: 09 October 2013 - 08:36 AM

Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3