I play with SQL Server Compact Edition 4 CTP1 because I would like to use it as a data store for low traffic web application. When I try to create a DataContext with a connection string indicating System.Data.SqlServerCe.4.0 (to use LINQ To SQL), I get the following error message:
Cannot open '|DataDirectory|\data.sdf'. Provider 'System.Data.SqlServerCe.3.5' not installed.
So why is my code not using SQL CE version 4?
Background: I use Visual Web Developer Express 2010 for development, but I downloaded the beta version of WebMatrix and used its constructor to create the SQL CE 4 .sdf containing some test data.
Using the SqlCeConnection/SqlCeCommand/SqlCeDataReader , I have successfully created a basic MVC application that extracts test data and displays it. SQL CE 4 binary files are copied to the bin application bin . In my controller:
var connectionString = ConfigurationManager.ConnectionStrings["Main"].ConnectionString; var tmp = new Dictionary<string, string>(); using(var conn = new SqlCeConnection(connectionString)) { conn.Open(); using (SqlCeDataReader r = new SqlCeCommand("select * from ttest", conn).ExecuteReader()) { while (r.Read()) { tmp.Add(r["id"].ToString(), r["name"].ToString()); } } } return View(new TestViewModel { Items = tmp });
The connection string in Web.config as follows:
<add name="Main" connectionString="Data Source=|DataDirectory|\data.sdf" providerName="System.Data.SqlServerCe.4.0" />
This works fine , so I know that the connection string is correct and that I installed the binaries correctly, etc. So I decided to try a little LINQ To SQL, this is how I want to create a real application:
[Table(Name = "tTest")] public class TestEntity { [Column(IsPrimaryKey = true, IsDbGenerated = true)] public int ID { get; set; } [Column] public string Name { get; set; } } public class Repository { private Table<TestEntity> testTable; public Repository() { var connectionString = ConfigurationManager.ConnectionStrings["Main"].ConnectionString; var context = new DataContext(connectionString); testTable = context.GetTable<TestEntity>(); } public IQueryable<TestEntity> TestEntities { get { return testTable; } } }
And then in the controller ( db is the Repository built in the controller constructor):
var tmp = db.TestEntities.ToDictionary(x => x.ID.ToString(), x => x.Name); return View(new TestViewModel { Items = tmp });
But when I browse the page using this code, I get the above error:
Cannot open '|DataDirectory|\data.sdf'. Provider 'System.Data.SqlServerCe.3.5' not installed.
How to make the application use the correct version? Can anyone help?