I am trying to use SQLite for a winforms application, what I can't figure out is where to put the datafile in the code, and what my connection string should be. Right now i have added an "App_Code" directory to my may project, and placed my datafile in that directory. I am getting an error that SQLite cannot find the table that does exist in the database. I am passing in to my constructor Application.StartupPath.
Can anyone help me out?
I would suggest creating an App_Data folder and placing the sqlite database file there.
The connection string would then be:
String connString = "Data Source=App_Data\filename.sqlite; Version=3";
You shouldn't need to worry about passing in Application.StartupPath because the file reference in the Connection String is relative to the WinForms *.exe.
Generally you should store data (on Vista at least) in either the users AppData directory (for per user data, like remembering configuration settings), or in ProgramData for machine wide data. You should always have read and write access to these.
They may be in different places on different versions or windows, and users may move them so you should use the suitable API's (check MSDN, I only remember the C-API methods) to get the paths for them.
You can NOT guarantee you have write access to the programs directory on the users computer, only read access.
As for structuing your code, in a single threaded enviroment I useually make the database connection object somewhere easily accessible (either global, or in the applications main class), and write the SQL stuff inline where its needed.
For very large projects or if there a lot of repeated queries you may want to create a DataBase class which connects when its constructed, and has methods for all the queries you need (eg DataBase.GetClientList to get a list of Client objects from the database). This would also give you an easy way to ensure that your not using a single connection object from multiple threads at once.
As an addition to what Fire Lancer said, you can also choose to store the database where the user choses to put it, if you're using them as documents. You could then use File -> Save, and put it wherever the users wants it to.
It really depends on what you're trying to do with the database what solution is best.