How do I make SqlServerCe accessible from my C# app?

I was just playing with databases in C# 2010 Express and while typing 'using System.Data.' the auto-complete thing showed me the SqlServerCe in System.Data as an option. Not sure what I did but when I made a new application and added the line 'using System.Data.SqlServerCe;' I got a "SqlServerCe does not exist" error. I tried adding so开发者_C百科me references but still doesn't work. Actually what I've been trying to do is to connect to SOME, ANY database but I still can't. All samples I've found seem to assume something I don't know. I've been trying to connect with SqlConnection by setting the ConnectionString to everything I can imagine, but I keep getting errors. I have installed SQL Server compact edition, and I think I also have SQL Server (normal edition?). Please I'm getting desperate! All I want is very, very simple. I want to have a data table, I don't care about where or how I store it since the only program that will use it is my program, and be able to do SQL queries on it. Actually I don't even need to save it after the program closes. I would just use a DataTable or even a 2d array if I could do SQL queries on them. Please if you can give me a sample would be great.

If you get an error from using that using statement, then you need to add a reference to that assembly.

On my XP box, the location of that is:

c:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\

Seeing you are using VS2010, just replace 3.5 with 4.0 in the directory name.

SqlServerCe is the namespace for SQL Server Compact Edition. You need to have this installed:

http://www.microsoft.com/sqlserver/2005/en/us/compact.aspx

You then use the SqlCeConnection, SqlCeCommand etc objects.

It's a file-based database system designed to be ultra lightweight.

If you don't want to get into databases just yet, then simply use the DataTable type found in System.Data. If your DataTable is inside a DataSet then you can optionally serialize this to XML anyway.

Let's just stick with SQL Server Express Edition for now. If you think there's something wrong with your connection string, try this trick.

Make a file on your desktop and name it something.udl. Open it and try different settings until you successfully connect to your database instance. Then press OK and open the file in notepad. There you'll find the proper connection string, just get rid of the Provider attribute, you don't need it.

But, if you didn't succeed connecting using the the .udl file, this shows that there's something wrong with your database installation and you'll know what to do next.