How to use a local database in c#?

Question

I've made a local database for a C# project:

I know basic SQL commands, but haven't worked with databases in C#. What I'd like to know specifically is:

• How to read from the database (query)
• How to add and update rows

The database only consists of 3 tables, so I don't think anything fancy is needed.

Answer 1

First, you should learn a bit about various technologies and APIs for connecting with a database.

The more traditional method is ADO.NET, which allows you to define connections and execute SQL queries or stored procedures very easily. I recommend digging up a basic tutorial on ADO.NET using Google, which may differ depending on what type of project you're creating (web app, console, WinForms, etc).

Now days, ORMs are becoming increasingly popular. They allow you to define your object model in code (such as every database table would be a class, and columns would be properties on that class) and bind to an existing database. To add a new row to a table, you'd just create an instance of a class and call a "Save" method when you're done.

The .NET framework has LINQ to SQL and the Entity Framework for this sort of pattern, both of which have plenty of tutorials online. An open source project I really like is Castle Active Record, which is built on top of NHibernate. It makes defining ORMs quite easy.

If you have specific questions about any of the above, don't hesitate to post a new question with more specific inquiries. Good luck!

Update:

I thought I'd also put in one last reference as it seems you might be interested in working with local database stores rather than building a client/server app. SQLite allows you to interact with local stores on the file system through SQL code. There's also a .NET binding maintained by the SQLite guys (which would in theory allow you to work with the other platforms I mentioned): http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

Answer 2

You can use SQLCE.

This blog will give you a good start.

http://weblogs.asp.net/scottgu/archive/2011/01/11/vs-2010-sp1-and-sql-ce.aspx