I want to use and embedded SQL Server Compact edition in my asp.net mvc 4 project. I try to approaches but I have problems with any approach:
I have created a new data connection from visual studio and from server explorer of type Microsoft SQL Server Compact 3.5 and I store the
.sdf file in my asp.net mvc 4 project under
\App_Data folder (I add this
.sdf file to this folder). I guess doing this I create and embedded SQL Server Compact database for my web application, am I right?
Finally, I create the following connection string in
<add name="DefaultConnection" providerName="System.Data.SqlServerCe.3.5" connectionString="Data Source=|DataDirectory|\App_Data\MyDatabaseSQLCe.sdf" />
I start my web application but an error occurs, database seems to be not found, well the error says something like:
SQL Server Compact is not intended for asp.net development
I am not sure if the connection string above described is the correct.
Also I have downloaded the SQL Server Compact 4.0 from here: http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/compact.aspx and I have installed. Once installed If I go to create a data connection as above described it does not appears SQL Server Compact 4.0 from Visual Studio. I would like to use an embedded SQL Server Compact in my project but I am completely lost.
Conclusion: my goal is to use SQL Server Compact edition as embedded in my asp.net mvc 4 web application and put this
.sdf file in my
\App_Data folder and make it works with the correct string connection in
|DataDirectory|\App_Data yields App_Data\App_Data, if I am not incorrect. Try Data Source=|DataDirectory|\MyDatabaseSQLCe.sdf.
You can test this by dumping
AppDomain.CurrentDomain.GetData("DataDirectory").ToString() on your webpage.
You should grab the toolkit for visual studio when dealing with Sql Server Compact.