Entity Framework: Setting correct connection string to SQL Server Compact in ASP.NET MVC 4

Question

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:

First attempt

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 web.config:

<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.

Second attempt

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 web.config.

|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. — user1228
– user1228, Commented Sep 17, 2013 at 20:00
You are right this was one error on the first approach.The second error in the first approach, regarding the message error, i googled and i find that sql server compact was not made to be used on ASP.NET,because of its several restrictions relating "normal" SQL Server editions and to avoid this message, I should put within Application_Start method in global.asax.cs the following line:AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true), then the error disappears and application goes ok, tables are creaded in database file, etc. — Willy
– Willy, Commented Sep 17, 2013 at 20:10
NOw I would like to know to solve 2 approach using sql server compact 4.0. I have installed it but when trying to create a new data connection from server explorer, the option SQL Server Compact 4.0 does not appear in visual studio, why? — Willy
– Willy, Commented Sep 17, 2013 at 20:12

user1228 · Accepted Answer · 2013-09-17 20:32:23Z

3

|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.

http://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1

answered Sep 17, 2013 at 20:32

user1228

Sign up to request clarification or add additional context in comments.

2 Comments

Willy Over a year ago

I have installed the sql server compact toolbox from the link you provided, using extension packages in visual studio but when creating dataconnection, sql server compact 4.0 does not appear, only 3.5. I have visual studio 2010 ultimate sp1, and net framework till 4.0.Any ideas? maybe it is not supported in asp.net mvc 4 web applications?

Willy Over a year ago

I have solved: you cannot add a data connection to sql server compact 4.0 from the server explorer and by right click on the data connections node. Instead you need to click on SQL Server Compact Toolbox button at the top of server explorer where appear some buttons. Then a window appears (SQL Server compact toolbox) and right click on SQL Server compact data connections node and in the menu select option Add SQL Server compact 4.0 connection, also you can choose from there 3.5. At first it is a bit confusing until one locates the sql server compact toolbox. Many thanks.

Collectives™ on Stack Overflow

Entity Framework: Setting correct connection string to SQL Server Compact in ASP.NET MVC 4

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related