SQL Compact 2008 Connection String Problem

Question

I have the following code to connect to a sql server compact edition 2008:

    private SqlConnection sqlConn;

    public void createConnection()
    {
        String connectionString = @"Data Source=C:\Projects\somefile.sdf;Persist Security Info=False";
        sqlConn = new SqlConnection(connectionString);
        sqlConn.Open();
    }

However, I keep getting the following error when sqlConn.Open() is executed:

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

Does anyone have any ideas what the problem might be? I can create a connection to the db in the database explorer but it doesn't seem to work in code.

Answer 1

The Sql* classes in System.Data.SqlClient can only be used to connect to a regular SQL Server instance.

To connect to a SQL CE database, you need to create a SqlCeConnection object in System.Data.SqlServerCe.dll.

Answer 2

Is the sdf file in the same directory as the executing application?

How to specify the location of the SDF file Often times the .SDF database is not running in the current directory so it becomes necessary to programatically set the path to the SDF file. This is an example (.net C#) on how to do this when the SDF file is located in the same directory as the executing application.

Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\MyData.sdf;Persist Security Info=False;

Source