How to connect to a SQL Server Compact from a .NET application?

Question

I would like to create a WPF application that is using a local database. I have created a simple database and added it so I can see my database NameDB.sdf in my Solution Explorer.

How do I connect to it from my application?

I have tried with an empty application that just tries to connect to the database:

...
using System.Data.SqlServerCe;
using System.Data;

public partial class MainWindow : Window
{
    public MainWindow()
    {
        InitializeComponent();

        SqlCeConnection connection =
            new SqlCeConnection(@"Data Source=|DataDirectory|\NameDB.sdf");
        SqlCeCommand command = new SqlCeCommand("SELECT * FROM Names");
        SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(command);
        DataSet ds = new DataSet();
        dataAdapter.Fill(ds);
        Console.WriteLine("Done");
    }
}

But I get XamlParseException with this message:

'The invocation of the constructor on type 'WpfDBApp.MainWindow' that matches the specified binding constraints threw an exception.' Line number '3' and line position '9'.

My connection string is @"Data Source=|DataDirectory|\NameDB.sdf", since I want to use a "local" path and not a full path. This is taken from the tutorial I link to below.

How can I create a simple application that connects to the local database?

I have tried to follow SQL Server 2005 Compact Edition Data Access with the SqlCeResultSet and Visual C#.NET but it doesn't seem to be done the same way with Visual Studio 2010 and WPF.

Answer 1

The problem is your connection string. The format should be "Data Source="{FullPath to YourDatabase.sdf}"

See this MSDN example for more details

Answer 2

You didn`t initialized your connection.

SqlCeCommand command = new SqlCeCommand("SELECT * FROM Names", connection);

Try now.