SQL Server Compact 4.0 Connection String for Entity Framework Code?

Question

I am building an application for Windows XP, so I am stuck in .Net framework 4.0. At first I tried to use SQL Server Compact and EF code, but it made a mistake if update-database .

I want to put the database in my code directory for deployment in a client machine.

This is my connection string:

 <add name="QuanLyKhoContext" connectionString="Data Source=MyData.sdf;Persist Security Info=False;AttachDBFileName=MyData.sdf" providerName="System.Data.SqlClient" /> 

And the error:

This operation requires a connection to the master database. The connection to the master database could not be created because the original database connection was open and the credentials were removed from the connection string. Install unopened connection.

Answer 1

In one of my projects, I used SQL Server Compact 4.0, and my connection string was fairly simple:

  <connectionStrings> <add name="MyDB" connectionString="Data Source=|DataDirectory|MyDB.sdf" providerName="System.Data.SqlServerCe.4.0" /> </connectionStrings> 

I think you should check your providerName . I am not sure if System.Data.SqlClient is correct.

Have you installed the EntityFramework.SqlServerCompact NuGet package ? After installing it, System.Data.SqlServerCe.4.0 added the name of the provider that should be used in the connection string.

Make sure the SqlServerCompact provider SqlServerCompact added to your web.config .

  <entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework"> <parameters> <parameter value="System.Data.SqlServerCe.4.0" /> </parameters> </defaultConnectionFactory> <providers> <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /> <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" /> </providers> </entityFramework> <system.data> <DbProviderFactories> <remove invariant="System.Data.SqlServerCe.4.0" /> <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> </DbProviderFactories> </system.data> 

Answer 2

To prevent this, you need to change the connection string so that:

  Trusted_Connection=False;Persist Security Info=True 

based on below link

http://jayhollingum.blogspot.in/2011/03/ef-codefirst-databasesetinitializser.html

Answer 3

After many searches, I decided to move on to a close SQL compact approach, this is LocalDB. I am changing it and this job:

 <add name="QuanLyKhoContext" connectionString="Data Source=(LocalDB)\v11.0; AttachDbFilename=|DataDirectory|MyDB.mdf;Integrated Security=True;Connect Timeout=60" providerName="System.Data.SqlClient" /> 

I think "System.Data.SqlClient" is a valid provider, because I use Sql Express LocalDB :) This is not my first goal, but it works anyway. Thank you all

I just don’t know when it is deployed in an XP window, do I need to manually install Sql Express or Sql Compact?