SQL Server Compact Edition 4.0: Error: 26 - Error Locating Server/Instance Specified

Question

I have been developing a console application (C# / .Net Framework 4.0 / VS2012). I created a SQL Server Compact database (*.sdf) and added a connection string as:

<connectionStrings>
    <add name="Dispatcher.Properties.Settings.FakeDataSetConnectionString"
        connectionString="Data Source=|DataDirectory|\FakeDataSet.sdf"
        providerName="Microsoft.SqlServerCe.Client.4.0" />
</connectionStrings>

But When I'm trying to execute the following code:

SqlConnection con = new SqlConnection();
con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Dispatcher.Properties.Settings.FakeDataSetConnectionString"].ToString();
con.Open();

It gives the following exception at con.Open():

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

1. What I'm doing wrong here?

2. SQL Server Agent and SQL Server Browser are both in "Started" Status. Does it actually matter when using SQL Server Compact Edition?

Answer 1

For SQL Server Compact, you need to use SqlCeConnection (not SqlConnection - that's for the "real" SQL Server versions).

SqlCeConnection con = new SqlCeConnection();
con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Dispatcher.Properties.Settings.FakeDataSetConnectionString"].ToString();
con.Open();

And of course, you'll also need to use SqlCeCommand (not SqlCommand) and so on...

See the MSDN SQL Server Books Online documentation for more details on all the classes in the System.Data.SqlServerCe namespace