How to configure SQLite db in Visual Studio?

Question

I've messed with Access a little bit in the past, had one class on OO theory, and one class on console c++ apps. Now, as a hobby project, I'm undertaking to write an actual app, which will be a database app using System.Data.SQLite and C#.

I have the db's table structure planned. I have System.Data.SQLite installed and connected to VS Pro. I entered my tables and columns in VS, but that's where I'm stuck. I really don't know how to finish the db set up so I can start creating queries and testing the db structure. Can someone give me guidance to online resources that will help me learn how to get the db properly set up so I can proceed with testing it?

I'm hoping for online resources specific to beginners using C# and System.Data.SQLite, but I'll use the closest I can get.

Thanks.

Update: Being somewhat comfortable with the notion (and steeled to it also, due to the anticipation of not having a choice), I think I want to use sql-language queries to communicate with the db instead of additional appliances (like ORM's or entity frameworks, etc). I would do this to keep my learning curve lower and it seems like I can use what little knowledge I have, since I already don't even know how to proceed configuring the db. So, question 2) Does that sound like a good plan?

Answer 1

What you need now is what is referred to as an Object-Relational Mapper (ORM). This piece of software interfaces your C# objects with your database - enabling you to write queries and updates without working with SQL strings.

You can either write a domain-specific ORM for your app, or use one of the millions of off the shelf products.

Off the shelf ORMs include:

• NHibernate - I have never used with Sqlite, but I assume it works
• sqlite-net - My own little library for working with Sqlite databases.

(There is Microsoft's Entity Framework, but, well, it didn't make the list.)

Answer 2

Hi Chris, for what I understood, you are actually trying to browser your database in order to prove that the table structure and the queries you're going to use are sound. Is that correct?

If so seems like you're looking for some kind of query interface. I've never used SQLite, but searching for query interfaces, I found this: http://sqlitebrowser.sourceforge.net/

It might help.

Also look at this question here: http://stackoverflow.com/questions/196909/editing-sql-query-with-visual-studio-2008

It might help you to do just what you're looking for.

Cheers, Wagner.

Answer 3

Since you're just starting off, I suspect it may be easier for you to start with SQL Server Compact. It's a zero install, single file database just like SQLite but a lot easier to deal with in a .Net environment in my opinion.

Once you get ADO.Net or Entity Framework running off that, it should be easy to migrate to another database.

EDIT

For EF configuration please see "Entity Framework(SQL Server Compact)".

This will get Entity Framework working off of your SQL Server Compact database file. From here you simply use LINQ Object Query to manage your data. Though there are a small set of limitations documented in the above link.

The great thing about that is, once you want to support SQL Server Express or SQL Server Standard, or maybe even MySQL in the future, you should only have to modify your connection strings.

You shouldn't have to touch your queries.