SQL Server CE error Cannot open '|DataDirectory|\Test.sdf'. Provider 'System.Data.SqlServerCe.3.5' not installed.

Question

I am trying to use SQL Server CE in my MVC 4 web app. I have a class library and I have added the reference to System.Data.SqlServerCe.dll (V4)

The code to create the SDF is:

String connString = "Data Source=|DataDirectory|\HomeController.sdf";
SqlCeEngine engine = new SqlCeEngine(connString);
engine.CreateDatabase();

Which create a SDF with no problems

Next I try to work with the SDF like this:

XmlDocument xmlDoc = new XmlDocument();
XmlTextReader xmlReader = new XmlTextReader(ConfigurationManager.AppSettings["ApplicationSettingsPath"] + @"\CreateHomeControllerDatabase.xml");
try
{
    xmlReader.Read();
    xmlDoc.Load(xmlReader);

    foreach (XmlNode jobNode in xmlDoc.SelectSingleNode("Jobs"))
    {
        //TODO look at the old version on my old machine and debug this to see what the issue is
        DataContext db = new DataContext(DATABASE);
        db.ExecuteCommand(jobNode.InnerXml.Replace("<![CDATA[", String.Empty).Replace("]]>", String.Empty).Trim());
    }
}
catch (Exception ex)
{
    //ErrorHandler.ErrorDelegate.LogError(ex); TODO
}

As soon as it it hits DataContext db = new DataContext(DATABASE); I get the exception Cannot open '|DataDirectory|\Test.sdf'. Provider 'System.Data.SqlServerCe.3.5' not installed.

First things that looks strange is why is it asking for 3.5 when I added the 4.0 instance of the dll? I have not installed the SQL CE 4.0 on my machine as the intention of using CE was to easily install this on a client by just adding the dlls. I have added the other dlls in that folder where possible but it just errors.

Any ideas please?

Answer 1

Hello,

What's the version of .Net framework? you can refer to the following thread about the same issue:

http://stackoverflow.com/questions/4804525/provider-system-data-sqlserverce-3-5-is-not-installed

Regards,

Fanny Liu

If you have any feedback on our support, please click here .

---

Fanny Liu
TechNet Community Support

Answer 2

You are not sharing everything but assuming that DataContext is a LINQ to SQL DataContext, and DATABASE is a constant on this line:

 DataContext db = new DataContext(DATABASE);

Then you must initialize the DataContext not with a string constant, but with a 4.0 SqlCeConnection object, otherwise LINQ  to SQL will not Work with version 4.0.

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog