SQL Server Compact 4.0 connection string for Entity Framework code first?

Question

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.

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

Answer 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

Answer 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?

Answer 4

According to MSDN documentation you can use the following:

<add name="ConnectionStringName"
    providerName="System.Data.SqlServerCe.4.0"
    connectionString="Data Source=|DataDirectory|\DatabaseFileName.sdf" />

PS: How to install SQL Server Compact