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.

Have a look at ConnectionStrings.com for SQL Server Compact - your connection string is invalid, it should be something like Data Source=MyData.sdf;Persist Security Info=False; (no AttachDbFileName= attribute or anything of that sort....) — marc_s
– marc_s, Commented Oct 18, 2014 at 15:18
What I don't understand is how you are using a SQL Server provider, but ask the question as SQL Server Compact. These providers are not interoperable. — Erik Philips
– Erik Philips, Commented Oct 19, 2014 at 9:22

Ilya Palkin · Accepted Answer · 2014-10-19 09:18:02Z

13

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>

edited Oct 19, 2014 at 9:18

answered Oct 18, 2014 at 22:38

Ilya Palkin

15.9k2 gold badges25 silver badges39 bronze badges

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

2 Comments

Andiana Over a year ago

I tried it but it's not working for mine, I don't know why it be:)

Andiana Over a year ago

Painful, I can't install SqlserverCompact via nuget Attempting to resolve dependency 'Microsoft.SqlServer.Compact (≥ 4.0.8854.1)'.

Midhun Mundayadan · Accepted Answer · 2014-10-18 15:37:22Z

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

answered Oct 18, 2014 at 15:37

Midhun Mundayadan

3,1923 gold badges24 silver badges34 bronze badges

Comments

Andiana · Accepted Answer · 2014-10-19 03:34:24Z

1

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?

answered Oct 19, 2014 at 3:34

Andiana

1,9627 gold badges38 silver badges75 bronze badges

1 Comment

Ilya Palkin Over a year ago

Yes, at least Sql Server Express should be installed in order to use LocalDB. If Sql Server Compact is used then you do not need to instal additional soft. It is enough to have Sql Server Compact related dlls in you application folder.

Aleh Baslak · Accepted Answer · 2018-05-25 21:10:44Z

0

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

answered May 25, 2018 at 21:10

Aleh Baslak

311 bronze badge

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related