SQL Server Compact 4.0 connection string for Entity Framework code first?
Solution 1
In one of my projects I used SQL Server Compact 4.0 and my connection string was simple enough:
<connectionStrings>
<add name="MyDB"
connectionString="Data Source=|DataDirectory|MyDB.sdf"
providerName="System.Data.SqlServerCe.4.0" />
</connectionStrings>
I think that you should check your providerName
. I am not sure if System.Data.SqlClient
is the right one.
Have you installed EntityFramework.SqlServerCompact NuGet package? Once it is installed System.Data.SqlServerCe.4.0
provider name is added and it should be used in the connection string.
Check if SqlServerCompact
provider is 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>
Solution 2
To prevent this you need to change your connection string to have:
Trusted_Connection=False;Persist Security Info=True
based on below link
http://jayhollingum.blogspot.in/2011/03/ef-codefirst-databasesetinitializser.html
Solution 3
After many search, I decide to move to a near approach of SQL compact, it's LocalDB. I change it and this work:
<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 am using Sql Express LocalDB :) It's not my first purpose, but it work anyway. Thanks all
I just don't know when deploy in window XP, do I need to install Sql Express or Sql Compact stuffs manually?
Andiana
Updated on June 04, 2022Comments
-
Andiana almost 2 years
I am creating an application for Windows XP so I am stuck with .Net framework 4.0. I tried to use SQL Server Compact and EF code first but it make an error when
update-database
.I wish to put the database in my code directory to deploy in customer 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. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection.