views:

179

answers:

3

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?

+1  A: 

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.)

Frank Krueger
Thanks Frank. Maybe I don't understand enough yet, but does using sqlite-net mean I would also need to use Linq? (It says on their page: "Linq support so that you don't have to write SQL" and that seems like what you said in your first paragraph.) Or can you tell me what I'm not understanding?
ChrisC
+1  A: 

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.

Wagner Silveira
I'm not sure if that' what I'm needing. I have only entered the tables and columns into VS. The problem is that I'm not sure how to proceed to finish the db setup (relationships, and everything else) so I can start creating queries and testing the db design.
ChrisC
So here's another one, that seems to be more of GUI tool. That maybe give you the ability to do what you want: http://www.razorsql.com/features/sqlite_features.html
Wagner Silveira
+1  A: 

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.

kervin
Can you tell me how it's easier? Also, what do you mean by getting "ADO.Net or Entity Framework running"? Thanks.
ChrisC
I've updated my answer. Basically LINQ and EF give you the option of a very flexible database abstraction. You can still use ADO.Net with SQL Server Compact, and the advantage of that is that you will be using very similar syntax to full-blown SQL Server. So having that server as an option would be easy to do in the future. Personally I don't see myself giving up Entity Framework for anything at this point.
kervin
I realize now that I knew less than I thought when I posted this question. Can you tell me, just so I can try to fit all these pieces together, can you tell me (or point me to beginner's level info), what are the differences and/or similarities between Linq/EF and ORM's? And how do they fit, or not fit, together?
ChrisC
I'd start with the Wikipedia article of each of those, then try a good book on the topic then a few practice problems. LINQ is more of a query language built into .Net languages. EF is Microsoft's ORM tool, there are others. You use LINQ together with EF to build applications that are not tied to any one database technology. See http://msdn.microsoft.com/en-us/library/bb399567.aspx and http://www.codeproject.com/KB/database/IntroEntityFramework3.aspx to start.
kervin